# 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: ```sql 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? ```sql 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? ```sql 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. ```sql 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](https://www.dbgorilla.com/learn/postgres/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. ```sql 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](https://www.dbgorilla.com/learn/postgres/pg-wal-directory-filling-up-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`: ```sql 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: ```sql 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: ```sql 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. ```sql -- 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: ```sql 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): ```sql 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): ```sql 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](https://www.dbgorilla.com/learn/postgres/what-is-table-bloat-in-postgresql-and-how-to-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 →](https://app.dbgorilla.com/signup) ## 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?](https://www.dbgorilla.com/learn/postgres/what-is-table-bloat-in-postgresql-and-how-to-fix-it/) - [How to fix idle in transaction connections in PostgreSQL](https://www.dbgorilla.com/learn/postgres/how-to-fix-idle-in-transaction-connections/) - [My pg_wal directory filled the disk, what is safe to delete?](https://www.dbgorilla.com/learn/postgres/pg-wal-directory-filling-up-disk/) - Product: [DBGorilla docs](https://www.dbgorilla.com/docs/)