Part 4 · 3 chapters · ~18 min

Indexes

The index access method interface, B-tree layout with deduplication and bottom-up deletion, index-only scans and the visibility map, hash, GIN, GiST, SP-GiST, BRIN and bloom indexes, partial, expression and covering indexes, multi-column ordering and correlation, and index bloat with REINDEX CONCURRENTLY.

Access methods, one per kind of data

code

-- the access methods installed
SELECT amname, amtype FROM pg_am;

-- GIN on JSONB: containment queries
CREATE INDEX ON events USING gin (payload jsonb_path_ops);
SELECT * FROM events WHERE payload @> '{"type": "transfer.failed"}';

-- GiST: an exclusion constraint (no overlapping bookings for the same room)
CREATE EXTENSION btree_gist;
ALTER TABLE bookings ADD CONSTRAINT no_overlap EXCLUDE USING gist (room_id WITH =, during WITH &&);

-- BRIN on an append-only time column
CREATE INDEX ON events USING brin (created_at) WITH (pages_per_range = 64);
SELECT pg_size_pretty(pg_relation_size('events_created_at_idx'));

ONE INDEX TYPE PER KIND OF DATA

every access method exists because some data needs it

swipe the figure sideways, or tap expand for full screen

1/6

B-tree

B-tree (Lehman-Yao with right links for concurrency) handles =, <, >, BETWEEN, ORDER BY and prefix LIKE. Deduplication stores repeated keys once with a posting list; bottom-up deletion removes version churn from non-HOT updates before a page splits.

the default: equality, ranges, sortingdedup and bottom-up deletion keep it small

Index-only scans, partial, expression and covering indexes

code

-- covering index: INCLUDE carries columns in the leaf without making them part of the key
CREATE INDEX ON transfers (account_id, created_at DESC) INCLUDE (amount_kobo, state);
EXPLAIN (ANALYZE, BUFFERS) SELECT created_at, amount_kobo, state FROM transfers WHERE account_id = 7 ORDER BY created_at DESC LIMIT 20;
-- Index Only Scan ... Heap Fetches: 0     ← only if the visibility map says those pages are all-visible

-- partial index: only the rows a hot query needs
CREATE INDEX ON transfers (created_at) WHERE state = 'pending';

-- expression index: must match the query expression exactly
CREATE INDEX ON users (lower(email));
SELECT * FROM users WHERE lower(email) = lower($1);

Index-only scans need the visibility map: the index has no visibility information, so for each entry Postgres checks whether the heap page is all-visible; if not, it must visit the heap (Heap Fetches). A table that vacuum rarely visits gets few index-only benefits.

Multi-column order: a B-tree on (a, b) serves a = ?, a = ? AND b = ? and a = ? ORDER BY b; it does not efficiently serve b = ? alone (skip scan support arrives in Postgres 18 for some cases). The planner's correlation statistic (physical order versus index order) decides whether an index range scan reads pages sequentially or randomly.

Index bloat and rebuilding online

code

-- unused indexes cost writes for nothing
SELECT relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes ORDER BY idx_scan ASC, pg_relation_size(indexrelid) DESC LIMIT 20;

-- rebuild a bloated index without blocking writes
REINDEX INDEX CONCURRENTLY transfers_account_id_created_at_idx;
-- build new indexes without blocking writes (two table scans; can leave an INVALID index on failure)
CREATE INDEX CONCURRENTLY ON transfers (merchant_id);

Index bloat comes from page splits and deleted entries that vacuum frees but cannot merge back. Deduplication and bottom-up deletion reduce it sharply for B-trees since 13 and 14. Check every index with a purpose: each one is paid for on every insert and every non-HOT update.