Idle in transaction, and what it actually holds · PostgreSQL

idle in transaction is the state most likely to be named as a root cause and least likely to be understood precisely. The usual statement is that such a session blocks VACUUM from reclaiming dead rows. That is often true in effect and wrong in mechanism, and the difference decides whether an investigation succeeds.

The states, and what distinguishes them

`state`	The session is	Server is waiting on
`active`	Executing a statement	Whatever `wait_event` says
`idle`	Connected, no transaction open	The client, between transactions
`idle in transaction`	Inside `BEGIN`, no statement running	The client, mid-transaction
`idle in transaction (aborted)`	Same, after an error, awaiting `ROLLBACK`	The client

The wait event for the two idle-in-transaction states is the giveaway:

Read-only / Safea genuine idle-in-transaction session— wait_event Client:ClientRead means the server is waiting for the application to send something. Captured live.

$ psql -U postgres -c 'SELECT pid, state, wait_event_type, wait_event, now()-xact_start AS xact_age, left(query,40) AS last_query FROM pg_stat_activity WHERE backend_type = ...'

 pid |        state        | wait_event_type | wait_event |    xact_age     |         last_query
-----+---------------------+-----------------+------------+-----------------+-----------------------------
924 | idle in transaction | Client          | ClientRead | 00:00:04.032709 | CREATE TEMP TABLE t(i int);
(1 row)

Client:ClientRead says the database has done its work and is waiting for the application. The query column shows the last statement executed, not a running one — a distinction worth internalising, because it makes the column look like an active query when it is history.

What three identical-looking sessions actually hold

Three sessions were opened on a live PostgreSQL 18.6 cluster. All three reach idle in transaction. They differ only in isolation level and in whether they wrote.

Read-only / Safefour idle-in-transaction sessions, and what each holds— Captured 2026-08-27. Same state, three different answers.

$ psql -U postgres -d bank -c 'SELECT pid, state, backend_xid AS holds_own_xid, backend_xmin AS holds_snapshot, left(query,42) AS last_query FROM pg_stat_activity ...'

 pid |        state        | holds_own_xid | holds_snapshot |              last_query
-----+---------------------+---------------+----------------+---------------------------------------
924 | idle in transaction |           773 |                | CREATE TEMP TABLE t(i int);
970 | idle in transaction |               |                | SELECT count(*) FROM acct;
980 | idle in transaction |               |            772 | SELECT count(*) FROM acct;
990 | idle in transaction |           776 |                | UPDATE acct SET bal=bal+1 WHERE id=1;
(4 rows)

pid	Isolation	Wrote?	`backend_xid`	`backend_xmin`	Bounds the horizon?
970	READ COMMITTED	no	—	—	No
980	REPEATABLE READ	no	—	772	Yes, via its snapshot
990	READ COMMITTED	yes	776	—	Yes, via its own xid
924	READ COMMITTED	yes	773	—	Yes, via its own xid

And the horizon itself confirms it:

Read-only / Safethe oldest running transaction ID— 773 is session 924's xid. The horizon is pinned to the oldest running transaction.

$ psql -U postgres -d bank -c 'SELECT pg_snapshot_xmin(pg_current_snapshot()) AS oldest_running_xid, txid_current() AS current'

 oldest_running_xid | current
--------------------+---------
              773 |     777
(1 row)

The rule that falls out of this is short:

A session holds back cleanup when it holds a transaction ID or a snapshot. idle in transaction is neither of those things; it is a state that frequently coincides with them.

READ COMMITTED takes a new snapshot per statement and releases it when the statement ends, so a read-only session sitting between statements holds nothing. REPEATABLE READ and SERIALIZABLE take one snapshot for the whole transaction and hold it until commit. Any session that has written has been assigned a transaction ID, and that ID is running until the transaction ends.

Why the folklore is usually right anyway, and why the mechanism still matters

Most application transactions write. An ORM that opens a transaction, issues an INSERT, and then waits on an external API call before committing has written, holds an xid, and does bound the horizon. So the common advice — hunt down idle-in-transaction sessions when vacuum is not keeping up — usually lands on the right session.

The mechanism matters in the cases where it does not. A long idle in transaction session that holds neither an xid nor a snapshot is a connection-slot and locking problem, and pursuing it as a vacuum problem produces an investigation that finds nothing and a remediation that changes nothing.

The query that tells you which case you are in is the one above. Sort by what is actually held rather than by how long the session has been idle:

SELECT pid, state, backend_xid, backend_xmin,
       now() - xact_start AS xact_age,
       application_name, client_addr, left(query, 60) AS last_query
  FROM pg_stat_activity
 WHERE backend_xid IS NOT NULL OR backend_xmin IS NOT NULL
 ORDER BY coalesce(age(backend_xmin), age(backend_xid)) DESC;

What every idle-in-transaction session holds regardless

Three costs apply whether or not the session bounds the horizon.

Locks. Every lock the transaction acquired is held until it ends. A transaction that ran ALTER TABLE and then went idle holds an exclusive lock, and every subsequent query on that table queues behind it. This is the mechanism in Part IX, and it does not care about isolation level or whether the session wrote afterwards.

A connection slot, counted against max_connections.

Its private memory, including whatever it allocated.

So an idle-in-transaction session is always worth investigating. The refinement is about which symptom to attribute to it.

idle_in_transaction_session_timeout

PostgreSQL will end these sessions for you.

Read-only / Safethe parameter, and its context— user context, so it can be attached to a role. Default 0 means disabled.

$ psql -U postgres -c "SELECT name, setting, unit, context, short_desc FROM pg_settings WHERE name = 'idle_in_transaction_session_timeout'"

                name                 | setting | unit | context |                          short_desc
-------------------------------------+---------+------+---------+-------------------------------------------------------------
idle_in_transaction_session_timeout | 0       | ms   | user    | Sets the maximum allowed idle time between queries, when in a transaction.
(1 row)

Because it is user context, the right place for it is usually a role rather than the server:

-- Bound the workloads whose behaviour you know, individually
ALTER ROLE app_web       SET idle_in_transaction_session_timeout = '30s';
ALTER ROLE reporting_ro  SET idle_in_transaction_session_timeout = '5min';
ALTER ROLE migrator      SET idle_in_transaction_session_timeout = '15min';

The related idle_session_timeout ends sessions idle outside a transaction. It is a much blunter instrument and is usually wrong against an application connection pool, because ending a pooled connection that is idle by design forces the pool to reconnect for no benefit. It is appropriate for interactive and ad-hoc roles.

Setting a server-wide idle_in_transaction_session_timeout without knowing what uses long transactions

A blanket value looks like a clean policy and can break a job that had a legitimate reason to hold a transaction open.

A schema migration that takes a lock, applies a change, verifies it, and commits may spend minutes between statements while a verification query runs elsewhere. A batch job that streams a large result set and processes each row against an external system may be idle between fetches by design. A backup or export tool may hold a transaction to get a consistent view. Terminating those mid-flight produces a partial migration or a failed export, and the application discovers it only at its next statement.

Set it per role, with a value chosen from what that workload actually does, and measure first:

SELECT usename, application_name,
       max(now() - state_change) AS longest_idle_in_txn
  FROM pg_stat_activity
 WHERE state = 'idle in transaction'
 GROUP BY 1, 2 ORDER BY 3 DESC;

Sampling that for a week tells you which roles genuinely need a long value and which have never exceeded a few seconds.

Why a read-only READ COMMITTED transaction holds nothing

The asymmetry in the table above comes from two independent optimisations, both of which exist to keep the cleanup horizon moving.

Transaction IDs are assigned lazily. BEGIN does not allocate one. A transaction that only reads never needs one, because a transaction ID exists to stamp rows the transaction writes so other transactions can judge their visibility. Assigning one to every transaction would burn through the ID space — the subject of wraparound in Part VIII — for no benefit. So backend_xid is null until the first write, and the session is invisible to the horizon calculation.

Snapshots are per statement under READ COMMITTED. The isolation level’s definition is that each statement sees rows committed before that statement began, which means a fresh snapshot per statement and no reason to retain one between them. Under REPEATABLE READ the definition is that the whole transaction sees a single consistent view, so the snapshot is taken once and held, and backend_xmin reports it for as long as the transaction lives.

The consequence for an operator is that isolation level is a durability-of-horizon decision as much as a correctness one. A reporting workload switched to REPEATABLE READ for consistent multi-query reports has, as a side effect, made every one of its long-running transactions a vacuum obstruction — including the ones that only read.

That trade is worth making deliberately. It is not worth discovering during a bloat investigation three months later.

Production discipline

Sort by what is held, not by how long the session has been idle. backend_xid and backend_xmin are the columns that decide whether a session is a vacuum problem.
Read Client:ClientRead as “the database is waiting for the application”. The query column shows the last statement, not a running one.
Treat every idle-in-transaction session as a locking and slot problem regardless, because those costs apply whatever the isolation level.
Set idle_in_transaction_session_timeout per role, from measurement. A blanket value terminates migrations and exports that were behaving correctly.
Record application_name and client_addr before terminating anything. Terminating resolves tonight and guarantees a repeat.
Treat a move to REPEATABLE READ as a vacuum decision too. It makes read-only long transactions bound the horizon, which READ COMMITTED does not.

Cross-course references

Linux for Production Sysadmins — Part XXII (Network troubleshooting) covers diagnosing a client that has stopped reading, which is what ClientRead on a long-idle session can indicate.
Observability for Production Sysadmins — Part XX (Alert quality) covers alerting on the oldest transaction age rather than on the count of idle sessions, which is the actionable signal.
Kubernetes for Production Sysadmins — Part X (Termination) covers pods being killed mid-transaction, a common source of sessions that go idle and never return.