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

 (dlt.github.io)
30 पॉइंट द्वारा GN⁺ 2026-01-26 | अभी कोई टिप्पणी नहीं है. | 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 सुनिश्चित करने के लिए आवश्यक है

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

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

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