बैकएंड इंटरव्यू: PostgreSQL 18 में वर्चुअल बनाम स्टोर्ड जनरेटेड कॉलम कैसे चुनें?

प्रॉम्प्ट और दायरा

एक ऑर्डर सर्विस में unit_price, quantity और discount हैं, और इसे केवल उसी पंक्ति से प्राप्त net_amount की आवश्यकता है। इंटरव्यूअर पूछता है कि इसे राइट पर प्रोसेस किया जाए या प्रत्येक रीड पर, जबकि डेटाबेस को इंडेक्स, लॉजिकल रेप्लिकेशन, रोलबैक और एक पुराने सब्सक्राइबर का समर्थन करना चाहिए। मुख्य कौशल सिंटैक्स याद रखना नहीं, बल्कि पर्सिस्टेंस सीमा तय करना है।

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

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

पहले स्पष्ट करने योग्य प्रश्न

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

30-सेकंड उत्तर रूपरेखा

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

चरण-दर-चरण तर्क

PostgreSQL 18 में VIRTUAL डिफ़ॉल्ट जनरेटेड-कॉलम प्रकार है: यह पंक्ति में कोई स्टोरेज नहीं लेता और पढ़े जाने पर प्रोसेस होता है। STORED इंसर्ट या अपडेट पर प्रोसेस होता है और स्टोरेज लेता है। किसी भी प्रकार को INSERT या UPDATE में सीधे असाइन नहीं किया जा सकता है; एक्सप्रेशन केवल वर्तमान पंक्ति और इम्यूटेबल फ़ंक्शंस को ही संदर्भित कर सकता है।

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

SQL और रेप्लिकेशन उदाहरण

यह उदाहरण स्पष्ट रूप से राशि को स्टोर करता है; STORED को VIRTUAL में बदलने पर रीड करने पर एक्सप्रेशन का फिर से मूल्यांकन होता है:

sql

CREATE TABLE order_line (
  id bigint PRIMARY KEY,
  unit_price numeric(12, 2) NOT NULL,
  quantity integer NOT NULL CHECK (quantity > 0),
  discount numeric(5, 4) NOT NULL CHECK (discount BETWEEN 0 AND 1),
  net_amount numeric(12, 2)
    GENERATED ALWAYS AS (unit_price * quantity * (1 - discount)) STORED
);

CREATE INDEX order_line_net_amount_idx ON order_line (net_amount);

CREATE PUBLICATION order_pub
  FOR TABLE order_line
  WITH (publish_generated_columns = 'stored');

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

माइग्रेशन, इंडेक्स और विफलता पाथ

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

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

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

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

आगे के प्रश्न

आप VIRTUAL को कब प्राथमिकता देंगे?

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

STORED की आवश्यकता कब होती है?

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

आप मिश्रित-संस्करण रेप्लिकेशन टोपोलॉजी को कैसे अपग्रेड करते हैं?

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

बैकएंड इंटरव्यू: PostgreSQL 18 में वर्चुअल बनाम स्टोर्ड जनरेटेड कॉलम कैसे चुनें?

प्रश्न