Long Transaction Blocking Vacuum
n_dead_tup for one or more tables stays high and climbs even though last_vacuum / last_autovacuum are recent. VACUUM completes 'successfully' but reclaims...
In 10 seconds
A table keeps growing and its query plans keep getting slower, yet autovacuum appears to be running constantly and pg_stat_user_tables shows n_dead_tup stuck high and never falling. Dead tuples are piling up faster than VACUUM can remove them -- not because VACUUM isn't running, but because it physically cannot reclaim them. Somewhere a single long-running (or idle-in-transaction) session is holding an old snapshot, and VACUUM is forbidden from removing any row version that session might still need to see. You need to prove that's what's happening, identify the exact backend pinning the horizon, and clear it so cleanup can resume.
Why This Happens
VACUUM may only remove a dead tuple if no still-running transaction could ever need to see it again. PostgreSQL tracks this with the cluster's oldest snapshot -- the xmin horizon. Any backend that began a transaction and still holds a snapshot publishes a backend_xmin; replication slots publish xmin/catalog_xmin; prepared transactions hold one too. VACUUM computes its 'removable cutoff' (PG15+) / 'oldest xmin' (PG14-) as the minimum of all of those, and refuses to reclaim any row version newer than it. A REPEATABLE READ transaction that ran even a single SELECT pins its snapshot for as long as it stays open -- and an application bug that leaves a connection 'idle in transaction' does exactly that, indefinitely.
So when one session holds an old snapshot and another session churns the table (UPDATEs and DELETEs), every superseded row version becomes dead-but-not-removable. autovacuum dutifully runs, sees it cannot advance past the cutoff, and removes nothing. The dead tuples accumulate as pure bloat until the holder's transaction ends, at which point the very next VACUUM reclaims them all at once. The dead tuples accumulate as pure bloat until the holder's transaction ends, at which point the very next VACUUM reclaims them all at once.
Diagnose
Free Tier -- Basic Approach
The first question: is any session old enough to be holding the horizon? List transactions by age and show whoever publishes a backend_xmin.
NOTICE: relation "ledger" already exists, skipping CREATE TABLE TRUNCATE TABLE INSERT 0 20000 VACUUM BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT count(*) FROM ledger; count ------- 20000 (1 row) UPDATE 20000 ----- long-running transactions (FREE: who is old?) ----- pid | state | xact_age_s | backend_xmin | last_query -----+---------------------+------------+--------------+------------------------------ 131 | idle in transaction | 3 | 701057 | SELECT count(*) FROM ledger; (1 row) ----- dead tuples still present in ledger ----- relname | n_live_tup | n_dead_tup | last_vacuum | last_autovacuum ---------+------------+------------+-------------------------------+------------------------------- ledger | 20000 | 20000 | 2026-06-19 09:26:58.652295+00 | 2026-06-19 09:26:57.863864+00 (1 row) ROLLBACK;
pg_stat_activity surfaces one backend in state 'idle in transaction' with a non-null backend_xmin (701057) and last_query 'SELECT count(*) FROM ledger;' -- the snapshot-holder. Right beside it, n_dead_tup is still 20000 despite a recent vacuum. That pairing -- an old idle-in-transaction backend + un-falling dead tuples -- is the free-tier diagnosis.
Fix
End the transaction that holds the snapshot -- here by terminating the idle-in-transaction backend -- then VACUUM. With the horizon released, the previously un-removable tuples become removable and are reclaimed in a single pass.
NOTICE: relation "ledger" already exists, skipping CREATE TABLE TRUNCATE TABLE INSERT 0 20000 VACUUM BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT count(*) FROM ledger; count ------- 20000 (1 row) UPDATE 20000 ----- FIX: terminate the long-running idle-in-transaction holder ----- pid | terminated -----+------------ 220 | t (1 row) ----- VACUUM (VERBOSE) ledger ----- INFO: vacuuming "appdb.public.ledger" INFO: finished vacuuming "appdb.public.ledger": index scans: 1 pages: 0 removed, 177 remain, 177 scanned (100.00% of total) tuples: 20000 removed, 20000 remain, 0 are dead but not yet removable removable cutoff: 701064, which was 0 XIDs old when operation ended new relfrozenxid: 701063, which is 2 XIDs ahead of previous value frozen: 0 pages from table (0.00% of total) had 0 tuples frozen index scan needed: 89 pages from table (50.28% of total) had 20000 dead item identifiers removed index "ledger_pkey": pages: 112 in total, 0 newly deleted, 0 currently deleted, 0 reusable I/O timings: read: 0.000 ms, write: 0.000 ms avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s buffer usage: 609 hits, 0 misses, 0 dirtied WAL usage: 467 records, 0 full page images, 200774 bytes system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s VACUUM ----- dead tuples still present in ledger ----- relname | n_live_tup | n_dead_tup | last_vacuum | last_autovacuum ---------+------------+------------+-------------------------------+------------------------------- ledger | 20000 | 0 | 2026-06-19 09:27:17.291835+00 | 2026-06-19 09:26:57.863864+00 (1 row) ROLLBACK; FATAL: terminating connection due to administrator command server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. connection to server was lost
pg_terminate_backend returns t for the holder (the FATAL line on session A is that backend being killed -- expected). The immediately following VACUUM (VERBOSE) now reports 'tuples: 20000 removed, 20000 remain, 0 are dead but not yet removable', does the index pass it skipped before ('89 pages ... had 20000 dead item identifiers removed'), and pg_stat_user_tables drops to n_dead_tup = 0. Same table, same VACUUM -- the only thing that changed is the snapshot is gone.
Verify
Confirm cleanup now keeps up: churn the table again, VACUUM, and check that dead tuples return to zero and no old snapshot-holder lingers.
UPDATE 20000
pg_sleep
----------
(1 row)
VACUUM
relname | n_live_tup | n_dead_tup
---------+------------+------------
ledger | 20000 | 0
(1 row)
lingering_old_xacts
---------------------
0
(1 row)
After another UPDATE 20000 and a VACUUM, n_dead_tup is back to 0 and lingering_old_xacts (idle-in-transaction backends with a non-null backend_xmin) is 0. The horizon is clear, so VACUUM reclaims normally -- the steady state is restored.
⏪ Rollback
Nothing here is destructive in the sense of data loss -- the dead row versions are, by definition, already superseded. The action that resolves it (ending the offending transaction, by application fix or pg_terminate_backend) only rolls back work that session had not committed anyway. The mistake to avoid is terminating the wrong backend, or terminating a legitimate long-running job (a big report, a backup) instead of finding the real culprit. Always confirm the holder is genuinely idle-in-transaction (or an orphaned slot / stranded prepared xact) before you cancel it, and prefer fixing the application's transaction lifecycle over repeatedly killing connections.
📦 Version Matrix (PG 14--18)
The mechanism is identical on every supported major version; only the VACUUM VERBOSE wording differs. Each version holds a snapshot, churns the table, and confirms VACUUM declares the dead tuples un-removable -- then reclaims them once the holder ends.
⚠ Common Mistakes
Stop this incident before it pages you
The free runbook clears today's failure. Pro adds the standing guardrails -- the alert query, the dashboard, and the managed-service knobs -- so it never becomes an incident again.
- ✓ A ready-to-paste alert query with the right thresholds.
- ✓ The topology change that prevents recurrence.
- ✓ The exact knobs on RDS, Azure Flexible Server & Cloud SQL.
Frequently Asked Questions
How do I tell 'vacuum can't keep up' from 'vacuum is blocked'?
Run VACUUM (VERBOSE) on the table. If it reports tuples as 'dead but not yet removable' (PG15+) or 'dead row versions cannot be removed yet, oldest xmin: N' (PG14-), VACUUM is being blocked by an old snapshot -- not falling behind. If it reports tuples 'removed', it simply needed to run.
Which session is holding the horizon?
SELECT pid, state, xact_start, backend_xmin, query FROM pg_stat_activity WHERE backend_xmin IS NOT NULL ORDER BY xact_start. The oldest xact_start with a non-null backend_xmin is the prime suspect; 'idle in transaction' is the classic application bug. Also check pg_replication_slots (xmin, catalog_xmin) and pg_prepared_xacts.
How do I confirm it's really that backend and not a coincidence?
Compare the holder's published snapshot xmin to VACUUM's removable cutoff / oldest xmin. When they match, that backend is provably the one defining the cutoff. The premium proof below shows the two values equal at 701060.
Can I just kill the connection?
If it is a genuinely stuck idle-in-transaction session, yes: SELECT pg_terminate_backend(pid). The right long-term fix is the application -- ensure it COMMITs/ROLLBACKs promptly and set idle_in_transaction_session_timeout so the server reaps such sessions automatically. Do not terminate a legitimate long-running job; wait for it or reschedule it.
After I clear the holder, do I have to VACUUM manually?
Autovacuum will reclaim the dead tuples on its next pass, but if the table is hot you can run VACUUM immediately to reclaim now. Once the snapshot is gone the cutoff advances and the previously un-removable tuples become removable -- the next VACUUM removes them all at once.