PostgreSQL प्रदर्शन और क्वेरी अनुकूलन · पाठ

Visibility Map और Index-Only Scans

Visibility map को अद्यतन रखें, ताकि planner heap fetches के बिना index-only scans दे सके।

पाठ 3, कुल 4 में से13 चरण

Visibility Map और Index-Only Scans, CoddyKit पर PostgreSQL प्रदर्शन और क्वेरी अनुकूलन का एक निःशुल्क पाठ है। यह 4 में से 3वाँ पाठ है। इस अध्ययन पथ के 3 तक कोई भी पाठ पूरा पढ़ना निःशुल्क है — इसके बाद CoddyKit PRO हर पाठ अनलॉक करता है, साथ ही अंतर्निर्मित कोड संपादक और चौबीसों घंटे एआई शिक्षक के साथ व्यावहारिक अभ्यास भी उपलब्ध कराता है। यह PostgreSQL प्रदर्शन और क्वेरी अनुकूलन सीखने के मार्ग का हिस्सा है और आपकी प्रगति वेब तथा CoddyKit ऐप पर सिंक होती रहती है। PostgreSQL प्रदर्शन और क्वेरी अनुकूलन पाठ्यक्रम में कुल 4 पाठ शामिल हैं।

इंडेक्स स्कैन अब भी हीप तक क्यों पहुँचते हैं

PostgreSQL में सामान्य इंडेक्स स्कैन इंडेक्स में मिलती-जुलती पंक्तियाँ ढूँढ़ लेता है, लेकिन केवल इंडेक्स के आधार पर यह भरोसे से नहीं जान सकता कि हर पंक्ति आपके लेन-देन को दृश्य है या नहीं। MVCC दृश्यता की जानकारी (xmin/xmax) केवल हीप ट्यूपल में रखता है, इंडेक्स प्रविष्टि में नहीं।

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

दृश्यता मानचित्र (VM) PostgreSQL को तब हीप फ़ेच छोड़ने देता है जब ऐसा करना निश्चित रूप से सुरक्षित हो, जिससे केवल-इंडेक्स स्कैन संभव होता है।

दृश्यता मानचित्र में क्या संग्रहित होता है

दृश्यता मानचित्र हर तालिका के साथ (एक _vm फ़ोर्क में) संग्रहित एक संक्षिप्त बिटमैप होता है। इसमें हर हीप पेज के लिए दो बिट होते हैं:

all-visible: पेज का हर ट्यूपल सभी वर्तमान और भविष्य के लेन-देन को दृश्य है।
all-frozen: पेज का हर ट्यूपल फ़्रीज़ किया गया है (रैपअराउंड-रोधी वैक्यूम के दौरान पेज छोड़ने के लिए उपयोगी)।

केवल-इंडेक्स स्कैन के लिए सिर्फ all-visible बिट महत्वपूर्ण है। यदि किसी हीप पेज का all-visible बिट सेट है, तो प्लानर जानता है कि उस पेज पर जिस भी ट्यूपल की ओर संकेत है वह दृश्य है, इसलिए वह केवल इंडेक्स प्रविष्टि से उत्तर दे सकता है।

All-Visible बिट कौन सेट करता है

all-visible बिट VACUUM (ऑटोवैक्यूम सहित) द्वारा सेट किया जाता है। जब वैक्यूम किसी हीप पेज को प्रोसेस करता है और पाता है कि सभी ट्यूपल हर किसी को दृश्य हैं तथा हटाने योग्य कोई मृत ट्यूपल नहीं है, तो वह उस पेज का all-visible बिट सेट कर देता है।

इंसर्शन, अपडेट और डिलीट प्रभावित पेज का बिट हटा देते हैं।
यह बिट केवल तब फिर से सेट होता है जब वैक्यूम उस पेज पर दोबारा आता है।

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

-- Force a vacuum so the VM bits get set for an existing table
VACUUM (VERBOSE) orders;

-- See how many heap pages are currently marked all-visible / all-frozen
SELECT relname,
       relpages,
       pg_relation_size(oid) AS heap_bytes
FROM pg_class
WHERE relname = 'orders';

pg_visibility से VM कवरेज की जाँच

pg_visibility एक्सटेंशन आपको ठीक-ठीक मापने देता है कि तालिका का कितना भाग all-visible चिह्नित है। केवल-इंडेक्स स्कैन की सेहत जाँचने के लिए यह सबसे उपयोगी निदान है।

pg_visibility_map_summary('tbl') all-visible और all-frozen पेजों की संख्या लौटाता है।
कवरेज अनुपात पाने के लिए इन संख्याओं की तुलना relpages से कीजिए।

जिस तालिका से केवल-इंडेक्स स्कैन की अपेक्षा है, उसमें कम कवरेज खतरे का संकेत है: VM बासी है और उसे वैक्यूम करने की आवश्यकता है।

CREATE EXTENSION IF NOT EXISTS pg_visibility;

SELECT c.relname,
       c.relpages,
       v.all_visible,
       v.all_frozen,
       round(100.0 * v.all_visible / NULLIF(c.relpages, 0), 1) AS pct_all_visible
FROM pg_class c
CROSS JOIN LATERAL pg_visibility_map_summary(c.oid) AS v
WHERE c.relname = 'orders';

केवल-इंडेक्स स्कैन की आवश्यकताएँ

प्लानर के केवल-इंडेक्स स्कैन चुनने के लिए तीन बातें एक साथ पूरी होनी चाहिए:

इंडेक्स में क्वेरी को आवश्यक हर कॉलम शामिल होना चाहिए (इंडेक्स कुंजी में या INCLUDE पेलोड के रूप में)।
क्वेरी को SELECT, WHERE, ORDER BY आदि में केवल उन्हीं शामिल कॉलमों का संदर्भ देना चाहिए।
तालिका के पर्याप्त पेज all-visible चिह्नित होने चाहिए, ताकि बचाए गए हीप फ़ेच इंडेक्स स्कैन की लागत से अधिक लाभ दें।

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

-- A covering index for: SELECT customer_id, status WHERE customer_id = ?
CREATE INDEX idx_orders_cust_status
    ON orders (customer_id) INCLUDE (status);

प्लान पढ़ना: हीप फ़ेच

VM के सही ढंग से काम करने का प्रमाण EXPLAIN (ANALYZE, BUFFERS) में मिलता है। केवल-इंडेक्स स्कैन नोड हीप फ़ेच काउंटर रिपोर्ट करता है।

हीप फ़ेच: 0 का अर्थ है कि हर मिलती हुई पंक्ति all-visible चिह्नित पेज से आई—यह आदर्श स्थिति है।
हीप फ़ेच की बड़ी संख्या का अर्थ है कि कई पेज all-visible नहीं थे, इसलिए स्कैन को अनियमित हीप पहुँच की लागत फिर भी चुकानी पड़ी।

भारी लेखन के बाद इस संख्या पर नज़र रखिए: अगले वैक्यूम द्वारा VM बिट फिर से सेट किए जाने तक यह तेज़ी से बढ़ेगी।

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT customer_id, status
FROM orders
WHERE customer_id = 42;

-- Look for:
--   Index Only Scan using idx_orders_cust_status on orders
--     Heap Fetches: 0

डेमो: बासी VM के कारण हीप फ़ेच

आप इस क्षरण को निश्चित रूप से दोहरा सकते हैं। पंक्तियाँ डालिए, केवल-इंडेक्स क्वेरी चलाइए और देखिए कि हीप फ़ेच कैसे बढ़ते हैं, क्योंकि नए डाले गए पेजों का all-visible बिट साफ़ हो जाता है।

इंसर्शन के तुरंत बाद नए पेज all-visible नहीं होते, इसलिए हीप फ़ेच > 0 होता है।
स्पष्ट रूप से VACUUM चलाने के बाद बिट फिर सेट हो जाते हैं और हीप फ़ेच घटकर 0 हो जाते हैं।

उत्पादन में लेखन-प्रधान तालिकाओं को प्रभावित करने वाला यह ठीक वही मौन प्रतिगमन है।

INSERT INTO orders (customer_id, status)
SELECT 42, 'NEW' FROM generate_series(1, 50000);

-- Heap Fetches will be high here (new pages not all-visible)
EXPLAIN (ANALYZE, COSTS OFF)
SELECT customer_id, status FROM orders WHERE customer_id = 42;

VACUUM orders;

-- Now Heap Fetches should be back near 0
EXPLAIN (ANALYZE, COSTS OFF)
SELECT customer_id, status FROM orders WHERE customer_id = 42;

VM को ताज़ा रखने के लिए ऑटोवैक्यूम ट्यून करना

स्थायी समाधान यह है कि व्यस्त तालिकाओं पर ऑटोवैक्यूम पर्याप्त बार चले। तालिका-स्तर के मुख्य विकल्प हैं:

autovacuum_vacuum_scale_factor — वैक्यूम शुरू होने से पहले तालिका के जितने अंश में बदलाव होना आवश्यक है। बड़ी और बहुत अधिक बदलने वाली तालिकाओं पर इसे कम कीजिए।
autovacuum_vacuum_threshold — बदली हुई पंक्तियों की निश्चित न्यूनतम संख्या।
autovacuum_vacuum_insert_scale_factor / _insert_threshold — PG13 में जोड़े गए ये विकल्प केवल-इंसर्शन वाली तालिकाओं पर वैक्यूम शुरू करते हैं, जिन पर पहले कभी वैक्यूम नहीं होता था और इसलिए उनका VM कभी सेट नहीं होता था।

ALTER TABLE ... SET के माध्यम से तालिका-स्तर पर किए गए ओवरराइड वैश्विक बदलावों की अपेक्षा बेहतर माने जाते हैं।

ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.02,
    autovacuum_vacuum_threshold = 1000,
    autovacuum_vacuum_insert_scale_factor = 0.02,
    autovacuum_vacuum_insert_threshold = 1000
);

केवल-इंसर्शन वाली तालिका का जाल

PostgreSQL 13 से पहले, केवल-अंत में जोड़ने वाली तालिकाएँ (लॉग, इवेंट, टाइम-सीरीज़) केवल-इंडेक्स स्कैन की विफलता का सामान्य कारण थीं: ऑटोवैक्यूम मृत ट्यूपल के आधार पर चलता है, और केवल इंसर्शन से कोई मृत ट्यूपल नहीं बनता, इसलिए वैक्यूम कभी नहीं चलता और VM खाली रहता है।

परिणाम: इन तालिकाओं पर केवल-इंडेक्स स्कैन को हमेशा पूरे हीप फ़ेच की लागत चुकानी पड़ती थी।
PG13 में इंसर्शन-आधारित ऑटोवैक्यूम ट्रिगर ने डिफ़ॉल्ट व्यवहार ठीक कर दिया।

पुराने संस्करणों में उपाय यह है कि हर बैच लोड के बाद all-visible बिट सेट करने के लिए निर्धारित समय पर VACUUM चलाया जाए (जैसे cron के माध्यम से)।

-- Pre-PG13 workaround: vacuum the append-only table after each batch load
-- (run on a schedule)
VACUUM (FREEZE) events;

-- FREEZE also sets all-frozen bits, helping anti-wraparound vacuum later

लंबे लेन-देन VM को बंधक बनाए रखते हैं

आक्रामक ऑटोवैक्यूम भी किसी पेज को all-visible चिह्नित नहीं कर सकता, यदि कोई पुराना लेन-देन अब भी उस पेज के ट्यूपल देखना चाहता हो (या उसने उन्हें बनाया हो)। लंबे समय तक चलने वाला लेन-देन या पुराना प्रतिकृति स्लॉट xmin सीमा को पीछे रोकता है।

वैक्यूम उस सीमा से आगे नहीं बढ़ सकता, इसलिए हाल में बदले गए पेजों के all-visible बिट सेट नहीं कर सकता।
लक्षण: आप कितनी भी बार वैक्यूम करें, VM कवरेज कम और हीप फ़ेच अधिक बने रहते हैं।

लेन-देन के दौरान निष्क्रिय सत्रों और पुराने प्रतिकृति स्लॉट का पता लगाइए—केवल-इंडेक्स स्कैन विफल होने के ये सामान्य छिपे हुए कारण हैं।

-- Find the oldest transaction holding back the xmin horizon
SELECT pid,
       state,
       now() - xact_start AS xact_age,
       backend_xmin,
       query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC
LIMIT 5;

पूरे चक्र की पुष्टि

जिन तालिकाओं से केवल-इंडेक्स स्कैन की अपेक्षा है, उनके लिए इन चरणों को दोहराए जा सकने वाली स्वास्थ्य-जाँच में जोड़िए:

पुष्टि कीजिए कि बार-बार चलने वाली क्वेरी के लिए कवर करने वाला इंडेक्स मौजूद है।
pg_visibility_map_summary से VM कवरेज मापिए।
EXPLAIN (ANALYZE, BUFFERS) चलाकर जाँचिए कि हीप फ़ेच कम हैं।
यदि कवरेज कम है: ऑटोवैक्यूम ट्यून कीजिए, लंबे लेन-देन समाप्त कीजिए या मैन्युअल वैक्यूम निर्धारित कीजिए।

लक्ष्य ऐसी स्थिर स्थिति है जिसमें हर लेखन-उछाल के बाद बढ़ने के बजाय वैक्यूम के बीच हीप फ़ेच लगभग शून्य रहें।

-- One-shot coverage + size snapshot for a candidate table
SELECT c.relname,
       c.relpages,
       v.all_visible,
       round(100.0 * v.all_visible / NULLIF(c.relpages, 0), 1) AS pct_all_visible,
       (SELECT count(*) FROM pg_index i WHERE i.indrelid = c.oid) AS n_indexes
FROM pg_class c
CROSS JOIN LATERAL pg_visibility_map_summary(c.oid) AS v
WHERE c.relname = 'orders';

त्वरित जाँच

लेखन-प्रधान तालिका पर बड़े पैमाने पर इंसर्शन के तुरंत बाद केवल-इंडेक्स स्कैन में Heap Fetches की संख्या अधिक है, जबकि पूरी तरह कवर करने वाला इंडेक्स मौजूद है। इसका सबसे सीधा कारण और समाधान क्या है?

इंटरैक्टिव पाठ में इसका उत्तर दें

पुनरावलोकन

केवल-इंडेक्स स्कैन केवल कवर करने वाले इंडेक्स पर नहीं, बल्कि दृश्यता मानचित्र पर भी निर्भर करते हैं।

VM का all-visible बिट निष्पादक को हीप फ़ेच छोड़ने देता है; इसे VACUUM सेट करता है और पेज पर कोई भी लेखन इसे साफ़ कर देता है।
pg_visibility_map_summary से ताज़गी मापिए और EXPLAIN (ANALYZE, BUFFERS) में हीप फ़ेच से पुष्टि कीजिए।
ऑटोवैक्यूम को ट्यून करके (केवल-अंत में जोड़ने वाली तालिकाओं के लिए इंसर्शन-आधारित ट्रिगर सहित) और लंबे समय तक चलने वाले लेन-देन तथा xmin सीमा को रोके रखने वाले पुराने प्रतिकृति स्लॉट हटाकर VM को ताज़ा रखिए।

कवरेज और ताज़गी का साथ होना ही हीप फ़ेच को शून्य बनाए रखता है।

शुरुआत निःशुल्क

एआई शिक्षक के साथ SQL सीखें — निःशुल्क

अपने ब्राउज़र में वास्तविक कोड लिखें और चलाएँ, चौबीसों घंटे एआई शिक्षक से तुरंत सहायता पाएँ, और वेब या ऐप पर वहीं से शुरू करें जहाँ आपने छोड़ा था।

पाठ्यक्रम: 22
पाठ: 88

निःशुल्क सीखना शुरू करें पूरा पाठ्यक्रम देखें

अक्सर पूछे जाने वाले प्रश्न

क्या “Visibility Map और Index-Only Scans” पाठ निःशुल्क है?

हाँ — PostgreSQL प्रदर्शन और क्वेरी अनुकूलन अध्ययन पथ के 3 तक कोई भी पाठ, जिसमें “Visibility Map और Index-Only Scans” भी शामिल है, यहाँ वेब पर पूरा पढ़ना निःशुल्क है। इसके बाद CoddyKit PRO हर पाठ अनलॉक करता है, साथ ही अंतर्निर्मित कोड संपादक और चौबीसों घंटे एआई शिक्षक के साथ इंटरैक्टिव अभ्यास भी उपलब्ध कराता है। PostgreSQL प्रदर्शन और क्वेरी अनुकूलन पाठ्यक्रम में कुल 4 पाठ शामिल हैं।

“Visibility Map और Index-Only Scans” में मैं क्या सीखूँगा?

Visibility map को अद्यतन रखें, ताकि planner heap fetches के बिना index-only scans दे सके। आप ब्राउज़र में सीधे चलाए जाने वाले व्यावहारिक कोड के साथ PostgreSQL प्रदर्शन और क्वेरी अनुकूलन का अभ्यास करते हैं, और पाठ पूरा करते समय 24/7 एआई ट्यूटर आपके प्रश्नों के उत्तर देता है।

क्या PostgreSQL प्रदर्शन और क्वेरी अनुकूलन शुरू करने के लिए मुझे किसी अनुभव की आवश्यकता है?

पहले के अनुभव की आवश्यकता नहीं है। CoddyKit पर PostgreSQL प्रदर्शन और क्वेरी अनुकूलन शुरुआती से लेकर उन्नत शिक्षार्थियों तक सभी के लिए व्यवस्थित किया गया है, इसलिए आप यहीं से या शुरुआत से सीखना शुरू कर सकते हैं और अपनी गति से आगे बढ़ सकते हैं। यह 4 में से 3वाँ पाठ है।

“Visibility Map और Index-Only Scans” पाठ पूरा करने में कितना समय लगता है?

CoddyKit का अधिकांश पाठ लगभग 5–10 मिनट में पूरा हो जाता है। हर पाठ छोटा और संवादात्मक है, इसलिए आप लगातार प्रगति करते हैं और वेब या ऐप पर वहीं से सीखना जारी रख सकते हैं जहाँ आपने छोड़ा था।

क्या मैं इस PostgreSQL प्रदर्शन और क्वेरी अनुकूलन पाठ में कोड लिख और चला सकता हूँ?

हाँ। हर PostgreSQL प्रदर्शन और क्वेरी अनुकूलन पाठ में एक अंतर्निर्मित कोड संपादक शामिल है, जिससे आप सीधे अपने ब्राउज़र में वास्तविक कोड लिख और चला सकते हैं और तुरंत एआई प्रतिक्रिया पा सकते हैं—स्थानीय सेटअप की आवश्यकता नहीं है।

इस पाठ्यक्रम के सभी पाठ

← PostgreSQL प्रदर्शन और क्वेरी अनुकूलन पर वापस जाएँ