PostgreSQL

PostgreSQL sessions stuck in “idle in transaction”

Written and reviewed by Sahil Srivastav

PostgreSQLTransactionsSilent damage
SELECT pid, state, now() - xact_start AS age FROM pg_stat_activity WHERE state = 'idle in transaction';

  pid  |        state        |       age
-------+---------------------+-----------------
 21884 | idle in transaction | 00:41:12.882134
 21903 | idle in transaction | 00:39:58.114201

What this error actually means

A session in `idle in transaction` has issued `BEGIN`, run at least one statement, and is now waiting for your application to send something else. The transaction is open. The snapshot is held. Any locks taken are still held. The database is doing nothing and cannot forget anything.

This state is not an error, which is precisely why it is dangerous — nothing logs, nothing fails, and the damage accumulates in three directions at once. It pins the oldest transaction id, so autovacuum cannot reclaim dead tuples newer than the snapshot, and tables bloat. It holds whatever locks the transaction acquired, so DDL and conflicting writes queue behind it. And it occupies a connection slot both in your pool and against `max_connections`.

The cause is always the same shape: something non-database happens inside the transaction boundary. An HTTP call, a message publish, a file write, a `Thread.sleep`, waiting on user input, or an exception path that returns without committing or rolling back. The transaction stays open for as long as that takes — or forever.

Causes, most common first

  1. 1A network call inside the transaction. The archetype. The handler opens a transaction, writes a row, calls a payment provider or a downstream service, then commits. The transaction stays open for the whole round trip — and if the downstream hangs with no timeout, it stays open indefinitely.
  2. 2An exception path that neither commits nor rolls back. Manual transaction management where the failure path returns early. The connection may even be handed back to the pool while the transaction is open, so a later request inherits a session with a stale snapshot.
  3. 3Autocommit disabled with no explicit commit. A driver or framework with autocommit off starts a transaction implicitly on the first statement. Read-only code that never commits therefore leaves a transaction open after a simple `SELECT` — this is the version people find most surprising.
  4. 4Batch or interactive sessions holding a transaction open. A migration script, a data-fix `psql` session, or a notebook left mid-transaction while somebody goes to lunch. Harmless-looking and capable of bloating a hot table for hours.
  5. 5Transaction scope wrapped around too much work. A framework transaction spanning an entire request, including template rendering, serialisation, or a loop over thousands of items with per-item processing. Correct on paper, but the open window is orders of magnitude longer than the write it protects.

When you see it

  • Table and index bloat grows while autovacuum appears to run normally and reclaim nothing
  • `ALTER TABLE` and index creation hang indefinitely with no obvious blocker
  • Connection pool exhaustion with the database showing near-zero CPU
  • Replication lag on standbys, or `max_standby_streaming_delay` conflicts
  • `age(datfrozenxid)` climbing steadily toward wraparound warnings

How to diagnose it

Step 1

Rank the offenders by transaction age

Age is the metric that matters, not count. `query` shows the last statement executed, which is usually enough to identify the code path even though the session is idle now.

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

Step 2

Measure what the oldest transaction is costing you

This is the number to show people: the oldest transaction bounds what vacuum can reclaim across the whole database. Minutes here means bloat; hours means a growing incident.

SELECT max(now() - xact_start) AS oldest_transaction
FROM pg_stat_activity WHERE xact_start IS NOT NULL;

Step 3

Check what those sessions are blocking

If DDL is hanging, this query names the blocker. An idle-in-transaction pid appearing as a blocking pid is conclusive.

SELECT blocked.pid AS blocked_pid, blocking.pid AS blocking_pid,
       blocking.state, left(blocking.query, 80)
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));

Step 4

Find the code path from the last query

Search the codebase for the statement shape shown in `query`, then read outward to the transaction boundary. You are looking for anything between the first write and the commit that is not a database operation.

The fix

Move non-database work out of the transaction. Fetch what you need, close the transaction, make the HTTP call, then open a second transaction to record the result. If the two must be atomic, use the transactional outbox pattern: write the intent to a table in the same transaction and let a separate worker perform the external call. That is the correct answer to "but I need the remote call and the write to be consistent".

Make commit and rollback unconditional. Use the framework’s declarative transaction management rather than manual `begin`/`commit`, so every exit path — including the exceptional ones — is covered. If you must manage it manually, roll back in `finally` and treat an open transaction at connection release as a bug.

Shrink transaction scope to the writes that genuinely need atomicity. Rendering, serialisation, validation of external data, and per-item processing loops belong outside. A transaction should be measured in milliseconds.

Set the database-side guards so application bugs cannot become database incidents: `idle_in_transaction_session_timeout` terminates the session, and `statement_timeout` bounds individual statements. These are containment, not fixes, and every production database should have them.

For interactive sessions, keep autocommit on by default in `psql` and admin tools, and never leave a `BEGIN` open while you think.

-- Contain the damage at the database level
ALTER SYSTEM SET idle_in_transaction_session_timeout = '30s';
ALTER SYSTEM SET statement_timeout = '30s';
SELECT pg_reload_conf();

-- Terminate a specific offender during an incident
SELECT pg_terminate_backend(21884);

How to stop it coming back

  • Alarm on oldest transaction age above a few seconds — it is the single best early-warning metric for this class of bug
  • Ban network calls inside transaction boundaries in review; use an outbox table when atomicity is genuinely required
  • Keep `idle_in_transaction_session_timeout` set in every environment including staging, so the bug fails loudly before production
  • Monitor dead-tuple counts and table bloat; unexplained bloat is nearly always a long transaction
  • Assert in integration tests that no connection is returned to the pool with an open transaction

Practise this failure in a real repository

The correct fix for "I need a remote call and a database write to be consistent" is an outbox. Gronex ships it as a repository challenge with tests for atomic enqueue, at-least-once delivery, idempotent consumption, and crash recovery mid-publish.

FAQ

Is idle in transaction the same as idle?

No, and the difference is the whole problem. `idle` means no transaction is open and the session is harmless. `idle in transaction` means a transaction is open with a snapshot and locks held. One is a resting connection; the other is a slow leak of vacuum headroom.

Why does autovacuum stop reclaiming space?

Vacuum can only remove tuples invisible to every open snapshot. An old transaction pins a snapshot, so every dead tuple newer than it must be retained across the whole database — not just the tables that transaction touched. That is why one idle session bloats unrelated hot tables.

Is idle_in_transaction_session_timeout safe to enable?

Yes, and it is standard practice. It terminates the session, which rolls the transaction back — the same outcome as the client disconnecting. Any application that cannot tolerate that already has a correctness bug. Size it above your slowest legitimate transaction.

Why does a read-only query leave a transaction open?

With autocommit disabled, the first statement implicitly begins a transaction, and a read-only path that never commits therefore leaves it open. This catches people out constantly: they look only at write paths and the offender turns out to be a `SELECT`.

Related

FATAL: sorry, too many clients alreadyHikariCP: connection is not availableLock timeout on ALTER TABLE

Other errors engineers hit next to this one

Full error and symptom index →