Long Transactions: The Cost You Don't See

A transaction that stays open holds a snapshot, and that snapshot decides the oldest row version the entire cluster is still obliged to keep. One analytics query, one connection sitting idle inside a transaction, one replication slot left behind after an experiment: each of them can stop dead rows being removed on every table in every database, including tables that session never read.

What makes this the most expensive item in the chapter is the distance between cause and symptom. The thing you notice is a table growing, plans getting slower, and a disk filling. The thing responsible is a connection doing nothing at all, three layers away, and no error is raised anywhere along the path.

The Horizon

Cleanup has one rule: a row version may be removed only when no snapshot could still legitimately need it. The cluster therefore tracks the oldest transaction id any live snapshot depends on, and vacuum treats that value as a floor. Versions expired before it can go. Versions expired after it must stay, however dead they look and however many gigabytes they occupy.

That single number decides whether cleanup works at all, and it is not a per-table or per-database property. It is one boundary for the whole cluster, and any backend can pin it. Chapter 7 is about what vacuum does with the space it is allowed to reclaim; this topic is about the number that decides how much of it there is.

The Four Ways to Hold It Back

A long-running query is the honest case. The nightly reconciliation over 40 million rows in orders genuinely needs the rows its snapshot describes, and it holds the horizon for its full runtime. A session sitting idle in transaction is the dishonest one: it holds exactly the same snapshot and is doing nothing with it, usually because a framework opened a transaction on request entry and an exception path skipped the commit. In pg_stat_activity that session looks idle, and it is not — it is holding the cluster's cleanup boundary at whatever time it started.

The third holder is a replication slot. A slot exists to guarantee that its consumer can still receive everything it has not yet processed, so it retains WAL and, for a logical slot, pins the transaction ids the decoding will need. A slot created for a one-afternoon logical-replication experiment and never dropped goes on doing that job for months, with nothing connected to it.

The fourth is the cruellest, because it appears in no connection list and survives a restart: a prepared transaction, left behind by two-phase commit. PREPARE TRANSACTION writes the transaction to disk and detaches it from any session, and one left in that state interferes with vacuum's ability to reclaim storage and can, in the extreme, force a shutdown to prevent transaction id wraparound. The saving grace is that max_prepared_transactions is zero by default, so the feature has to have been deliberately enabled before this can happen to you. If an external transaction manager is not tracking them, leaving it at zero is the documented recommendation.

The Symptom Chain

The horizon stops moving. Dead tuples accumulate on the tables with real write rates — inventory first, because it is small and hot. The tables and their indexes grow, so the same rows now occupy more pages, so scans read more pages and plans get slower. Autovacuum runs on schedule, works correctly, removes nothing, and reports precisely that.

automatic vacuum of table "cartwheel.public.inventory": index scans: 0
  pages: 0 removed, 115200 remain
  tuples: 0 removed, 12000 remain, 4200000 are dead but not yet removable
  removable cutoff: 487355012, which was 1904223 XIDs old when operation ended

That log entry is a diagnosis, not a vacuum problem. Zero tuples removed with four million dead ones present says the work ran and was forbidden to do anything, and the removable cutoff being nearly two million transaction ids old names the reason: something has been holding a snapshot for roughly two million transactions' worth of time. No autovacuum setting will move that cutoff.

Finding the Holder in One Query

The answer lives in three catalogue views, and the documentation names all three for exactly this purpose: pg_stat_activity for backends, pg_replication_slots for slots, and pg_prepared_xacts for two-phase transactions. Ranking them by the age of the id each one pins puts the culprit on the first row.

SELECT 'backend' AS kind, pid::text AS who, state,
       age(backend_xmin) AS xid_age, xact_start
  FROM pg_stat_activity
 WHERE backend_xmin IS NOT NULL
UNION ALL
SELECT 'slot', slot_name, active::text,
       age(xmin), NULL
  FROM pg_replication_slots
 WHERE xmin IS NOT NULL
UNION ALL
SELECT 'prepared', gid, 'prepared',
       age(transaction), prepared
  FROM pg_prepared_xacts
 ORDER BY xid_age DESC NULLS LAST
 LIMIT 10;

Run it as the first step of every bloat investigation, before looking at a single autovacuum setting. A backend row with a state of idle in transaction and an xact_start from this morning is the whole answer; so is a slot with no consumer attached. Keep the query in the runbook rather than in someone's shell history, because the moment it is needed is not the moment to be composing SQL. For an inactive slot the active column is the tell, and for a prepared transaction the gid is what the external transaction manager knows it by.

The Timeouts That Prevent It

This class of incident becomes a non-event on three settings, and all three ship disabled. idle_in_transaction_session_timeout terminates a session that has been idle inside an open transaction for longer than the limit, and it is arguably the highest-value non-default setting in this book. statement_timeout aborts a statement that runs too long. transaction_timeout, added in 17, terminates a session whose transaction as a whole exceeds the limit, which is the one that catches a transaction that keeps busy with a stream of short statements and never commits.

ALTER ROLE cartwheel_app       SET idle_in_transaction_session_timeout = '15s';
ALTER ROLE cartwheel_app       SET statement_timeout                  = '10s';
ALTER ROLE cartwheel_app       SET transaction_timeout                = '60s';

ALTER ROLE cartwheel_analytics SET idle_in_transaction_session_timeout = '5min';
ALTER ROLE cartwheel_analytics SET statement_timeout                   = '30min';

Per-role limits are what make this workable: checkout has no business holding a transaction for fifteen seconds, and the reconciliation job legitimately needs half an hour. There are two details worth knowing before setting them. If transaction_timeout is shorter than or equal to one of the other two, the longer of those is ignored, so the three interact rather than stacking. And prepared transactions are explicitly not subject to transaction_timeout, so the fourth holder is caught by monitoring or not at all.

The Legitimate Long Transaction

Some long transactions are correct. A nightly reconciliation that must see one consistent instant across nine tables needs its hour-long snapshot, and shortening it would break the thing it exists to do. The answer is to run it where it costs least, which for Cartwheel means pg-replica-a rather than pg-primary, give it its own role with its own limits, and know the number it is costing rather than pretending it is free.

Moving it to the replica moves the tension rather than deleting it. With hot_standby_feedback off, which is the default, the replica reports nothing upstream and a long query there can be cancelled once replay needs to remove rows it is reading — max_standby_streaming_delay allows 30 seconds of that before the cancellation, which happens to be the same 30 seconds the dashboard already tolerates. Turn the feedback on and the query survives, at the cost of the primary keeping those rows: the manual's own framing is that it delays cleanup on the primary and may cause undesirable bloat, but no more than running the query on the primary would have. Chapter 7 picks the story up from here, with the vacuum that finally has room to work.