Postgres 19 का इंतज़ार: आखिरकार अब 'समय' को संभालने की बारी

• डेटा किसी खास तारीख (पिछले मंगलवार) के समय कैसा दिखता था, ऐसे सवालों का जवाब देने के लिए Postgres 19 native temporal table सपोर्ट ला रहा है, जिससे अलग audit trigger सिस्टम के बिना बदलाव से पहले और बाद का डेटा ट्रैक किया जा सकेगा
• SQL:2011 standard ने एक दशक से भी पहले Temporal tables को परिभाषित किया था, लेकिन Postgres अब जाकर इसे core में ला रहा है, यानी दूसरे DB engines की तुलना में यह देर से जुड़ रहा है
• पहले valid_from/valid_to दो कॉलम और btree_gist extension पर आधारित exclusion constraint का इस्तेमाल होता था, लेकिन अब एक ही range type कॉलम और WITHOUT OVERLAPS constraint के साथ इसे ज्यादा सहज तरीके से व्यक्त किया जा सकेगा
• FOR PORTION OF syntax के जरिए UPDATE·DELETE के समय rows का automatic split होगा, और time axis पर gap या overlap रोकने का काम engine खुद करेगा
• यह जोड़ bi-temporal systems के सिर्फ valid time (application time) वाले आधे हिस्से को कवर करता है; system time अभी समर्थित नहीं है, लेकिन आगे की releases के लिए नींव तैयार हो गई है

---

पुराना तरीका - The Old-Fashioned Way

• किसी product की price history को समय के हिसाब से ट्रैक करने की शुरुआती कोशिश valid_from, valid_to दो date columns और valid_from < valid_to CHECK constraint से बनती थी
  • लेकिन यह एक ही product के लिए date range overlap करने वाली दो rows को insert होने से नहीं रोक पाता था (जैसे product 42 का उसी मंगलवार को $9.99 और $14.99 दोनों होना)
• पारंपरिक समाधान btree_gist extension और exclusion constraint का उपयोग था
  • EXCLUDE USING gist (product_id WITH =, daterange(valid_from, valid_to) WITH &&) जैसे रूप में, overlap करने वाली row insert करने पर error मिलता था
• इस तरीके की समस्याएँ
  • GiST Postgres का अपना index type है, जिसे समझने के लिए अनुभव चाहिए, और optional extension होने से entry barrier भी बनता है
  • exclusion constraint का syntax सहज नहीं है, इसलिए इसे standard approach के रूप में सोचना आसान नहीं
  • table में temporal awareness built-in नहीं होती, इसलिए time range बदलने पर rows को manually split या merge करना पड़ता है, और temporal consistency का बोझ application पर आ जाता है

समय का संक्षिप्त इतिहास - A Brief History of Time

• SQL:2011 standard ने valid time period (APPLICATION TIME), WITHOUT OVERLAPS constraint, और temporal data manipulation के लिए FOR PORTION OF syntax पेश किया
• Henrietta Dombrovskaya (Hetti) ने Chad Slaughter के साथ मिलकर pg_bitemporal extension विकसित किया, जो PL/pgSQL में Postgres के भीतर bi-temporal tables को manage करने का framework है
  • 2015 से कई conferences में इस concept को पेश किया गया, और valid time (वह समय जब वास्तविक दुनिया में कोई तथ्य सही है) तथा transaction time (वह समय जब DB ने उस तथ्य को रिकॉर्ड किया) को साथ ट्रैक करने का तरीका दिखाया गया
• दो time dimensions का फर्क
  • valid time का मतलब है: "यह price जनवरी से जून तक valid थी"
  • transaction time का मतलब है: "यह row 12 मार्च को 3:47 PM पर insert हुई, और 3 अप्रैल को 9:01 AM पर replace हुई" — यानी DB के नज़रिये से
  • दोनों को जोड़ने पर ऐसा bi-temporal table बनता है जो यह जवाब दे सकता है: "पिछले मंगलवार की price के बारे में उस समय हम क्या मानते थे?"
• pg_bitemporal EXCLUDE USING gist का दो बार उपयोग करता है, एक effective range (valid time) के लिए और एक asserted range (transaction time) के लिए
  • यह bi-temporal insert, update, correction, inactivation, deletion functions और temporal inference के लिए Allen's interval relationships भी देता है
• extension की सीमाएँ
  • query planner को temporal conditions समझाने, engine-level constraint system के साथ integrate करने, या native manipulation syntax देने की क्षमता नहीं होती → इसलिए core support ज़रूरी था
  • Postgres 19 bi-temporal system के valid time वाले आधे हिस्से को अपनाता है; पूरा नहीं, पर यह बड़ी प्रगति है

range से समाधान - Ranges to the Rescue

• Postgres 19 में अलग valid_from/valid_to के बजाय एक ही range type कॉलम valid_at DATERANGE इस्तेमाल होता है
  • PRIMARY KEY (product_id, valid_at WITHOUT OVERLAPS) के साथ अब btree_gist extension और exclusion constraint की ज़रूरत नहीं रहती
  • WITHOUT OVERLAPS यह सुनिश्चित करता है कि product_id किसी भी समय बिंदु पर unique रहे, लेकिन range overlap न होने पर उसी product की कई rows हो सकती हैं
• अंदरूनी तौर पर अभी भी GiST index इस्तेमाल होता है, और key के non-temporal columns के लिए btree_gist चाहिए, लेकिन constraint initialize करते समय Postgres इसकी dependency खुद संभाल लेता है
• range notation में [ का मतलब include और ) का मतलब exclude है → [2025-01-01, 2025-07-01) में 1 जनवरी शामिल है, 1 जुलाई नहीं
  • आख़िरी Gadget row [2026-01-01,) open-ended range है, यानी current price के लिए कोई defined end date नहीं
• overlap protection कैसे काम करता है
  • गलत range [2025-03-01, 2025-01-01) insert करने पर "range lower bound must be less than or equal to range upper bound" जैसी error मिलती है
  • overlap करने वाली range [2025-03-01, 2025-09-01) insert करने पर products_pkey exclusion constraint violation error आती है
  • सिर्फ range का उपयोग करके दो तरह की validation एक साथ मिल जाती है

काटना और बाँटना - Slicing and Dicing

• अगर किसी product की price सिर्फ मार्च 2025 से सितंबर 2025 तक $10.99 करनी हो, तो पुराने तरीके में rows को manually split और insert करना पड़ता था, और गलती होने पर gap या overlap बन सकता था
• temporal history table में इसे सीधे इरादे के अनुसार लिखा जा सकता है
  • UPDATE products FOR PORTION OF valid_at FROM '2025-03-01' TO '2025-09-01' SET price = 10.99 WHERE product_id = 1
• नतीजे में Widget की rows 3 से बढ़कर 5 हो जाती हैं
  • पुरानी $9.99 row [2025-01-01, 2025-03-01) तक सिमट जाती है
  • बची हुई range के लिए नई $10.99 row जुड़ती है
  • पुरानी $12.99 row [2025-09-01, 2026-01-01) तक सिमट जाती है
  • और बची हुई [2025-07-01, 2025-09-01) range के लिए नई $10.99 row जुड़ती है
• $10.99 दो rows में बँटने का कारण यह है कि FOR PORTION OF match होने वाली हर row को अलग-अलग process करता है, और बाद में adjacent ranges को merge (coalesce) नहीं करता
  • अंतिम परिणाम में न gap होता है, न overlap; यह सिर्फ exclusion logic से मिलने वाले परिणाम से बेहतर है
• edge cases
  • अगर FOR PORTION OF range किसी एक existing row के भीतर पूरी तरह आती है, तो अधिकतम 2 residual rows (पहले और बाद के हिस्से) बन सकती हैं
  • अगर यह existing boundary से ठीक-ठीक मेल खाए, तो residual rows की ज़रूरत नहीं
• नई बनने वाली temporal residual rows के लिए INSERT permission की ज़रूरत नहीं होती, लेकिन existing INSERT triggers फिर भी fire होते हैं → इसलिए audit logging या SECURITY DEFINER trigger functions में सावधानी चाहिए

इतिहास मिटाना - Erasing History

• FOR PORTION OF DELETE में भी काम करता है; उदाहरण के लिए, किसी खास product को जून 2025 से अक्टूबर 2025 तक catalog से अस्थायी रूप से हटाना
  • DELETE FROM products FOR PORTION OF valid_at FROM '2025-06-01' TO '2025-10-01' WHERE product_id = 2
• परिणाम
  • जून से अक्टूबर का हिस्सा कट जाता है, और [2025-04-01, 2026-01-01) को कवर करने वाली $22.99 row दो residual rows में बँट जाती है — एक जून पर खत्म होती है और दूसरी अक्टूबर से शुरू होती है
  • gap के पहले और बाद की price information अपनी मूल value के साथ बनी रहती है, यानी DELETE से rows की संख्या बढ़ भी सकती है
• temporal history table का management mechanism यह सब automatically संभाल लेता है, जिससे application level पर over-delete या orphaned fragment का खतरा खत्म होता है

विज्ञापन की सच्चाई - Truth in Advertising

• temporal history tables temporal foreign key के बिना पूरी नहीं मानी जा सकतीं, और Postgres 19 इसे PERIOD keyword के साथ support करता है
  • FOREIGN KEY (product_id, PERIOD valid_at) REFERENCES products (product_id, PERIOD valid_at) जैसे रूप में
• PERIOD keyword यह बताता है कि foreign key खुद temporal है
  • referenced product को variant के valid_at range की पूरी अवधि के दौरान मौजूद होना चाहिए
  • reference table की सभी matching rows का combination referenced row की पूरी अवधि को cover करना चाहिए
• product के time range से आगे जाने वाली variant बनाने की कोशिश ([2025-01-01, 2027-01-01)) reject कर दी जाती है
  • अगर Widget की price सिर्फ 2026 के मध्य तक defined है, तो 2027 तक valid होने का दावा करने वाला variant foreign key constraint violation से reject होगा
• एक अहम सीमा
  • temporal foreign key referential action के रूप में सिर्फ NO ACTION support करती है; CASCADE, SET NULL, SET DEFAULT समर्थित नहीं हैं
  • अगर कोई product row, जिस पर variant निर्भर है, delete की जाती है, तो हमेशा error आएगी; temporal cascade operations की जटिलता के कारण इसे application को explicitly handle करना होगा

छोटे कदम - Baby Steps

• अभी जो capabilities मिली हैं: overlap protection के साथ valid-time-based temporal history tables, temporal data manipulation, और temporal foreign keys
• सबसे बड़ी कमी अब भी transaction time, यानी system time है
  • valid time उस समय को ट्रैक करता है जब तथ्य वास्तविक दुनिया में सही था, जबकि system time उस समय को ट्रैक करता है जब DB ने उसे जाना; बहुत से systems दोनों का इस्तेमाल करते हैं
  • यही वह क्षेत्र है जिसे 2015 से pg_bitemporal extension भरता आ रहा है
  • triggers के जरिए system time का emulation संभव है, लेकिन यह उन नए temporal features जैसा नहीं है जिन्हें engine transparently manage करता है
• temporal history tables के docs भी साफ़ कहते हैं कि system time का native support नहीं है, हालांकि उसका emulation किया जा सकता है; Postgres 20 या उसके बाद यह आएगा या नहीं, तय नहीं, लेकिन आधार बन चुका है

अंतिम विचार - Final Thoughts

• EXCLUDE USING gist वाला तरीका काम करता है, लेकिन यह अपेक्षाकृत भद्दा workaround है; pg_bitemporal जैसे extensions ने concept को साबित किया और चर्चा को आगे बढ़ाया
• GiST exclusion constraints की तुलना में यह तरीका कहीं ज्यादा सहज है
  • primary key में WITHOUT OVERLAPS साधारण अंग्रेज़ी जैसा पढ़ा जाता है, और FOR PORTION OF अपने व्यवहार को सीधे व्यक्त करता है
  • temporal update और delete के समय automatic row splitting संभावित bugs की एक पूरी श्रेणी को खत्म कर देता है
• SQL:2011 से Postgres 19 तक की यात्रा लंबी रही है; Hetti और community ने वर्षों तक इस pattern की ज़रूरत और व्यवहारिकता साबित की, और अब यह core में आ गया है
• आगे की releases में system time support पर नज़र रखना ज़रूरी होगा; जब Postgres bi-temporal के दोनों आधे हिस्से पा लेगा, तो इसकी संभावनाएँ बहुत बढ़ जाएँगी