Autovacuum Is Not Optional

January 14, 2026postgres

The complaint was that a query had gotten slow. The query hadn't changed, the row count hadn't meaningfully changed, and the plan was the same. The table had gone from 9 GB to 41 GB.

Why the table grows when the data doesn't

Postgres never updates a row in place. An UPDATE writes a new version and leaves the old one as a dead tuple, which is what lets concurrent readers keep seeing a consistent snapshot without blocking. Vacuum is the process that eventually reclaims those dead tuples for reuse. A table where vacuum can't keep up doesn't lose correctness — it just gets bigger forever, and every sequential scan pays for the emptiness.

Autovacuum was running. It wasn't finishing.

This is the part that wastes a day if you don't know to look for it. pg_stat_activity showed an autovacuum worker on the table. It had been there for nine hours. It was being throttled to roughly the speed of a modem by the cost-delay settings, which are tuned for a 2008 disk and had never been touched:

-- what we found
autovacuum_vacuum_cost_delay   = 20ms   -- pauses constantly
autovacuum_vacuum_cost_limit   = 200    -- tiny budget between pauses
autovacuum_vacuum_scale_factor = 0.2    -- wait for 20% of the table to die
autovacuum_max_workers         = 3      -- shared across 400 tables

On a table of this size, a scale factor of 0.2 means waiting for roughly eighty million dead tuples before autovacuum is even eligible to start. By the time it starts there is far too much work to do, and the cost delay guarantees it can't finish before the table qualifies again. It never catches up. It isn't stuck; it's losing a race.

The thing that was actually blocking it

Even a fast vacuum can't remove a dead tuple that might still be visible to some open snapshot. Two things hold that horizon back and both were true here:

SELECT pid, state, xact_start, query
  FROM pg_stat_activity
 WHERE xact_start < now() - interval '1 hour'
 ORDER BY xact_start;

SELECT slot_name, active, wal_status,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn))
  FROM pg_replication_slots;

A reporting job had been sitting in idle in transaction for eleven days, and there was an inactive replication slot left over from a replica that had been decommissioned in the autumn. Either one alone will pin the xmin horizon and make every vacuum on every table a no-op for the tuples you care about. Dropping the slot and killing the session did more in a minute than any amount of tuning.

What we set

The per-table override matters more than the global ones. Small tables are fine with the defaults; it's the handful of large, hot tables that need a scale factor near zero and a fixed threshold instead.

Reclaiming the existing bloat is a separate job — plain vacuum only makes the space reusable, it doesn't return it to the filesystem. That needed pg_repack and a maintenance window. Worth doing once. Much more worth not needing again.

The alert we didn't have

We monitored table size and we monitored query latency, and both of those are lagging indicators of this problem by weeks. What we monitor now is the age of the oldest transaction, the existence of inactive replication slots, and dead tuples as a fraction of live ones per table. All three would have fired in October.