Postgres Autovacuum: Tuning From Production Stalls

Fix autovacuum stalls with per-table overrides on your hottest tables: drop autovacuum_vacuum_scale_factor from the default 0.2 to about 0.02 and raise the cost limit, so vacuum runs small and often. Then shorten autovacuum_naptime cluster-wide and watch dead-tuple ratios. One neglected table, not the whole database, is usually the culprit.

The classic autovacuum stall looks like this: a write-heavy database slows sharply for minutes at a time, and the culprit is a single table autovacuum has been losing ground on for weeks. This post covers the defaults we change, why, and the dashboards that catch the same issue earlier.

What autovacuum actually does

Postgres uses MVCC: every UPDATE leaves a "dead" row version behind. DELETE is the same: the row is marked dead, not removed. Autovacuum is the background process that reclaims those dead rows so the table doesn't grow unboundedly, the indexes stay efficient, and the visibility map stays accurate for index-only scans.

It also runs ANALYZE periodically to refresh planner statistics, and it freezes old rows to prevent transaction ID wraparound. Three jobs, one daemon, mostly invisible.

When autovacuum is keeping up, you never think about it. When it isn't, several things go wrong at once: table bloat grows, index efficiency drops, planner stats go stale, and eventually you hit one of three failure modes: an anti-wraparound vacuum you cannot cancel, a runaway query plan that scans the bloated heap, or a vacuum so large that its I/O starves foreground queries.

How a hot table gets into trouble

Picture a high-write event stream table taking thousands of UPDATEs per second at peak. Two default settings combine to cause the stall:

1. Default autovacuum_vacuum_scale_factor = 0.2, meaning autovacuum waits until 20% of the table is dead tuples before kicking in. On a 200M-row table, that's 40M dead rows of work before the daemon even starts. PostgreSQL 18 adds autovacuum_vacuum_max_threshold (default 100,000,000 tuples) as a ceiling, which helps only on very large tables.
2. Default autovacuum_naptime = 1min: the launcher checks each database once a minute. Fine for a quiet database, too lazy for a hot one.

By the time autovacuum finally fires on that table, it has hours of accumulated work to do, and the pass runs long. A plain VACUUM takes a SHARE UPDATE EXCLUSIVE lock that does not block reads or writes, so the damage is I/O: the vacuum, the bloated heap and the stale plans together saturate the disk, and foreground latency spikes.

What we changed first (table-level overrides)

The most important lesson: don't tune autovacuum globally to fix one table. Hot tables and quiet tables want different schedules. Use ALTER TABLE ... SET for per-table overrides.

For the hot table:

-- per-table storage parameters on the hot table
ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 1000,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_vacuum_cost_limit = 2000
);

What changed:

• vacuum_scale_factor = 0.02 (default 0.2): vacuum when 2% of rows are dead instead of 20%. Tighter, more frequent, smaller passes.
• vacuum_threshold = 1000 (default 50): the base count added to the percentage. Irrelevant on the big table; it stops this template from vacuuming tiny tables constantly if someone copies it.
• analyze_scale_factor = 0.01 (default 0.1): refresh stats more often, so the planner keeps up with a rapidly changing data distribution.
• vacuum_cost_limit = 2000 (default -1, which inherits vacuum_cost_limit = 200): autovacuum throttles itself against a cost budget; raising the limit lets each pass work faster. Trade: more I/O during the vacuum. Tables with their own cost settings are also excluded from the cost balancing shared across workers.

The goal is many short passes instead of one long one, so foreground queries barely notice. Confirm it with last_autovacuum and the dead-tuple query below rather than assuming.

What we changed cluster-wide

A smaller set of changes applied globally:

autovacuum_naptime = 15s
autovacuum_max_workers = 6
autovacuum_vacuum_cost_delay = 2ms

naptime = 15s is the big one. The daemon now considers vacuuming every 15 seconds instead of every minute: a small change with a big effect on hot tables that cross their threshold between checks.

max_workers = 6 (default 3) lets more tables vacuum in parallel. On PostgreSQL 18 it is capped by autovacuum_worker_slots (typically 16, set at server start), so you can raise it with a reload as long as you stay under the slot count.

cost_delay = 2ms is already the default since PostgreSQL 12. We set it explicitly because clusters upgraded with an old config file often still carry the pre-12 value of 20ms, which makes vacuum painfully slow under load.

What we measure now

Three dashboards, checked weekly:

Per-table dead tuple ratio. Query pg_stat_all_tables for n_dead_tup / (n_live_tup + n_dead_tup) and flag any table over 10%. The old "is autovacuum keeping up" question, with hard numbers.

-- tables autovacuum is losing ground on
SELECT relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       now() - last_autovacuum AS since_autovacuum
FROM pg_stat_all_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY n_dead_tup DESC
LIMIT 20;

Last autovacuum age per table. now() - last_autovacuum for tables with high write volume. Anything older than about 6 hours on a hot table is suspicious.

Autovacuum worker concurrency. pg_stat_activity filtered for backend_type = 'autovacuum worker'. If you are pinned at autovacuum_max_workers most of the time, you need more workers or more aggressive per-table thresholds.

We graph all three on one dashboard so drift is obvious. Bloated indexes are a separate signal; detecting and fixing Postgres index bloat covers the queries for that half.

The bigger lessons

A few patterns worth keeping:

Hot tables need explicit attention. The defaults are designed for "average" tables. Production workloads have outliers. Audit the top 10 by write volume and tune them by hand.

Bloat compounds. A table that's 20% dead today is 40% dead next week if autovacuum can't keep up, and cleanup cost grows faster than linear because indexes degrade with the heap.

Vacuum during traffic, not after. The instinct to run a manual vacuum at 3 AM backfires. Vacuum during peak is mostly fine if it's small and frequent; vacuum after the fact runs long and hammers the disk. Better to bleed the dead rows continuously.

Watch for anti-wraparound vacuums. When a table's relfrozenxid age passes autovacuum_freeze_max_age, Postgres forces a vacuum that, unlike a normal autovacuum, is not automatically cancelled when a conflicting lock request (an ALTER TABLE, say) arrives. Your migration then queues behind it, and everything queues behind the migration. In pg_stat_activity it shows up as (to prevent wraparound). We've never hit this in prod, but the postmortems we've read are grim, and the dashboards above catch the conditions long before.

What we didn't change

A few settings we left alone:

• maintenance_work_mem stays at the cluster's general value (we use 256 MB). Bumping it speeds vacuum, but every autovacuum worker can claim it, so six workers multiply the RAM cost.
• autovacuum_freeze_max_age stays at the default 200 million transactions. The forced vacuum only matters if regular vacuums have been failing for a long time, and the dashboards catch that much earlier.
• Manual vacuum schedules. We considered cron-driven VACUUM ANALYZE jobs and decided against them: a correctly tuned autovacuum already does that job.

Questions people ask

Why is autovacuum not running on my table?

Usually the table has not crossed its threshold yet. Autovacuum fires when dead tuples exceed autovacuum_vacuum_threshold plus autovacuum_vacuum_scale_factor times the row count, which is 50 plus 20% by default. On a 100M-row table that is 20M dead rows. Check n_dead_tup and last_autovacuum in pg_stat_all_tables, and lower the scale factor on that table with ALTER TABLE ... SET.

Does autovacuum lock the table?

A normal autovacuum takes a SHARE UPDATE EXCLUSIVE lock, which allows SELECT, INSERT, UPDATE, and DELETE but blocks schema changes such as ALTER TABLE. It cancels itself if a conflicting lock request arrives, unless it is running to prevent transaction ID wraparound. Only VACUUM FULL takes an ACCESS EXCLUSIVE lock that blocks everything, which is why you should avoid it on busy tables.

What is a good autovacuum_vacuum_scale_factor for large tables?

For tables with tens of millions of rows and heavy updates, 0.01 to 0.05 is a common starting point, set per table rather than globally. Pair it with a higher autovacuum_vacuum_cost_limit so each pass finishes quickly. Then watch the dead-tuple ratio for a week: if it stays under about 10% and passes stay short, you are done.

What to read next

Adjacent posts in the corpus:

• Cloud-native databases: choosing the right database for each workload, on when Postgres is and isn't the right tool
• Database backup restoration testing, the safety net Postgres tuning doesn't replace
• Postgres query analysis with pg_stat_statements, for finding the queries that bloat makes slow

Autovacuum is one of those subsystems where the default settings work for the median case and quietly break for the long tail. Tuning takes an hour per important table and pays off for years. The dashboards keep you honest about whether the tuning is holding.

Sources

• PostgreSQL 18 Documentation: Vacuuming configuration parameters
• PostgreSQL 18 Documentation: Routine vacuuming
• PostgreSQL 18 Documentation: The cumulative statistics system