Postgres Autovacuum: The Knobs Most Teams Never Touch

Postgres ships with autovacuum enabled by default, which prevents the catastrophic-degradation mode that older versions could enter when administrators forgot to run VACUUM manually. What it does not do is configure itself per-table for the workload it actually has. The defaults assume tables with predictable update rates and modest sizes, and production tables almost never match those assumptions. The result is a database that runs adequately for years and then surprises a team with mysterious slow queries, disk usage that does not match the row count, or an emergency wraparound vacuum that locks tables for hours.

We have run into autovacuum tuning issues on the SQLite-free portions of analytics work across DocuMint, CronPing, FlagBit, and WebhookVault. The autovacuum subsystem rewards even small amounts of attention. Most teams that touch it are surprised at how much performance was sitting on the floor.

What autovacuum actually does

Autovacuum runs three jobs that the database needs done. VACUUM marks dead tuples (rows that have been updated or deleted but not yet cleaned up) as reusable space and updates the visibility map so that index-only scans can skip pages with all-visible tuples. ANALYZE updates the planner statistics that drive query plans. Wraparound prevention advances the frozen XID horizon to prevent transaction-ID exhaustion that would force a database shutdown.

The launcher process wakes up every autovacuum_naptime seconds (default 60) and checks each table to see if it needs work. The threshold formula is autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * pg_class.reltuples. With defaults of 50 and 0.2, a table with 1 million rows triggers vacuum at 200,050 dead tuples. For a small table this is fine. For a 100-million-row table, you wait for 20 million dead tuples to accumulate before any cleanup happens, by which time queries have been scanning bloated indexes for hours.

The scale factor problem

The single most important tuning decision is the per-table autovacuum_vacuum_scale_factor. The 0.2 default is reasonable for tables in the thousands-to-millions row range. For larger tables, it should be much smaller — 0.01 or 0.005 — and for very small high-update tables it can be higher. The pattern that works in production is to override the default per table:

ALTER TABLE webhook_deliveries SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_analyze_scale_factor = 0.01
);

This says: vacuum when 1% of the table has changed, rather than 20%. For a 100-million-row table this means triggering at 1 million dead tuples instead of 20 million. The cleanup happens more frequently, in smaller increments, with much less impact on query performance.

The throttling parameters

Autovacuum has a cost-based throttling system that is intended to prevent it from saturating I/O. The relevant parameters are autovacuum_vacuum_cost_limit (default 200 on the launcher; -1 means use vacuum_cost_limit which defaults to 200) and autovacuum_vacuum_cost_delay (default 2ms on modern Postgres). The math is: every vacuum operation has a cost (1 for a page in shared buffers, 2 for missed buffer, 20 for dirtying a page), and when accumulated cost hits cost_limit, the worker sleeps for cost_delay.

The defaults are conservative because autovacuum was designed for systems where I/O was scarce. On modern SSDs and NVMe, the throttling is often the bottleneck — autovacuum spends most of its time sleeping rather than working. For write-heavy production systems on fast storage, raising the cost limit to 2000-10000 and lowering the delay to 1ms can dramatically increase autovacuum throughput without measurable impact on user queries.

The long-running transaction trap

The single most common autovacuum failure mode in production is a long-running transaction blocking cleanup. VACUUM can only reclaim space from tuples that are no longer visible to any current transaction. If a session has been holding an open transaction for hours — typically a forgotten psql session or an application bug — every dead tuple created after that transaction's snapshot becomes unreclaimable until the transaction ends.

The signal is in pg_stat_activity: look for state = 'idle in transaction' with xact_start more than a few minutes old. The query that finds the worst offenders:

SELECT pid, usename, application_name, state,
       NOW() - xact_start AS txn_age, query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
ORDER BY xact_start ASC;

The idle_in_transaction_session_timeout parameter is the right safety net — set it to 5-15 minutes in production. This kills transactions that have been idle for too long, preventing the autovacuum starvation pattern from accumulating.

The wraparound emergency

Postgres uses a 32-bit transaction ID, which wraps around every 2 billion transactions. To prevent confusion about which transactions are in the past versus the future, Postgres tracks a "frozen" XID horizon and aggressively vacuums tables whose oldest XID is approaching the wraparound threshold. If wraparound vacuums fall behind, Postgres enters a read-only emergency mode that requires manual intervention.

The signal is age(datfrozenxid) for each database in pg_database. Above 200 million, normal autovacuum is running wraparound-aware. Above 1 billion, the emergency autovacuum is running and cannot be cancelled. Above 1.5 billion, the database refuses new transactions until VACUUM completes.

The right defense is to avoid the situation by running autovacuum aggressively enough that wraparound is never close. The autovacuum_freeze_max_age parameter controls when wraparound vacuums start (default 200 million transactions). On busy systems, lower this to 100 million or 50 million to spread the wraparound work over time.

The reluctant vacuum problem

Autovacuum can be configured aggressively and still not run because the launcher process has limited concurrency. autovacuum_max_workers (default 3) limits how many tables can be vacuumed simultaneously. On a database with hundreds of large tables and continuous updates, three workers may not be enough — the launcher falls behind, queues up tables, and individual tables wait longer than their scale factor would suggest.

Raising max_workers to 5-10 on systems with dozens of busy tables is a common adjustment. The trade-off is that each worker consumes a connection slot and some I/O bandwidth.

The five operational signals

The dashboard for autovacuum health should include five signals. Dead tuple ratio per table from pg_stat_user_tables: alert above 20%. Time since last autovacuum from pg_stat_user_tables.last_autovacuum: alert if no recent autovacuum on a table that should be getting them. Frozen XID age from pg_database.datfrozenxid: alert above 500 million. Idle-in-transaction count from pg_stat_activity: alert if any transaction has been idle in transaction for more than 5 minutes. Autovacuum worker utilization by counting active workers in pg_stat_activity where application_name like 'autovacuum%'.

The deeper observation

Autovacuum is one of those subsystems that is invisible when working and catastrophic when not. The defaults are calibrated for an average that does not exist — most tables either need less aggressive vacuuming than the defaults provide or much more. The right discipline is to treat autovacuum settings as part of the schema rather than as a global database parameter, and to override per-table based on actual update rates and table sizes. The investment is hours per year and pays back in queries that stay fast as tables grow.