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 they need different fixes. Either it is not being triggered often enough, the default autovacuum_vacuum_scale_factor of 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 before you tune anything.

The decision path

Most autovacuum guides hand you a parameter list. Start here instead:

1. Are dead tuples growing on a specific table? → It is a triggering problem. Per-table scale_factor override.
2. Did autovacuum run recently but n_dead_tup did not drop? → It is an xmin horizon problem. Tuning autovacuum harder will do nothing. Find the blocker.
3. Is autovacuum running constantly and never finishing? → It is a throughput problem. Cost delay, worker count, or the table is simply too big for one pass.

Each branch below.

How do I confirm autovacuum is behind?

Start with the per-table stats view. This is the single 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_count
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC
LIMIT 20;

Signal	What it means
n_dead_tup large and rising, last_autovacuum hours/days old	Autovacuum is not being triggered, 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, xmin horizon problem
autovacuum_count climbing fast on one table	It is vacuuming in a loop, likely losing to write volume

A caveat: 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 for accounting.

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_for
FROM pg_stat_progress_vacuum p
JOIN pg_stat_activity a USING (pid)
LEFT JOIN pg_class c ON c.oid = p.relid
ORDER BY running_for DESC;

heap_blks_scanned / heap_blks_total gives you a real 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, a sign the table has far more dead tuples than one pass can carry, and a reason to raise autovacuum_work_mem (or maintenance_work_mem, which it falls back to).

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 size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_class t ON t.oid = c.reltoastrelid
WHERE c.relkind IN ('r', 'm')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY greatest(age(c.relfrozenxid), age(t.relfrozenxid)) DESC
LIMIT 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 approaches 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, and that is an urgent problem, not a tuning preference.

Why does not autovacuum trigger on my big table?

The trigger is arithmetic, and it is the single most misunderstood part of autovacuum:

vacuum threshold = autovacuum_vacuum_threshold
                 + autovacuum_vacuum_scale_factor × estimated live rows

Defaults 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+

That is the whole problem. The default scales the wrong way. On a billion-row table you wait for 200 million dead tuples, hundreds of gigabytes of bloat, before cleanup even begins. And once it does begin. It is a single enormous vacuum that takes hours, during which more dead tuples pile up behind it.

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 surprise multi-hour freeze vacuum.

Why does vacuum run but dead tuples stay high?

This is the branch where tuning is useless, and it is the one people miss.

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, burn 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 query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
   OR state = 'idle in transaction'
ORDER BY age(backend_xmin) DESC NULLS LAST
LIMIT 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_lsn
FROM pg_replication_slots
ORDER BY active, slot_name;

An inactive slot with a non-null xmin is a dead consumer holding your database hostage. 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 point 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_age
FROM pg_stat_replication
WHERE backend_xmin IS NOT NULL
ORDER 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. Catalog bloat that no amount of autovacuum tuning will touch, 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. Nothing you do to autovacuum settings will move it.

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

Two things about this that surprise people:

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. If you want more cleanup throughput, raise the cost limit (or lower the delay); if you want more tables covered 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 extremely conservative. Raising autovacuum_vacuum_cost_limit to 1000–2000 (and leaving the delay at 2ms) is a common, low-risk starting point on a server that is not I/O saturated. Watch disk utilization after the change; that is the resource you are spending.

How do I safely make autovacuum more aggressive?

Prefer per-table overrides over global changes. Global tuning affects every table including tiny 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
);

This is safe. 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

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 is exactly what you want when 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 quarterly monster. 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 25–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?

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 get a specific table cleaned up now, 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.

And to state the obvious: VACUUM will not help at all if the xmin horizon is pinned. Check that first, every time.

Why does aI-generated code cause this?

Your AI coding agent (Claude Code, Cursor) writes an UPDATE ... SET status = ... that is entirely 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 specific patterns worth watching for:

• A long transaction wrapping a batch job. BEGIN, process 100k records with external API calls in between, COMMIT. Correct code. It also pins the xmin horizon for the whole run and stops cleanup cluster-wide.
• Update-every-row maintenance jobs. A nightly "recompute score for all rows" creates one dead tuple per row, per night. On a large table that never reaches the 20% default trigger fast enough to matter, until it does, all at once.
• A soft-delete pattern with no vacuum consideration. Flipping deleted_at is an UPDATE, 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 is a property of write volume, table size, and settings that only exist in the running database.

How do I stop it coming back?

• Alert on n_dead_tup ratio and last_autovacuum age for your top-churn tables. Silence here is how you find out at 3am.
• Alert on age(relfrozenxid) at some fraction of autovacuum_freeze_max_age (60–70% gives real lead time). Wraparound risk is the one that becomes an outage. Include TOAST relations via reltoastrelid, an alert that only reads heap relfrozenxid will 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_timeout so a forgotten BEGIN cannot pin the horizon indefinitely. Note this terminates the session, safe for well-behaved apps that retry, 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 DBGorilla helps

DBGorilla connects read-only and gives your AI coding agent (Claude Code, Cursor) the actual autovacuum picture: which tables have dead tuples piling up, when each last got vacuumed, what is currently running, and, the part that usually cracks the case, whether a long transaction or replication slot is holding the xmin horizon back so vacuum cannot remove anything. Your agent gets the real numbers alongside your code, so it can tell you which branch of the decision path you are on and what the override should be. It surfaces and explains; it does not run VACUUM, change your configuration, or terminate sessions. Get started free →

FAQ

How do I tell if autovacuum is keeping up? Query pg_stat_user_tables for n_dead_tup, n_live_tup, and last_autovacuum. Rising dead tuples with an old or NULL last_autovacuum means it is behind on that table. pg_stat_progress_vacuum shows what is running 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 rows. With the default 0.2 scale factor, on PostgreSQL 17 and earlier a billion-row table needs about 200 million dead tuples before autovacuum starts; PostgreSQL 18 caps that at 100 million via autovacuum_vacuum_max_threshold. Override scale_factor per table.

Why does VACUUM run but dead tuples stay high? Something is holding the xmin horizon back, a long-running or idle-in-transaction session, an inactive replication slot, a standby with hot_standby_feedback, or a dangling prepared transaction. Vacuum runs, scans, and legitimately removes nothing.

Is ALTER TABLE ... SET (autovacuum_...) safe in production? Yes. It is a catalog-only change under a SHARE UPDATE EXCLUSIVE lock, no rewrite, no blocking of reads or normal writes. It can wait briefly behind a running vacuum or schema change 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 (typically 16, but initdb lowers it when kernel limits require, check SHOW autovacuum_worker_slots rather than assuming); on 17 and earlier it requires a restart.

Related

• What is table bloat in PostgreSQL and how do I fix it?
• How to fix idle in transaction connections in PostgreSQL
• My pg_wal directory filled the disk, what is safe to delete?
• Product: DBGorilla docs