डेटा इंटरव्यू: एक रिपोर्टिंग एक्सप्रेशन को PostgreSQL 18 वर्चुअल जनरेटेड कॉलम में माइग्रेट करना

प्रॉम्प्ट और संदर्भ

यह प्रश्न हर जनरेटेड-कॉलम विकल्प की तुलना नहीं करता है। यह एक दोहराए जाने वाले रो एक्सप्रेशन को PostgreSQL 18 वर्चुअल जनरेटेड कॉलम में माइग्रेट करने पर केंद्रित है। इसका लक्ष्य स्टोर्ड-कॉलम बैकफिल के बिना एक एकल गणना अनुबंध (calculation contract) बनाना है, साथ ही रीड CPU, एक्सप्रेशन प्रतिबंधों और प्रिविलेज परिवर्तनों को नियंत्रित करना है।

इंटरव्यूअर क्या मूल्यांकन करता है

यह जानना कि PostgreSQL 18 ने वर्चुअल जनरेटेड कॉलम पेश किए हैं और उन्हें डिफ़ॉल्ट बनाया है।
यह साबित करना कि एक्सप्रेशन केवल वर्तमान रो और इम्यूटिएबल (immutable) बिल्ट-इन फ़ंक्शंस और प्रकारों का उपयोग करता है।
केवल सफल DDL के बजाय डुअल रीड्स और प्रोडक्शन क्वेरी प्लान्स के साथ मान्य (validate) करना।
यह समझना कि वर्चुअल मान रीड के समय परिकलित किए जाते हैं, कोई रो स्टोरेज नहीं लेते हैं, और विभाजन कुंजी (partition key) नहीं हो सकते हैं।

उत्तर देने से पहले स्पष्टीकरण

पुष्टि करें कि एक्सप्रेशन केवल बिल्ट-इन्स का उपयोग करता है, इसे संदर्भित करने वाले रीड्स और फ़िल्टरों की पहचान करें, मौजूदा एक्सप्रेशन इंडेक्स की जांच करें, और पूछें कि क्या एप्लिकेशन अस्थायी रूप से पुराने एक्सप्रेशन की तुलना नए कॉलम से कर सकता है। रोल्स की भी समीक्षा करें, क्योंकि जनरेटेड और बेस कॉलम के अलग-अलग प्रिविलेज होते हैं। यदि जनरेटेड कॉलम का उद्देश्य बेस कॉलम को अलग (isolate) करना है, तो सत्यापित करें कि एक्सप्रेशन में प्रत्येक फ़ंक्शन, ऑपरेटर और कास्ट LEAKPROOF आवश्यकता को पूरा करता है।

30-सेकंड का उत्तर ढांचा

मैं पहले यह साबित करूंगा कि एक्सप्रेशन PostgreSQL 18 वर्चुअल-कॉलम प्रतिबंधों को पूरा करता है, फिर स्पष्ट रूप से VIRTUAL कॉलम जोड़ूंगा। माइग्रेशन के दौरान, एप्लिकेशन नल (nulls), यूनिकोड, असामान्य इनपुट और ऐतिहासिक पार्टिशन्स में पुराने एक्सप्रेशन और नए कॉलम का शैडो-रीड करता है। मैं प्रतिनिधि क्वेरीज़ के लिए CPU, टेल लेटेंसी और प्लान्स की तुलना करूंगा। सिमेंटिक, परफॉर्मेंस और प्रिविलेज गेट्स पास होने के बाद, पुराने एक्सप्रेशन पर त्वरित रोलबैक बनाए रखते हुए रीड्स नए कॉलम पर स्विच हो जाते हैं।

चरण-दर-चरण विस्तृत विवरण

sql

ALTER TABLE report_events
ADD COLUMN normalized_country text
GENERATED ALWAYS AS (lower(trim(country_code))) VIRTUAL;

एक वर्चुअल मान तब परिकलित किया जाता है जब इसे पढ़ा जाता है और यह कोई रो स्टोरेज नहीं लेता है। इसका एक्सप्रेशन केवल वर्तमान रो को संदर्भित कर सकता है, इसमें सबक्वेरीज़ या कोई अन्य जनरेटेड कॉलम नहीं हो सकता है, और इसे इम्यूटिएबल फ़ंक्शंस का उपयोग करना चाहिए। एक वर्चुअल कॉलम यूज़र-डिफ़ाइंड फ़ंक्शंस या प्रकारों पर भी निर्भर नहीं हो सकता है। स्पष्ट रूप से VIRTUAL लिखने से माइग्रेशन का उद्देश्य PostgreSQL 18 के डिफ़ॉल्ट पर निर्भर रहने से बच जाता है।

ऐतिहासिक डेटा को स्कैन करें और normalized_country IS NOT DISTINCT FROM lower(trim(country_code)) की तुलना करें, जिसमें NULL, व्हाइटस्पेस, केस और गैर-ASCII इनपुट शामिल हैं। फिर प्रतिनिधि EXPLAIN (ANALYZE, BUFFERS) जांच चलाएं। रीड-टाइम गणना CPU को बढ़ा सकती है; यदि फ़िल्टरों को एक इंडेक्स की आवश्यकता है, तो यह मानने के बजाय कि कोई स्टोरेज नहीं होने का मतलब कोई लागत नहीं है, सटीक समर्थित इंडेक्स पाथ और राइट लागत को सत्यापित करें।

चरणों में रिलीज़ करें: कॉलम जोड़ें; दोनों रूपों को शैडो-रीड करें जबकि पुराना एक्सप्रेशन आधिकारिक बना रहे; शून्य अंतर और स्वीकार्य परफॉर्मेंस के बाद ही स्विच करें। रोलबैक पुराने एक्सप्रेशन को पुनर्स्थापित करता है। अंत में, वास्तविक एप्लिकेशन रोल्स के साथ बेस और जनरेटेड-कॉलम प्रिविलेज का परीक्षण करें। यदि किसी फ़ंक्शन, ऑपरेटर या कास्ट को LEAKPROOF साबित नहीं किया जा सकता है, तो जनरेटेड-कॉलम प्रिविलेज बेस कॉलम के चारों ओर एक पूर्ण सुरक्षा सीमा नहीं बनाते हैं।

मजबूत नमूना उत्तर

मैं इसे क्वेरी-कॉन्ट्रैक्ट माइग्रेशन के रूप में मानूंगा। यह साबित करने के बाद कि एक्सप्रेशन केवल वर्तमान-रो इम्यूटिएबल बिल्ट-इन्स का उपयोग करता है, मैं एक स्पष्ट वर्चुअल कॉलम जोड़ूंगा। यह फिजिकल बैकफिल से बचाता है लेकिन काम को रीड्स पर स्थानांतरित करता है, इसलिए प्रोडक्शन-आकार के CPU और टेल-लेटेंसी परीक्षण अनिवार्य हैं।

एप्लिकेशन पहले पुराने और नए परिणामों की तुलना करता है और इनपुट वर्ग द्वारा विसंगतियों को समूहीकृत करता है। यह सिमेंटिक, प्लान और प्रिविलेज गेट्स पास होने के बाद ही स्विच करता है। यदि व्यवहार में गिरावट आती है, तो बेस डेटा बरकरार रहता है और क्वेरीज़ तुरंत पुराने एक्सप्रेशन पर वापस आ जाती हैं।

सामान्य गलतियाँ

VIRTUAL को छोड़ना और आशय समझाने के लिए वर्ज़न-विशिष्ट डिफ़ॉल्ट पर निर्भर रहना।
केवल DDL के दौरान वोलेटाइल फ़ंक्शंस, सबक्वेरीज़ या यूज़र-डिफ़ाइंड प्रकारों का पता चलना।
साधारण ASCII मानों का परीक्षण करना जबकि NULL और यूनिकोड व्यवहार को अनदेखा कर देना।
बिना रो स्टोरेज को बिना क्वेरी CPU के रूप में मानना।
कटओवर पर पुराने एक्सप्रेशन को हटा देना और त्वरित रोलबैक खो देना।

फॉलो-अप प्रश्न

यहाँ STORED का उपयोग क्यों नहीं किया गया?

कार्य एक सस्ते रीड-टाइम सामान्यीकरण को लक्षित करता है और फिजिकल बैकफिल से बचना चाहता है। यदि प्रोडक्शन स्कैन, सॉर्ट या फ़िल्टर रीड CPU को अस्वीकार्य बनाते हैं, तो स्टोर्ड कॉलम एक अलग स्टोरेज-और-रेप्लिकेशन निर्णय बन जाता है।

क्या वर्चुअल कॉलम एक पार्टीशन की (partition key) हो सकता है?

नहीं। PostgreSQL 18 जनरेटेड कॉलम को पार्टीशन की के रूप में अनुमति नहीं देता है। जब पार्टीशन रूटिंग को मान की आवश्यकता हो तो राइट पाथ द्वारा बनाए रखे जाने वाले नियमित कॉलम का उपयोग करें।

आप कैसे परीक्षण करते हैं कि प्रिविलेज का विस्तार नहीं हुआ है?

वास्तविक एप्लिकेशन रोल्स के साथ बेस और जनरेटेड कॉलम को क्वेरी करें, कॉलम ग्रांट्स और एक्सप्रेशन में फ़ंक्शंस के लिए निष्पादन प्रिविलेज की जांच करें। जब जनरेटेड कॉलम का उद्देश्य बेस कॉलम को छिपाना हो, तो यह भी जांचें कि क्या ऑपरेटरों और कास्ट्स के पीछे के फ़ंक्शंस सहित प्रत्येक फ़ंक्शन को LEAKPROOF चिह्नित किया गया है; PostgreSQL एप्लिकेशन के लिए इस शर्त को लागू नहीं करता है। यदि किसी एक्सप्रेशन पाथ को लीकप्रूफ साबित नहीं किया जा सकता है, तो जनरेटेड-कॉलम ग्रांट को पूर्ण अलगाव (isolation) न मानें।

डुअल रीड्स कब समाप्त हो सकते हैं?

एक पूर्ण व्यावसायिक चक्र, ऐतिहासिक पार्टिशन्स और पीक लोड शून्य सिमेंटिक अंतर और स्वीकार्य परफॉर्मेंस और प्रिविलेज परिणामों के साथ बीत जाने के बाद।