Long-running transactions · PostgreSQL

Everything so far has been mechanism. This lesson is the bill.

The measurement

A table with 50,000 rows, freshly vacuumed and analysed.

Read-only / Safebaseline— Captured on 18.6 in a disposable cluster.

$ psql -U postgres -c "SELECT pg_size_pretty(pg_relation_size('churn')) AS size, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname='churn'"

  size   | n_live_tup | n_dead_tup
---------+------------+------------
1776 kB |      50000 |          0
(1 row)

One session then does the least it possibly can while still holding a snapshot: opens a REPEATABLE READ transaction, runs a single SELECT count(*), and goes quiet. It holds no locks anyone would notice. It has written nothing, so it has no transaction id at all.

While it sits there, the workload runs ten rounds of UPDATE churn SET v = v + 1, each followed by an explicit VACUUM.

Service impact possibleafter ten rounds of update and vacuum, with the snapshot still held— Captured on 18.6. Ten vacuums ran to completion during this window.

$ psql -U postgres -c "SELECT pg_size_pretty(pg_relation_size('churn')) AS size, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname='churn'"

 size  | n_live_tup | n_dead_tup
-------+------------+------------
19 MB |      50000 |     500000
(1 row)

1776 kB to 19 MB. The same 50,000 live rows. Half a million dead versions that ten separate vacuums declined to remove.

Vacuum says so itself, in as many words:

Read-only / SafeVACUUM VERBOSE during the same window— Real output from 18.6. The phrase to recognise is 'dead but not yet removable'.

$ psql -U postgres -c "VACUUM (VERBOSE) churn"

tuples: 0 removed, 550000 remain, 500000 are dead but not yet removable
removable cutoff: 683682, which was 10 XIDs old when operation ended
index scan not needed: 0 pages from table (0.00% of total) had 0 dead
item identifiers removed

0 removed. Not slow, not partial, not deferred. Zero. Vacuum ran, read the whole table, and correctly concluded that it was not allowed to touch anything, because a snapshot exists that might still need those versions.

And it does not come back

The holding transaction commits. Vacuum runs once more.

Read-only / Safeafter the holder ends, one more VACUUM— Captured on 18.6 immediately after the holding transaction committed.

$ psql -U postgres -c "VACUUM churn" -c "SELECT pg_size_pretty(pg_relation_size('churn')) AS size, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname='churn'"

 size  | n_live_tup | n_dead_tup
-------+------------+------------
19 MB |      50000 |          0
(1 row)

The dead rows are gone. The 19 MB is not. Part VI established why: vacuum makes space reusable, it does not return it to the filesystem.

So the table is now eleven times its original size, permanently, until something rewrites it under an exclusive lock. The session responsible read one row count and then did nothing at all.

`idle in transaction` is the dangerous state

pg_stat_activity.state distinguishes cases that look similar in a process list and are not remotely equivalent.

State	What it means	Holding a snapshot?
`active`	Executing a statement	Yes
`idle in transaction`	In a transaction, waiting for the client	Yes
`idle in transaction (aborted)`	Same, after an error	Yes
`idle`	No transaction open	No

idle in transaction is worse than active. An active query is at least making progress and will end. An idle in transaction session has stopped doing anything and is waiting on a client that may never speak again — a laptop that closed, a deployment that killed a pod mid-request, a developer’s psql behind a BEGIN typed before lunch.

It holds the snapshot for exactly as long as it takes anyone to notice.

The three timeouts, and what each actually does

-- Kill sessions idle inside a transaction. The important one.
ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';

-- Kill sessions idle with no transaction open. Frees connections; does
-- nothing for the horizon.
ALTER SYSTEM SET idle_session_timeout = '30min';

-- Cap any single statement.
ALTER SYSTEM SET statement_timeout = '0';   -- default; consider per-role

idle_in_transaction_session_timeout is the one that bounds this exposure, and 0 (unlimited) is the default. Setting it is one of the highest-value changes available on a cluster that has never had it.

Two cautions worth stating plainly.

It terminates the connection, not just the transaction. The application sees a closed connection and must reconnect. A pool handles this; a bare psycopg connection in a script may not.

It does not bound a long active query. A six-hour report is active throughout and this timeout never fires. statement_timeout is the tool for that, and it is usually right to set it per role — a tight value for the application, a generous one for a reporting role — rather than globally.

Both are user context, so they can be set per role with ALTER ROLE … SET, which is almost always the better shape than one cluster-wide value.

Finding the holder

During an incident, the question is “what is holding the horizon”, and the answer is the maximum of age(backend_xmin).

SELECT pid,
       usename,
       application_name,
       state,
       age(backend_xmin)                              AS xmin_age,
       now() - xact_start                             AS xact_duration,
       now() - state_change                           AS in_state_for,
       wait_event_type, wait_event,
       left(query, 80)                                AS query
  FROM pg_stat_activity
 WHERE backend_xmin IS NOT NULL
 ORDER BY age(backend_xmin) DESC
 LIMIT 10;

Read xmin_age first, not xact_duration. A transaction open for two hours on an idle cluster has done no damage; one open for two minutes during a bulk load may have done a great deal. Age in transactions is the measure of harm, because it counts what has happened since.

If the top of that list is empty, the holder is not a session

Three other things hold the horizon and appear in none of the session views:

SELECT gid, prepared, age(transaction) AS xid_age FROM pg_prepared_xacts;

SELECT slot_name, active, age(xmin) AS xmin_age, age(catalog_xmin) AS cat_age
  FROM pg_replication_slots;

SELECT application_name, state, age(backend_xmin) AS xmin_age
  FROM pg_stat_replication;

Prepared transactions survive disconnection and restart. There is no session, no query, and no client to blame.

Replication slots hold xmin for a consumer that may be gone. An inactive slot left behind by a decommissioned replica will hold the horizon until the slot is dropped — and, separately, will retain WAL until the disk fills.

hot_standby_feedback propagates a standby’s oldest snapshot back to the primary. A long report on a replica then blocks cleanup on the primary, which is a genuinely surprising causal chain the first time you meet it. Part XIV covers the trade this setting makes.

Terminating a holder

pg_terminate_backend(pid) ends the session and rolls back its transaction. For an idle in transaction session this is nearly always safe: it has, by definition, not been doing anything.

For an active session, decide deliberately. Part IX covers cancel versus terminate in full, with the measurements. The short form matters here because the two verbs behave differently depending on what the session is doing:

Against a session running a statement inside a transaction, pg_cancel_backend() aborts that transaction, which does release its snapshot and its locks. The session then sits idle in transaction (aborted), holding nothing but a connection slot.
Against a session that is already idle in transaction, pg_cancel_backend() returns true and does nothing at all — there is no statement to cancel, and the snapshot is still held. This was measured on 18.6 and is exactly the case this lesson is about.

So for the idle in transaction holder, terminate is the only verb that works.

Why vacuum cannot be cleverer about this

The natural objection is that vacuum ought to be able to tell that the reporting session will never look at the churn table, and clean it anyway.

It cannot, and the reason is not an implementation gap.

The horizon is a single cluster-wide value derived from the oldest snapshot in the system. A snapshot is not a list of tables; it is a statement about which transactions are visible. Any session holding one may, at any moment, run a query against any table in the database it is connected to. Nothing in the snapshot records intent.

PostgreSQL does refine this in specific, provable directions. It tracks a separate horizon for temporary tables, which no other session can reach, so a temp table can be cleaned regardless of what other sessions are holding. And the GlobalVisState machinery lets vacuum recompute the horizon during a long run, so a vacuum that starts while a snapshot is held can pick up ground if that snapshot ends mid-scan.

But the general case would require knowing what a session will do next, and that is not knowable. The correct engineering response is not a cleverer vacuum; it is not holding snapshots open.

This also explains a diagnostic detail from the capture above. removable cutoff: 683682, which was 10 XIDs old when operation ended is vacuum telling you exactly where its horizon was. When that cutoff is far behind the current transaction id, you are looking at the problem directly, and the next question is which of the four sources is holding it.

What to take from this

One held snapshot took a table from 1776 kB to 19 MB in ten cycles and ten vacuums removed zero rows. The size did not come back.
The horizon is cluster-wide. What the holding session queried is irrelevant.
idle in transaction is worse than active, because nothing will end it.
Set idle_in_transaction_session_timeout, preferably per role. The default is unlimited.
Sort by age(backend_xmin), not by duration. If no session appears, check prepared transactions, replication slots and replica feedback.

Cross-course references

Linux for Production Sysadmins — Part VI (Processes) covers identifying the client process behind an idle-in-transaction backend, and Part LXXXI (Incident command) covers the decision to terminate somebody else’s session.
Observability for Production Sysadmins — Part LIX (Database observability) covers alerting on transaction duration, and Part XX (Alert quality) covers why an alert on this needs a threshold nobody routinely exceeds.