Indexes That Earn Their Keep

An index is not free storage that makes reads faster. It’s a second data structure that every INSERT, UPDATE and DELETE has to maintain. On a write-heavy table, five unused indexes can cost more than the slow query you were worried about.

Find the dead weight first

Postgres tracks index usage, so you never have to guess:

SELECT
  relname AS table,
  indexrelname AS index,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Anything with idx_scan = 0 after a full business cycle — a month, not a day — is a candidate for deletion. Check it isn’t backing a constraint first.

Column order is the whole game

A composite index on (a, b) can serve WHERE a = ? and WHERE a = ? AND b = ?. It cannot efficiently serve WHERE b = ?. The leading column is the one that has to appear in the predicate — this is why (customer_id, created_at) and (created_at, customer_id) are entirely different indexes with entirely different use cases.

Rule I follow: equality columns first, range column last.

-- WHERE customer_id = 42 AND created_at > now() - interval '30 days'
CREATE INDEX ON invoices (customer_id, created_at);

Partial indexes for skewed data

If you only ever query one slice of a table, only index that slice:

CREATE INDEX ON jobs (created_at) WHERE status = 'pending';

On a jobs table where 99% of rows are done, this index is a hundredth of the size, stays in cache, and costs almost nothing to maintain — because completed rows never touch it.

Covering indexes end the debate

INCLUDE lets an index carry extra columns purely so the query never has to visit the heap:

CREATE INDEX ON invoices (customer_id) INCLUDE (total, status);

Now SELECT total, status FROM invoices WHERE customer_id = ? is an index-only scan. Verify it — EXPLAIN (ANALYZE, BUFFERS) should say Index Only Scan with Heap Fetches: 0. If heap fetches are high, the visibility map is stale and you need a VACUUM.

The short version

Add an index when a real query is slow and EXPLAIN shows a sequential scan over a table that will keep growing. Drop it when pg_stat_user_indexes says nobody used it. Everything else is speculation, and speculation is billed on every write.