← All notes

Tracking down index bloat in a 400GB Postgres table

A reporting query that used to finish in under two seconds started taking ninety. Nothing had shipped. The plan looked identical. The table had not grown much. What had grown was the index.

Confirming it is bloat and not something else

The first thing worth doing is comparing the logical size of an index against what it should be. pgstattuple gives you the honest answer: 

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT
  i.relname AS index_name,
  pg_size_pretty(pg_relation_size(i.oid)) AS size,
  round(s.avg_leaf_density::numeric, 1)   AS leaf_density
FROM pg_class i
JOIN LATERAL pgstatindex(i.oid) s ON true
WHERE i.relkind = 'i'
  AND pg_relation_size(i.oid) > 1e9
ORDER BY pg_relation_size(i.oid) DESC;

A healthy btree sits somewhere around 70–90% leaf density. Ours was at 22%. That is roughly three quarters of the reads going to pages that hold almost nothing.

Why it happened

The table was append-heavy but had a nightly job that updated a status column on old rows. Every update writes a new tuple, and every new tuple needs a new index entry, even when the indexed column did not change. HOT updates would have saved us, but the column was itself indexed, so HOT was off the table.

Autovacuum was running and finishing. Vacuum reclaims space for reuse inside the index; it does not give the pages back or re-pack them. Bloat can be stable and still be terrible.

The fix

REINDEX CONCURRENTLY is the boring correct answer, available since 12 and safe to run on a live table. It needs disk headroom equal to the index size, which is worth checking first: 

REINDEX INDEX CONCURRENTLY events_account_created_idx;

Ninety seconds went back to 1.4. The longer-term fix was dropping the index on the status column entirely (it was selective enough to not need one) so that the nightly update could go back to being HOT.

What I would do differently