Postgres Autovacuum Tuning

Autovacuum is Postgres's background process for reclaiming dead tuple space, updating planner statistics, and preventing transaction ID wraparound. The defaults work fine for small tables but quietly fall behind on large, high-churn tables — leading to bloat, stale statistics, and eventually a production crisis. This guide covers the threshold math and how to fix it.

What autovacuum does

Every UPDATE and DELETE leaves dead tuple versions in the heap. These dead tuples occupy space, slow down sequential scans, and inflate indexes. Autovacuum's vacuum pass marks dead tuples as reusable space. Its analyze pass refreshes the planner statistics in pg_statistic that the query planner uses to estimate row counts and choose join strategies.

Without autovacuum, two things go wrong: tables bloat indefinitely, and planner statistics go stale. Stale statistics are often the root cause of sudden plan regressions — the planner thinks a table has 10,000 rows when it actually has 10 million.

The trigger threshold formula

Autovacuum fires a vacuum pass on a table when the number of dead tuples exceeds:

autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × pg_class.reltuples

Defaults: autovacuum_vacuum_threshold = 50 and autovacuum_vacuum_scale_factor = 0.20. For a table with 10 million rows, that's 2,000,050 dead tuples before autovacuum fires. On a write-heavy table, those 2 million dead tuples represent real I/O overhead on every sequential scan and real wasted space on disk.

The analyze trigger uses the same math with its own pair of settings: autovacuum_analyze_threshold (default 50) and autovacuum_analyze_scale_factor (default 0.10). A 10M-row table needs 1,000,050 changed rows before statistics are refreshed — far too infrequent for volatile data.

Per-table configuration

You can override global settings per table using storage parameters. This is almost always the right approach — you want aggressive settings on high-churn tables without hammering every small lookup table.

-- Fire vacuum when 1% of rows are 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    = 500
  );

For very large tables (hundreds of millions of rows), scale factors break down entirely — even 1% is millions of rows. Use an absolute threshold instead:

ALTER TABLE events
  SET (
    autovacuum_vacuum_scale_factor  = 0,
    autovacuum_vacuum_threshold     = 50000,
    autovacuum_analyze_scale_factor = 0,
    autovacuum_analyze_threshold    = 10000
  );

Monitoring vacuum activity

The most useful view is pg_stat_user_tables:

SELECT
  relname,
  n_dead_tup,
  n_live_tup,
  round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
  last_autovacuum,
  last_autoanalyze,
  autovacuum_count,
  autoanalyze_count
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC
LIMIT 20;

Flag any table where dead_pct is above 10% and last_autovacuum is more than an hour ago. That combination means autovacuum is not keeping up.

To see what autovacuum is doing right now:

SELECT pid, relid::regclass, phase, heap_blks_scanned, heap_blks_vacuumed
FROM pg_stat_progress_vacuum;

Transaction ID wraparound: the hard deadline

Beyond bloat, autovacuum has a second job: freezing old tuples to prevent transaction ID (XID) wraparound. Postgres uses 32-bit transaction IDs. When the counter wraps at ~2 billion, the database can no longer distinguish past from future transactions — all data becomes invisible. This is a catastrophic failure.

Autovacuum automatically freezes tuples when a table's age (max XID minus oldest unfrozen XID) approaches autovacuum_freeze_max_age (default 200 million transactions). Check how close your tables are:

SELECT relname,
       age(relfrozenxid) AS xid_age,
       pg_size_pretty(pg_total_relation_size(oid)) AS size
FROM pg_class
WHERE relkind = 'r'
ORDER BY age(relfrozenxid) DESC
LIMIT 10;

Any table with an age above 150 million is approaching the forced-vacuum zone. Above 1.5 billion, Postgres will force an aggressive vacuum that can spike I/O. Above 2 billion, the database shuts down.

Autovacuum workers and cost throttling

By default, only 3 autovacuum worker processes run simultaneously (autovacuum_max_workers = 3). On a busy instance with many tables, they can queue up. Increasing to 5–6 on a high-write instance is usually safe.

Autovacuum is throttled by autovacuum_vacuum_cost_delay (default 2ms) to avoid overwhelming I/O. If vacuum is always behind, reducing this to 0ms (no throttle) or 1ms will speed it up at the cost of higher I/O pressure. On SSD-backed instances this trade-off is almost always worth it.

Quick checklist

Check pg_stat_user_tables for tables with dead_pct > 10%.
Tighten vacuum_scale_factor to 0.01 on high-churn tables.
Use absolute thresholds (scale_factor = 0) on tables over 10M rows.
Monitor XID age — alert if any table exceeds 150M transactions.
Consider raising autovacuum_max_workers on write-heavy instances.
Reduce autovacuum_vacuum_cost_delay on SSD storage.