PostgreSQL इंडेक्स का परिचय

 (dlt.github.io)
30 पॉइंट द्वारा GN⁺ 2026-01-26 | 1 टिप्पणियां | WhatsApp पर शेयर करें
  • PostgreSQL इंडेक्स डेटा एक्सेस की गति बढ़ाने के लिए एक मुख्य संरचना है, जो डिस्क से पढ़े जाने वाले डेटा की मात्रा कम करके क्वेरी परफ़ॉर्मेंस को बेहतर बनाती है
  • इंडेक्स Btree, Hash, BRIN, GIN, GiST, SP-GiST जैसे कई रूपों में उपलब्ध हैं, और हर एक अलग डेटा विशेषताओं व क्वेरी पैटर्न के लिए अनुकूलित है
  • इंडेक्स के साथ डिस्क स्पेस, write performance, query planner complexity, memory usage जैसी कई लागतें भी जुड़ी होती हैं
  • partial index, multi-column index, covering index, expression index जैसी उन्नत सुविधाओं के ज़रिए खास परिस्थितियों में दक्षता को अधिकतम किया जा सकता है
  • सही इंडेक्स का चयन और प्रबंधन PostgreSQL performance optimization का मुख्य तत्व होने पर ज़ोर दिया गया है

इंडेक्स की बुनियादी अवधारणा

  • इंडेक्स वह संरचना है जो डेटाबेस में डिस्क से पढ़े जाने वाले डेटा की मात्रा कम करके क्वेरी की गति बढ़ाती है
    • primary key, unique key, exclusion constraint आदि भी इंडेक्स के माध्यम से लागू किए जाते हैं
    • जब क्वेरी परिणाम पूरी टेबल के 15~20% से कम हों, तब इंडेक्स प्रभावी होता है; उससे अधिक होने पर sequential scan अधिक कुशल हो सकता है
  • PostgreSQL डिफ़ॉल्ट रूप से 6 प्रकार के इंडेक्स प्रदान करता है, और extension के माध्यम से और भी प्रकार इस्तेमाल किए जा सकते हैं
    • हर इंडेक्स key value और संबंधित डेटा लोकेशन (TID) को जोड़ता है

डिस्क पर स्टोर होने वाली डेटा संरचना

  • PostgreSQL की टेबल heap फ़ाइल के रूप में स्टोर होती हैं, और 8KB pages की इकाइयों में बनी होती हैं
  • हर row (tuple) बिना किसी निश्चित क्रम के स्टोर होती है, और उसका आंतरिक पता ctid (current tuple id) से पहचाना जाता है
    • उदाहरण: (0,1) का अर्थ है page 0 का पहला tuple
  • इंडेक्स heap की इन लोकेशनों (ctid) को tree structure में जोड़कर तेज़ खोज को संभव बनाता है

इंडेक्स डेटा एक्सेस को कैसे तेज़ करता है

  • इंडेक्स न होने पर PostgreSQL सभी pages पढ़ते हुए sequential scan करता है
    • उदाहरण क्वेरी में name='Ronaldo' खोजने के लिए 6272 pages पढ़े गए और 265ms लगे
    विज्ञापन
  • इंडेक्स जोड़ने पर यह Index Scan में बदल जाता है, जिसमें केवल 4 pages पढ़े जाते हैं और 0.077ms में काम पूरा हो जाता है
    • इंडेक्स value और ctid को map करके केवल ज़रूरी rows को तेज़ी से ढूँढता है
  • इंडेक्स फ़ाइल का आकार टेबल के आकार के समान हो सकता है (उदाहरण: 30MB table → 30MB index)

इंडेक्स की लागत के तत्व

  • इंडेक्स performance सुधार के साथ कई overhead भी लाता है

डिस्क स्पेस

  • इंडेक्स अलग स्टोरेज स्पेस लेते हैं, और टेबल से बड़े भी हो सकते हैं
    • backup, replication, disaster recovery के समय अतिरिक्त लागत आती है
    • partial index, multi-column index, BRIN आदि के ज़रिए space efficiency बेहतर की जा सकती है

write operations

  • UPDATE, INSERT, DELETE के समय यदि इंडेक्स वाले column बदलते हैं, तो index update overhead होता है

query planner

  • जितने अधिक इंडेक्स होंगे, उतने अधिक options planner को consider करने पड़ेंगे, जिससे query plan बनाने का समय बढ़ता है
विज्ञापन

memory usage

  • इंडेक्स pages को shared buffer में लोड करके cache किया जाता है, इसलिए इंडेक्स बढ़ने पर memory overhead भी बढ़ता है
  • btree node size limit के कारण column बड़े होने पर tree की depth बढ़ सकती है
  • sorting, multi-column scan, vacuum, reindex आदि में भी work memory अतिरिक्त रूप से उपयोग होती है

इंडेक्स के प्रमुख प्रकार

Btree

  • PostgreSQL की डिफ़ॉल्ट इंडेक्स संरचना, और अधिकांश DBMS में इस्तेमाल होने वाला general-purpose index
    • O(log n) time complexity के साथ तेज़ खोज प्रदान करता है
    • सभी leaf nodes एक ही depth पर होने वाली balanced tree structure
    • ORDER BY, JOIN operations के लिए उपयुक्त, और primary key·unique key constraints में उपयोग होता है
  • internal nodes child node pointers रखते हैं, जबकि leaf nodes key और heap pointer स्टोर करते हैं
  • left·right node pointers के ज़रिए bidirectional traversal संभव है
multiple indexes का उपयोग
  • PostgreSQL कई इंडेक्सों को bitmap AND/OR operations से जोड़कर complex conditions को हैंडल कर सकता है
    • उदाहरण: age=30 AND login_count=100 शर्त में दो इंडेक्सों के bitmap को जोड़ा जाता है
multi-column index
  • कई columns को एक इंडेक्स में जोड़कर space saving और speed improvement हासिल किया जा सकता है
    • लेकिन column order महत्वपूर्ण है, और केवल बाएँ से मेल खाने वाली शर्तें ही इंडेक्स का उपयोग कर सकती हैं
partial index
  • condition expression का उपयोग करके केवल कुछ rows को index किया जाता है
    • index का आकार कम होता है, RAM fit बेहतर होता है, और lookup speed सुधरती है
    • उदाहरण: create index on rules(status) where status='enabled';
    • value distribution असमान होने पर उपयोगी (status <> 'TODO' आदि)
    विज्ञापन
covering index
  • यदि क्वेरी के लिए आवश्यक सभी columns इंडेक्स में शामिल हों, तो heap access के बिना result लौटाया जा सकता है (index-only scan)
    • create index abc_cov_idx on bar(a, b) including c;
    • यह multi-column index की तुलना में अधिक space-efficient होता है
expression index
  • column value के बजाय function या expression के result को index किया जाता है
    • उदाहरण: CREATE INDEX idx_lower_name ON customers (lower(name));
    • LOWER(name) जैसे transformed value पर खोज के लिए उपयोगी
    • केवल immutable functions का उपयोग किया जा सकता है

Hash

  • hashmap structure पर आधारित इंडेक्स, जो लंबे strings या UUID जैसे मामलों में space-efficient हो सकता है
    • 32-bit hash code स्टोर करके आकार कम करता है
    • केवल equality comparison (=) को support करता है; sorting या multi-column index संभव नहीं
    • hash distribution समान होने पर Btree से तेज़ read performance मिल सकती है
  • आधिकारिक दस्तावेज़ों के अनुसार, hash index bucket page तक direct access के कारण बड़े tables में I/O घटा सकता है
विज्ञापन

BRIN (Block Range Index)

  • यह ऐसा इंडेक्स है जो हर block range के minimum·maximum values ही स्टोर करता है
    • बहुत compact और cache-friendly
    • large-scale, append-only, time-series data के लिए उपयुक्त
  • यदि rows बार-बार update हों, तो MVCC के कारण duplicate storage से इसकी efficiency घट सकती है
  • pages_per_range setting के ज़रिए accuracy और size के बीच trade-off को समायोजित किया जा सकता है

GIN (Generalized Inverted Index)

  • composite data search के लिए उपयुक्त इंडेक्स
    • text, array, JSONB आदि में specific elements की खोज को support करता है
    • हर data type के लिए dedicated strategy (opclass) का उपयोग होता है
    • JSON के लिए JSONB column, और text के लिए tsvector या pg_trgm extension के साथ उपयोग की सिफारिश की जाती है

GiST & SP-GiST

  • generalized search tree (GiST) और space-partitioned tree (SP-GiST) खास data types के लिए इंडेक्स implementation framework हैं
    • GiST balanced tree को support करता है, जबकि SP-GiST unbalanced structure को support करता है
    • geospatial information, inet, range, text vector आदि में उपयोग
    • GIN तेज़ lookup देता है, जबकि GiST का build·maintenance cost कम होता है
    • full-text search में दोनों में से चयन आवश्यकताओं के अनुसार किया जाता है

निष्कर्ष

  • इंडेक्स PostgreSQL performance optimization का मुख्य आधार है, और read speed improvement तथा write·storage cost के बीच संतुलन महत्वपूर्ण है
  • डेटा की विशेषताओं और क्वेरी पैटर्न के अनुरूप सही इंडेक्स प्रकार चुनने पर तेज़ और कुशल database operations संभव हैं
  • सही इंडेक्स डिज़ाइन large-scale systems की scalability और stability सुनिश्चित करने के लिए आवश्यक है

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

1 टिप्पणियां

 
GN⁺ 2026-01-26
Hacker News की राय

  • PostgreSQL की आधिकारिक documentation वाकई बहुत अच्छी तरह लिखी गई है और पढ़ने में भी मज़ेदार है, इसलिए साझा कर रहा हूँ
    PostgreSQL Indexes परिचय दस्तावेज़

  • multi-column index वाला हिस्सा लगभग वैसा ही है जैसा मैंने सीखा था
    लेकिन मैं सोच रहा था कि क्या नवीनतम PostgreSQL version में भी यह अब भी ऐसा ही है
    मैंने पहले तीसरे उदाहरण जैसी query में bitmap index scan का उपयोग होते देखा था, और तभी से पुरानी ‘स्थापित धारणा’ पर फिर से सोचना शुरू किया
    वैसे index से जुड़ी चीज़ों के लिए Use The Index, Luke वेबसाइट और किताब टीम के सभी लोगों के पढ़ने लायक classic resource हैं

    • PostgreSQL 18 में index skip scan जोड़ा गया है, इसलिए अब multi-column index के सिर्फ निचले column के आधार पर भी efficient search संभव हो गई है
      पहले के versions में भी यह संभव था, लेकिन तब पूरे index scan की ज़रूरत पड़ती थी, इसलिए यह inefficient था
      संबंधित वीडियो: YouTube लिंक
    • bitmap index scan उन pages को सीमित कर देता है जहाँ data होने की संभावना है, लेकिन वास्तविक condition verification फिर से करनी पड़ती है, इसलिए इसकी performance सामान्य index scan से कम होती है

  • मुझे लगता है कि PostgreSQL में incremental view maintenance का built-in support होना अच्छा होगा
    यह index की तरह base data बदलने पर अपने-आप update हो जाता है, लेकिन यह सिर्फ किसी खास view तक सीमित नहीं बल्कि arbitrary views पर भी लागू होने वाला concept है

    • इसमें transaction processing जुड़ा हुआ है, इसलिए यह काफ़ी कठिन समस्या है
      Noria, Materialize, Apache Flink, GCP Continuous Queries, Spark Streaming Tables, Delta Tables, ClickHouse streaming tables, TimescaleDB, ksqlDB, StreamSQL आदि जैसे कई संबंधित projects हैं
      PostgreSQL में हाल ही में pg_ivm नाम का extension इस समस्या को address करना शुरू कर चुका है
    • अगर data time-series है, तो TimescaleDB का continuous aggregates feature पहले से यही काम करता है

  • B-tree vs Hash index पर चर्चा दिलचस्प है
    बहुत से लोग सोचते हैं कि ID column के लिए hash बेहतर होगा, लेकिन वास्तव में default B-tree ज़्यादा efficient होता है
    खासकर लगभग sequential values insert करने पर tree structure ज़्यादा फायदेमंद होता है
    हालांकि, इस बार उल्लेख किए गए blog post में इसके उलट hash benchmark में जीता बताया गया है

  • इस लेख का timing अच्छा था
    multi-column index का leading column rule हमेशा उलझाने वाला रहा है, लेकिन bitmap index scan की वजह से यह पहले जितना गंभीर नहीं रह गया
    PostgreSQL 18 का skip scan feature पुरानी समझ को काफ़ी बदल देता है, इसलिए जिन्होंने पुराने version के आधार पर सीखा है उन्हें अपना mental model update करना होगा

  • PostgreSQL के लिए यह सचमुच शानदार सामग्री लगती है
    B-tree index के मामले में मैं काफ़ी समय से Use The Index, Luke को अक्सर संदर्भ के तौर पर देखता आया हूँ

  • मुझे लगता है यह अनिवार्य रूप से पढ़ने लायक है
    यह सिर्फ एक साधारण परिचयात्मक लेख से आगे बढ़कर गहराई भी देता है, और जब तक यह internal structure की बात नहीं करता तब तक काफ़ी पढ़ने योग्य बना रहता है

  • मुझे ऐसी सरल और विनम्र writing style पसंद है
    ज्ञान को सीधे पहुँचाने का तरीका अच्छा लगता है