Autovacuum, explained with a bathtub

Picture a bathtub. Water flows in from the tap and out through the drain. As long as the drain keeps up, the water level stays low. If the drain is too slow, or something blocks it, the tub fills up and eventually overflows.

PostgreSQL tables work the same way, and the water is dead rows.

The tap: dead rows

PostgreSQL uses MVCC (multi-version concurrency control). When you UPDATE a row, Postgres doesn't overwrite it in place. It writes a new version of the row and marks the old one as expired. DELETE works the same way: the row is only marked as gone.

This is what lets readers and writers work at the same time without blocking each other: a query that started before your update still sees the old version. But it means every UPDATE and DELETE leaves a dead row behind. A busy orders table that updates each row's status five times creates four dead rows per order.

Dead rows take up space on disk and in memory, and every sequential scan still has to step over them. That's bloat: the table is bigger than its live data, and queries get slower for no visible reason.

The drain: VACUUM

VACUUM walks through a table, finds row versions that no running transaction can see any more, and marks their space as free for reuse. New rows then fill those gaps instead of growing the file. (A plain VACUUM doesn't usually give space back to the operating system; it makes it reusable inside the table.)

You don't run VACUUM by hand in production. The autovacuum launcher wakes up regularly (every minute by default, autovacuum_naptime), checks each table's statistics and starts a worker for tables that need it.

When the drain opens

Autovacuum vacuums a table once its dead rows pass this threshold:

-- the default trigger, per table
dead rows > autovacuum_vacuum_threshold            -- 50 rows
          + autovacuum_vacuum_scale_factor * rows  -- 0.2 = 20% of the table

For a 10,000-row table that's about 2,050 dead rows: fine. For a 200-million-row table it's 40 million dead rows before the drain even opens. By then the table is bloated, and the vacuum itself is a huge job.

The fix is to lower the scale factor on your large, busy tables, so they're vacuumed little and often:

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,  -- vacuum after 1% instead of 20%
  autovacuum_analyze_scale_factor = 0.02  -- keep planner statistics fresh too
);

To see which tables are filling up, ask the statistics views:

SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(100.0 * n_dead_tup / greatest(n_live_tup, 1), 1) AS dead_pct,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

What blocks the drain

VACUUM can only remove a dead row when no transaction anywhere might still need to see it. So the oldest open transaction in the database sets a limit for every table: nothing newer than its snapshot can be cleaned up.

That makes these the usual suspects when bloat keeps growing even though autovacuum is running:

• Long-running transactions: a report that runs for six hours holds back cleanup for six hours.
• Sessions left "idle in transaction": an application that opens a transaction and then waits for a user, or forgets to commit.
• Abandoned replication slots: a slot whose consumer has gone away keeps old row versions (and WAL) around.
• hot_standby_feedback on a replica that runs long queries: the replica asks the primary to keep old rows for it.

Find the oldest transactions like this:

SELECT pid, state, now() - xact_start AS running_for, left(query, 60) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 5;

Two settings stop the worst of it: idle_in_transaction_session_timeout ends sessions that sit idle inside a transaction, and statement_timeout (set per role or per application) stops runaway queries.

Making the drain faster

Autovacuum is deliberately gentle so it doesn't hurt your workload. It does a bit of work, then pauses, controlled by a cost budget:

• autovacuum_vacuum_cost_limit: how much work a worker does before pausing. By default it inherits vacuum_cost_limit, which is 200, a value that dates from slow spinning disks.
• autovacuum_vacuum_cost_delay: how long it pauses (2 ms by default in current versions).
• autovacuum_max_workers: how many tables can be vacuumed at once (3 by default). Note that the cost limit is shared between the running workers.

On modern SSD or NVMe storage, raising the cost limit (to somewhere around 1,000 to 2,000) lets vacuum keep up without anyone noticing. Change one thing at a time and watch disk I/O while you do.

ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 1000;
SELECT pg_reload_conf();

The overflow: wraparound

There's one more reason vacuum matters, and it's the serious one. PostgreSQL transaction IDs are 32-bit counters that eventually wrap around. VACUUM "freezes" old rows so they stay visible forever, regardless of the counter.

If a table goes too long without being frozen, autovacuum starts an aggressive anti-wraparound vacuum (at autovacuum_freeze_max_age, 200 million transactions by default) that runs even if you've disabled autovacuum for that table. If that can't complete, PostgreSQL will eventually stop accepting new writes to protect your data. You never want to get there, and the same culprits from above (long transactions, stuck slots) are what usually cause it.

Keep an eye on the oldest unfrozen transaction age per database:

SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;

If that number keeps climbing toward hundreds of millions, find out why vacuum isn't finishing.

What to take away

The bathtub only overflows when the tap runs faster than the drain, or the drain is blocked. So:

1. Open the drain earlier on big tables with a per-table scale factor.
2. Unblock it by hunting down long transactions, idle sessions and stale replication slots.
3. Widen it by letting autovacuum work faster on modern storage.

Do those three things and autovacuum quietly does its job, which is exactly how it should be.