Idle in transaction, and what it holds

Idle in transaction is the state of a session that opened a transaction, ran at least one statement, and then went quiet without committing or rolling back. The backend is doing nothing and can do nothing else. The transaction is still open, so its locks are still held, its transaction id is still assigned if it wrote, and its snapshot still counts if the isolation level keeps one. pg_stat_activity reports it in state; the (aborted) variant means the same after an error.

What an idle transaction costs, and what it does not

The costs are usually listed in the wrong order. Locks come first and are the immediate danger: a session that ran a single UPDATE and then went to lunch blocks every writer for that row and blocks anything that needs a conflicting table lock, including some forms of schema change and, importantly, the lock an anti-wraparound vacuum eventually wants. A pool sitting on a dozen of these looks idle in every process listing and is holding the database still.

The cost people quote first, holding back the cleanup horizon, is real but conditional. Under the default isolation level a read-only transaction releases its snapshot at the end of each statement, so a session idling after a SELECT is not necessarily pinning anything. A session that wrote, or one using repeatable read or serializable, is. Treating every idle transaction as a vacuum problem produces a lot of noise and hides the ones that matter.

The part that is unconditional is that nothing stops it. Three timeouts exist and all three are off in a default configuration, so an idle transaction lasts until the application, the pooler or the network ends it, and a client that has crashed without closing its socket ends it much later than anyone expects.

Checking what would actually intervene

The settings are more informative than the state count on a quiet server.

SELECT current_setting('idle_in_transaction_session_timeout') AS idle_in_transaction_timeout,
       current_setting('idle_session_timeout') AS idle_session_timeout,
       current_setting('statement_timeout') AS statement_timeout,
       count(*) FILTER (WHERE state = 'idle in transaction') AS in_that_state_now
FROM pg_stat_activity;

 idle_in_transaction_timeout | idle_session_timeout | statement_timeout | in_that_state_now 
-----------------------------+----------------------+-------------------+-------------------
 0                           | 0                    | 0                 |                 0
(1 row)

Three zeros is three timeouts disabled, which is the shipped default on every supported major and the single most useful thing this query tells you. Set the first to something a human transaction would never need, and set it per role or per database rather than globally if a migration tool legitimately holds transactions open.

The count is zero here for a reason worth naming: a session can never catch itself in this state, because looking requires running a statement, and a session running a statement is active. Whatever you see in that column, your own connection is not in it.

Where the treatment lives

If these sessions arrive from an application pool that opens a transaction before it knows whether it needs one, the fix is in the pool rather than the server, and connection pooling and PgBouncer covers which pooling mode makes this impossible and what that mode costs you. When the symptom people report is queries hanging rather than sessions idling, work from the other end with lock contention and blocking trees.