Indexing Strategies

Analogy categoryAnalogies

Indexing Is a Trade-off, Not a Default

Every index speeds up some reads but slows down every INSERT, UPDATE, and DELETE that touches the indexed columns, because PostgreSQL must maintain each index's structure in lockstep with the heap. A good indexing strategy starts from actual query patterns captured via pg_stat_statements rather than indexing every column that appears in a WHERE clause somewhere in the codebase. The rule of thumb is to index columns with high selectivity (few matching rows per value) that appear in frequent, performance-sensitive queries, and to periodically review pg_stat_user_indexes for indexes with near-zero idx_scan counts that are pure maintenance overhead.

🏏

Cricket analogy: It is like a captain choosing fielding positions: placing a fielder at every possible spot slows the whole team down, so you position players (indexes) only where the ball (queries) actually goes most often, based on the batter's real shot tendencies.

Ordering Columns in Multicolumn Indexes

For a composite index like (tenant_id, status, created_at), PostgreSQL can use it efficiently for a query filtering on tenant_id alone, on tenant_id and status, or on all three columns, but generally not for a query filtering only on status or created_at without tenant_id, because the index is physically sorted by tenant_id first. The standard guideline is to place equality-filtered columns before range-filtered columns, and to put the column with the highest selectivity (most distinct values relative to row count) earliest among the equality columns, since that narrows the scanned leaf range fastest. Including a trailing column purely for an ORDER BY can also let the planner skip a separate sort step, an optimization worth checking in EXPLAIN output.

🏏

Cricket analogy: It is like a scorecard filed by tournament, then team, then match date; you can quickly find 'India's matches in the 2027 World Cup' but not 'every match played on a Tuesday' across all tournaments without scanning everything.

sql

-- Composite index optimized for the most common query shape
CREATE INDEX idx_events_tenant_status_created
  ON events (tenant_id, status, created_at);

-- Uses the index efficiently: equality on tenant_id and status, range on created_at
SELECT * FROM events
WHERE tenant_id = 42
  AND status = 'failed'
  AND created_at >= now() - interval '7 days'
ORDER BY created_at DESC;

-- Find unused indexes that are pure write overhead
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;

Use CREATE INDEX CONCURRENTLY on production tables to avoid taking an ACCESS EXCLUSIVE lock that blocks writes during index creation. It takes longer and requires two table scans internally, but reads and writes continue normally throughout the build.

Covering Indexes and Index-Only Scans

An Index Only Scan avoids visiting the heap entirely by reading needed column values straight from the index, which is dramatically faster when the visibility map confirms the relevant pages are all-visible (no recent uncommitted changes). Adding non-key columns to an index via the INCLUDE clause, for example CREATE INDEX ... (tenant_id, status) INCLUDE (order_total), lets a query that selects order_total alongside filtering on tenant_id and status be answered entirely from the index without widening the sort key or bloating the B-tree's comparison logic. This is especially valuable for narrow, frequently-run aggregate or listing queries where heap access is the dominant cost.

🏏

Cricket analogy: It is like a scorecard that already lists each batter's strike rate next to their name, so a commentator citing strike rates never needs to flip to the detailed stats sheet, exactly as INCLUDE columns avoid a heap visit.

Index Only Scans still require visiting the heap for pages not marked all-visible in the visibility map. On tables with heavy update/delete churn and infrequent VACUUM, the visibility map can lag badly, silently degrading what looks like an Index Only Scan back into effectively a full heap-touching scan.

Every index adds write overhead, so index only columns backed by real, measured query patterns (pg_stat_statements).
In multicolumn indexes, place equality-filtered columns before range-filtered columns, ordered by selectivity.
A trailing column matching ORDER BY can let PostgreSQL skip an explicit sort step.
CREATE INDEX CONCURRENTLY avoids blocking writes during index creation on live tables.
INCLUDE columns enable Index Only Scans for queries that select extra columns without widening the sort key.
Index Only Scans depend on the visibility map; heavy churn without regular VACUUM degrades their benefit.
Regularly audit pg_stat_user_indexes for idx_scan = 0 entries to remove indexes that only add overhead.