Idle-in-Transaction Sessions

Overview

A session in state 'idle in transaction' has an open transaction but is not executing any query. The application opened a BEGIN, ran some work, and then paused—perhaps waiting for user input, a network call, or a bug that leaked the connection without a COMMIT or ROLLBACK. The transaction holds all its acquired locks and pins the VACUUM xmin horizon for the entire duration.

Idle-in-transaction sessions are especially dangerous in connection-pooled environments because the pool believes the connection is free while Postgres has it locked up. The recommended fix is to set idle_in_transaction_session_timeout at the database or role level so Postgres automatically terminates these sessions.

Detection SQL

Run this query with a read-only role. No writes, no locks.

-- Sessions stuck in 'idle in transaction' for more than 2 minutes
SELECT
  pid,
  usename,
  application_name,
  client_addr,
  state,
  now() - state_change         AS idle_duration,
  now() - xact_start           AS xact_open_duration,
  left(query, 120)             AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - state_change > interval '2 minutes'
ORDER BY xact_start;

Fix SQL

Replace placeholders in <angle brackets> with values from the detection output before running.

-- Terminate the idle-in-transaction session
SELECT pg_terminate_backend(<pid>);

-- Prevent future occurrences at the role or database level
ALTER ROLE <app_role> SET idle_in_transaction_session_timeout = '5min';
-- OR
ALTER DATABASE <dbname> SET idle_in_transaction_session_timeout = '5min';

Worked example

Common false positives

• Two-phase commit (PREPARE TRANSACTION) workflows intentionally leave transactions open — these appear as idle in transaction but are managed by the application's XA coordinator.
• Interactive psql sessions where a developer typed BEGIN and walked away — expected in development environments.