PostgreSQL Indexes
Choosing between B-tree, GIN, GiST, BRIN and hash indexes, plus partial, covering and expression indexes with the queries they serve.
PostgreSQL offers several index types, and choosing the right one matters more than tuning the query that uses it.
Index types
B-tree essentials
Column order in a composite index follows equality, then range, then sort — see Index Design.
-- Covering index: the query never touches the heap.
CREATE INDEX orders_customer_created_idx
ON orders (customer_id, created_at DESC)
INCLUDE (status, total_cents);
-- Partial index: index only the rows that are queried.
CREATE INDEX orders_pending_idx ON orders (created_at)
WHERE status = 'pending';
-- Expression index: the query must use the identical expression.
CREATE INDEX users_lower_email_idx ON users (lower(email));An index-only scan additionally requires the table's visibility map to be current, which means the table must be vacuumed. A "covering" index on a table that never gets vacuumed still reads the heap.
GIN for jsonb and text search
-- jsonb containment queries.
CREATE INDEX events_payload_idx ON events USING gin (payload jsonb_path_ops);
SELECT * FROM events WHERE payload @> '{"type":"signup"}';
-- Full-text search over a generated tsvector column.
ALTER TABLE articles ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;
CREATE INDEX articles_search_idx ON articles USING gin (search_vector);
SELECT id, title FROM articles WHERE search_vector @@ plainto_tsquery('english', 'index bloat');jsonb_path_ops produces a smaller index than the default operator class and supports only
containment — usually the right trade when containment is all you query.
BRIN for large ordered tables
A BRIN index stores the minimum and maximum value per block range, so it is tiny — often a thousandth of the equivalent B-tree — and it only works when physical row order correlates with the indexed column.
CREATE INDEX events_created_brin ON events USING brin (created_at) WITH (pages_per_range = 64);Ideal for append-only time-ordered tables. Useless once rows are updated and reordered; check
correlation in pg_stats before relying on it.
Maintenance
-- Indexes that are never used, largest first.
SELECT relname AS table_name, indexrelname AS index_name,
pg_size_pretty(pg_relation_size(indexrelid)) AS size, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexrelid NOT IN (SELECT conindid FROM pg_constraint)
ORDER BY pg_relation_size(indexrelid) DESC;Interpret idx_scan = 0 in context: statistics reset on pg_stat_reset() and on some upgrades,
and an index that serves a monthly report will look unused for 29 days.