Dead tuples accumulate silently in PostgreSQL. When autovacuum can't keep up, tables bloat, queries slow, and XID wraparound becomes a risk. Here's how to detect and respond.

PostgreSQL VACUUM: When Autovacuum Falls Behind and How to Know Before It’s Too Late

Autovacuum falling behind — dead tuples accumulate while the DBA looks elsewhere

A table holds 50,000 rows. It has been that way for six months. The physical size on disk is 9 GB and growing. Autovacuum shows as running on it constantly. Sequential scans that took 40 milliseconds now take 4 seconds. A new index helps briefly. A week later, performance degrades again.

The DBA looks at row counts, index usage, query plans. Nobody checks n_dead_tup. The table has 12 million dead row versions sitting alongside the 50,000 live ones.

The answer

PostgreSQL’s MVCC model never overwrites a row in place. Every UPDATE creates a new row version and marks the old one as dead. Every DELETE marks the row as dead without removing it. Dead tuples accumulate on disk until VACUUM removes them. Autovacuum handles this automatically, but it is intentionally throttled to avoid overwhelming production I/O — and on high-churn tables, it can fall behind. When it does, tables bloat, queries slow down, and the frozen transaction ID age climbs toward the wraparound threshold that forces PostgreSQL to take emergency action. Detecting this early requires querying pg_stat_user_tables and pg_database, not just watching query performance.

What you will learn

Why dead tuples accumulate and what happens when they are not removed
How autovacuum decides when to run and why it falls behind on busy tables
How to identify which tables are at risk using four diagnostic queries
How to respond: from tuning autovacuum per-table to handling XID wraparound urgency

Scope: PostgreSQL 13–17. Some columns and parameters are version-specific and noted where relevant. Behaviour on cloud-managed services (RDS, Cloud SQL, Azure Database for PostgreSQL) follows the same model but parameter names and modification paths differ by provider. The autovacuum cost delay default changed from 20ms to 2ms in PostgreSQL 13 — behaviour on older versions differs.

Why dead tuples accumulate

PostgreSQL implements MVCC by keeping multiple versions of each row on disk simultaneously. When a transaction updates a row, the old version is not overwritten — it is marked with an expiry transaction ID and left in place. The new version is written separately. Both versions exist on the same heap page until VACUUM removes the old one.

Before UPDATE:
  Page: [Row v1: xmin=100, xmax=NULL, data='A']

After UPDATE in transaction 200:
  Page: [Row v1: xmin=100, xmax=200, data='A']   ← dead after txn 200 commits
        [Row v2: xmin=200, xmax=NULL, data='B']   ← live

A row version becomes dead when no active transaction can see it any longer. PostgreSQL determines visibility using the transaction snapshot held by each active connection. A dead tuple cannot be removed while any transaction holds a snapshot that predates its expiry — which is why long-running transactions slow VACUUM down, covered later.

DELETE follows the same pattern: the row is marked dead at the expiry XID, but the heap space is not reclaimed until VACUUM runs.

The consequences of accumulated dead tuples:

Table bloat: heap file size grows even when live row count is stable
Index bloat: indexes contain entries pointing to dead tuples; index scans must skip or check them
Slower sequential scans: the scan must process all pages including those containing only dead tuples
Slower index scans: dead index entries reduce the effectiveness of index-only scans
Wasted I/O: pages filled with dead tuples are read into shared_buffers and then discarded

How autovacuum decides when to run

Autovacuum wakes periodically (controlled by autovacuum_naptime, default 1 minute) and checks each table against two thresholds:

VACUUM triggers when:
  n_dead_tup > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * n_live_tup

Defaults:
  autovacuum_vacuum_threshold    = 50
  autovacuum_vacuum_scale_factor = 0.20

For a table with 1,000,000 live rows: 50 + 0.20 × 1,000,000 = 200,050 dead tuples before autovacuum considers the table eligible. On a table with heavy UPDATE or DELETE activity, this threshold may not be reached proportionally — but the 20% default means a table must accumulate a fifth of its live-row count in dead versions before vacuum is triggered.

After it decides to run, autovacuum is throttled by autovacuum_vacuum_cost_delay (default 2ms in PostgreSQL 13+; was 20ms before) and autovacuum_vacuum_cost_limit (default 200). These settings pause autovacuum between I/O operations to prevent it from saturating disk. On a heavily loaded system with fast storage, this throttling is generous. On a high-churn table with many pages to clean, autovacuum may clean far too slowly to keep pace.

PostgreSQL 13 introduced autovacuum_vacuum_insert_scale_factor and autovacuum_vacuum_insert_threshold to trigger vacuum on INSERT-only tables, which previously never received autovacuum (only ANALYZE). If you are on PostgreSQL 12 or earlier, INSERT-heavy tables accumulate visibility map staleness and XID age without autovacuum intervention.

Why autovacuum falls behind

Threshold too coarse for the table. A 0.2 scale factor on a 10-million-row table means 2,000,050 dead tuples before autovacuum triggers. On a table that processes 100,000 updates per minute, that threshold accumulates quickly and the periodic cleanup cycle is too infrequent to keep the dead tuple count low.

Cost throttling limits throughput. The default cost delay and limit were designed for spinning disks and moderate load. On NVMe storage with high write rates, autovacuum’s default pace may process dead tuples more slowly than they are created.

Long-running transactions block cleanup. VACUUM cannot remove a dead tuple if any active transaction holds a snapshot that could still see it. A transaction open for hours — a long-running report, an idle-in-transaction session, a forgotten BEGIN — prevents VACUUM from removing any tuple that expired after that transaction started. The dead tuple accumulates indefinitely for those rows.

Worker count limits. autovacuum_max_workers (default 3) limits how many tables can be vacuumed simultaneously. On an instance with hundreds of active tables, three workers may not be enough.

Autovacuum cancellation. Autovacuum can be cancelled by a conflicting lock or by explicit pg_cancel_backend(). Unlike user queries, autovacuum does not hold locks aggressively — it backs off rather than blocking. This is safe for production but means a table under sustained lock contention receives incomplete vacuum passes.

The escalating consequence: XID wraparound

PostgreSQL uses 32-bit transaction IDs. With approximately 4 billion XIDs available, the database must periodically “freeze” old row versions — marking them as permanently visible so their XIDs can be reused. VACUUM performs this freezing. The key table-level counter is relfrozenxid in pg_class, which tracks the oldest non-frozen XID in the table.

When age(relfrozenxid) approaches autovacuum_freeze_max_age (default 200,000,000 transactions), autovacuum will run on the table to freeze old tuples regardless of dead tuple count, cost delay, or naptime settings. This forced run is called an anti-wraparound vacuum.

If freezing falls further behind — past vacuum_failsafe_age (default 1,600,000,000 transactions, introduced in PostgreSQL 14) — PostgreSQL issues warnings in the log. If it approaches 2 billion, PostgreSQL halts all transaction processing and refuses new connections until an emergency VACUUM FREEZE is completed. This is a full production outage.

This scenario is preventable with adequate monitoring, but it develops silently over time and is not visible in query performance until the forced anti-wraparound vacuums begin imposing I/O overhead.

Four diagnostic queries

1. Tables with the most dead tuples

SELECT
    schemaname,
    tablename,
    n_live_tup,
    n_dead_tup,
    ROUND(
        n_dead_tup::numeric / NULLIF(n_live_tup + n_dead_tup, 0) * 100,
        2
    )                           AS dead_pct,
    last_autovacuum,
    last_vacuum,
    autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Flag tables where dead_pct exceeds 10–20% or where last_autovacuum is more than a few hours old on a high-churn table. A NULL in last_autovacuum on a table that receives heavy writes means autovacuum has never run on it — investigate whether it is being excluded or whether the threshold has simply never been reached.

2. Active autovacuum workers

SELECT
    pv.pid,
    pd.datname,
    pc.relname,
    pv.phase,
    pv.heap_blks_total,
    pv.heap_blks_scanned,
    pv.heap_blks_vacuumed,
    pv.index_vacuum_count,
    pv.num_dead_tuples,
    pv.max_dead_tuples
FROM pg_stat_progress_vacuum AS pv
JOIN pg_class                AS pc ON pc.oid = pv.relid
JOIN pg_database             AS pd ON pd.oid = pv.datid;

This shows vacuum progress in real time (available since PostgreSQL 9.6). A worker stuck in the scanning heap phase on the same table repeatedly is a signal that it cannot keep pace. num_dead_tuples approaching max_dead_tuples means the worker must perform multiple passes through indexes before it can continue heap scanning.

3. Transactions blocking vacuum

SELECT
    pid,
    usename,
    state,
    backend_xmin,
    age(backend_xmin)               AS xmin_age,
    now() - xact_start              AS transaction_age,
    left(query, 120)                AS query_snippet
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC NULLS LAST;

Any session with a backend_xmin is holding a transaction snapshot that prevents VACUUM from removing tuples older than that XID. A session showing idle in transaction with a high xmin_age is a common cause of dead tuple accumulation on busy tables. The transaction_age column shows how long the session has been open.

4. XID wraparound exposure

SELECT
    datname,
    age(datfrozenxid)                                           AS xid_age,
    2000000000 - age(datfrozenxid)                              AS xids_remaining,
    ROUND(age(datfrozenxid)::numeric / 2000000000 * 100, 2)    AS pct_toward_wraparound
FROM pg_database
ORDER BY age(datfrozenxid) DESC;

Alert when xid_age exceeds 500,000,000 (25% toward wraparound). Investigate when it exceeds 1,000,000,000. Treat anything above 1,500,000,000 as urgent — this is where PostgreSQL will begin forced anti-wraparound vacuums and may issue warnings. At 1,800,000,000 and above, escalate immediately.

For per-table exposure:

SELECT
    n.nspname                                                   AS schemaname,
    c.relname                                                   AS tablename,
    age(c.relfrozenxid)                                         AS table_xid_age,
    pg_size_pretty(pg_total_relation_size(c.oid))               AS total_size
FROM pg_class AS c
JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY age(c.relfrozenxid) DESC
LIMIT 20;

How to respond

Immediate: run VACUUM manually on problem tables

Manual VACUUM does not block reads or writes on the table (except for a brief lock at the start). Run it directly on the tables identified in query 1:

VACUUM (VERBOSE, ANALYZE) schema_name.table_name;

VERBOSE prints progress to the session, useful for confirming which tables were cleaned and how many dead tuples were removed. ANALYZE updates statistics at the same time, which is usually necessary on bloated tables where planner estimates have drifted.

For XID wraparound urgency, use VACUUM FREEZE:

VACUUM (FREEZE, VERBOSE, ANALYZE) schema_name.table_name;

VACUUM FREEZE advances the frozen XID for all eligible tuples, not just the newly-dead ones. It is more I/O intensive than a standard VACUUM but is required to bring relfrozenxid forward. Only run this outside peak hours on large tables.

Per-table autovacuum tuning

For high-churn tables, reduce the thresholds at the table level without changing the instance-wide defaults:

ALTER TABLE schema_name.table_name SET (
    autovacuum_vacuum_scale_factor  = 0.01,
    autovacuum_vacuum_threshold     = 100,
    autovacuum_vacuum_cost_delay    = 2
);

A 1% scale factor on a 1,000,000-row table triggers autovacuum after 10,100 dead tuples rather than 200,050. The reduced cost delay allows autovacuum to run faster on this specific table. Verify the change took effect:

SELECT reloptions
FROM pg_class
WHERE relname = 'table_name';

Instance-wide tuning

If multiple tables are falling behind simultaneously, adjust the instance-wide parameters in postgresql.conf:

autovacuum_max_workers          = 5       # default 3; increase for instances with many tables
autovacuum_vacuum_cost_limit    = 400     # default 200; allows more I/O per autovacuum cycle
autovacuum_vacuum_cost_delay    = 2ms     # already default in PG 13+; confirm it has not been raised
autovacuum_vacuum_scale_factor  = 0.05   # default 0.20; triggers sooner on large tables

Changes to autovacuum_max_workers and autovacuum_vacuum_cost_limit require a reload (pg_reload_conf() or SELECT pg_reload_conf()). Changes to autovacuum_max_workers require a server restart.

Test parameter changes on a non-production instance first. Increasing autovacuum_vacuum_cost_limit raises I/O throughput for autovacuum, which on storage-constrained systems may impact foreground query performance.

Address long-running transactions

If query 3 identifies a session holding an old backend_xmin:

-- Identify idle-in-transaction sessions
SELECT pid, usename, state, now() - xact_start AS open_for
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY open_for DESC;

An idle in transaction session with an open duration measured in hours is almost always a bug in application connection handling. Set idle_in_transaction_session_timeout in postgresql.conf to automatically terminate sessions that hold transactions without executing queries:

idle_in_transaction_session_timeout = 300000   # 5 minutes, in milliseconds

This is safe for well-written applications and eliminates one of the most common causes of autovacuum falling behind.

In practice

Dead tuple accumulation is most severe on:

Tables with high UPDATE rates: every update creates a dead version of the previous row. A table receiving 10,000 updates per second generates 864,000,000 dead tuples per day — the autovacuum defaults cannot keep pace.
Tables with frequent bulk DELETEs: large delete operations create a spike of dead tuples that overwhelm a single autovacuum pass.
Tables held by long-running analytics queries: a reporting query open for two hours prevents vacuum from cleaning anything that changed since it started.
Partitioned tables: each partition is vacuumed independently. A parent table with many partitions may saturate autovacuum_max_workers, leaving some partitions unvacuumed for extended periods.
INSERT-heavy tables on PostgreSQL 12 and earlier: autovacuum did not trigger on INSERT-only tables before PostgreSQL 13, making XID age the only driver of vacuum on write-once tables.

Do not do this

Do not run VACUUM FULL as a first response to bloat. VACUUM FULL rewrites the entire table and acquires an ACCESS EXCLUSIVE lock that blocks all reads and writes for the duration. It is a table rebuild, not a maintenance operation. Use it only when you need to permanently reclaim disk space and can schedule a maintenance window.
Do not disable autovacuum on high-write tables with autovacuum_enabled = false. Dead tuples will accumulate without limit, and XID wraparound age will advance unchecked. If autovacuum is too aggressive, tune its cost parameters — do not disable it.
Do not rely on query performance alone to detect bloat. Query plan changes and index regressions are lagging indicators; by the time queries degrade noticeably, the table may already carry millions of dead tuples and significant XID age.
Do not leave idle_in_transaction_session_timeout unset on instances with application connection pools. A pooler that returns connections without committing open transactions silently blocks VACUUM on every table the session touched.

Official references

Conclusion

A table with 50,000 live rows and 12 million dead ones is not a storage anomaly — it is a VACUUM problem. The PostgreSQL MVCC model is correct by design; managing the consequences of that design is an operational responsibility. Autovacuum handles this correctly under typical conditions, but high-churn tables, long-running transactions, and undersized worker counts create gaps it cannot close on its own.

Check pg_stat_user_tables regularly. Watch XID age. Set idle_in_transaction_session_timeout. Tune autovacuum thresholds per table where the defaults are too coarse. None of this is complex — but it must be done before the emergency, not during it.

Continue reading

Marios Pavlidis Principal Database Administrator

PostgreSQL VACUUM: When Autovacuum Falls Behind and How to Know Before It's Too Late