Vacuum, bloat, and the cost of waiting

2026-09-14

A table with a heavy update workload accumulates dead tuples faster than autovacuum reclaims them. The failure mode is quiet: query plans change, sequential scans get slower, and nobody notices until the graph looks wrong.

Things that helped us, in order of effort:

1. Raise autovacuum_vacuum_scale_factor per table instead of globally.
2. Watch pg_stat_user_tables.n_dead_tup and alert on trend, not absolute values.
3. Run VACUUM (VERBOSE, ANALYZE) manually once to learn how long the table actually takes.

The lesson for me was that defaults are tuned for small tables. Once a table passes a few tens of millions of rows, the defaults stop meaning anything.