PostgreSQL "idle in transaction": Find, Kill and Prevent Stuck Sessions

PostgreSQL idle in transaction is the state of a session that started a transaction, ran something, and then stopped sending queries without committing or rolling back. It uses no CPU and looks harmless in a process list. In reality it can hold locks for hours, make a two-second migration hang the whole application, and stop VACUUM from removing dead rows. This guide shows how to find these sessions, end them without making things worse, and set the limits that keep them from coming back.

What "idle in transaction" actually means

The pg_stat_activity documentation defines it as a backend that "is in a transaction, but is not currently executing a query". There is a close relative, idle in transaction (aborted): the same situation, except one of the statements in the transaction failed. Until the client sends ROLLBACK, every further command in that transaction fails with current transaction is aborted, and the transaction stays open.

Two kinds of damage follow from an open transaction that nobody is using:

• Locks stay held. Row locks from an UPDATE or SELECT ... FOR UPDATE, and table-level locks from any statement, are kept until the transaction ends. Other sessions that need a conflicting lock simply wait.
• VACUUM cannot clean up. The configuration docs say it directly: even when no significant locks are held, "an open transaction prevents vacuuming away recently-dead tuples that may be visible only to this transaction", so a session idling for a long time contributes to table bloat. If you have read our autovacuum and bloat guide, this is the "xmin horizon" problem.

Where these sessions come from

Almost always it is the application, not PostgreSQL. Typical patterns:

• Drivers that open transactions implicitly. The psycopg 3 documentation notes that by default "even a simple SELECT will start a transaction", and if the program never commits, the session "will remain idle in transaction". Long-running scripts are the usual victims.
• Work done in the middle of a transaction. The code opens a transaction, reads a row, then calls a slow HTTP API, sends an email or waits for a message queue before writing the result.
• Error paths that skip the rollback. An exception is caught and logged, but the connection goes back to the pool with the transaction still open. That is the typical source of idle in transaction (aborted).
• Humans. Someone types BEGIN; in psql on production, runs an update to check something, and goes to lunch.

Step 1: find them

Start with the list of sessions in either idle state, oldest transaction first:

SELECT pid, usename, application_name, client_addr, state,
       now() - xact_start   AS xact_age,
       now() - state_change AS idle_for,
       backend_xmin,
       left(query, 120)     AS last_query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
ORDER BY xact_start;

How to read the columns:

• xact_age is how long the transaction has been open; idle_for is how long since the session last changed state. A transaction open for three hours that has been idle for the last two is not a slow query; it is a forgotten one.
• query is the last statement the session ran, not something currently running. It is still your best clue to which part of the code left the transaction open.
• application_name, usename and client_addr tell you which service to talk to. Setting application_name in every connection string pays off exactly at this moment.
• backend_xmin being set means the session is holding back the cleanup horizon.

Step 2: check whether it is blocking anything

An idle transaction is a bug; an idle transaction that other sessions are queued behind is an incident. pg_blocking_pids() returns the sessions that block a given process from acquiring a lock:

SELECT w.pid               AS waiting_pid,
       left(w.query, 80)   AS waiting_query,
       b.pid               AS blocker_pid,
       b.state             AS blocker_state,
       now() - b.xact_start AS blocker_xact_age
FROM pg_stat_activity w
CROSS JOIN LATERAL unnest(pg_blocking_pids(w.pid)) AS bp(pid)
JOIN pg_stat_activity b ON b.pid = bp.pid
WHERE w.wait_event_type = 'Lock';

The documentation makes an important point about this function: a process counts as blocking not only when it holds a conflicting lock, but also when it is waiting for one and is ahead in the queue. That is how an idle transaction turns into an outage. A migration runs ALTER TABLE, which needs an exclusive lock and waits behind the idle session; then ordinary queries on that table queue up behind the waiting ALTER TABLE. The fix is the same session at the head of the chain. The docs also warn that calling the function very frequently has some performance cost, so use it for diagnosis rather than every-second polling.

For a broader view of how lock chains form, see our article on PostgreSQL "deadlock detected".

Step 3: end it safely

PostgreSQL has two signalling functions, and the difference matters here:

• pg_cancel_backend(pid) cancels the session's current query. An idle-in-transaction session is not running one, so this is not the tool for ending its transaction.
• pg_terminate_backend(pid, timeout) terminates the whole session. Its open transaction is rolled back and its locks are released. With a positive timeout in milliseconds, the function waits until the process has actually gone and returns false with a warning if it has not.

Before terminating, decide whether the work in that transaction matters. If it belongs to a batch job that has been writing for an hour, killing it throws that hour away, and the job may simply start again. Talk to the owner if you can. If production is blocked, terminate the head of the chain first and re-check; you rarely need to kill everything.

A cleanup query for clearly forgotten sessions looks like this:

SELECT pid, pg_terminate_backend(pid, 5000)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND backend_type = 'client backend'
  AND now() - state_change > interval '30 minutes';

By default only superusers can call these functions; members of the session's role and roles with pg_signal_backend can signal non-superuser sessions.

Step 4: prevent it with timeouts

idle_in_transaction_session_timeout terminates any session that has been idle inside an open transaction for longer than the given time. It has existed since PostgreSQL 9.6 and is disabled by default (0). Values without units are milliseconds. The terminated session ends with SQLSTATE 25P03; an idle client typically notices only when it next tries to use the connection, so make sure the application treats that as "reconnect and retry" rather than crashing.

PostgreSQL 17 added transaction_timeout, which limits the total duration of a transaction, active or idle, and terminates the session when it is exceeded (SQLSTATE 25P04). If it is shorter than or equal to idle_in_transaction_session_timeout or statement_timeout, the longer timeout is ignored. The documentation advises against setting transaction_timeout and statement_timeout globally in postgresql.conf, because they would affect every session, including maintenance. Prepared transactions are not covered by transaction_timeout.

Setting the limits per role or per database keeps them targeted:

-- per application role (applies to new sessions)
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '5min';

-- or per database
ALTER DATABASE appdb SET idle_in_transaction_session_timeout = '10min';

-- PostgreSQL 17+: cap the whole transaction, busy or idle
ALTER ROLE report_user SET transaction_timeout = '2h';

There is also idle_session_timeout (PostgreSQL 14+), but it is for sessions idle outside a transaction. The docs say such sessions impose no large costs and warn against using it on connections that go through a pooler, which may not handle unexpected disconnects well.

If you use PgBouncer

With transaction pooling, a client that is idle in a transaction keeps its server connection pinned, so a handful of them can drain a small pool. PgBouncer has its own idle_transaction_timeout: a client that stays "idle in transaction" longer than this many seconds is disconnected. It is disabled by default. Setting it on the pooler protects the pool itself, while the PostgreSQL setting protects locks and VACUUM; it is reasonable to use both. Our PgBouncer guide covers pool sizing in more detail.

What to monitor

• The age of the oldest transaction in idle in transaction or idle in transaction (aborted) state, from pg_stat_activity. Alert at a threshold that fits your workload, for example minutes for an OLTP application.
• The number of sessions waiting on locks (wait_event_type = 'Lock'). A sudden jump usually has a single blocker behind it.
• Errors with SQLSTATE 25P03 and 25P04 in application logs after you enable the timeouts. They show you which code paths still leave transactions open.