What "idle in transaction" sessions mean and how to fix them

What "idle in transaction" means

In pg_stat_activity, the state column shows what a backend is currently doing. The value "idle in transaction" means the client opened a transaction with BEGIN (or the application's connection library opened one implicitly) and then stopped sending commands — while the transaction remained open.

The connection is not executing any query. But from Postgres's perspective, the transaction is still active. Postgres cannot reclaim any resources tied to that transaction until it commits or rolls back.

Why it causes problems

Idle in transaction sessions hold three kinds of resources that affect the rest of your database:

Row-level locks. Any rows the transaction has modified (via UPDATE or DELETE) are locked until the transaction ends. Other transactions that need those rows must wait. Depending on your workload, this can cascade into a lock pile-up where a single idle session blocks dozens of other queries.

Transaction ID (XID) horizon. Postgres assigns a transaction ID to every transaction that modifies data. The oldest active XID is the "horizon". Autovacuum cannot reclaim dead tuples created after the horizon. An idle in transaction session from 3 hours ago means autovacuum has been unable to reclaim dead tuples on any table for 3 hours. On a high-write database, that means table bloat accumulating unchecked.

Shared memory. Each backend consumes roughly 5 MB of shared memory. At 100 idle in transaction sessions, that is 500 MB of RAM doing nothing useful.

How to detect idle in transaction sessions

SELECT
  pid,
  usename,
  application_name,
  state,
  wait_event_type,
  wait_event,
  EXTRACT(EPOCH FROM (NOW() - state_change)) AS seconds_in_state,
  LEFT(query, 100) AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY seconds_in_state DESC;

The seconds_in_state column is the most important. A session that has been idle in transaction for more than a few seconds is a bug. One that has been idle for hours is a crisis.

The last_query column shows the last statement that ran inside the transaction. This is your primary clue about which application or service opened the transaction and forgot to close it.

Setting idle_in_transaction_session_timeout

The fastest fix is to configure Postgres to kill idle in transaction sessions after a timeout:

-- At the database level (applies to all new connections)
ALTER DATABASE mydb SET idle_in_transaction_session_timeout = '30s';

-- Or at the session level (for testing)
SET idle_in_transaction_session_timeout = '30s';

When the timeout fires, Postgres sends an error to the client. The connection closes and the transaction rolls back automatically. Your application receives an exception. If the application handles it with a retry, the user sees nothing. If it does not handle it, you have discovered a bug in your error handling — better discovered this way than during a lock pile-up incident.

The right value depends on your workload. For a web API, 30 seconds is generous. For a batch import job, 5 minutes may be appropriate. The value should be less than the time it takes for lock contention to become visible to users.

Finding the root cause in application code

Setting the timeout stops the bleeding. Fixing the root cause requires examining the application.

1. Uncaught exception inside a transaction block. In Python with psycopg2 or asyncpg:

# Bug: exception leaves the transaction open until GC
with conn.transaction():
    do_thing()  # raises — transaction stays open

# Fix: use the context manager correctly; it rolls back on exception
async with conn.transaction():
    await do_thing()

2. Missing error handling in Node.js.

// Bug: if doSomething() throws, no ROLLBACK runs
const client = await pool.connect();
await client.query('BEGIN');
const result = await doSomething();
await client.query('COMMIT');
client.release();

// Fix: guarantee ROLLBACK in finally
const client = await pool.connect();
try {
  await client.query('BEGIN');
  await doSomething();
  await client.query('COMMIT');
} catch (e) {
  await client.query('ROLLBACK');
  throw e;
} finally {
  client.release();
}

3. Admin tool left with an open transaction. A developer opened psql, ran BEGIN, and closed the terminal. The TCP session was not cleanly closed. idle_in_transaction_session_timeout catches this automatically.

Terminating existing stuck sessions

For immediate relief without changing any settings:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND EXTRACT(EPOCH FROM (NOW() - state_change)) > 60;

This terminates all sessions idle in transaction for more than 60 seconds. The transactions roll back automatically. Connection pools reconnect on the next request.

insightral PG-R05: idle in transaction detection

insightral's rule PG-R05 fires when any session has been in idle in transaction state for more than 30 seconds. The finding includes the session count, the longest duration, the application name, and the last query that ran inside the transaction.

The rule also catches idle in transaction (aborted) — a variant where the transaction encountered an error but the client never issued a ROLLBACK. This is equally dangerous since it still holds the XID horizon.

Summary

1. Query pg_stat_activity WHERE state = 'idle in transaction' and check seconds_in_state. Values above 30 seconds warrant investigation. 2. Set idle_in_transaction_session_timeout at the database level. 30 seconds is a safe starting point for web applications. 3. Identify the offending application via application_name and last_query, then fix the exception-handling path leaving transactions open. 4. Use try/finally blocks to guarantee ROLLBACK on exceptions in all transaction-handling code. 5. Terminate existing stuck sessions with pg_terminate_backend() — transactions roll back automatically and connection pools reconnect.