Why Did Autovacuum Stall and My Table Keep Bloating?

Your dead-tuple count keeps climbing, autovacuum is clearly running, and the table on disk just keeps growing. The vacuum is not broken. Something in your cluster is holding the xmin horizon open, and until you find it, no amount of vacuuming will reclaim a single row.

This is one of the most misdiagnosed problems in PostgreSQL operations. People tune autovacuum thresholds, raise maintenance_work_mem, add more workers, and watch the bloat carry on regardless. The tuning is not the issue. Vacuum is doing exactly what it is told: it will not remove a dead tuple that could still be visible to some transaction’s snapshot. If the oldest snapshot in the system is pinned in the past, every row version created since then is off-limits. The fix is to find what is pinning it. The examples below use PostgreSQL 17, but the mechanism is unchanged back to 9.4.

Why vacuum leaves the tuples where they are

Every row version in PostgreSQL carries an xmin (the transaction that created it) and, when deleted or updated, an xmax. A dead tuple can only be physically removed once no running transaction could ever need to see it again. The cut-off for that decision is the xmin horizon: the oldest transaction ID that any live snapshot still cares about. Vacuum will not remove a dead tuple newer than that horizon, because doing so could break a transaction that is entitled to the older view of the data.

So when autovacuum runs and the dead tuples stay put, the question is never “why is vacuum failing?” It is “what is holding the horizon open?” There are three usual suspects, and all three are visible from the catalogs: a long-running or idle-in-transaction session, an abandoned prepared transaction, and a stale replication slot.

Confirm the diagnosis first

Before hunting the cause, confirm that unremovable dead tuples are really the symptom. Check the table’s statistics, then run a verbose vacuum and read the cut-off it prints.

-- 1. Is the dead-tuple count actually the problem?
SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(n_dead_tup::numeric / nullif(n_live_tup, 0), 3) AS dead_ratio,
       last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'orders';

-- 2. Ask vacuum directly what it could not remove and why.
VACUUM (VERBOSE) orders;
-- Look for a line such as:
--   "1423911 dead row versions cannot be removed yet, oldest xmin: 987654321"
-- and the "removable cutoff" reported on recent versions.

If verbose vacuum reports a large number of dead rows that “cannot be removed yet” alongside an oldest xmin value, you have your confirmation. That xmin is the horizon. Now find who owns it.

Find what pins the horizon

Three queries, one per suspect. Run all three; more than one can be guilty at once, and vacuum is held back by whichever horizon is oldest.

-- Suspect 1: long-running or idle-in-transaction backends.
-- backend_xmin is the horizon this session forces vacuum to respect.
SELECT pid,
       state,
       backend_xmin,
       age(backend_xmin)          AS xmin_age,
       now() - xact_start         AS txn_age,
       now() - state_change       AS idle_for,
       left(query, 60)            AS last_query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC
LIMIT 10;

-- Suspect 2: abandoned prepared (two-phase) transactions.
-- These survive disconnects and hold their xmin indefinitely.
SELECT gid, prepared, owner, database, transaction AS xid
FROM pg_prepared_xacts
ORDER BY prepared;

-- Suspect 3: stale replication slots (physical or logical CDC).
-- An inactive slot keeps xmin/catalog_xmin frozen in the past.
SELECT slot_name,
       slot_type,
       active,
       xmin,
       catalog_xmin,
       age(xmin)         AS xmin_age,
       wal_status
FROM pg_replication_slots
ORDER BY age(xmin) DESC NULLS LAST;

Read them in that order. A session sitting in idle in transaction for hours, with a large xmin_age, is the most common culprit — usually an application that opened a transaction, ran one statement, and then went to sleep waiting on something else. A row in pg_prepared_xacts that nobody remembers is an orphaned distributed transaction, often left by a crashed application server or a misconfigured XA coordinator. And an active = false replication slot with an old xmin is a change-data-capture consumer or a decommissioned replica that was never cleaned up.

Clear it, then confirm reclaim

Match the fix to the suspect. Each command releases a different hold on the horizon.

-- Idle-in-transaction backend: cancel or terminate by pid.
SELECT pg_cancel_backend(48213);      -- try to cancel first
SELECT pg_terminate_backend(48213);   -- terminate if it will not budge

-- Orphaned prepared transaction: resolve it by its gid.
ROLLBACK PREPARED 'foo_txn_7f3a91';

-- Stale replication slot: drop it (only if you are certain
-- no consumer still needs it -- dropping a live slot breaks it).
SELECT pg_drop_replication_slot('cdc_orders_v1');

-- Now prove the horizon has moved and vacuum can reclaim.
VACUUM (VERBOSE) orders;
SELECT n_dead_tup FROM pg_stat_user_tables WHERE relname = 'orders';

One caveat that catches people out at the finish line: a plain VACUUM makes the reclaimed space reusable by the table, but it does not hand the pages back to the operating system. The file on disk stays the same size; it simply stops growing. If the table has already bloated badly and you need the disk space back, you need VACUUM FULL (which takes an exclusive lock and rewrites the table) or pg_repack (which does it online). Clearing the horizon stops the bleeding; shrinking the file is a separate decision.

Stop it happening again

Fixing the incident is the easy part. The recurring version of this problem is a technical-debt problem: applications that leak open transactions, CDC pipelines that create slots and never reap them, and no alerting on any of it. A few durable guardrails:

• Set idle_in_transaction_session_timeout (for example, '5min') so the database terminates sessions that hold a transaction open and then go idle. This alone eliminates the most common cause.
• Set max_slot_wal_keep_size so a forgotten slot cannot fill the disk with retained WAL; watch for wal_status = 'lost', which tells you a slot has been invalidated.
• Alert on age(backend_xmin) across pg_stat_activity and on the oldest slot xmin, not just on raw dead-tuple counts. The horizon age is the leading indicator; the bloat is the lagging one.

Horizon age belongs on the same operational dashboard as the metrics you already track for delivery and recovery. If you are already instrumenting change-failure rate and MTTR, add the oldest xmin horizon next to them — it is the difference between catching a leaked transaction in ten minutes and finding it after a weekend of unchecked bloat. And the timeout and slot limits above are exactly the kind of enforced default that belongs in your baseline configuration rather than tribal knowledge, so the next engineer inherits the guardrail instead of the incident.

Autovacuum almost never deserves the blame it gets. When dead tuples will not clear, stop tuning the vacuum and go find the transaction, the prepared xact, or the slot that is holding the horizon open. The database will tell you which one it is if you ask it the right question.