PostgreSQL इंटरव्यू: MVCC और VACUUM एक साथ कैसे काम करते हैं?

प्रॉम्प्ट और प्रासंगिक संदर्भ

एक PostgreSQL 18 orders टेबल में 500 मिलियन लाइव रो हैं और निरंतर अपडेट हो रहे हैं। टीम चार तथ्यों को नोटिस करती है:

• n_dead_tup लगातार बढ़ता जा रहा है;
• autovacuum वर्कर्स समय-समय पर दिखाई देते हैं;
• सामान्य वैक्यूमिंग के बाद भी pg_relation_size('orders') कम नहीं होता है;
• एक ऐप्लिकेशन सेशन छह घंटे से idle in transaction है और एक पुराना backend_xmin एक्सपोज़ कर रहा है।

MVCC विजिबिलिटी से लेकर फिजिकल क्लीनअप तक की पूरी शृंखला को समझाएं। घटना का निदान करें, एक सुरक्षित रिकवरी क्रम चुनें, और इसे हल घोषित करने से पहले आवश्यक साक्ष्य बताएं। ये संख्याएँ केवल अभ्यास के इनपुट हैं, सार्वभौमिक ऑपरेटिंग थ्रेसहोल्ड नहीं।

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

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

पहला परीक्षण यह है कि क्या उम्मीदवार यह समझता है कि UPDATE वर्जन क्रिएशन है। PostgreSQL टपल मेटाडेटा जैसे कि इन्सर्ट करने वाला ट्रांजैक्शन ID (xmin) और डिलीट या सुपरसीड करने वाला ट्रांजैक्शन ID (xmax) स्टोर करता है। एक स्नैपशॉट यह तय करने के लिए ट्रांजैक्शन बाउंड्रीज़ और कमिट स्थिति को जोड़ता है कि कौन सा वर्जन दिखाई देगा। यह नियम “सबसे बड़े xmin वाली रो चुनें” से कहीं अधिक सटीक है।

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

तीसरा परीक्षण ऑपरेशनल सटीकता है। सादा VACUUM आमतौर पर रिलेशन के अंदर डेड स्पेस को पुन: उपयोग योग्य (reusable) बनाता है; यह आमतौर पर रिलेशन फ़ाइल को छोटा नहीं करता है। VACUUM FULL रिलेशन को दोबारा लिखता है (rewrite करता है), अतिरिक्त अस्थायी डिस्क स्पेस की आवश्यकता होती है, और एक ACCESS EXCLUSIVE लॉक लेता है। यह एक असाधारण मेंटेनेंस ऑपरेशन है, न कि बढ़ते डेड-टपल अनुमान के प्रति पहली प्रतिक्रिया।

अंत में, उम्मीदवार को चार सिग्नलों को अलग-अलग समझना चाहिए: अनुमानित टपल संख्या, रिक्लेम करने योग्य स्पेस, रिलेशन साइज़, और यूजर-विजिबल परफॉर्मेंस। ये संबंधित हैं लेकिन एक दूसरे के पूरक (interchangeable) नहीं हैं। घटता हुआ n_dead_tup यह साबित नहीं करता कि ऑपरेटिंग-सिस्टम फ़ाइल सिकुड़ गई है, और अपरिवर्तित फ़ाइल साइज़ यह साबित नहीं करता कि वैक्यूम विफल रहा।

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

• कौन से आइसोलेशन लेवल्स उपयोग में हैं? Read Committed के तहत, प्रत्येक स्टेटमेंट को सामान्यतः एक नया स्नैपशॉट मिलता है; Repeatable Read और Serializable एक ट्रांजैक्शन-लेवल स्नैपशॉट रखते हैं। एक आइडल ट्रांजैक्शन बिना कोई काम किए भी अपने स्नैपशॉट होराइजन को बनाए रख सकता है।
• क्या पुराना backend_xmin ही ग्लोबल ब्लॉकर है? इसे मान लेने के बजाय सहसंबंध (correlation) स्थापित करें। प्रिपेयर्ड ट्रांजैक्शन, लॉजिकल या फिजिकल रेप्लिकेशन स्लॉट, और अन्य सेशन भी एक पुराने होराइजन को बनाए रख सकते हैं।
• क्या स्टैटिस्टिक्स घटना को गाइड करने के लिए पर्याप्त रूप से ताज़ा हैं? n_dead_tup और n_live_tup अनुमान हैं। उन्हें अंतिम-वैक्यूम समय, प्रगति, लॉग, रिलेशन साइज़ और वर्कलोड बिहेवियर के साथ पढ़ें।
• क्या डिस्क रीयूज़ या तत्काल फ़ाइल का सिकुड़ना लक्ष्य है? रूटीन वैक्यूमिंग का लक्ष्य स्थिर-अवस्था में पुन: उपयोग (steady-state reuse) होता है। ऑपरेटिंग सिस्टम को बड़ी मात्रा में स्पेस वापस करने के लिए रीराइट या एक उपयुक्त ऑनलाइन-रीबिल्ड योजना की आवश्यकता होती है।
• क्या ऐप्लिकेशन छह घंटे के ट्रांजैक्शन को सुरक्षित रूप से समाप्त (terminate) कर सकता है? पहले ओनर और बिजनेस ऑपरेशन की पहचान करें। इसे कैंसिल या टर्मिनेट करने से इसका खुला काम रोलबैक हो जाता है और यह यूजर फ्लो को प्रभावित कर सकता है।
• वर्कलोड में क्या बदलाव आया? अपडेट दर, इंडेक्स किए गए कॉलम, रो की चौड़ाई, autovacuum सेटिंग्स, वर्कर सैचुरेशन और ट्रांजैक्शन लाइफटाइम सभी वर्जन चर्न और क्लीनअप क्षमता को प्रभावित करते हैं।
• कितने लॉक और I/O प्रभाव की अनुमति है? एक रिकवरी योजना को केवल मेंटेनेंस जल्दी खत्म करने के बजाय लेटेंसी, रेप्लिकेशन, डिस्क हेडरूम और उपलब्धता को भी बनाए रखना चाहिए।

30-सेकंड का उत्तर ढाँचा (Framework)

“PostgreSQL MVCC प्रत्येक स्टेटमेंट को एक सुसंगत स्नैपशॉट पढ़ने देता है जबकि अपडेट नए टपल वर्जन बनाते हैं। पुराना वर्जन तब तक रहता है जब तक कोई भी सक्रिय स्नैपशॉट इसे देख सकता है। यहाँ, छह घंटे का खुला ट्रांजैक्शन backend_xmin को रोक सकता है, इसलिए autovacuum टेबल को स्कैन तो कर सकता है लेकिन उन वर्जन्स को नहीं हटा सकता जो संभावित रूप से दृश्यमान (visible) बने रहते हैं।

मैं सबसे पहले सेशंस, प्रिपेयर्ड ट्रांजैक्शन्स और रेप्लिकेशन स्लॉट्स में सबसे पुराने होराइजन्स की पुष्टि करूँगा; उन्हें टेबल स्टैटिस्टिक्स, वैक्यूम प्रोग्रेस, लॉग्स और साइज़ के साथ कोरिलेट करूँगा; फिर ओनिंग ऐप्लिकेशन के माध्यम से सत्यापित ब्लॉकर को समाप्त करूँगा। मैं मापी गई I/O सीमाओं के तहत सामान्य वैक्यूम चलाऊँगा या उसे पूरा होने दूँगा और सत्यापित करूँगा कि डेड-टपल अनुमान और रीयूज़ बिहेवियर स्थिर हो गए हैं। सादा VACUUM स्पेस को पुन: उपयोग योग्य बनाता है और आमतौर पर फ़ाइल को छोटा नहीं करता है। VACUUM FULL टेबल को रीराइट और एक्सक्लूसिवली लॉक करता है, इसलिए इसके लिए एक अलग मेंटेनेंस निर्णय की आवश्यकता होती है। अंत में, मैं ट्रांजैक्शन लाइफटाइम को सीमित करूँगा, प्रति रिलेशन हॉट टेबल्स को ट्यून करूँगा, XID एज की निगरानी करूँगा, और फ्रीजिंग बनाए रखूँगा ताकि पुराने XID कभी भी रैपअराउंड होराइजन को पार न कर सकें।”

चरण-दर-चरण गहन विश्लेषण (Deep Dive)

चरण 1: MVCC के माध्यम से एक अपडेट को ट्रेस करें

मान लीजिए ट्रांजैक्शन 100 एक ऑर्डर वर्जन इन्सर्ट करता है। इसका टपल हेडर xmin में एक इन्सर्शन XID रिकॉर्ड करता है। बाद में, ट्रांजैक्शन 220 ऑर्डर को अपडेट करता है। PostgreSQL एक सक्सेसर (उत्तराधिकारी) टपल बनाता है और xmax सहित ट्रांजैक्शन मेटाडेटा का उपयोग करके पुराने वर्जन को सुपरसीडेड के रूप में चिह्नित करता है; यह पुराने बाइट्स को उसी जगह ओवरराइट नहीं करता जैसा कि लॉजिकल मॉडल सुझाव दे सकता है।

एक रीडर यह तय करने के लिए अपने स्नैपशॉट और ट्रांजैक्शन कमिट स्थिति की जांच करता है कि कौन सा वर्जन दृश्यमान है। सरल शब्दों में, यह उन ट्रांजैक्शन्स द्वारा इन्सर्ट किए गए वर्जन्स को अस्वीकार करता है जो अनकमिटेड थे या स्नैपशॉट के भविष्य में थे, और यह उस वर्जन को बनाए रख सकता है जिसका डिलीट करने वाला ट्रांजैक्शन अभी तक दृश्यमान नहीं था। वास्तविक विजिबिलिटी नियम वर्तमान ट्रांजैक्शन, अबॉर्टेड ट्रांजैक्शन, कमांड ID और हिंट बिट्स को भी संभालते हैं, इसलिए केवल संख्यात्मक xmin और xmax मानों की तुलना करना एक सही कार्यान्वयन नहीं है।

यह मॉडल रीड/राइट लॉक विरोध को कम करता है: सामान्य रीडर्स राइटर्स को ब्लॉक नहीं करते हैं, और राइटर्स सामान्य रीडर्स को ब्लॉक नहीं करते हैं। इसका मतलब यह नहीं है कि राइटर्स कभी एक-दूसरे को ब्लॉक नहीं करते हैं। एक ही लॉजिकल रो को अपडेट करने वाले दो ट्रांजैक्शन अभी भी प्रतीक्षा कर सकते हैं या टकरा सकते हैं, और उच्च आइसोलेशन लेवल्स अपनी गारंटी बनाए रखने के लिए ट्रांजैक्शन्स को अबॉर्ट कर सकते हैं।

चरण 2: क्लीनअप होराइजन प्राप्त करें

ट्रांजैक्शन 220 के कमिट होने के बाद, पुराना टपल नए स्नैपशॉट्स के लिए अप्रचलित (obsolete) हो जाता है। यदि पहले शुरू हुआ कोई स्नैपशॉट अभी भी इसे देख सकता है तो यह तुरंत हटाने योग्य नहीं होता है। वैक्यूम सबसे पुराने प्रासंगिक होराइजन के आधार पर एक कटऑफ चुनता है। उस सुरक्षा सीमा से नए वर्जन “हाल ही में मृत” (recently dead) हो सकते हैं: वर्तमान कार्य के लिए तार्किक रूप से अप्रचलित लेकिन अभी तक हटाने के लिए सुरक्षित नहीं।

छह घंटे का idle in transaction सेशन खतरनाक है क्योंकि क्लाइंट ने एक खुला ट्रांजैक्शन छोड़ दिया है। इसका backend_xmin एक पुराने स्नैपशॉट को बनाए रख सकता है, भले ही सर्वर अगले क्लाइंट कमांड की प्रतीक्षा कर रहा हो। उसी जांच में निम्नलिखित शामिल होना चाहिए:

• pg_prepared_xacts, क्योंकि एक प्रिपेयर्ड ट्रांजैक्शन एक पुराना XID बनाए रख सकता है;
• pg_replication_slots, क्योंकि xmin या catalog_xmin आवश्यक रो या कैटलॉग बनाए रख सकते हैं;
• पुराने backend_xid या backend_xmin वाली अन्य pg_stat_activity रो;
• रेप्लिका फीडबैक और लॉजिकल डिकोडिंग कॉन्फ़िगरेशन, क्योंकि रेप्लिकेशन आवश्यकताएं क्लीनअप को प्रभावित कर सकती हैं।

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

चरण 3: अनुमान, प्रगति और साइज़ के साथ अलग-अलग निदान करें

एक्टिविटी और रिलेशन स्टैटिस्टिक्स के केवल-पढ़ने योग्य (read-only) स्नैपशॉट से शुरुआत करें:

SELECT pid,
       usename,
       application_name,
       state,
       xact_start,
       age(backend_xid) AS xid_age,
       age(backend_xmin) AS xmin_age,
       wait_event_type,
       wait_event,
       left(query, 120) AS query_sample
FROM pg_stat_activity
WHERE backend_xid IS NOT NULL OR backend_xmin IS NOT NULL
ORDER BY GREATEST(
  COALESCE(age(backend_xid), 0),
  COALESCE(age(backend_xmin), 0)
) DESC;

SELECT relid::regclass AS relation,
       n_live_tup,
       n_dead_tup,
       n_tup_upd,
       n_tup_hot_upd,
       last_vacuum,
       last_autovacuum,
       vacuum_count,
       autovacuum_count
FROM pg_stat_user_tables
WHERE relid = 'orders'::regclass;

SELECT pg_size_pretty(pg_relation_size('orders')) AS heap_size,
       pg_size_pretty(pg_indexes_size('orders')) AS index_size,
       pg_size_pretty(pg_total_relation_size('orders')) AS total_size;

n_dead_tup एक अनुमान है, सटीक ब्लोट माप नहीं। last_autovacuum यह साबित करता है कि एक वर्कर ने काम पूरा किया, न कि उसने हर अप्रचलित वर्जन को हटा दिया। एक बड़ा रिलेशन स्वस्थ हो सकता है यदि मुक्त किए गए पेजों को उसी दर पर पुन: उपयोग किया जाता है जिस दर पर नए वर्जन आते हैं। इसके विपरीत, स्थिर रिलेशन साइज़ बढ़ती लेटेंसी या इंडेक्स चर्न को छिपा सकता है।

जब वैक्यूम सक्रिय हो, तो इसके फेज और स्कैन किए गए हीप ब्लॉक्स के लिए pg_stat_progress_vacuum का निरीक्षण करें। यह जानने के लिए कि कितने टपल्स हटाए गए, कितने गैर-हटाने योग्य रहे, और क्या फ्रीजिंग आगे बढ़ी, autovacuum लॉग्स या VACUUM (VERBOSE) आउटपुट का उपयोग करें। उसी टाइमलाइन पर pg_stat_all_tables, रिलेशन साइज़ हिस्ट्री, क्वेरी लेटेंसी, बफर और I/O प्रेशर, WAL रेट, रेप्लिकेशन लैग और डिस्क हेडरूम की जांच करें।

चरण 4: सबसे सुरक्षित क्रम में रिकवर करें

सबसे पहले सबसे पुराने ट्रांजैक्शन के ओनर और उद्देश्य की पहचान करें। यदि यह परित्यक्त (abandoned) है, तो इसे ऐप्लिकेशन या कनेक्शन ओनर के माध्यम से बंद करें। यदि यह सक्रिय व्यावसायिक कार्य है, तो तय करें कि रद्दीकरण से पहले रोलबैक स्वीकार्य है या नहीं। PostgreSQL pg_cancel_backend और pg_terminate_backend एक्सपोज़ करता है, लेकिन किसी फ़ंक्शन तक पहुंच प्रोडक्शन को बाधित करने का अधिकार नहीं है।

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

होराइजन आगे बढ़ने के बाद, autovacuum को कैच-अप करने दें या एक मापी गई विंडो के दौरान एक लक्षित सादा VACUUM (VERBOSE, ANALYZE) orders चलाएं। लेटेंसी, I/O, WAL, रेप्लिकेशन लैग, वैक्यूम प्रोग्रेस और शेष डिस्क हेडरूम पर नज़र रखें। केवल इसलिए कई प्रतिस्पर्धी मेंटेनेंस कार्य शुरू न करें क्योंकि पहले वाले में समय लग रहा है।

फिर परिणामों को सत्यापित करें:

• सबसे पुराना प्रासंगिक backend_xmin या स्लॉट होराइजन आगे बढ़ा;
• वैक्यूम रिपोर्ट करता है कि पहले बनाए रखे गए डेड वर्जन हटाने योग्य हैं और हटा दिए गए हैं;
• स्टैटिस्टिक्स रिफ्रेश के बाद n_dead_tup नीचे की ओर प्रवृत्त होता है;
• नए अपडेट उपलब्ध स्पेस का पुन: उपयोग करते हैं और रिलेशन वृद्धि एक अपेक्षित स्थिर अवस्था में वापस आ जाती है;
• रिक्वेस्ट लेटेंसी, इंडेक्स स्कैन लागत, WAL और रेप्लिका लैग अपनी सहमत सीमाओं के भीतर रहते हैं;
• age(relfrozenxid) और डेटाबेस XID एज में सुरक्षित हेडरूम है।

इस साक्ष्य के बाद ही टीम को फिजिकल कॉम्पैक्शन का मूल्यांकन करना चाहिए। VACUUM FULL orders एक नई कॉम्पैक्ट कॉपी बनाता है, रीराइट के दौरान अतिरिक्त डिस्क की आवश्यकता होती है, और एक ACCESS EXCLUSIVE लॉक रखता है। 500 मिलियन-रो वाली टेबल के लिए इसके बजाय एक ऑनलाइन रीबिल्ड रणनीति या नियोजित विभाजन प्रतिस्थापन (partition replacement) की आवश्यकता हो सकती है। सही विकल्प डाउनटाइम, फ्री डिस्क, रेप्लिकेशन, विदेशी कुंजियों (foreign keys), राइट कन्वर्जेंस और रोलबैक पर निर्भर करता है—न कि केवल एक साइज़ मेट्रिक को छोटा करने की इच्छा पर।

चरण 5: बिना किसी जादू के Autovacuum को समझें

Autovacuum संचयी आँकड़ों (cumulative statistics) पर प्रतिक्रिया करता है। अपडेट और डिलीट के लिए, PostgreSQL 18 निम्न रूप के ट्रिगर का उपयोग करता है:

vacuum threshold = min(
  autovacuum_vacuum_max_threshold,
  autovacuum_vacuum_threshold
    + autovacuum_vacuum_scale_factor * pg_class.reltuples
)

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

एक बहुत बड़े, अत्यधिक अपडेट वाले रिलेशन पर, एक ग्लोबल स्केल फ़ैक्टर बहुत सारे परिवर्तनों की प्रतीक्षा कर सकता है या बर्स्टी कार्य उत्पन्न कर सकता है। मापे गए वर्जन उत्पादन और वैक्यूम क्षमता से टेबल स्टोरेज पैरामीटर्स को ट्यून करें। वर्कर की उपलब्धता और लागत विलंब (cost delay) का भी निरीक्षण करें: एक सही ट्रिगर यह गारंटी नहीं देता कि एक वर्कर तुरंत शुरू हो जाएगा या वर्कलोड द्वारा कचरा उत्पन्न करने की तुलना में तेजी से समाप्त हो जाएगा।

रोकथाम राइट पाथ में भी शामिल है। ट्रांजैक्शन्स को छोटा रखें और उनके अंदर नेटवर्क कॉल या यूजर थिंक टाइम से बचें। उपयुक्त ऐप्लिकेशन रोल्स पर चुनिंदा रूप से idle_in_transaction_session_timeout लागू करें, क्योंकि कनेक्शन पूल्स और लंबे वैध कार्यों को अनुकूल हैंडलिंग की आवश्यकता होती है। केवल आवश्यक कॉलम अपडेट करें। जब कोई इंडेक्स किया गया कॉलम नहीं बदलता है और नया टपल उसी हीप पेज पर फिट बैठता है, तो एक HOT अपडेट नई इंडेक्स प्रविष्टियों से बच सकता है; fillfactor अधिक पेज स्पेस को शुरू में अप्रयुक्त छोड़ने की कीमत पर उस अवसर को बेहतर बना सकता है।

चरण 6: फ्रीजिंग को XID रैपअराउंड से जोड़ें

सामान्य ट्रांजैक्शन ID 32-बिट होते हैं और एक सर्कुलर स्पेस में तुलना किए जाते हैं। एक सामान्य XID में लगभग दो अरब ID पुराने और दो अरब नए माने जाते हैं। यदि कोई टपल अनिश्चित काल तक एक सामान्य इन्सर्शन XID रखता है, तो अंततः एक बहुत पुराना मान भविष्य में प्रतीत हो सकता है।

वैक्यूम पर्याप्त रूप से पुराने कमिटेड टपल वर्जन्स को फ्रीज़ करके इसे रोकता है। आधुनिक PostgreSQL फोरेंसिक दृश्यता के लिए मूल xmin को संरक्षित करते हुए टपल स्थिति के साथ फ्रीजिंग का प्रतिनिधित्व करता है; फ़्रोज़न वर्जन को प्रत्येक सामान्य ट्रांजैक्शन से पुराना माना जाता है। टेबल और डेटाबेस फ़्रोज़न-XID मार्कर रिकॉर्ड करते हैं कि यह कार्य कितनी दूर तक आगे बढ़ा है।

यह एक शुद्धता की आवश्यकता (correctness requirement) है, न कि वैकल्पिक ब्लोट हाउसकीपिंग। age(pg_class.relfrozenxid) और age(pg_database.datfrozenxid) की निगरानी करें, एंटी-रैपअराउंड वैक्यूम्स की जांच करें, और उनके पूरा होने के लिए पर्याप्त क्षमता बनाए रखें। फ्रीज सीमाओं को बढ़ाने से केवल काम टलता है और सुरक्षा विंडो कम होती है; यह सर्कुलर XID बाधा को नहीं हटाता है।

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

“मैं इस घटना को वर्जन प्रोडक्शन बनाम सुरक्षित रिक्लेमेशन के रूप में मॉडल करूँगा। PostgreSQL MVCC प्रत्येक स्टेटमेंट या ट्रांजैक्शन को एक स्नैपशॉट देता है। एक अपडेट एक सक्सेसर टपल बनाता है और ट्रांजैक्शन मेटाडेटा के माध्यम से पुराने वर्जन को चिह्नित करता है। एक दृश्यमान वर्जन का चयन करने के लिए एक रीडर xmin, xmax, कमिट स्थिति और अपने स्नैपशॉट का मूल्यांकन करता है। यह सामान्य रीड्स और राइट्स को बिना किसी परस्पर विरोधी रीड लॉक के आगे बढ़ने देता है, जबकि एक ही रो वाले राइटर्स अभी भी ब्लॉक या अबॉर्ट हो सकते हैं।

पुराने वर्जन को तब तक नहीं हटाया जा सकता जब तक कि कोई प्रासंगिक स्नैपशॉट इसे देखना बंद न कर दे। मैं पुराने backend_xid और backend_xmin के लिए सभी सेशंस का निरीक्षण करूँगा, फिर प्रिपेयर्ड ट्रांजैक्शन्स और रेप्लिकेशन स्लॉट्स की जांच करूँगा। छह घंटे का आइडल ट्रांजैक्शन एक मजबूत संदिग्ध है क्योंकि एक खुला ट्रांजैक्शन अपने स्नैपशॉट होराइजन को बनाए रख सकता है, लेकिन मैं इसे समाप्त करने से पहले यह साबित करूँगा कि यह सबसे पुराना ब्लॉकर है।

मैं उस होराइजन को pg_stat_user_tables, pg_stat_progress_vacuum, autovacuum लॉग्स, हीप और इंडेक्स साइज़, रिलेशन ग्रोथ, लेटेंसी, I/O, WAL और रेप्लिकेशन लैग के साथ कोरिलेट करूँगा। n_dead_tup एक अनुमान है, और एक autovacuum टाइमस्टैम्प केवल यह साबित करता है कि एक रन हुआ था। यदि ओनर पुष्टि करता है कि ट्रांजैक्शन छोड़ दिया गया है, तो मैं इसे बंद कर दूंगा, किसी भी पुराने होराइजन को हल करूँगा, और एक लक्षित सादे वैक्यूम को मापे गए लोड के तहत कैच-अप करने दूंगा।

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

रोकथाम के लिए, मैं ट्रांजैक्शन लाइफटाइम को सीमित करूँगा, रोल-उपयुक्त आइडल-ट्रांजैक्शन टाइमआउट सेट करूँगा, सबसे पुराने होराइजन्स और XID एज की निगरानी करूँगा, मापे गए चर्न से प्रति हॉट टेबल autovacuum को ट्यून करूँगा, और जहाँ स्कीमा और वर्कलोड अनुमति देते हैं वहाँ HOT अपडेट्स को प्रोत्साहित करूँगा। वैक्यूम पर्याप्त रूप से पुराने कमिटेड वर्जन्स को फ्रीज भी करता है ताकि उनके XIDs को हमेशा अतीत के रूप में माना जाए, जिससे रैपअराउंड को रोका जा सके। सफलता का अर्थ है कि ब्लॉकिंग होराइजन आगे बढ़ता है, हटाने योग्य टपल्स साफ हो जाते हैं, स्पेस रीयूज़ विकास को स्थिर करता है, सर्विस SLOs स्वस्थ रहते हैं, और फ़्रोज़न-XID एज सुरक्षित हेडरूम बनाए रखती है।”

सामान्य गलतियाँ और सुधार

• यह कहना कि UPDATE एक रो को उसी स्थान पर संशोधित करता है → PostgreSQL आमतौर पर एक नया हीप टपल वर्जन बनाता है → MVCC मेटाडेटा के माध्यम से पूर्ववर्ती (predecessor) और उत्तराधिकारी (successor) को ट्रेस करें।
• विजिबिलिटी को xmin < current_xid तक सीमित करना → कमिट स्थिति, स्नैपशॉट बाउंड्रीज़, सक्रिय ट्रांजैक्शन्स, xmax, और कमांड नियम मायने रखते हैं → बिना कोई संख्यात्मक शॉर्टकट बनाए स्नैपशॉट निर्णय का वर्णन करें।
• यह दावा करना कि रीडर्स और राइटर्स कभी ब्लॉक नहीं करते → MVCC सामान्य रीड/राइट लॉक विरोध को हटाता है, जबकि एक ही रो वाले राइटर्स और स्पष्ट लॉक अभी भी टकराते हैं → संकीर्ण गारंटी बताएं।
• यह मान लेना कि एक पूर्ण autovacuum ने हर डेड टपल को हटा दिया → पुराने होराइजन्स वर्जन्स को गैर-हटाने योग्य छोड़ सकते हैं → बनाए रखे गए टपल्स, ब्लॉकर होराइजन्स, लॉग्स और प्रगति का निरीक्षण करें।
• n_dead_tup को सटीक ब्लोट बाइट्स मानना → यह एक अनुमानित रो गणना है → हीप, इंडेक्स, ग्रोथ, रीयूज़ और परफॉर्मेंस को अलग से मापें।
• अपरिवर्तित फ़ाइल साइज़ को वैक्यूम विफलता कहना → सादा वैक्यूम आमतौर पर खाली किए गए स्पेस को पुन: उपयोग के लिए रिलेशन के अंदर रखता है → कॉम्पैक्शन की मांग करने से पहले स्थिर-अवस्था रीयूज़ का आकलन करें।
• तुरंत VACUUM FULL चलाना → रीराइट के लिए अतिरिक्त डिस्क और एक एक्सक्लूसिव लॉक की आवश्यकता होती है → कॉम्पैक्शन योजना बनाने से पहले ब्लॉकर्स को हटाएं और सामान्य वैक्यूम के साथ कैच-अप करें।
• केवल ग्लोबल स्केल फ़ैक्टर को ट्यून करना → हॉट टेबल्स और वर्कर क्षमता भिन्न होती है → वर्जन दर, पूरा होने का समय और SLO साक्ष्य द्वारा समर्थित प्रति-टेबल सेटिंग्स का उपयोग करें।
• ओनरशिप जांच के बिना सबसे पुराने PID को समाप्त करना → इसका ट्रांजैक्शन रोलबैक हो जाता है और क्लाइंट फ्लो विफल हो सकता है → पहले उद्देश्य, प्रभाव और रिकवरी पाथ की पुष्टि करें।
• फ्रीज को स्टोरेज ऑप्टिमाइज़ेशन के रूप में देखना → फ्रीजिंग सर्कुलर XID तुलना में शुद्धता की रक्षा करती है → सुरक्षा नियंत्रण के रूप में फ़्रोज़न-XID एज और एंटी-रैपअराउंड कार्य की निगरानी करें।

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

फॉलो-अप 1: सफल VACUUM के बाद भी टेबल समान साइज़ की क्यों रह सकती है?

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

फॉलो-अप 2: Autovacuum चला लेकिन कई डेड वर्जन्स क्यों छोड़ दिए?

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

फॉलो-अप 3: VACUUM और ANALYZE में क्या अंतर है?

वैक्यूम पुन: उपयोग योग्य स्पेस को पुनः प्राप्त करता है, इंडेक्स और विजिबिलिटी मैप को बनाए रखता है, और पुराने ट्रांजैक्शन मेटाडेटा को फ्रीज करता है। Analyze प्लानर स्टैटिस्टिक्स को अपडेट करने के लिए डेटा का नमूना लेता है। VACUUM (ANALYZE) दोनों कार्य करता है, लेकिन एक दूसरे का स्थान नहीं लेता: सटीक स्टैटिस्टिक्स डेड टपल्स को नहीं हटाते हैं, और रिक्लेम किया गया स्पेस एक सटीक डेटा वितरण मॉडल की गारंटी नहीं देता है।

फॉलो-अप 4: HOT अपडेट्स वैक्यूम प्रेशर को कैसे कम करते हैं?

जब कोई अपडेट किसी इंडेक्स किए गए कॉलम को नहीं बदलता है और सक्सेसर उसी हीप पेज पर फिट बैठता है, तो PostgreSQL नई इंडेक्स प्रविष्टियों को जोड़ने से बच सकता है। HOT चेन में मध्यवर्ती वर्जन्स को सामान्य पेज एक्सेस के दौरान भी हटाया (pruned किया) जा सकता है। HOT MVCC या वैक्यूम आवश्यकताओं को समाप्त नहीं करता है, लेकिन यह इंडेक्स चर्न और क्लीनअप कार्य को कम करता है। कुल अपडेट्स के मुकाबले n_tup_hot_upd की निगरानी करें और स्पेस तथा कैश लागतों के विरुद्ध किसी भी fillfactor परिवर्तन का परीक्षण करें।

फॉलो-अप 5: क्या आप Autovacuum को अक्षम करके उसके स्थान पर एक नाइटली जॉब चला सकते हैं?

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

फॉलो-अप 6: आइडल ट्रांजैक्शन्स में कौन सा गार्डरेल मदद करता है?

idle_in_transaction_session_timeout उस सेशन को समाप्त कर सकता है जो एक खुले ट्रांजैक्शन के अंदर बहुत लंबे समय तक प्रतीक्षा करता है। इसे संगत भूमिकाओं (compatible roles) पर लागू करें और पूल बिहेवियर, पुनः प्रयासों और वैध कार्यों का परीक्षण करें। ऐप्लिकेशन बाउंड्री को भी ठीक करें: डेटाबेस कार्य से ठीक पहले ट्रांजैक्शन शुरू करें, तुरंत कमिट या रोलबैक करें, और इसे खुला रखते हुए कभी भी यूजर इनपुट या रिमोट सर्विस की प्रतीक्षा न करें।