default B-tree से परे, PostgreSQL कई विशेष index types प्रदान करता है — GIN, GiST, BRIN, Hash — प्रत्येक अलग data और query patterns के लिए उपयुक्त, साथ ही flexibility के लिए partial और expression indexes। सही index type चुनना विशेष queries के लिए performance में नाटकीय रूप से सुधार कर सकता है।
CREATE INDEX idx_users_email ON users(email); -- default = B-tree
-- great for: equality (=), ranges (<, >, BETWEEN), sorting (ORDER BY), most queries
B-tree default है और अधिकांश मामलों को संभालता है — साधारण columns पर equality, ranges और sorting।
-- GIN (Generalized Inverted Index) — for values CONTAINING multiple items
CREATE INDEX idx_attrs ON products USING GIN (attributes); -- JSONB
CREATE INDEX idx_tags ON posts USING GIN (tags); -- arrays
CREATE INDEX idx_search ON docs USING GIN (to_tsvector('english', body)); -- full-text
-- great for: JSONB containment, array membership, full-text search
GIN indexes JSONB, arrays और full-text search के लिए आवश्यक हैं — containment या membership जाँचने वाली queries।
-- GiST — geometric, range, nearest-neighbor (e.g. PostGIS, range types)
CREATE INDEX idx_geo ON places USING GiST (location);
-- BRIN (Block Range INdex) — tiny index for LARGE tables with naturally-ordered data
CREATE INDEX idx_time ON logs USING BRIN (created_at); -- great for huge time-series
-- tiny size, efficient for large append-only/ordered data
-- Hash — equality only (=); rarely needed (B-tree handles equality too)
CREATE INDEX idx_hash ON t USING HASH (col);
-- PARTIAL index — index only SOME rows (smaller, targeted)
CREATE INDEX idx_active ON users(email) WHERE active = true; -- only active users
-- EXPRESSION index — index a computed value
CREATE INDEX idx_lower_email ON users(LOWER(email)); -- for case-insensitive lookups
SELECT * FROM users WHERE LOWER(email) = '[email protected]'; -- uses the index
Partial indexes (केवल प्रासंगिक rows को index करें — छोटे, तेज़) और expression indexes (एक computed value को index करें — indexed function-based queries को सक्षम करते हुए) शक्तिशाली, flexible उपकरण हैं।
PostgreSQL के विविध index types को समझना performance optimization के लिए मूल्यवान ज्ञान है, क्योंकि किसी दिए गए data और query pattern के लिए सही index type उन queries को नाटकीय रूप से तेज़ कर सकता है जिन्हें default B-tree खराब तरीके से या बिल्कुल नहीं संभालता।
जबकि B-tree (default) अधिकांश मामलों को कवर करता है (equality, ranges, sorting), विशेष types विशेष data के लिए performance अनलॉक करते हैं: GIN JSONB, arrays और full-text search के लिए आवश्यक है (containment/membership queries — Postgres की शक्तिशाली JSONB और array विशेषताओं को performant बनाना); GiST geometric, range और nearest-neighbor queries (PostGIS geospatial, range types) को संभालता है; और BRIN बड़ी naturally-ordered tables (जैसे time-series/append-only data — विशाल tables के लिए एक मूल्यवान विकल्प जहाँ एक पूर्ण B-tree महँगा होगा) के लिए छोटे, कुशल indexes प्रदान करता है।
यह जानना कि कौन सा index type किस परिदृश्य में फिट होता है (JSONB/arrays/full-text के लिए GIN, geometric/range के लिए GiST, बड़े ordered data के लिए BRIN) Postgres की उन्नत विशेषताओं को optimize करने की कुंजी है।
समान रूप से मूल्यवान हैं partial indexes (केवल प्रासंगिक rows को indexing — जब आप एक subset query करते हैं तो छोटे और अधिक कुशल, जैसे active users) और expression indexes (computed values को indexing — case-insensitive email search जैसी तेज़ function-based lookups को सक्षम करना, जिसे एक plain index समर्थन नहीं कर सकता)।
चूँकि index का चुनाव query performance को महत्वपूर्ण रूप से प्रभावित करता है, और चूँकि Postgres के विशेष index types (विशेष रूप से JSONB/arrays/full-text के लिए GIN) वही हैं जो इसकी उन्नत data types को performant बनाते हैं, index types और प्रत्येक का उपयोग कब करें को समझना — partial और expression indexes सहित — PostgreSQL databases को optimize करने के लिए मूल्यवान, बार-बार प्रासंगिक ज्ञान है, जो उन developers को अलग करता है जो विविध query patterns के लिए Postgres को तेज़ बना सकते हैं और database की पूर्ण performance क्षमताओं का लाभ उठाने का एक प्रमुख पहलू है।