PostgreSQL Autovacuum Failure Workflow

When n_dead_tup climbs and autovacuum isn’t keeping up, you have roughly two problems running in parallel: the bloat you can see today, and the transaction ID wraparound risk you might not notice until PostgreSQL forces an emergency shutdown. The failure modes compound — bloat slows queries, which slows transactions, which delays vacuum, which grows bloat further. Getting out requires understanding which part of the cycle broke first.

Situation

PostgreSQL’s MVCC model keeps old row versions in the heap rather than updating in place. Autovacuum’s job is to reclaim those dead tuples and keep the transaction ID horizon from advancing too far. Under moderate write load, autovacuum usually runs unnoticed. Under high write volume — bulk loads, frequent deletes, update-heavy workloads — it falls behind.

When autovacuum falls behind, the visible effects are: growing table size on disk, sequential scans replacing index scans as indexes become less selective relative to bloat, and queries that were running in single-digit milliseconds start showing variance. The less visible effect is age(relfrozenxid) creeping toward the 2-billion wraparound limit, at which point PostgreSQL will refuse to serve any read or write until a full-table vacuum completes.

The root cause is almost never “autovacuum is broken.” It is almost always one of three things: a long-running transaction blocking vacuum from removing dead tuples, the autovacuum_vacuum_scale_factor threshold being too coarse for a large table, or autovacuum_vacuum_cost_delay throttling throughput below what the write rate demands.

Symptoms

Signal	Where to see it	What it means
`n_dead_tup` rising continuously	`pg_stat_user_tables`	Vacuum not keeping up with write rate
Table size growing without row count growth	`pg_size_pretty(pg_total_relation_size(...))`	Physical bloat accumulating in heap
Sequential scans replacing index scans	`pg_stat_user_tables.seq_scan` increasing	Planner estimates degrading due to bloat
`age(datfrozenxid)` > 1.5 billion	`pg_database`	Transaction ID wraparound risk is real
Last autovacuum timestamp hours or days stale	`pg_stat_user_tables.last_autovacuum`	Vacuum is being blocked or never triggered
Long-lived idle-in-transaction sessions	`pg_stat_activity`	Blocking vacuum horizon advancement

First Five Checks

Dead tuple accumulation by table — find which tables are most behind:

SELECT
  schemaname,
  tablename,
  n_dead_tup,
  n_live_tup,
  round(n_dead_tup::numeric / nullif(n_live_tup + n_dead_tup, 0) * 100, 2) AS dead_pct,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

High dead_pct on a large table tells you where to focus. A last_autovacuum that is hours old on a high-write table means the trigger threshold was never crossed or vacuum was blocked.

Active blocking transactions — long-running transactions prevent vacuum from advancing the horizon:

SELECT
  pid,
  usename,
  state,
  wait_event_type,
  wait_event,
  now() - xact_start AS xact_duration,
  left(query, 80) AS query_preview
FROM pg_stat_activity
WHERE state != 'idle'
  AND xact_start IS NOT NULL
ORDER BY xact_duration DESC;

Any session with xact_duration over 10 minutes that is idle in transaction is a primary vacuum-blocker candidate. PostgreSQL cannot remove dead tuples older than the oldest open transaction’s snapshot.

Transaction ID wraparound risk — check how close each database is to the 2-billion limit:

SELECT
  datname,
  age(datfrozenxid) AS xid_age,
  2000000000 - age(datfrozenxid) AS xid_remaining
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

PostgreSQL issues a WARNING at age > 1.5 billion and becomes read-only at age > 1.95 billion. Any value above 1 billion warrants attention. Above 1.5 billion, treat it as an incident in progress.

Current autovacuum scale factor — determine whether the threshold is too coarse:

SHOW autovacuum_vacuum_scale_factor;
-- Also check per-table overrides:
SELECT relname, reloptions
FROM pg_class
WHERE reloptions IS NOT NULL
  AND relkind = 'r';

The default autovacuum_vacuum_scale_factor = 0.2 means autovacuum triggers after 20% of the table’s live rows have become dead. On a 100-million-row table, that is 20 million dead tuples before vacuum runs — enough bloat to double the table’s physical size.

Background writer and checkpoint pressure — determine if I/O is the bottleneck:

SELECT
  checkpoints_timed,
  checkpoints_req,
  checkpoint_write_time,
  checkpoint_sync_time,
  buffers_clean,
  maxwritten_clean,
  buffers_backend
FROM pg_stat_bgwriter;

High maxwritten_clean means the background writer hit its bgwriter_lru_maxpages limit repeatedly. High buffers_backend means backends are doing their own dirty buffer flushing — a sign that I/O throughput is limiting vacuum’s ability to write.

Decision Tree

flowchart TD
    A[n_dead_tup growing] --> B{last_autovacuum recent?}
    B -->|no — never triggered| C{autovacuum=on globally?}
    C -->|no| D[Enable autovacuum in postgresql.conf]
    C -->|yes| E{scale_factor too high?}
    E -->|yes| F[Lower per-table scale_factor]
    B -->|yes — vacuum ran but did not help| G{oldest xact blocking vacuum?}
    G -->|yes| H{safe to terminate?}
    H -->|yes| I[pg_terminate_backend — then VACUUM]
    H -->|no| J[Wait for transaction — then VACUUM]
    G -->|no| K{cost_delay throttling?}
    K -->|yes| L[Reduce cost_delay per-table]
    K -->|no| M{xid_age above 1.5B?}
    M -->|yes| N[VACUUM FREEZE — emergency]
    M -->|no| O[Manual VACUUM VERBOSE — diagnose output]

Remediation Options

Option 1 — Manual VACUUM to clear immediate bloat

Run a manual VACUUM VERBOSE to force reclamation and get diagnostic output:

VACUUM VERBOSE tablename;

The verbose output shows how many dead tuples were removed, how many pages were scanned, and whether any tuples could not be removed due to transaction horizon constraints. If the output shows tuples “not removable due to oldest xmin,” a blocking transaction is the problem, not the configuration.

For wraparound risk specifically, add FREEZE:

VACUUM FREEZE tablename;

FREEZE advances relfrozenxid and is the only action that reduces age(datfrozenxid). It is I/O-intensive on large tables, so run it during off-peak hours when possible.

Option 2 — Tune per-table autovacuum thresholds

For high-write tables where the global scale_factor is too coarse, override at the table level:

ALTER TABLE high_write_table SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 1000,
  autovacuum_vacuum_cost_delay = 2,
  autovacuum_vacuum_cost_limit = 400
);

scale_factor = 0.01 triggers autovacuum after 1% dead tuples instead of 20%. cost_delay = 2ms with cost_limit = 400 doubles autovacuum’s I/O budget relative to the default (cost_delay = 20ms, cost_limit = 200). These are per-table and do not affect global behavior.

To verify the override is active:

SELECT relname, reloptions
FROM pg_class
WHERE relname = 'high_write_table';

Option 3 — Terminate blocking long-running transactions

If pg_stat_activity shows a session that has been idle in transaction for an extended period and it cannot be resolved through application-layer means, terminate it:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - xact_start > interval '10 minutes';

After terminating, run VACUUM VERBOSE on the affected table immediately to reclaim the dead tuples that were being held.

To prevent recurrence, set the session-level timeout in postgresql.conf or per-role:

ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';
SELECT pg_reload_conf();

Rollback Plan

VACUUM and VACUUM FREEZE are read-safe operations. They do not lock tables for reads or writes (except at the very start of each heap page scan, which is a brief shared lock). They can be run and stopped at any time without data risk.
Per-table autovacuum_* overrides via ALTER TABLE ... SET (...) are immediately active and immediately reversible: ALTER TABLE tablename RESET (autovacuum_vacuum_scale_factor) returns to the global default.
pg_terminate_backend terminates the target session’s transaction — the application will see a connection error and must retry. This is the most disruptive remediation and should only be used when the blocking duration justifies it.
idle_in_transaction_session_timeout changes take effect for new transactions immediately after pg_reload_conf(). Existing connections are not affected until they start a new transaction.

Automation Opportunity

The most impactful automation is a scheduled query that surfaces tables where n_dead_tup exceeds a threshold before vacuum falls far enough behind to cause bloat. Using pg_cron (if installed):

-- Run every hour; log tables where dead_pct > 10%
SELECT cron.schedule('vacuum-watch', '0 * * * *', $$
  INSERT INTO ops.vacuum_alerts (tablename, n_dead_tup, dead_pct, captured_at)
  SELECT
    tablename,
    n_dead_tup,
    round(n_dead_tup::numeric / nullif(n_live_tup + n_dead_tup, 0) * 100, 2),
    now()
  FROM pg_stat_user_tables
  WHERE n_dead_tup > 10000
    AND round(n_dead_tup::numeric / nullif(n_live_tup + n_dead_tup, 0) * 100, 2) > 10
  ORDER BY n_dead_tup DESC;
$$);

Separately, a daily alert on age(datfrozenxid) crossing 500 million gives operational lead time well before the 1.5-billion warning threshold.

For the deeper argument on why autovacuum should be treated as a capacity planning problem rather than a maintenance task, see Autovacuum Is a Capacity Problem, Not a Maintenance Task.

The foundation of what autovacuum is doing and why its defaults are sized the way they are is covered in PostgreSQL Autovacuum: What Every Engineer Should Know.

In Practice

PostgreSQL’s autovacuum documentation describes the trigger formula directly: a table is eligible for autovacuum when n_dead_tup > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * pg_class.reltuples. The default scale_factor of 0.2 was sized for databases where tables have at most a few million rows. For tables with tens or hundreds of millions of rows, the documented recommendation from PostgreSQL wiki is to lower scale_factor to 0.01 or even 0.001 and raise autovacuum_vacuum_threshold to a fixed low count.

The documented pattern from the PostgreSQL MVCC documentation is that vacuum cannot remove a dead tuple that is still visible to any open transaction. This is not a bug — it is a consequence of snapshot isolation. The oldest running transaction’s xmin forms the vacuum horizon; dead tuples older than that horizon cannot be reclaimed regardless of how aggressively autovacuum is configured.

Where It Breaks

Failure mode	Trigger	Fix
Vacuum makes no progress despite running	Long-running transaction holds vacuum horizon	Terminate the blocking session; set `idle_in_transaction_session_timeout`
Autovacuum never triggers on large table	`scale_factor` too high; threshold never crossed	Lower `scale_factor` to 0.01 per-table
`VACUUM FREEZE` takes hours, blocks operations	Emergency freeze on a table with billions of rows	Run during maintenance window; break into table partition chunks if possible
`cost_delay` throttles vacuum below write rate	Default 20ms delay limits vacuum I/O to burst	Lower `cost_delay` to 2ms and raise `cost_limit` to 400 per-table
Manual vacuum returns immediately with no work	`pg_stat_activity` shows active `xmin` holding horizon	Wait for long transaction to close, then re-run vacuum

What to Do Next

Problem: Autovacuum falling behind grows bloat silently until queries slow, and eventually creates transaction ID wraparound risk that can force an emergency database shutdown.
Solution: Tune per-table autovacuum_vacuum_scale_factor and cost_delay for high-write tables, and set idle_in_transaction_session_timeout to prevent long transactions from blocking the vacuum horizon.
Proof: After applying per-table overrides, last_autovacuum timestamps on affected tables should refresh within minutes, and n_dead_tup should stabilize rather than grow between checks.
Action: Run the dead tuple query from Check 1 this week against your production database. If any table has dead_pct > 10% and a last_autovacuum older than an hour, that table needs a per-table threshold override today.

Checklist

Query pg_stat_user_tables to identify tables with high n_dead_tup and stale last_autovacuum
Check pg_stat_activity for sessions in idle in transaction state longer than 5 minutes
Check age(datfrozenxid) in pg_database — alert if any value exceeds 500 million
Verify autovacuum = on is set globally in postgresql.conf
Check per-table reloptions for existing autovacuum overrides on affected tables
If no blocking transaction: run VACUUM VERBOSE tablename and inspect output for horizon messages
Apply per-table autovacuum_vacuum_scale_factor = 0.01 to any table with > 10 million rows
Apply per-table autovacuum_vacuum_cost_delay = 2 for high-write tables
If xid_age > 1.5 billion: schedule emergency VACUUM FREEZE immediately
Set idle_in_transaction_session_timeout = '5min' in postgresql.conf to prevent recurrence
Verify changes with pg_reload_conf() and re-check pg_stat_user_tables after 15 minutes
Add a monitoring alert for n_dead_tup / n_live_tup > 0.1 on your largest tables

Situation

Symptoms

First Five Checks

Decision Tree

Remediation Options

Rollback Plan

Automation Opportunity

In Practice

Where It Breaks

What to Do Next

Checklist

Rajiv

Related Posts

Autovacuum Is a Capacity Problem, Not a Maintenance Task

When Autovacuum Becomes a Backpressure Signal

Prometheus + Grafana for Database Engineers: Open-Source Monitoring That Actually Works