Long & idle transactions

The most damaging thing a session can do in PostgreSQL is nothing — inside an open transaction. It holds locks, it pins VACUUM's horizon for the whole database, and it occupies a pooled connection. This lesson is about finding those sessions, then making sure they can't live long.

Finding them

A starts a 'quick report' at REPEATABLE READ… and never gets around to committing.

A> BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN

A> SELECT count(*)::int AS orders FROM orders;
 orders 
--------
      2 
(1 row)

Detector 1 — the oldest open transaction, and how long it's been open:

M> SELECT application_name, state, now() - xact_start > interval '1 second' AS older_than_1s
   FROM pg_stat_activity
   WHERE xact_start IS NOT NULL AND pid <> pg_backend_pid()
   ORDER BY xact_start LIMIT 1;
 application_name |        state        | older_than_1s 
------------------+---------------------+---------------
 A                | idle in transaction | t             
(1 row)

Detector 2 — sessions holding a transaction open while doing nothing:

M> SELECT application_name
   FROM pg_stat_activity
   WHERE state = 'idle in transaction' AND now() - state_change > interval '1 second';
 application_name 
------------------
 A                
(1 row)

Detector 3 — the reason it matters even for a read-only report: A's snapshot (backend_xmin) is what VACUUM must preserve.

M> SELECT application_name, backend_xid IS NOT NULL AS wrote_anything,
          backend_xmin IS NOT NULL AS pins_vacuum_horizon
   FROM pg_stat_activity
   WHERE backend_xmin IS NOT NULL AND pid <> pg_backend_pid();
 application_name | wrote_anything | pins_vacuum_horizon 
------------------+----------------+---------------------
 A                | f              | t                   
(1 row)

A> COMMIT;
COMMIT

Verified against PostgreSQL 18.4 · Run it yourself · Scenario source

Note what detector 3 proved: the report never wrote a thing (backend_xid is null, because no transaction id was ever assigned) and still pins the vacuum horizon through its snapshot (backend_xmin). Read-only is not harmless, and age is what matters: the session with the oldest xact_start is almost always the story.

Guardrails: make the database enforce it

Detection finds today's incident; timeouts prevent next month's. PostgreSQL ships three, from narrowest to widest:

statement_timeout: the per-statement seatbelt.

A> SET statement_timeout = '100ms';
SET

A> SELECT pg_sleep(2); -- query_canceled
ERROR:  57014: canceling statement due to statement timeout

Only the statement died — the session and its transaction state are fine.

A> SELECT 'still here' AS session;
  session   
------------
 still here 
(1 row)

transaction_timeout (PostgreSQL 17+): a hard ceiling on the whole transaction — idle or busy.

A> RESET statement_timeout;
RESET

A> SET transaction_timeout = '500ms';
SET

A> BEGIN;
BEGIN

A> UPDATE accounts SET balance = 999 WHERE id = 1;
UPDATE 1

This timeout doesn't cancel a statement — it terminates the backend:

M> SELECT count(*)::int AS backends FROM pg_stat_activity WHERE application_name = 'A';
 backends 
----------
        0 
(1 row)

A> COMMIT; -- the server logged FATAL; Bun sees a dead socket
ERROR:  ERR_POSTGRES_CONNECTION_CLOSED: Connection closed

The killed transaction's work rolled back, as always:

M> SELECT balance FROM accounts WHERE id = 1;
 balance 
---------
     100 
(1 row)

Verified against PostgreSQL 18.4 · Run it yourself · Scenario source

• statement_timeout: "Abort any statement that takes more than the specified amount of time". Error 57014, session survives — the seatbelt for runaway queries.
• idle_in_transaction_session_timeout kills exactly the "went to lunch" pattern — chapter 5 proved it, server-side FATAL and all.
• transaction_timeout (PostgreSQL 17+): "Terminate any session that spans longer than the specified amount of time in a transaction" — the hard ceiling that catches both previous cases and the slow-but-busy transaction neither of them can. One caveat from the manual, pointing straight back at chapter 6: "Prepared transactions are not subject to this timeout".

Three columns of pg_stat_activity carry the whole story: xact_start for age, state for the idle-in-transaction pattern, and backend_xmin for the vacuum horizon — the scenario's three detectors are copy-paste ready. Remember that a read-only transaction pins VACUUM just as hard as a writer, so age is what you sort on, not write activity. Set statement_timeout and idle_in_transaction_session_timeout on every application role, sized to what the app actually needs, and add transaction_timeout on 17+ as the backstop, because a kill switch beats pager duty.

Further reading

• PostgreSQL docs: client connection defaults — all three timeouts
• PostgreSQL docs: pg_stat_activity
• The same lesson on MySQL