Postgres index bloat: what actually worked
A table with 12 GB of live rows carried a 40 GB index. Autovacuum was running and reporting success. The query planner had quietly stopped using the index for anything, which is the part that hurts: bloat does not just waste disk, it makes the planner choose worse plans.
Diagnosis before heroics
First, confirm it is really bloat and not an unvacuumed tail. The
pgstattuple extension gives you real numbers instead of guesses:
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstatindex('idx_events_created_at');
-- leaf_fragmentation > 40 with a high avg_leaf_density is the tell
Ours was 61% fragmented. The fix for that index was REINDEX INDEX
CONCURRENTLY, which took 96 minutes on a table that was still receiving writes
the whole time.
What stopped it coming back
- Lower the per-table
autovacuum_vacuum_scale_factorfor hot tables; the default of 0.2 is fine for a quiet table and hopeless for a busy one. - Track index size over time. If you only look at table size, you will always be surprised.
- Prefer a partial index. Half of our index entries were for rows in a state we never query.
The partial index ended up at 6 GB and the planner started using it immediately. Total disk saved: 34 GB. Total code change: one migration.