Why is autovacuum not keeping up (and how do I safely make it more aggressive)?
Quick answer: Autovacuum falls behind for one of three reasons, and each needs a different fix. Either it is not being triggered often enough (the default
autovacuum_vacuum_scale_factorof 0.2 scales with table size, so a huge table waits for a huge pile of dead tuples); or it runs and cannot remove anything because a long transaction, idle session, replication slot, standby feedback, or prepared transaction is pinning the xmin horizon; or it runs constantly and never finishes, throttled by the cost delay or simply outrun by your write rate. Diagnose which branch applies before you tune anything.
The decision path
Section titled “The decision path”Work out which branch applies before you change a parameter:
- Are dead tuples growing on a specific table? → It is a triggering problem.
The fix is a per-table
scale_factoroverride. - Did autovacuum run recently but
n_dead_tupdid not drop? → It is an xmin horizon problem. Tuning autovacuum harder will do nothing. Find the blocker. - Is autovacuum running constantly and never finishing? → It is a throughput problem: cost delay, worker count, or a table simply too big for one pass.
How do I confirm autovacuum is behind?
Section titled “How do I confirm autovacuum is behind?”Start with the per-table stats view. This is the most useful query:
SELECT schemaname, relname, n_live_tup, n_dead_tup, round(n_dead_tup::numeric / nullif(n_live_tup, 0), 3) AS dead_ratio, last_autovacuum, last_autoanalyze, autovacuum_countFROM pg_stat_user_tablesWHERE n_dead_tup > 1000ORDER BY n_dead_tup DESCLIMIT 20;| Signal | What it means |
|---|---|
n_dead_tup large and rising, last_autovacuum hours/days old | Autovacuum is not being triggered: a threshold problem |
last_autovacuum is NULL on a busy table | It has never run there. Check the trigger math below |
last_autovacuum is recent but n_dead_tup stayed high | Vacuum ran and could not remove rows: an xmin horizon problem |
autovacuum_count climbing fast on one table | It is vacuuming in a loop, likely losing to write volume |
These are estimates from the cumulative statistics collector. They can be stale, and they reset if statistics are reset or the cluster is re-initialized. Use them for direction, not accounting.
What is vacuuming right now?
Section titled “What is vacuuming right now?”SELECT p.pid, p.datname, c.relname, p.phase, p.heap_blks_total, p.heap_blks_scanned, p.heap_blks_vacuumed, p.index_vacuum_count, a.state, now() - a.xact_start AS running_forFROM pg_stat_progress_vacuum pJOIN pg_stat_activity a USING (pid)LEFT JOIN pg_class c ON c.oid = p.relidORDER BY running_for DESC;heap_blks_scanned / heap_blks_total gives you a progress percentage. If
index_vacuum_count is greater than 1, vacuum is making multiple passes over the
indexes because its dead-tuple workspace filled up. The table has more dead tuples
than one pass can carry, so consider raising autovacuum_work_mem (or
maintenance_work_mem, which it falls back to).
Is it an anti-wraparound vacuum?
Section titled “Is it an anti-wraparound vacuum?”SELECT c.relname, greatest(age(c.relfrozenxid), age(t.relfrozenxid)) AS xid_age, age(c.relfrozenxid) AS heap_xid_age, age(t.relfrozenxid) AS toast_xid_age, pg_size_pretty(pg_total_relation_size(c.oid)) AS sizeFROM pg_class cJOIN pg_namespace n ON n.oid = c.relnamespaceLEFT JOIN pg_class t ON t.oid = c.reltoastrelidWHERE c.relkind IN ('r', 'm') AND n.nspname NOT IN ('pg_catalog', 'information_schema')ORDER BY greatest(age(c.relfrozenxid), age(t.relfrozenxid)) DESCLIMIT 20;The LEFT JOIN on reltoastrelid is not optional. A TOAST table has
relkind = 't' and its own relfrozenxid, so a query filtered to 'r' and
'm' cannot see it, and the TOAST relation is frequently the oldest thing
in the database, because the rows that get TOASTed are the ones that rarely get
updated. Miss it and you will stare at a heap age of 40 million while a TOAST
relation sits at 190 million and the anti-wraparound vacuum you cannot explain
keeps launching. (greatest() ignores NULLs, so tables with no TOAST relation
still report their heap age correctly.)
When xid_age exceeds autovacuum_freeze_max_age (default 200 million),
PostgreSQL launches an anti-wraparound autovacuum. These are not optional: they run even if autovacuum is disabled, and unlike ordinary autovacuum they do
not yield the table when a conflicting lock request arrives. If a table’s
xid_age is climbing toward that limit and never resetting, something is blocking
freezing. This requires prompt attention.
Why does not autovacuum trigger on my big table?
Section titled “Why does not autovacuum trigger on my big table?”The trigger is arithmetic and is often misunderstood:
vacuum threshold = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × estimated live rowsDefaults are autovacuum_vacuum_threshold = 50 and
autovacuum_vacuum_scale_factor = 0.2.
Work that through:
| Table size | Dead tuples needed before autovacuum starts |
|---|---|
| 1,000 rows | ~250 |
| 1,000,000 rows | ~200,050 |
| 100,000,000 rows | ~20,000,050 |
| 1,000,000,000 rows | ~200,000,050 (PG ≤17); capped at 100,000,000 on PG18+ |
On a billion-row table, the default waits for 200 million dead tuples before cleanup begins. Once it does begin, it is a single large vacuum that can take hours, while more dead tuples accumulate.
Analyze has its own equivalent pair (autovacuum_analyze_threshold, default 50,
and autovacuum_analyze_scale_factor, default 0.1) driven by
n_mod_since_analyze. Stale statistics on a high-churn table produce bad plans,
so this matters even when bloat does not.
Modern PostgreSQL also vacuums insert-only tables via
autovacuum_vacuum_insert_threshold / autovacuum_vacuum_insert_scale_factor
(added in PostgreSQL 13). Before that, an append-only table could go unvacuumed
until anti-wraparound forced it, which is why old, insert-only tables sometimes
produce a multi-hour freeze vacuum.
Why does vacuum run but dead tuples stay high?
Section titled “Why does vacuum run but dead tuples stay high?”This is the branch where tuning is ineffective.
Vacuum can only remove a dead row version if no transaction anywhere in the system might still need to see it. That boundary is the xmin horizon. Anything holding an old snapshot holds the horizon back, and vacuum will scan the whole table, use I/O, and remove nothing.
Four things hold the horizon:
1. Long-running and idle-in-transaction sessions. A session that ran BEGIN and then went to lunch pins the horizon at its snapshot.
SELECT pid, state, now() - xact_start AS xact_age, now() - state_change AS idle_for, backend_xmin, age(backend_xmin) AS backend_xmin_age, left(query, 80) AS queryFROM pg_stat_activityWHERE backend_xmin IS NOT NULL OR state = 'idle in transaction'ORDER BY age(backend_xmin) DESC NULLS LASTLIMIT 20;Sort by age(backend_xmin), not by xact_start. A transaction can be hours old
and pin nothing. xact_start records when it began, while backend_xmin is the
snapshot horizon it actually holds. The largest backend_xmin_age is your ceiling; a long xact_age with a null backend_xmin is not your problem. See
how to fix idle in transaction connections
for the application-side causes and the idle_in_transaction_session_timeout
guard.
2. Replication slots. A slot holds xmin / catalog_xmin so a consumer that
reconnects can still resolve rows it has not processed.
SELECT slot_name, slot_type, active, xmin, catalog_xmin, restart_lsnFROM pg_replication_slotsORDER BY active, slot_name;An inactive slot with a non-null xmin is an unused consumer holding the
database’s cleanup horizon. It also pins WAL; see
my pg_wal directory filled the disk.
3. hot_standby_feedback = on with a long query on a standby. The standby
reports its oldest snapshot upstream, and the primary holds the horizon so the
standby’s query does not hit a recovery conflict. That is the purpose of the
setting, but a reporting query running for hours on a replica blocks cleanup on the primary.
Run this on the primary: the standby’s feedback lands in pg_stat_replication, not in pg_stat_activity:
SELECT application_name, client_addr, state, backend_xmin, age(backend_xmin) AS backend_xmin_ageFROM pg_stat_replicationWHERE backend_xmin IS NOT NULLORDER BY age(backend_xmin) DESC;If the standby connects through a physical replication slot, its xmin is
tracked on the slot instead and pg_stat_replication.backend_xmin reads null; that case is covered by the pg_replication_slots query above. Check both.
4. Prepared transactions. A two-phase commit left dangling holds a snapshot indefinitely:
SELECT gid, prepared, owner, database FROM pg_prepared_xacts ORDER BY prepared;These are rare and usually mean a transaction manager crashed. Resolving them
(COMMIT PREPARED / ROLLBACK PREPARED) is a dangerous operation. It finalizes
or discards real business data and needs the owning application’s input, not a
guess at 3am.
A single query for how far back the horizon sits:
SELECT (SELECT max(age(backend_xmin)) FROM pg_stat_activity) AS max_backend_xmin_age, (SELECT max(age(xmin)) FROM pg_replication_slots) AS max_slot_xmin_age, (SELECT max(age(catalog_xmin)) FROM pg_replication_slots) AS max_slot_catalog_xmin_age, (SELECT max(age(backend_xmin)) FROM pg_stat_replication) AS max_standby_feedback_age, (SELECT max(age(transaction)) FROM pg_prepared_xacts) AS max_prepared_xact_age;catalog_xmin has to be its own column. It is not covered by xmin. A slot
pins two separate horizons: xmin is the oldest transaction whose table rows
must be retained, catalog_xmin the oldest whose system catalog rows must be.
A logical slot frequently has a null xmin and a very old catalog_xmin,
which means a max(age(xmin))-only query reports nothing at all for the most
common slot-related bloat source there is. The resulting catalog bloat, which no
amount of autovacuum tuning will touch, stays invisible.
All of those columns have to be in the query (four causes, five columns) because a slot pins two horizons. A standby running with hot_standby_feedback = on and no replication slot pins the horizon from pg_stat_replication alone; it appears in none of the other three, so a three-column version of this query reports a horizon that looks fine while autovacuum keeps failing to reclaim anything.
Whichever of those is largest is the thing you have to fix. Autovacuum settings will not move it.
Is autovacuum throttled?
Section titled “Is autovacuum throttled?”Autovacuum uses cost-based delay: it accumulates a cost per page it touches, and when the accumulated cost hits the limit it sleeps.
| Parameter | Typical default | Effect |
|---|---|---|
autovacuum_vacuum_cost_limit | -1 (inherits vacuum_cost_limit, default 200) | Work allowed before sleeping |
autovacuum_vacuum_cost_delay | 2ms in current versions (was much higher in older ones) | How long it sleeps |
autovacuum_max_workers | 3 | Concurrent autovacuum workers |
autovacuum_naptime | 1min | How often the launcher checks a database |
The cost limit is shared, not per-worker. The budget is divided among the
running workers. Raising autovacuum_max_workers without raising
autovacuum_vacuum_cost_limit gives you more workers each going proportionally
slower: the same total throughput, spread thinner. To increase cleanup throughput,
raise the cost limit or lower the delay. To cover more tables concurrently, raise
workers and the cost limit together.
autovacuum_max_workers is reloadable from PostgreSQL 18 onward (SELECT pg_reload_conf()), capped by the restart-only autovacuum_worker_slots (typically 16, but initdb lowers it when kernel limits require; check SHOW autovacuum_worker_slots rather than assuming). On PostgreSQL 17 and earlier it requires a restart. Most of the others are reloadable
with SELECT pg_reload_conf() after editing postgresql.conf. Plan accordingly:
do not promise a restart-requiring change as a live fix.
On modern SSD-backed storage the historical defaults are conservative. Raising
autovacuum_vacuum_cost_limit to 1000 to 2000 (and leaving the delay at 2ms)
is a common starting point on a server that is not I/O saturated. Watch disk
utilization after the change.
How do I safely make autovacuum more aggressive?
Section titled “How do I safely make autovacuum more aggressive?”Prefer per-table overrides over global changes. Global tuning affects every table including small ones that were fine; per-table overrides target the churn.
-- High-churn table: vacuum at ~1% dead instead of ~20%ALTER TABLE orders SET ( autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_threshold = 1000, autovacuum_analyze_scale_factor = 0.005, autovacuum_analyze_threshold = 1000);Setting autovacuum storage parameters is a catalog-only change. It takes a
SHARE UPDATE EXCLUSIVE lock, does not rewrite the table, and does not block
reads or ordinary writes. It does conflict with concurrent schema changes and
with vacuum on the same table, so it can wait briefly behind a running autovacuum.
Use a short lock_timeout if the table is hot.
To inspect or remove overrides:
SELECT relname, reloptions FROM pg_class WHERE reloptions IS NOT NULL;
ALTER TABLE orders RESET (autovacuum_vacuum_scale_factor);Concrete starting points by workload shape
Section titled “Concrete starting points by workload shape”These are starting points to measure from, not universal truths. Set them, then
re-check n_dead_tup and last_autovacuum over a few days.
Queue / job table (rows inserted, worked, deleted within minutes; small live set, enormous churn):
ALTER TABLE job_queue SET ( autovacuum_vacuum_scale_factor = 0.0, -- ignore table size entirely autovacuum_vacuum_threshold = 500, -- vacuum every 500 dead tuples autovacuum_analyze_scale_factor = 0.0, autovacuum_analyze_threshold = 500, autovacuum_vacuum_cost_delay = 0 -- do not throttle this one);Setting scale_factor = 0 makes the trigger a flat count, which suits a workload
where the live row count swings between 10 and 100,000.
Large, frequently-updated table (100M+ rows, steady UPDATE traffic):
ALTER TABLE events SET ( autovacuum_vacuum_scale_factor = 0.005, -- ~500k dead tuples at 100M rows autovacuum_vacuum_threshold = 10000, autovacuum_analyze_scale_factor = 0.002);The goal is many small vacuums rather than one infrequent large vacuum. Small passes finish, keep the visibility map warm, and let index-only scans work.
Append-mostly table (logs, time-series, few updates): the insert-driven
thresholds matter more than the dead-tuple ones. Lowering
autovacuum_vacuum_insert_scale_factor keeps the visibility map current and
spreads freezing work out instead of saving it up for an anti-wraparound vacuum.
Small, hot lookup table (a few thousand rows, updated constantly): the defaults
already vacuum it often, but the fixed threshold of 50 can still be too coarse
relative to how fast it churns. Drop autovacuum_vacuum_threshold to between 25 and 50 with a
scale_factor near 0.01.
Partitioned tables: set these on the partitions, not the parent. The parent is empty; autovacuum works on the physical relations.
What about just running VACUUM manually?
Section titled “What about just running VACUUM manually?”Running VACUUM (VERBOSE, ANALYZE) tablename; is safe: it takes a
SHARE UPDATE EXCLUSIVE lock and does not block reads or writes. It is a reasonable
thing to do at 3am to clean a specific table, and VERBOSE tells you exactly how many tuples it could not remove and why.
VACUUM FULL is dangerous during an incident: it takes an ACCESS EXCLUSIVE
lock for the entire rewrite, blocking everything against that table, and it needs
free disk equal to the new copy. It reclaims disk space, which plain vacuum mostly
does not. See
what is table bloat and how do I fix it
for when that trade is worth making and why pg_repack is usually the better tool.
VACUUM will not help at all if the xmin horizon is
pinned. Check that first, every time.
Why does AI-generated code cause this?
Section titled “Why does AI-generated code cause this?”Your AI coding agent writes an UPDATE ... SET status = ... that is correct in
isolation. What it cannot see is that the job runs every 30 seconds against 40 million
rows, that the table has default autovacuum settings, and that a background worker
holds a transaction open for the length of its batch.
Three patterns are worth watching for:
- A long transaction wrapping a batch job.
BEGIN, process 100k records with external API calls in between,COMMIT. The code is correct, but it also pins the xmin horizon for the whole run and stops cleanup cluster-wide. - Update-every-row maintenance jobs. A nightly “recompute
scorefor all rows” creates one dead tuple per row, per night. One full pass leaves a dead tuple for every row, far past the 20% default trigger, so cleanup lands all at once. - A soft-delete pattern with no vacuum consideration. Flipping
deleted_atis anUPDATE, so soft deletes generate exactly as much dead-tuple churn as hard deletes, plus the rows stay.
None of this is visible in the source. It depends on write volume, table size, and settings that exist only in the running database.
How do I stop it coming back?
Section titled “How do I stop it coming back?”- Alert on
n_dead_tupratio andlast_autovacuumage for your top-churn tables. Silence here is how you find out at 3am. - Alert on
age(relfrozenxid)at some fraction ofautovacuum_freeze_max_age(60% to 70% gives real lead time). Wraparound risk is the one that becomes an outage. Include TOAST relations viareltoastrelid: an alert that only reads heaprelfrozenxidwill stay green while a TOAST relation walks into wraparound. - Alert on inactive replication slots. They cause both this problem and the pg_wal one.
- Set
idle_in_transaction_session_timeoutso a forgottenBEGINcannot pin the horizon indefinitely. This terminates the session, which is safe for well-behaved apps that retry but worth testing before rolling out broadly. - Make autovacuum settings part of the migration that creates a high-churn table, so the override ships with the schema instead of being discovered later.
How do I tell if autovacuum is keeping up in PostgreSQL?
Query pg_stat_user_tables for n_dead_tup, n_live_tup, and last_autovacuum. If dead tuples keep climbing on a table whose last_autovacuum is old, or is NULL, autovacuum is behind on that table. Check pg_stat_progress_vacuum to see what is running right now and how far along it is.
Why does autovacuum wait so long on large tables?
The trigger is autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * estimated row count. With the default scale factor of 0.2, on PostgreSQL 17 and earlier a one-billion-row table must accumulate roughly 200 million dead tuples before autovacuum starts; PostgreSQL 18 caps this at 100 million via autovacuum_vacuum_max_threshold. The threshold scales with the table, so big tables wait far too long by default.
Why does VACUUM run but dead tuples stay high?
Vacuum can only remove row versions older than the oldest transaction that might still need to see them, the xmin horizon. A long-running transaction, an idle-in-transaction session, an unused replication slot, a prepared transaction, or a standby with hot_standby_feedback on can hold that horizon back. Vacuum then runs, does work, and removes nothing.
Is ALTER TABLE SET autovacuum_vacuum_scale_factor safe to run in production?
Yes. Setting autovacuum storage parameters is a catalog-only change that takes a SHARE UPDATE EXCLUSIVE lock. It does not rewrite the table and does not block reads or normal writes. It briefly conflicts with other schema changes and with vacuum on that table.
Should I raise autovacuum_max_workers?
Only together with autovacuum_vacuum_cost_limit. The cost budget is shared across workers, so adding workers alone just makes each one slower. On PostgreSQL 18 it is reloadable, capped by the restart-only autovacuum_worker_slots; check SHOW autovacuum_worker_slots rather than assuming the typical 16, because initdb lowers it when kernel limits require. On 17 and earlier it requires a restart.