tiny notes

Finding index bloat in Postgres without extensions

A reporting query on a table with roughly 12 million rows crept from under a second to about 40 seconds over a few months. The plan still showed an index scan, so nothing looked obviously wrong until I compared the index size against the table size.

The quick look

This gives you sizes per index, biggest first:

SELECT
  i.relname            AS index_name,
  t.relname            AS table_name,
  pg_size_pretty(pg_relation_size(i.oid)) AS index_size,
  pg_size_pretty(pg_relation_size(t.oid)) AS table_size,
  s.idx_scan           AS scans
FROM pg_class i
JOIN pg_index x ON i.oid = x.indexrelid
JOIN pg_class t ON t.oid = x.indrelid
LEFT JOIN pg_stat_user_indexes s ON s.indexrelid = i.oid
WHERE i.relkind = 'i' AND t.relnamespace > 99
ORDER BY pg_relation_size(i.oid) DESC
LIMIT 20;

An index larger than the table it indexes is not automatically broken, but on a narrow two-column index it is a strong hint. Mine was 4.1 GB against a 2.8 GB table.

Why it happens

Updates in Postgres write a new row version and leave the old one for vacuum to clean up. Vacuum reclaims space inside existing index pages, but it will not give pages back to the filesystem or rebalance a tree that has gone sparse. A table with a hot update pattern on indexed columns slowly turns its indexes into swiss cheese.

Checking whether autovacuum is even keeping up:

SELECT relname, n_live_tup, n_dead_tup,
       last_autovacuum, autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

The fix

Rebuild concurrently so you do not take a write lock for the duration:

REINDEX INDEX CONCURRENTLY orders_customer_created_idx;

It took eleven minutes, the index came out at 740 MB, and the query went back to roughly 300 ms. Worth knowing that a failed concurrent reindex leaves an invalid index behind, so check for those afterwards:

SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;

Lowering autovacuum_vacuum_scale_factor on that specific table has kept it from coming back:

ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.02);

Note to self: measure before reindexing, otherwise you spend an afternoon rebuilding indexes that were fine.