Beyond the default B-tree, PostgreSQL offers several specialized index types — GIN, GiST, BRIN, Hash — each suited to different data and query patterns, plus partial and expression indexes for flexibility. Choosing the right index type can dramatically improve performance for specialized queries.
CREATE INDEX idx_users_email ON users(email); -- default = B-tree
-- great for: equality (=), ranges (<, >, BETWEEN), sorting (ORDER BY), most queries
B-tree is the default and handles the majority of cases — equality, ranges, and sorting on ordinary columns.
-- 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 are essential for JSONB, arrays, and full-text search — queries checking containment or membership.
-- 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 (index only relevant rows — smaller, faster) and expression indexes (index a computed value — enabling indexed function-based queries) are powerful, flexible tools.
Understanding PostgreSQL's variety of index types is valuable knowledge for performance optimization, because the right index type for a given data and query pattern can dramatically speed up queries that the default B-tree handles poorly or not at all.
While B-tree (the default) covers most cases (equality, ranges, sorting), specialized types unlock performance for specialized data: GIN is essential for JSONB, arrays, and full-text search (containment/membership queries — making Postgres's powerful JSONB and array features performant); GiST handles geometric, range, and nearest-neighbor queries (PostGIS geospatial, range types); and BRIN provides tiny, efficient indexes for large naturally-ordered tables (like time-series/append-only data — a valuable option for huge tables where a full B-tree would be costly).
Knowing which index type fits which scenario (GIN for JSONB/arrays/full-text, GiST for geometric/range, BRIN for large ordered data) is key to optimizing Postgres's advanced features.
Equally valuable are partial indexes (indexing only relevant rows — smaller and more efficient when you query a subset, like active users) and expression indexes (indexing computed values — enabling fast function-based lookups like case-insensitive email search, which a plain index can't support).
Since index choice significantly affects query performance, and since Postgres's specialized index types (especially GIN for JSONB/arrays/full-text) are what make its advanced data types performant, understanding the index types and when to use each — including partial and expression indexes — is valuable, frequently-relevant knowledge for optimizing PostgreSQL databases, distinguishing developers who can make Postgres fast for diverse query patterns and a key aspect of leveraging the database's full performance capabilities.