PostgreSQL Autovacuum Tuning

Introduction¶

PostgreSQL's MVCC means an UPDATE or DELETE doesn't remove the old row version — it marks it dead. Vacuum reclaims that space and updates visibility info. Autovacuum does it automatically; when it can't keep up, you get bloat, slow scans, and — eventually — a forced shutdown to prevent transaction-ID wraparound.

What vacuum does¶

• Removes dead tuples (obsolete row versions), freeing space for reuse (not returned to the OS — that needs VACUUM FULL).
• Updates the visibility map so index-only scans and future vacuums can skip all-visible pages.
• Freezes old tuples to advance relfrozenxid, preventing XID wraparound.
• ANALYZE (autovacuum also runs this) refreshes planner statistics.

The autovacuum trigger¶

Autovacuum vacuums a table when:

dead_tuples > autovacuum_vacuum_threshold
            + autovacuum_vacuum_scale_factor × reltuples

Defaults: threshold = 50, scale_factor = 0.2 (20%). So a 10M-row table isn't vacuumed until 2 million dead rows accumulate — far too lax for a big, churny table. Lower the scale factor per-table:

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,      -- 2%
  autovacuum_vacuum_threshold    = 1000,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_vacuum_cost_delay = 2ms          -- default 2ms (was 20ms pre-12); lower = faster/more IO
);

Making autovacuum faster¶

Global settings (postgresql.conf):

autovacuum_max_workers = 5                    # more parallel tables (each still single-threaded)
autovacuum_vacuum_cost_limit = 3000           # raise from default 200 -> vacuum does more work per cycle
autovacuum_naptime = 15s                      # check more often
maintenance_work_mem = 1GB                    # bigger = fewer index passes per vacuum

autovacuum_vacuum_cost_limit + cost_delay throttle vacuum's I/O. Defaults are conservative to avoid impacting queries; on modern SSDs you can be far more aggressive. Raising cost_limit to 2000–4000 is the single most effective knob on a busy database.

Transaction-ID wraparound¶

XIDs are 32-bit and wrap. If a table's oldest unfrozen XID gets within autovacuum_freeze_max_age (200M) of wrapping, autovacuum launches an aggressive anti-wraparound vacuum that can't be skipped. Within ~1M of the limit, Postgres refuses new writes to protect the data.

-- How close is anything to wraparound?
SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database ORDER BY xid_age DESC;

SELECT relname, age(relfrozenxid) AS xid_age
FROM pg_class WHERE relkind IN ('r','m','t')
ORDER BY xid_age DESC LIMIT 20;

An xid_age climbing toward 200M+ on a table means autovacuum can't freeze it — usually a long-running transaction or an abandoned replication slot holding back the horizon (see below).

Diagnosing¶

-- Bloat and last vacuum
SELECT relname, n_live_tup, n_dead_tup,
       last_autovacuum, last_autoanalyze, autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 20;

-- What's blocking the vacuum horizon?
SELECT pid, state, xact_start, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL ORDER BY xact_start LIMIT 5;

SELECT slot_name, active, restart_lsn,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained
FROM pg_replication_slots;

-- Is autovacuum currently running on something?
SELECT * FROM pg_stat_progress_vacuum;

Verification and troubleshooting¶

• Table bloated, last_autovacuum is null or old — scale factor too high for the table size (set it to 0.01–0.02), autovacuum disabled on the table (ALTER TABLE ... SET (autovacuum_enabled = true)), or all workers are busy on bigger tables (raise autovacuum_max_workers).
• Autovacuum runs constantly but n_dead_tup stays huge — it's throttled (raise autovacuum_vacuum_cost_limit), or a long transaction / idle replication slot means the dead tuples aren't dead yet (still visible to an old snapshot). Kill the long transaction, drop the stale slot.
• ERROR: database is not accepting commands to avoid wraparound — emergency. Stop the long transaction / drop the offending replication slot, then VACUUM (FREEZE, VERBOSE) the tables with the highest age(relfrozenxid). Single-user mode if it won't start.
• Vacuum ran but disk didn't free — plain vacuum frees space for reuse, not to the OS. Use pg_repack (online) or VACUUM FULL (locks the table) to actually shrink.
• Query plans suddenly bad after bulk load — autoanalyze didn't fire yet; run ANALYZE manually after big data changes.
• Autovacuum causing I/O spikes — the opposite problem; raise cost_delay or lower cost_limit on the noisy table, or schedule manual vacuums off-peak.

Related tools and reading¶

• On-site: SQL / JSON formatter.
• Related posts: Database deadlocks explained, Reading a MySQL EXPLAIN plan.