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

• 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 के रूप में उपयोगी है

[GN⁺]

Hacker News टिप्पणियाँ

• इंडेक्स 214MB का है, यानी पूरी टेबल के लगभग आधे आकार का
  analyst के नज़रिए से यह अच्छा है, लेकिन write performance के हिसाब से write amplification की समस्या पैदा होती है
  इंडेक्स का डिज़ाइन read/write ratio के अनुसार बदलता है, और इसी वजह से data warehouse या read replica रखे जाते हैं
  अगर बहुत ज़्यादा users को serve करना हो, तो OLTP DB में BI/OLAP इंडेक्स न रखना बेहतर है

  • अच्छा होता अगर PostgreSQL clustered index (Oracle के Index Organized Table) को support करता
    अगर table access pattern स्थिर हो, तो टेबल खुद ही इंडेक्स बन सकती है और write amplification के बिना efficiency मिल सकती है

• मुझे लगता है पहले उदाहरण में Plan को enum type के रूप में define करना बेहतर होता
  यह text से हल्का होता है, और गलत filter input पर खाली result देने के बजाय error देता है, इसलिए ज़्यादा सुरक्षित है

• यह शानदार लेख था। PostgreSQL और MySQL को दशकों से इस्तेमाल करने के बावजूद, यह पढ़कर लगा कि मैं अब भी इसकी संभावनाओं का सिर्फ़ एक हिस्सा ही जानता था

  • मैं भी 10 साल से ज़्यादा समय से Postgres इस्तेमाल कर रहा हूँ, लेकिन हर बार docs देखते समय अब भी लगता है कि मैं बस ऊपरी परत ही खुरच रहा हूँ। यह सच में बहुत शक्तिशाली सिस्टम है
  • PostgreSQL कुछ-कुछ Emacs जैसा है। ऊपर से सरल दिखता है, लेकिन असल में इसमें operating system जैसी flexibility है

• लेख के अंत में आया MERGE syntax सबसे दिलचस्प लगा
  आम तौर पर मैं upsert के लिए INSERT ... ON CONFLICT DO UPDATE इस्तेमाल करता हूँ, लेकिन MERGE ज़्यादा शक्तिशाली लगता है और अलग-अलग situations में काम आ सकता है

  • MERGE SQL standard में काफ़ी पहले से था, लेकिन Postgres ने MVCC model की non-atomicity समस्या की वजह से इसे देर से जोड़ा
    यह बात pganalyze ब्लॉग पोस्ट में भी समझाई गई है
    व्यक्तिगत रूप से मैं INSERT ... ON CONFLICT को पसंद करता हूँ, और सिर्फ़ सच में ज़रूरत हो तभी MERGE इस्तेमाल करता हूँ, साथ ही error handling बहुत सावधानी से करता हूँ
  • concurrency के लिहाज़ से INSERT ... ON CONFLICT ज़्यादा predictable है
    modern-sql.com की तुलना वाली पोस्ट देखें
  • अगर बड़े पैमाने पर batch insert करना हो, तो binary format में COPY INTO इस्तेमाल करना सबसे तेज़ है। इसमें server-side overhead लगभग नहीं के बराबर होता है

• लेख में शामिल न किया गया BRIN इंडेक्स भी दिलचस्प लगा
  अगर डेटा monotonic बढ़ता हो, तो यह बहुत छोटा और तेज़ इंडेक्स बनकर आदर्श होता है

  • डेटा का पूरी तरह monotonic होना ज़रूरी नहीं है। ज़्यादातर monotonic हो तो भी यह काफ़ी अच्छा काम करता है
    उदाहरण के लिए, server पर आने वाला timestamp data, जहाँ क्रम थोड़ा-बहुत बिगड़ जाता है, वहाँ भी यह अच्छा है
    UUIDv7 के मामले में pages_per_range को adjust करना पड़ सकता है

• hash इंडेक्स पर unique constraint न लगा पाने की बात हमेशा खलती है
  ऐसा लगता है कि बस exclusion constraint में बदलने वाला थोड़ा-सा glue code हो, तो यह हल हो जाना चाहिए, समझ नहीं आता कि अभी तक ऐसा क्यों नहीं है

• hash-आधारित uniqueness validation को इंडेक्स में support नहीं किया जाता, क्योंकि collision handling नहीं होती
  प्रस्तावित समाधान भी उसी समस्या से जूझेगा

  • यह बिल्कुल सही नहीं है। इंडेक्स सिर्फ़ hash store करता है, लेकिन टेबल में पूरा value store होता है
    Postgres तभी duplicate मानता है जब hash और असली value दोनों match करें
  • यह बात dbfiddle उदाहरण में भी देखी जा सकती है

• लेख की बातें ताज़गीभरी लगीं। virtual column और hash इंडेक्स दिलचस्प हैं, लेकिन अभी भी लगता है कि वे ecosystem में पूरी तरह integrated नहीं हुए हैं

  • virtual column लगभग पूरा हो चुका है। PostgreSQL 18 में इसका अधिकांश हिस्सा implement हो गया है
    hash इंडेक्स में लंबे समय तक कई सीमाएँ थीं, लेकिन उनमें धीरे-धीरे सुधार हो रहा है, और automatic unique constraint अब भी बाकी काम है

• मुझे लगा stored generated column इस्तेमाल करके सीधे इंडेक्स बनाया जा सकता है, है ना?

  • लेख में बताया गया है कि इस तरीके से बचने की वजह क्या है
    PostgreSQL 14 से यह supported है, लेकिन क्योंकि परिणाम भौतिक रूप से store होता है और अतिरिक्त storage लेता है, इसलिए यह drawback है
  • क्या expression-आधारित partial index बनाना भी संभव नहीं होगा? यह सवाल भी आया
  • आखिरकार storage बढ़ता ही है, इसलिए लेख के उदाहरण में यह वह approach है जिससे बचना चाहा गया है

• cloud पर जाने के बाद fixed server environment में pgsql को सीधे संभालने का काम कम हो गया है
  लेख में दिखाया गया SQL syntax highlighting built-in feature है या कोई अलग tool, यह जानना चाहा

  • मैं pgcli इस्तेमाल करता हूँ। इसमें transaction status display, autocomplete, highlighting जैसी कई सुविधाजनक features हैं
    लेकिन लंबी query copy करते समय line break के बाद अपने-आप spaces जुड़ जाना असुविधाजनक है
  • IntelliJ जैसे IDE इस्तेमाल करने पर syntax highlighting और autocomplete दोनों साथ में मिल जाते हैं