PostgreSQL के लिए गैर-पारंपरिक ऑप्टिमाइज़ेशन तकनीकें

 (hakibenita.com)
6 पॉइंट द्वारा GN⁺ 2026-01-21 | अभी कोई टिप्पणी नहीं है. | WhatsApp पर शेयर करें
  • PostgreSQL query performance बढ़ाने के लिए पारंपरिक ढांचे से हटकर ऑप्टिमाइज़ेशन तरीके पेश किए गए हैं, जो index जोड़ने या query rewrite करने के बजाय 3 रचनात्मक approaches पर केंद्रित हैं
  • CHECK constraint के आधार पर full table scan हटाना, function-based index से low cardinality optimization, Hash index के जरिए unique constraint लागू करना
  • 1. constraint_exclusion सेटिंग का उपयोग करके गलत condition वाली queries में अनावश्यक scan को रोका जा सकता है
  • 2. function-based index और virtual generated column का उपयोग करके index size घटाया जा सकता है और query consistency बनाए रखी जा सकती है
  • 3. Hash index और exclusion constraint को मिलाकर बड़े text columns पर unique constraint को कुशलता से लागू किया जा सकता है, जिससे storage space में बड़ी बचत होती है

CHECK constraint के आधार पर full table scan हटाना

  • plan column में केवल 'free', 'pro' वैल्यू स्वीकार करने वाला CHECK constraint होने के बावजूद, अगर 'Pro' के साथ गलत query चलाई जाए तो PostgreSQL पूरी table scan करता है
    • execution plan में सभी 100,000 rows पढ़ी जाती हैं, जबकि actual result 0 rows होता है
  • constraint_exclusion parameter को 'on' पर सेट करने से PostgreSQL constraints को ध्यान में रखकर scan को पूरी तरह छोड़ देता है
    • execution time 7.4ms से घटकर 0.008ms हो जाता है
  • इसका default मान 'partition' है, और simple queries में planning overhead बढ़ सकता है
    • लेकिन BI और reporting environments में users अक्सर गलत conditions डालते हैं, इसलिए 'on' सेटिंग उपयोगी हो सकती है

function-based index से low cardinality optimization

  • 10 million sales records वाली sale table में daily revenue aggregation query चलाने पर full scan के कारण 627ms लगते हैं
  • sold_at column पर B-Tree index जोड़ने पर समय 187ms तक घटता है, लेकिन index size 214MB तक बढ़ जाता है
  • date_trunc('day', sold_at) expression पर function-based index बनाने से size 66MB तक घट जाता है और execution time 145ms तक और बेहतर हो जाता है
    • low cardinality की वजह से index deduplication संभव हो पाता है
  • लेकिन query expression का index definition से बिल्कुल मेल खाना जरूरी है, इसलिए expression consistency बनाए रखना जरूरी है
    • इसके लिए वही expression शामिल करने वाला VIEW बनाया जा सकता है, या
    • PostgreSQL 18 से समर्थित virtual generated column जोड़कर consistency को अपने-आप सुनिश्चित किया जा सकता है
  • virtual generated column का उपयोग करने पर index अपने-आप इस्तेमाल होता है, और छोटा index, तेज query, expression consistency — तीनों फायदे मिलते हैं
  • हालांकि PostgreSQL 18 में virtual column पर index बनाना अभी समर्थित नहीं है, और इसका समर्थन आगे आने वाले version 19 में अपेक्षित है

Hash index से unique constraint लागू करना

  • लंबे URL स्टोर करने वाली urls table में duplicate URL रोकने के लिए B-Tree आधारित unique index बनाया जाए तो index size 154MB तक पहुंच जाता है
  • Hash index actual value स्टोर नहीं करता, सिर्फ hash value स्टोर करता है, इसलिए यह काफी छोटा होता है
    • PostgreSQL डिफ़ॉल्ट रूप से unique Hash index को support नहीं करता, लेकिन
    • exclusion constraint की मदद से EXCLUDE USING HASH (url WITH =) के रूप में unique constraint को वैकल्पिक तरीके से लागू किया जा सकता है
  • इस तरीके में भी duplicate insert होने पर error आती है, और query performance भी B-Tree से तेज रहती है (0.022ms vs 0.046ms)
  • index size 32MB है, जो B-Tree की तुलना में 5 गुना से भी ज्यादा छोटा है
  • कमियां:
    • foreign key reference संभव नहीं (REFERENCES constraint संभव नहीं)
    • INSERT ... ON CONFLICT syntax के साथ compatibility सीमित है
    • इसके बदले ON CONFLICT ON CONSTRAINT या MERGE syntax इस्तेमाल किया जा सकता है
  • Hash index बड़े text columns में uniqueness सुनिश्चित करने के लिए उपयुक्त है, और जहां foreign key की जरूरत न हो वहां यह space-efficient alternative के रूप में उपयोगी है

संबंधित पढ़ाई

अभी कोई टिप्पणी नहीं है.

अभी कोई टिप्पणी नहीं है.