Long transactions block VACUUM
Everything in this chapter converges here. Old row versions must be kept as long as any snapshot might still read them — so one forgotten transaction, holding one old snapshot, pins garbage collection for the whole database. Not only the tables it read: every table, because PostgreSQL tracks "this backend's snapshot needs xids from here back", not which rows it will touch.
VACUUM ran, cleaned nothing
A opens a long report transaction — one query, then it sits there idle.
A> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
A> SELECT xmin, id, status FROM jobs;
xmin | id | status
------+----+--------
1001 | 1 | new
(1 row)Meanwhile B churns through the row, leaving dead versions behind.
B> UPDATE jobs SET status = 'running' WHERE id = 1 RETURNING xmin, status;
xmin | status
------+---------
1002 | running
(1 row)
B> UPDATE jobs SET status = 'done' WHERE id = 1 RETURNING xmin, status;
xmin | status
------+--------
1003 | done
(1 row)
B> UPDATE jobs SET status = 'archived' WHERE id = 1 RETURNING xmin, status;
xmin | status
------+----------
1004 | archived
(1 row)
B> SELECT lp, t_xmin, t_xmax, t_ctid
FROM heap_page_items(get_raw_page('jobs', 0)) ORDER BY lp;
lp | t_xmin | t_xmax | t_ctid
----+--------+--------+--------
1 | 1001 | 1002 | (0,2)
2 | 1002 | 1003 | (0,3)
3 | 1003 | 1004 | (0,4)
4 | 1004 | 0 | (0,4)
(4 rows)
B> VACUUM jobs;
VACUUMVACUUM ran, reported success — and removed nothing. A's snapshot might still need every one of those versions.
B> SELECT lp, t_xmin, t_xmax, t_ctid
FROM heap_page_items(get_raw_page('jobs', 0)) ORDER BY lp;
lp | t_xmin | t_xmax | t_ctid
----+--------+--------+--------
1 | 1001 | 1002 | (0,2)
2 | 1002 | 1003 | (0,3)
3 | 1003 | 1004 | (0,4)
4 | 1004 | 0 | (0,4)
(4 rows)And indeed: A still reads the version from before all three updates.
A> SELECT id, status FROM jobs;
id | status
----+--------
1 | new
(1 row)
A> COMMIT;
COMMIT
B> VACUUM jobs;
VACUUMSame command, a moment after A commits — now the three dead versions are gone.
B> SELECT lp, lp_flags, t_xmin, t_xmax, t_ctid
FROM heap_page_items(get_raw_page('jobs', 0)) ORDER BY lp;
lp | lp_flags | t_xmin | t_xmax | t_ctid
----+----------+--------+--------+--------
1 | 2 | | |
2 | 0 | | |
3 | 0 | | |
4 | 1 | 1004 | 0 | (0,4)
(4 rows)Verified against PostgreSQL 18.4 · Run it yourself · Scenario source
The first VACUUM is the quiet failure mode: it succeeds. No error, no warning in your terminal — and the heap page is byte-for-byte unchanged, because the manual's rule is that "the row version must not be deleted while it is still potentially visible to other transactions", and A's Repeatable Read snapshot, taken before all three updates, can still see every one of them (the scenario proves it: A still reads 'new'). The instant A commits, the identical command clears the page.
Autovacuum hits the same wall. A dashboard that shows autovacuum running on schedule can sit right next to a table that's ballooning — the vacuums are running and keeping nothing, while every UPDATE adds another dead tuple behind the pinned horizon.
Spotting the offender
The horizon is visible in pg_stat_activity.backend_xmin — the oldest xid each backend's snapshot still needs. The classic triage query (illustrative here; the production chapter turns it into monitoring):
SELECT pid, state, application_name,
age(backend_xmin) AS snapshot_age_xids,
now() - xact_start AS tx_duration
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC;The usual suspects: an app that did BEGIN and went idle (state = 'idle in transaction' — the same villain as in the DDL outage), a many-hour analytics query against the primary, a stuck migration. Guardrails exist for each:
idle_in_transaction_session_timeout— kills sessions that hold a transaction open while doing nothing;transaction_timeout(PostgreSQL 17+) — a hard ceiling on total transaction duration;statement_timeout— bounds any single query.
The lesson underneath all three is that a long transaction is a database-wide tax, even when it's read-only and touches one tiny table. The snapshot is what's expensive, not the query — the cost is the time you hold one open. And the failure is silent: VACUUM and autovacuum degrade to no-ops behind an old snapshot, invisible until the bloat itself becomes visible. So keep transactions short by design and set idle_in_transaction_session_timeout as a seatbelt; chapter 8 builds the alerting version of the query above.