Long-running transactions

This is the most common way a healthy Postgres database quietly becomes an unhealthy one. Nothing errors. Nothing is obviously slow at first. Queries just get a little worse every hour.

What actually happens

Postgres never overwrites a row in place. An UPDATE writes a new version of the row and marks the old one as no longer current; a DELETE only marks. The old versions stay on disk because some transaction that started earlier might still be entitled to see them.

Cleaning them up is VACUUM's job, and VACUUM has one rule: it can only remove a row version that is invisible to every transaction currently open. The oldest open transaction therefore sets a floor - the horizon - and nothing newer than that floor can be reclaimed, in the entire database.

So one session that ran BEGIN and then went idle holds the floor where it is. Meanwhile the application keeps writing. Dead row versions pile up behind the floor, and:

• Sequential scans read dead rows, check them, and discard them - work that produces nothing.
• Index scans follow entries pointing at rows that are no longer visible, costing an extra page read each.
• The table's files keep growing, so less of it fits in cache.
• Autovacuum keeps running, keeps finding nothing it is allowed to remove, and keeps costing I/O.

The moment that transaction commits or is killed, the floor moves and the next vacuum clears the backlog. The problem does not degrade gracefully and it does not resolve gradually - it holds, then it is gone.

The usual causes

• A BEGIN in application code with an early return or a thrown exception on a path that forgets to roll back.
• An ORM that opens a transaction per request and holds it across an outbound HTTP call.
• A connection pool with a leaked connection that is mid-transaction.
• A long analytical query or a pg_dump - these are legitimately holding the horizon, but they are also legitimately doing work. Duration alone does not make a transaction a bug.
• An interactive psql session where somebody typed BEGIN an hour ago.

Finding it

The culprit is the session with the lowest backend_xmin, which is not necessarily the one that has been connected longest:

SELECT pid,
       usename,
       state,
       age(backend_xmin)                            AS xmin_age,
       now() - xact_start                           AS transaction_age,
       now() - state_change                         AS in_this_state_for,
       left(query, 120)                             AS query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC
LIMIT 10;

Read it like this:

• state = 'idle in transaction' and a large transaction_age - this is the bug. The session is holding the horizon and doing nothing with it.
• state = 'active' with a large transaction_age - a genuinely long query. It is still holding the horizon, but killing it loses real work; decide deliberately.
• xmin_age in the millions with everything else looking fine - cleanup has been blocked for a long time. Expect a large backlog once it clears.

To end one, once you know which:

SELECT pg_cancel_backend(<pid>);     -- cancel the query, keep the connection
SELECT pg_terminate_backend(<pid>);  -- drop the connection entirely

pg_cancel_backend does nothing to a session that is idle in a transaction - there is no query to cancel. Use pg_terminate_backend for those.

Checking the damage afterwards

SELECT relname,
       n_live_tup,
       n_dead_tup,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

Autovacuum will work through the backlog on its own. It is worth running VACUUM (VERBOSE) your_table; on the worst offender if you would rather not wait - it does not lock the table against reads or writes.

Note that VACUUM returns space for reuse by that table, it does not shrink the files on disk. If a table bloated badly enough that you want the space back, that is a rewrite - VACUUM FULL (takes an exclusive lock for the duration) or, better, see Large tables.

The advisory

CapyDB raises a Long-running transaction advisory when a transaction has been open past the threshold for a sustained period. It is a dashboard advisory - it does not email you - because the answer is almost always "look at pg_stat_activity and decide", not "wake up".

It exists alongside the Vacuum needed advisory on purpose. Vacuum needed measures dead rows: the symptom. This one measures the horizon: the cause. If both are open, fix this one and the other resolves on its own.

Preventing it

Your cell already has idle_in_transaction_session_timeout set to 10 minutes, which catches abandoned transactions without touching queries that are actually running - see Timeouts. What reaches the advisory is therefore usually a transaction that is genuinely still active.

Beyond that, the fixes are in application code:

• Open the transaction as late as possible and commit as early as possible. Never hold one across a network call to something that is not the database.
• Use your driver's scoped transaction helper (db.transaction(...)) rather than issuing BEGIN and COMMIT by hand, so an exception cannot skip the rollback.
• Give reporting and batch work their own role with its own statement_timeout, so a slow report is bounded without bounding the web app.