Database Query Optimization: Indexes, Execution Plans & Partitioning

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

मुख्य बातें

• व्याख्या विश्लेषण आपका सबसे शक्तिशाली निदान उपकरण है - किसी भी चीज़ को अनुकूलित करने से पहले निष्पादन योजनाओं को पढ़ना सीखें
• रणनीतिक रूप से सूचकांक प्रकार चुनें: समानता और सीमा के लिए बी-ट्री, पूर्ण-पाठ और JSONB के लिए GIN, फ़िल्टर किए गए सबसेट के लिए आंशिक सूचकांक
• N+1 क्वेरीज़ ORM-आधारित अनुप्रयोगों में सबसे आम प्रदर्शन हत्यारा हैं - क्वेरी लॉगिंग के साथ उनका शीघ्र पता लगाएं
• जब तालिकाओं की संख्या 10-50 मिलियन से अधिक हो जाती है, तो तालिका विभाजन आवश्यक हो जाता है, जिससे क्वेरी योजना का समय कम हो जाता है और कुशल डेटा जीवनचक्र प्रबंधन सक्षम हो जाता है।

---

व्याख्या विश्लेषण के साथ निष्पादन योजनाओं को पढ़ना

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

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

• सेक स्कैन -- डेटाबेस तालिका में प्रत्येक पंक्ति को पढ़ता है। छोटी तालिकाओं (10,000 पंक्तियों से कम) के लिए स्वीकार्य लेकिन बड़ी तालिकाओं के लिए लाल झंडा।
• इंडेक्स स्कैन -- डेटाबेस मिलान पंक्तियों को कुशलतापूर्वक खोजने के लिए एक इंडेक्स का उपयोग करता है। बड़ी तालिकाओं पर फ़िल्टर की गई क्वेरी के लिए आप यही चाहते हैं।
• केवल इंडेक्स स्कैन -- डेटाबेस तालिका को छुए बिना पूरी तरह से इंडेक्स से क्वेरी का उत्तर देता है। सबसे तेज़ स्कैन प्रकार.
• नेस्टेड लूप -- बाहरी तालिका में प्रति पंक्ति एक बार आंतरिक तालिका को स्कैन करके तालिकाओं से जुड़ता है। कुशल जब आंतरिक स्कैन एक इंडेक्स का उपयोग करता है।
• हैश जॉइन -- जॉइन के एक तरफ से हैश टेबल बनाता है, फिर दूसरी तरफ से इसकी जांच करता है। बड़े परिणाम सेटों के लिए कुशल।
• सॉर्ट करें - एक स्पष्ट सॉर्ट चरण, अक्सर ऑर्डर बाय के लिए। डिस्क पर फैलने वाले सॉर्ट पर नज़र रखें ("सॉर्ट विधि: बाहरी मर्ज" द्वारा दर्शाया गया है)।

क्या देखना है

निष्पादन योजना में सबसे महत्वपूर्ण संकेत अनुमानित और वास्तविक पंक्तियों के बीच का अंतर है। जब PostgreSQL 10 पंक्तियों का अनुमान लगाता है लेकिन 100,000 पाता है, तो उसने गलत योजना चुनी है। ऐसा तब होता है जब तालिका के आँकड़े पुराने हो जाते हैं - उन्हें अद्यतन करने के लिए तालिका पर ANALYZE चलाएँ।

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

---

सूचकांक प्रकार और उनका उपयोग कब करें

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

बी-ट्री इंडेक्स

बी-ट्री डिफ़ॉल्ट और सबसे बहुमुखी सूचकांक प्रकार है। यह समानता (=), रेंज (<, >, बीच), सॉर्टिंग (ऑर्डर बाय), और प्रीफ़िक्स पैटर्न मिलान (जैसे 'एबीसी%') का समर्थन करता है। WHERE, JOIN, और ORDER BY क्लॉज में अधिकांश कॉलम के लिए, B-ट्री इंडेक्स सही विकल्प है।

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

जीआईएन सूचकांक

GIN अनुक्रमित समग्र मूल्यों के भीतर खोज करने में उत्कृष्टता प्राप्त करते हैं। वे पूर्ण-पाठ खोज (tsvector कॉलम), JSONB रोकथाम क्वेरी (@>, ?), और सरणी ओवरलैप क्वेरी (&&, @>) के लिए आवश्यक हैं। जीआईएन इंडेक्स बी-ट्री इंडेक्स की तुलना में बड़े और धीमे होते हैं, इसलिए उनका उपयोग केवल वहीं करें जहां बी-ट्री क्वेरी पैटर्न की सेवा नहीं दे सकता है।

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

आंशिक सूचकांक

आंशिक अनुक्रमणिका केवल WHERE स्थिति से मेल खाने वाली अनुक्रमणिका पंक्तियाँ। वे उन तालिकाओं के लिए शक्तिशाली हैं जहां क्वेरीज़ डेटा के एक छोटे उपसमूह के लिए लगातार फ़िल्टर करती हैं।

उदाहरण के लिए, यदि आपकी ऑर्डर तालिका में 10 मिलियन पंक्तियाँ हैं, लेकिन आप लगभग विशेष रूप से सक्रिय ऑर्डर (तालिका का 5%) क्वेरी करते हैं, तो (customer_id, create_at) पर एक आंशिक सूचकांक जहां स्थिति = 'सक्रिय' पूर्ण सूचकांक से 20 गुना छोटा है और आपके वास्तविक प्रश्नों के लिए उतना ही तेज़ है।

---

एन+1 क्वेरीज़ का पता लगाना और उन्हें ठीक करना

ORM का उपयोग करने वाले अनुप्रयोगों में N+1 क्वेरी समस्या सबसे आम प्रदर्शन समस्या है। ऐसा तब होता है जब कोड एन रिकॉर्ड की एक सूची लोड करता है, फिर संबंधित डेटा लोड करने के लिए प्रति रिकॉर्ड एक अतिरिक्त क्वेरी निष्पादित करता है, जिसके परिणामस्वरूप 1-2 के बजाय एन + 1 कुल क्वेरी होती है।

N+1 क्वेरीज़ कैसे होती हैं

उनके ग्राहक नामों के साथ ऑर्डर की एक सूची लोड करने पर विचार करें। एक सहज कार्यान्वयन ऑर्डर सूची (1 क्वेरी) को लोड करता है, फिर प्रत्येक ऑर्डर के लिए ग्राहक (एन क्वेरी) को लोड करता है। 100 ऑर्डर के साथ, यह 101 डेटाबेस राउंड ट्रिप उत्पन्न करता है। प्रति क्वेरी 1 एमएस पर, यह 101 एमएस है - लेकिन कनेक्शन पूल विवाद के साथ समवर्ती लोड के तहत, यह आसानी से 500 एमएस या अधिक हो सकता है।

पता लगाने के तरीके

1. क्वेरी लॉगिंग - PostgreSQL क्वेरी लॉगिंग को अस्थायी रूप से सक्षम करें और विभिन्न पैरामीटर मानों के साथ बार-बार समान क्वेरी देखें
2. ओआरएम-स्तरीय लॉगिंग - ड्रिज़ल ओआरएम, प्रिज्मा और टाइपओआरएम सभी क्वेरी लॉगिंग का समर्थन करते हैं जो निष्पादित प्रत्येक एसक्यूएल स्टेटमेंट को दिखाता है
3. एपीएम टूल्स - डेटाडॉग, न्यू रेलिक और सेंट्री एंडपॉइंट द्वारा प्रश्नों को समूहित कर सकते हैं और एन+1 पैटर्न को स्वचालित रूप से हाइलाइट कर सकते हैं
4. pg_stat_statements -- यह PostgreSQL एक्सटेंशन क्वेरी निष्पादन आंकड़ों को ट्रैक करता है और बार-बार निष्पादित समान क्वेरी टेम्पलेट्स का खुलासा करता है

एन+1 क्वेरीज़ को ठीक करना

समाधान आपके ORM और क्वेरी पैटर्न पर निर्भर करता है:

• उत्सुक लोडिंग -- ORM को JOINs का उपयोग करके प्रारंभिक क्वेरी में संबंधित डेटा लोड करने के लिए कहें। ड्रिज़ल में, क्वेरी बिल्डर्स में with विकल्प का उपयोग करें।
• बैच लोडिंग - सभी विदेशी कुंजी आईडी एकत्र करें, फिर संबंधित रिकॉर्ड को एक WHERE id IN (...) क्वेरी में लोड करें। यह डेटालोडर पैटर्न है.
• असामान्यीकरण -- रीड-हैवी उपयोग के मामलों के लिए, संबंधित डेटा को सीधे मूल रिकॉर्ड पर संग्रहीत करें। पढ़ने के प्रदर्शन के लिए व्यापार लेखन जटिलता।

---

क्वेरी पुनर्लेखन तकनीकें

कभी-कभी क्वेरी को केवल बेहतर अनुक्रमणिका की ही नहीं, बल्कि पुनर्गठन की भी आवश्यकता होती है।

रूपांतरण में शामिल होने के लिए सबक्वेरी

सहसंबद्ध उपश्रेणी बाहरी क्वेरी में प्रति पंक्ति एक बार निष्पादित होती है। उन्हें JOINs में परिवर्तित करने से PostgreSQL को अधिक कुशल जॉइन रणनीतियों का उपयोग करने की अनुमति मिलती है।

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

सामान्य तालिका अभिव्यक्तियाँ (सीटीई)

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

विंडो फ़ंक्शंस बनाम ग्रुप बाय

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

---

टेबल विभाजन रणनीतियाँ

जब तालिकाएँ 10-50 मिलियन पंक्तियों से आगे बढ़ती हैं, तो सूचकांक गहराई, वैक्यूम ओवरहेड और योजनाकार जटिलता के कारण अच्छी तरह से अनुक्रमित क्वेरी भी धीमी हो जाती हैं। एकल तार्किक तालिका इंटरफ़ेस को बनाए रखते हुए विभाजन एक बड़ी तालिका को छोटे भौतिक खंडों में विभाजित करता है।

विभाजन के प्रकार

तिथि के अनुसार रेंज विभाजन

सबसे आम पैटर्न टाइमस्टैम्प कॉलम द्वारा मासिक विभाजन है। प्रत्येक माह का डेटा अपने स्वयं के विभाजन में रहता है। तिथि के अनुसार फ़िल्टर करने वाली क्वेरीज़ स्वचालित रूप से केवल प्रासंगिक विभाजन (विभाजन छंटाई) को स्कैन करती हैं।

समय-आधारित विभाजन के लाभ:

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

विभाजन संबंधी विचार

विभाजन से जटिलता बढ़ती है. विभाजन को कार्यान्वित करने के लिए प्रत्येक क्वेरी को अपने WHERE क्लॉज में विभाजन कुंजी को शामिल करना होगा। अद्वितीय बाधाओं में विभाजन कुंजी शामिल होनी चाहिए। विभाजित तालिकाओं को संदर्भित करने वाली विदेशी कुंजियों की सीमाएँ हैं। विभाजन तभी शुरू करें जब आपने यह माप लिया हो कि तालिका का आकार प्रदर्शन में गिरावट का कारण बन रहा है।

---

PostgreSQL कॉन्फ़िगरेशन ट्यूनिंग

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

इन सेटिंग्स को आपके विशिष्ट हार्डवेयर और कार्यभार के लिए ट्यून किया जाना चाहिए। यह सत्यापित करने के लिए कि परिवर्तनों से प्रदर्शन में सुधार होता है, pg_stat_bgwriter, pg_stat_activity, और pg_stat_user_tables की निगरानी करें।

---

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

एक टेबल में कितने इंडेक्स होने चाहिए?

कोई निश्चित सीमा नहीं है, लेकिन प्रत्येक सूचकांक INSERT, UPDATE और DELETE संचालन को धीमा कर देता है क्योंकि सूचकांक को बनाए रखा जाना चाहिए। अंगूठे का एक अच्छा नियम उन कॉलमों के लिए इंडेक्स बनाना है जो आपके सबसे लगातार प्रश्नों के WHERE, JOIN ON, और ORDER BY क्लॉज में दिखाई देते हैं। अप्रयुक्त इंडेक्स को खोजने के लिए pg_stat_user_indexes का उपयोग करें जिन्हें हटाया जा सकता है।

क्या मुझे प्रदर्शन के लिए यूयूआईडी या पूर्णांक प्राथमिक कुंजी का उपयोग करना चाहिए?

पूर्णांक प्राथमिक कुंजियाँ (BIGSERIAL) जुड़ने और अनुक्रमण के लिए तेज़ होती हैं क्योंकि वे छोटी होती हैं (8 बाइट्स बनाम 16 बाइट्स) और स्वाभाविक रूप से क्रमबद्ध होती हैं। यूयूआईडी समन्वय के बिना वैश्विक विशिष्टता प्रदान करते हैं, जो वितरित प्रणालियों के लिए मायने रखता है। अधिकांश अनुप्रयोगों के लिए, बाहरी-सामना करने वाले पहचानकर्ताओं के लिए यूयूआईडी और आंतरिक जुड़ाव के लिए पूर्णांक का उपयोग करें।

प्रतिकृतियां पढ़ने के लिए मुझे एकल डेटाबेस से कब स्विच करना चाहिए?

जब आपका पढ़ा हुआ कार्यभार आपके डेटाबेस की क्षमता का 70-80% से अधिक हो जाता है, या जब रिपोर्टिंग क्वेरी संसाधनों के लिए लेनदेन संबंधी प्रश्नों के साथ प्रतिस्पर्धा करती है। पढ़ें प्रतिकृतियां रीड लोड को संभालती हैं जबकि प्राथमिक लेखन पर ध्यान केंद्रित करता है। एक विशिष्ट वेब एप्लिकेशन के लिए आमतौर पर 5,000-10,000 समवर्ती उपयोगकर्ताओं की आवश्यकता होती है।

मैं बिना डाउनटाइम के उत्पादन में धीमी क्वेरी को कैसे संभाल सकता हूं?

तालिका को लॉक करने से बचने के लिए समवर्ती विकल्प के साथ अनुक्रमणिका बनाएं। सबसे धीमी क्वेरी की पहचान करने के लिए pg_stat_statements का उपयोग करें। फ़ीचर फ़्लैग के पीछे क्वेरी अनुकूलन परिनियोजित करें। तालिकाओं को फिर से लिखने वाले स्कीमा परिवर्तनों के लिए, तालिकाओं को लॉक किए बिना पुनर्व्यवस्थित करने के लिए pg_repack जैसे टूल का उपयोग करें।

---

आगे क्या है

डेटाबेस अनुकूलन प्लेटफ़ॉर्म प्रदर्शन का आधार है। pg_stat_statements को सक्षम करके प्रारंभ करें, अपनी सबसे धीमी क्वेरी की पहचान करें, और EXPLAIN ANALYZE के साथ व्यवस्थित रूप से उन पर काम करें। गुम अनुक्रमणिकाएँ जोड़ें, N+1 पैटर्न ठीक करें, और अपनी सबसे बड़ी तालिकाओं के लिए विभाजन पर विचार करें।

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

ECOSIRE Odoo ERP और कस्टम अनुप्रयोगों सहित PostgreSQL-समर्थित प्लेटफार्मों के लिए विशेषज्ञ डेटाबेस अनुकूलन प्रदान करता है। डेटाबेस प्रदर्शन ऑडिट के लिए हमसे संपर्क करें।

---

ECOSIRE द्वारा प्रकाशित - Odoo ERP, Shopify eCommerce, और OpenClaw AI में AI-संचालित समाधानों के साथ व्यवसायों को बढ़ाने में मदद करना।