Postgres autovacuum, demystified — the tuning that prevents 3am wraparound panic

I have a small list of Postgres failure modes that have personally cost me sleep. Transaction ID wraparound is at the top. The morning is always the same: a customer's Postgres has gone read-only at 2am, every write transaction is rejected with "database is not accepting commands to avoid wraparound data loss," and the only path forward is a VACUUM FREEZE against a 300GB table that's going to take six hours.

The tooling exists to prevent this. Autovacuum has existed since 2005. The default settings are tuned for "small database, small server," and on any meaningfully large database, they're wrong in predictable ways. This post is the autovacuum tuning we apply by default on every managed Postgres database we run.

What autovacuum is doing, briefly

Two distinct jobs:

1. Dead tuple cleanup. When you UPDATE or DELETE a row, Postgres doesn't immediately remove it — it marks it as dead. Autovacuum reclaims the space and updates the visibility map so future queries can skip dead rows.

2. Transaction ID freezing. Postgres assigns a 32-bit transaction ID (XID) to each transaction. After ~2 billion transactions, XIDs wrap. To prevent old rows from appearing to be in the future, autovacuum periodically "freezes" old rows, marking them with a special XID that's outside the wrap window.

The first job is what most discussions of vacuum focus on. The second is what actually breaks production at 2am.

The metrics to watch

Three numbers per table tell you whether autovacuum is keeping up:

SELECT 
  schemaname || '.' || relname AS table,
  n_live_tup,
  n_dead_tup,
  round(n_dead_tup::numeric / NULLIF(n_live_tup, 0), 3) AS dead_ratio,
  last_autovacuum,
  age(relfrozenxid) AS xid_age
FROM pg_stat_user_tables 
JOIN pg_class ON pg_class.oid = relid
ORDER BY age(relfrozenxid) DESC
LIMIT 20;

What you're looking for:

• dead_ratio — fraction of rows that are dead. Should be <0.1 (10%) for high-churn tables, <0.2 for moderate. Anything north of 0.5 is bloat that's costing you query performance.
• last_autovacuum — when did autovacuum last touch this table? If it's days ago on a busy table, your thresholds are wrong.
• xid_age — how many XIDs ago was the oldest unfrozen row on this table. Postgres triggers an "anti-wraparound" autovacuum at autovacuum_freeze_max_age (default 200M). Wraparound danger starts around 1.5 billion. You want to keep this under 500M for any large table.

Grafana dashboards on these three metrics, per table, would have prevented every wraparound incident I've ever seen.

The default thresholds are wrong for large tables

Default autovacuum triggers on:

autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.2

Meaning: vacuum a table when dead tuples exceed 50 + 20% × n_live_tup. For a 10-row table, that's 52 dead tuples. For a 100-million-row table, that's 20 million dead tuples — at which point your queries are already bloated, and the vacuum will be enormous and slow.

The fix is per-table tuning:

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,    -- 2% instead of 20%
  autovacuum_vacuum_threshold = 1000,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_freeze_max_age = 100000000,    -- freeze at 100M XID age, not 200M
  autovacuum_vacuum_cost_limit = 2000        -- faster vacuum on this table
);

Apply aggressive thresholds to your largest, most-churned tables. Leave the small tables on default — there's no point burning autovacuum cycles on a 100-row reference table.

A rule of thumb: any table over 10GB or 10 million rows gets per-table tuning.

The cost-limit story

Autovacuum runs in the background and is rate-limited by a "cost system." Each I/O operation has a notional cost; autovacuum sleeps when it accumulates autovacuum_vacuum_cost_limit worth of cost.

autovacuum_vacuum_cost_limit = 200          -- default
autovacuum_vacuum_cost_delay = 2ms          -- default

Default cost limit of 200 is comically low for modern hardware. SSDs can handle 50,000 random IOPS without breathing hard, and the default autovacuum throttles itself to maybe 200 IOPS.

For most production databases on SSDs we set:

autovacuum_vacuum_cost_limit = 2000
autovacuum_vacuum_cost_delay = 2ms

That's a 10x bump. You'll see autovacuum complete much faster, and (since vacuum competes with queries for I/O) you might see slightly higher background I/O contention. On any modern SSD-backed database, this is the right trade. On a magnetic-disk database — but you're not still on magnetic disks in 2026, are you?

The number of workers

autovacuum_max_workers = 3                   -- default

Three concurrent autovacuum workers, across the entire instance. If you have 50 tables that all want vacuuming at once, 47 of them wait.

For databases with more than ~20 frequently-updated tables, bump this to 5-8. The workers compete for the same cost budget by default; if you want them genuinely concurrent you also need to bump the cost limit proportionally:

autovacuum_max_workers = 6
autovacuum_vacuum_cost_limit = 6000          -- 1000 per worker

This is what our managed RDS Postgres customers tend to need at scale. RDS lets you set these as parameter group overrides; same for Cloud SQL and Azure Database for Postgres.

Wraparound: the autovacuum that you can't disable

When age(datfrozenxid) for a database approaches autovacuum_freeze_max_age, Postgres triggers an anti-wraparound autovacuum. This is special:

• It runs even if autovacuum = off
• It cannot be cancelled by SIGINT — only by killing the backend
• It blocks DDL on the table being vacuumed
• It's not throttled by the usual cost system

If anti-wraparound vacuum hits during business hours on a 500GB table, you have problems. The table gets implicit lock-on-DDL for as long as the vacuum runs, which can be hours.

The prevention is to freeze proactively, on your schedule, before anti-wraparound fires:

-- Run during a maintenance window on large tables
VACUUM (FREEZE, VERBOSE) orders;

Or set per-table:

ALTER TABLE orders SET (autovacuum_freeze_max_age = 50000000);  -- 50M

So that the automatic freeze fires much earlier, when the table is smaller and easier to vacuum. The trade-off is more frequent vacuum activity; the benefit is that no single vacuum is enormous.

Bloat that autovacuum can't fix

A note: autovacuum reclaims space within the table file, making the dead tuple slots available for new rows. It does not return space to the OS. A table that grew to 100GB during a bulk import and is now mostly empty will stay 100GB-on-disk until you VACUUM FULL or pg_repack it.

VACUUM FULL takes an exclusive lock on the table for the duration — unacceptable on a live table.

pg_repack does an online table rebuild with minimal locking — what we use on every managed customer. Run it on a schedule against your most-bloated tables:

pg_repack -d mydb -t public.orders --no-superuser-check

Combined with monitoring pg_relation_size() vs pg_total_relation_size() deltas, this is how we keep customer databases from gradually consuming the disk.

Index bloat

Less talked about, but the same problem applies to indexes. Indexes don't get the equivalent of vacuum — they accumulate dead entries over time, and only REINDEX reclaims the space. Postgres 12+ added REINDEX CONCURRENTLY which is online; use it:

REINDEX TABLE CONCURRENTLY orders;

Run quarterly on your hottest indexes. Combined with pgstattuple to identify which indexes have actually bloated, this is usually a 30-minute monthly task that keeps query plans efficient.

What we ship by default

For managed Postgres customers:

• Per-table autovacuum tuning for tables >10GB or >10M rows
• autovacuum_max_workers = 5 and autovacuum_vacuum_cost_limit = 2000 as cluster defaults
• Weekly pg_repack cron against the most-bloated tables
• Monthly REINDEX CONCURRENTLY against the hottest indexes
• Grafana dashboard tracking xid_age, dead_ratio, and last_autovacuum per top-50 tables
• Alerts firing at xid_age > 1B (well before wraparound)

If your Postgres has been running for years and nobody's looked at autovacuum tuning, you have bloat. The good news: it's a one-time tuning exercise with ongoing low-cost monitoring. Reach out if you'd like an audit — we'll surface the per-table tuning that's missing and a 30-day remediation plan.

Sudhanshu K. is a Staff DevOps engineer at EdgeServers (RemotIQ Pty Ltd, ABN 91 682 628 128). He has personally pulled the all-night VACUUM FREEZE shift more times than he cares to recount.