Idle in transaction in Postgres: what it means, how to end it

The API slows down, then a migration hangs, and pg_stat_activity shows a session in idle in transaction with a transaction that started forty minutes ago. It runs nothing and uses no CPU, and the rest of the table’s traffic waits on it.

What the state means

The state column in pg_stat_activity tells you what a backend is doing:

State	Meaning
active	running a statement
idle	waiting for a command, no open transaction
idle in transaction	waiting for a command, inside a transaction
idle in transaction (aborted)	the same, and a statement in it failed

The session did some work, the statement finished, and the client has not sent COMMIT or ROLLBACK. PostgreSQL waits for it.

Why it hurts

It holds its locks. A transaction keeps each lock until it ends. An UPDATE that finished twenty minutes ago still holds its row locks, and a plain SELECT still holds ACCESS SHARE on the table, which is enough to stop an ALTER TABLE and everything queued behind it: the lock queue, step by step.

It can hold back vacuum. If the transaction wrote something, it has a transaction ID (backend_xid). If it runs at REPEATABLE READ or keeps a cursor open, it holds a snapshot (backend_xmin). Either one stops vacuum from removing rows that died after that point, so dead tuples pile up across the database, and over a long enough time wraparound gets closer. A read-only transaction at the default READ COMMITTED holds no snapshot between statements, so check both columns before you blame it for bloat.

It takes a connection slot. With an application pool, a connection stuck in a transaction is also one the pool can’t hand out. Enough of them and you get too many clients already.

Find them

SELECT pid, usename, application_name, client_addr,
       now() - xact_start   AS xact_age,
       now() - state_change AS idle_for,
       backend_xid, backend_xmin,
       pg_blocking_pids(pid) AS blocked_by,
       left(query, 80)      AS last_statement
FROM pg_stat_activity
WHERE state LIKE 'idle in transaction%'
ORDER BY xact_start;

In this state, query shows the last statement the session ran, which tells you where in the code the transaction stopped. application_name and client_addr tell you who owns it. To see whether anyone waits on it, look for its pid in the blocked_by column of the sessions that wait: finding blocking queries.

Who left it open

• A slow call inside a transaction. The code opens a transaction, then calls a payment API or sends an email. You see many short ones until that API slows down.
• An error path that skips the rollback. An exception escapes, and the connection goes back to the pool or stays open with the transaction still running.
• An ORM session that lives too long, such as one held for a whole web request or background job.
• A person. Someone ran BEGIN in psql, or a GUI client opened a transaction for them, and went to lunch.

End it

pg_cancel_backend does nothing here, since there is no statement to cancel. Use terminate:

SELECT pg_terminate_backend(12345);

PostgreSQL rolls back the transaction, releases the locks and closes the session. Whatever it wrote is gone, and the client sees a dropped connection. You can do this for your own role’s sessions, or for other non-superuser roles’ sessions with membership in pg_signal_backend.

Before you do, check last_statement. If the session belongs to a person or a job you can reach, a message may cost less than a rollback.

Keep it from coming back

Set a limit per role. The default of 0 turns the limit off:

ALTER ROLE app SET idle_in_transaction_session_timeout = '2min';

The setting applies to new sessions, so existing pooled connections keep the old value until they reconnect. PostgreSQL 17 also has transaction_timeout, which caps the whole transaction.

The lasting fix sits in the code: finish calls to other services before you open the transaction, or after you commit it.

On the phone

HeapDeck’s Sessions tile on Health reads 12 active · 9 idle in tx and turns amber at 5 such sessions. Tap it to open Activity, then filter by Idle in tx. Each row counts its time from the start of the transaction, amber at 10 seconds and red at 4 minutes, and marks the session if it blocks others.

Session details show Transaction open for, the client address and the SQL under the label LAST STATEMENT. For these sessions HeapDeck offers Terminate session only and explains that the transaction rolls back. If the session sits at the root of a lock chain, Locks offers Terminate root (unblocks N) and asks you to type its pid.

Seeing all of this is free for one saved server. Terminating is part of HeapDeck Pro.

Questions

Is idle in transaction bad?

For a few milliseconds between statements it is normal. Minutes of it means a client opened a transaction and stopped talking, and those locks and that slot stay taken until it ends.

What is the difference between idle and idle in transaction?

An idle session has no open transaction and holds no locks, only a connection slot. An idle in transaction session has an open transaction and keeps each lock it took.

Does pg_cancel_backend work on idle in transaction?

No. pg_cancel_backend interrupts a running statement, and this session runs none. Use pg_terminate_backend, which closes the session and rolls the transaction back.

What is idle in transaction (aborted)?

A statement in the transaction failed and the client hasn't sent ROLLBACK yet. The session can't run anything else until it does, and it keeps its connection slot in the meantime.

What should I set idle_in_transaction_session_timeout to?

The default of 0 turns it off. Set it per role to a little more than your longest legitimate pause, such as a few minutes for an application role. Leave out roles that run long manual work.