When autovacuum cannot keep up: six failure modes · PostgreSQL

“Autovacuum isn’t keeping up” is a symptom, not a diagnosis, and the six causes need six different responses. The most common mistake in this whole part of the course is applying the fix for mode 3 to a table suffering from mode 1, then concluding that autovacuum is unreliable.

Work through them in order. The first three questions eliminate most cases in under a minute.

Mode 1: the horizon is held

Symptom. n_dead_tup climbs and never falls. last_autovacuum is recent. autovacuum_count increments normally. Everything looks like vacuum is running, because it is.

Diagnostic. One line of VACUUM VERBOSE output settles it.

Read-only / Safethe line that identifies mode 1— Real output from 18.6, captured in lesson VII-05 while one session held a snapshot.

$ psql -U postgres -c "VACUUM (VERBOSE) churn"

tuples: 0 removed, 550000 remain, 500000 are dead but not yet removable
removable cutoff: 683682, which was 10 XIDs old when operation ended

Then find the holder:

SELECT 'oldest running xact'     AS source,
       coalesce(max(age(backend_xmin))::text, 'none') AS xid_age
  FROM pg_stat_activity WHERE backend_xmin IS NOT NULL
UNION ALL
SELECT 'oldest prepared xact',
       coalesce(max(age(transaction))::text, 'none') FROM pg_prepared_xacts
UNION ALL
SELECT 'oldest replication slot',
       coalesce(max(age(xmin))::text, 'none') FROM pg_replication_slots
UNION ALL
SELECT 'oldest replica feedback',
       coalesce(max(age(backend_xmin))::text, 'none') FROM pg_stat_replication;

Fix. End the holder. Set idle_in_transaction_session_timeout. Drop abandoned replication slots. Reconsider hot_standby_feedback if a replica’s long reports are the cause.

What does not work. Any autovacuum setting whatsoever. This is the mode where tuning thresholds actively wastes resources.

Mode 2: insufficient throughput

Symptom. Vacuum removes tuples — the counts are not zero — but the table keeps growing anyway. Autovacuum on this table is nearly always running.

Diagnostic. Compare the rate of dead tuple production against what a pass achieves. From the autovacuum log:

LOG:  automatic vacuum of table "public.orders": index scans: 1
      tuples: 1240000 removed, 88000000 remain, 0 are dead but not yet removable
      system usage: ... elapsed: 903.12 s

1.24 million tuples removed in fifteen minutes. Now:

-- run twice, a minute apart
SELECT relname, n_tup_upd + n_tup_del AS churn FROM pg_stat_user_tables
 WHERE relname = 'orders';

If churn per fifteen minutes exceeds what a pass removed in fifteen minutes, vacuum cannot catch up regardless of how often it starts.

Also check pg_stat_progress_vacuum while a pass runs:

SELECT p.pid, p.phase, p.heap_blks_scanned, p.heap_blks_total,
       round(100.0 * p.heap_blks_scanned / nullif(p.heap_blks_total,0), 1) AS pct,
       p.index_vacuum_count,
       a.query
  FROM pg_stat_progress_vacuum p JOIN pg_stat_activity a USING (pid);

Fix. Raise autovacuum_vacuum_cost_limit or lower autovacuum_vacuum_cost_delay — the arithmetic from lesson VIII-02 says the default ceiling is around 39 MB/s of dirtied pages, shared. Raise maintenance_work_mem if index_vacuum_count exceeds 1. Reduce the churn itself: batch updates, or split the hot columns into a narrower table.

What does not work. Raising autovacuum_max_workers. The budget is shared, so this makes each worker slower.

Mode 3: thresholds too lax

Symptom. n_dead_tup sits persistently below the computed threshold, and nothing happens because nothing is due.

Diagnostic. The measurement from lesson VIII-03: 67,206 dead tuples, 7.04% of the table, threshold 100,050, no vacuum coming.

Fix. Per-table autovacuum_vacuum_scale_factor and autovacuum_vacuum_threshold.

This is the only mode that thresholds fix. It is also the one people assume they have.

Mode 4: every pass is cancelled

Symptom. last_autovacuum is null or very old despite obvious churn. autovacuum_count may not increment at all.

Diagnostic. The server log.

ERROR:  canceling autovacuum task
CONTEXT:  while vacuuming "public.orders"

Autovacuum holds SHARE UPDATE EXCLUSIVE and yields whenever another session wants a conflicting lock. Frequent DDL on a table means the worker starts, makes partial progress, is cancelled, and starts over from the beginning next time.

Fix. Reduce DDL frequency on that table, or schedule it. Partitioned tables with hourly attach/detach are the classic case; so is an ORM that issues ALTER TABLE on every deploy. Where the DDL is unavoidable, run VACUUM manually in a window when it will not be interrupted — a manual vacuum does not yield.

What does not work. Thresholds. The work never completes regardless of how often it is scheduled.

Mode 5: autovacuum is disabled

Symptom. Same as mode 4 from the statistics views: nothing has happened.

Diagnostic. Two places, because there are two ways to be off.

-- cluster-wide
SHOW autovacuum;

-- per table
SELECT c.oid::regclass AS relation, c.reloptions
  FROM pg_class c
 WHERE c.relkind IN ('r','m','p')
   AND c.reloptions::text LIKE '%autovacuum_enabled%';

Fix. Turn it on. If it was turned off deliberately, find out why before turning it on — a cluster where someone disabled autovacuum during an incident and never re-enabled it may have a very large backlog that will all be attempted at once.

Turn it back on during a quiet period, and consider raising the cost limit temporarily so the catch-up completes rather than dragging for days.

Mode 6: worker pool saturation

Symptom. Individual tables are fine. Many tables are slightly behind. The three workers are always busy, and small tables wait behind large ones.

Diagnostic.

SELECT count(*) AS running_workers
  FROM pg_stat_activity
 WHERE backend_type = 'autovacuum worker';

Consistently equal to autovacuum_max_workers means the pool is saturated. Combine with a count of tables that are eligible but not being processed.

Fix. This is the one case where raising autovacuum_max_workers genuinely helps — the constraint is how many tables can be in progress at once, not throughput on any one of them. In PostgreSQL 18 this can be done without a restart, up to autovacuum_worker_slots.

Raise autovacuum_vacuum_cost_limit at the same time, or the extra workers will simply divide the same budget more finely.

Lowering autovacuum_naptime shortens the interval between visits to any given database, which helps when tables cross their thresholds shortly after a worker has already been and gone.

The decision table

Mode	`last_autovacuum`	`n_dead_tup`	Distinguishing evidence	Fix
1 Horizon held	Recent	Climbs	“dead but not yet removable”	End the holder
2 Throughput	Recent, always running	Climbs	Removal rate < churn rate	Cost limit, delay, `maintenance_work_mem`
3 Thresholds	Old but real	Steady below threshold	`n_dead_tup` < computed threshold	Per-table scale factor
4 Cancelled	Null or stale	Climbs	`canceling autovacuum task`	Reduce DDL; manual vacuum
5 Disabled	Null	Climbs	`reloptions` or `SHOW autovacuum`	Re-enable, carefully
6 Saturation	Stale on many tables	Mild on many	Workers always at max	More workers and more budget

The failsafe, and what it tells you about priorities

There is a point at which PostgreSQL stops being polite.

vacuum_failsafe_age, default 1,600,000,000, is the transaction age at which vacuum abandons its own throttling. When a table crosses it, vacuum:

ignores the cost delay entirely, running at full speed;
skips index vacuuming, because removing index entries is expensive and freezing is what is urgent;
and continues regardless of what else is happening on the system.

It also says so in the log, with a WARNING naming the table it is bypassing nonessential maintenance for.

This course did not reproduce that warning. Triggering it requires a table whose age approaches vacuum_failsafe_age, which is 1.6 billion transactions by default, and no honest test in a disposable container produces that. The behaviour above is from the documentation, not from a capture, and it is described here rather than quoted so that nothing on this page pretends to be output it is not.

Two things to take from this.

Seeing the failsafe trigger is a monitoring failure, not a vacuum failure. The gap between autovacuum_freeze_max_age at 200 million and the failsafe at 1.6 billion is enormous — on a cluster consuming a thousand transactions per second, roughly sixteen days during which the anti-wraparound vacuum was supposed to be running and something prevented it. An alert at 90% of autovacuum_freeze_max_age gives you that entire window.

The priority ordering is deliberate and worth internalising. Given the choice between reclaiming space and preventing a wraparound shutdown, PostgreSQL abandons space every time. Bloat is expensive; a cluster refusing transactions is an outage. Every escalation in this system — anti-wraparound vacuum ignoring autovacuum_enabled, refusing to yield its lock, then bypassing index cleanup — follows the same ordering.

What to take from this

Six modes, three questions, one minute. Diagnose before tuning.
Only mode 3 is fixed by thresholds, and it is the one everyone assumes they have.
Modes 4 and 5 are silent in the statistics views and accumulate freeze debt while looking like nothing at all.
More workers helps mode 6 and nothing else.
The failsafe firing means roughly two weeks of unnoticed warning.

Cross-course references

Linux for Production Sysadmins — Part XLI (Storage Performance) covers proving that the throttle rather than the device is the limit, and Part LXXIX (Troubleshooting methodology) covers working through six candidate causes rather than assuming the first.
Observability for Production Sysadmins — Part CII (Slow queries) covers correlating a vacuum that cannot keep up with the query latency it eventually produces.