idle in transaction in PostgreSQL: what it holds

idle in transaction in pg_stat_activity means a session has started a transaction, finished its last statement and is now waiting for the client to send the next one — without a COMMIT or ROLLBACK. Nothing runs, but the transaction is still open, and what it took stays taken: its locks, its connection slot and, depending on what it did, the rows vacuum would otherwise clean up. The default is to wait forever: idle_in_transaction_session_timeout is 0 on PostgreSQL 16 and 17.

We left one session idle in a transaction on PostgreSQL 17.11, next to a table of 200,000 jobs, and measured what the other sessions could still do — and which timeout ended it, with which message.

What does “idle in transaction” mean in PostgreSQL?

The documentation defines the state as “The backend is in a transaction, but is not currently executing a query.” In our session, after BEGIN and one SELECT, pg_stat_activity showed state = idle in transaction and wait_event = ClientRead: the server was waiting for the client. The usual causes are application code that opens a transaction and then does something else — calls an API, renders a page, waits for a user — or code that forgets to commit on one path.

idle in transaction (aborted) is the same, after a statement failed. Our session ran select 1/0, the next statement got ERROR: current transaction is aborted, commands ignored until end of transaction block (SQLSTATE 25P02), and only ROLLBACK ended it.

What does an idle-in-transaction session block?

Writes to the rows it changed. Our session updated job 5 and went idle. A second session’s update jobs set status = 'y' where id = 5 waited for it and, with lock_timeout = '2s', gave up after 2.0 seconds with canceling statement due to lock timeout. Without lock_timeout it kept waiting until we stopped it with a 4-second statement_timeout; in pg_stat_activity it showed wait_event = transactionid, and pg_blocking_pids() named the idle session.

Schema changes, even after a read. A transaction that only ran a SELECT keeps an ACCESS SHARE lock on the table until it ends. That lock is enough to stop ALTER TABLE jobs ADD COLUMN note text, which waited 2.0 seconds and gave up. Worse, while that ALTER TABLE waited (we gave it 5 seconds), a plain SELECT on the same table from another session waited behind it and timed out after 2 seconds: one forgotten transaction plus one migration is enough to stop reads on the table. That queue is measured in more detail in schema migrations on a shared multi-tenant table.

Vacuum — when it holds a snapshot or has written. This is where the usual advice needs a qualification. The documentation says “an open transaction prevents vacuuming away recently-dead tuples that may be visible only to this transaction”. We measured when that happens:

In READ COMMITTED each statement gets its own snapshot; between statements our read-only session had no backend_xmin and held nothing back. In REPEATABLE READ the snapshot lives as long as the transaction, and so do the dead rows. A session that has written is different: it has a transaction ID (backend_xid), and with an empty backend_xmin it still kept 100,000 dead rows from being removed until it rolled back. What that does to a job queue over 100,000 dequeues is measured in PostgreSQL job workers: retries, crashes, tenant context.

A connection slot. Every idle-in-transaction session is a connection nobody else can use. When enough of them pile up, new connections get remaining connection slots are reserved — measured in remaining connection slots are reserved in PostgreSQL.

How do you find sessions that are idle in transaction?

select pid, usename, application_name, state,
       now() - xact_start   as transaction_age,
       now() - state_change as idle_for,
       backend_xid, backend_xmin, left(query, 60) as last_query
from pg_stat_activity
where state in ('idle in transaction', 'idle in transaction (aborted)')
order by xact_start;

query is the last statement the session ran, not one it is running: that is the clue to where in the code the transaction was left open. backend_xmin or backend_xid set means it is holding back vacuum. pg_terminate_backend(pid) ended our idle session; its next statement got FATAL: terminating connection due to administrator command (SQLSTATE 57P01) and its update of job 7 was rolled back. An ordinary role could terminate another session of its own role, but not a superuser’s: permission denied to terminate process.

Which timeout ends an idle-in-transaction session?

Two things to know before you switch them on. All are 0, off, by default on 17; on 16 statement_timeout and idle_in_transaction_session_timeout are 0 too, and transaction_timeout does not exist. And the first two end the connection, not just the transaction: psql reported server closed the connection unexpectedly, so the application has to reconnect, and a pool has to throw that connection away. transaction_timeout is the strictest — it also killed a single long statement in autocommit — and the documentation advises against setting it in postgresql.conf “because it would affect all sessions”. idle_in_transaction_session_timeout only fires when the session is waiting for its client, which is exactly the case this article is about. Set it per role, for the application: ALTER ROLE app SET idle_in_transaction_session_timeout = '30s'; applies to new sessions of that role. In our test with 2s, a session that was already connected still showed 0 and was not ended; a new one showed 2s and was.

In a multi-tenant application this matters twice, because the safe way to set the tenant for row-level security is inside a transaction (SET LOCAL or set_config(…, true)). A request that opens that transaction early and then waits on something slow holds all of the above for every tenant. Why the tenant belongs in a transaction, and what happens behind a pooler when it is not, is in PgBouncer and row-level security.

What we did not measure

Vacuum next to an idle transaction that holds an open cursor. Prepared transactions (PREPARE TRANSACTION), which the documentation says are not subject to transaction_timeout. How specific drivers and pools react to a connection the server closed.

Sources: PostgreSQL 17 documentation on client connection defaults (statement_timeout, transaction_timeout, idle_in_transaction_session_timeout) and the cumulative statistics system (pg_stat_activity). Read on 16 October 2026. All measurements are our own, on PostgreSQL 17.11 (defaults also on 16.15) in containers, on 16 October 2026.