PostgreSQL 'idle in transaction' sessions: how they block DDL, stop vacuum and bloat tables, and how to stop them for good

#Symptoms

• An ALTER TABLE or CREATE INDEX in a deploy hangs, and then every query on that table queues behind it.
• Autovacuum runs but dead tuples keep growing; tables and indexes bloat.
• pg_stat_activity shows sessions with state = 'idle in transaction' for minutes or hours.
• Transaction ID age climbs and wraparound warnings appear in the logs.

#Why it hurts

A session that ran BEGIN, did some work, and then stopped sending commands (without COMMIT or ROLLBACK) keeps:

1. Its locks. Even a plain SELECT takes an ACCESS SHARE lock that conflicts with ALTER TABLE's ACCESS EXCLUSIVE lock.
2. Its snapshot (xmin). Vacuum can't remove row versions that this transaction might still need to see, anywhere in the database.

The lock queue makes it worse: once ALTER TABLE waits for the idle session, new queries wait behind the ALTER TABLE. One forgotten transaction can stall the whole application.

Typical causes: application code that opens a transaction and then waits on an HTTP call or user input; connection pools with autocommit off; ORMs that begin a transaction on first read; someone's psql or GUI session left open with an uncommitted BEGIN.

#Step 1: Find them

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

last_query is the last statement it ran, not what it's doing now. It's often the clue to which code path opened the transaction.

#Step 2: What are they blocking?

SELECT blocked.pid                    AS blocked_pid,
       left(blocked.query, 80)        AS blocked_query,
       now() - blocked.query_start    AS waiting_for,
       blocking.pid                   AS blocking_pid,
       blocking.state                 AS blocking_state,
       left(blocking.query, 80)       AS blocking_last_query
FROM   pg_stat_activity blocked
JOIN   LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid) ON true
JOIN   pg_stat_activity blocking ON blocking.pid = b.pid
ORDER  BY waiting_for DESC;

If the root of the chain is an idle in transaction session, that's your culprit.

#Step 3: Is it holding back vacuum?

-- Oldest snapshot holders
SELECT pid, state, backend_xmin, age(backend_xmin) AS xmin_age, now() - xact_start AS xact_age
FROM   pg_stat_activity
WHERE  backend_xmin IS NOT NULL
ORDER  BY age(backend_xmin) DESC
LIMIT  5;

-- Tables with lots of dead tuples
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM   pg_stat_user_tables
ORDER  BY n_dead_tup DESC
LIMIT  10;

Autovacuum logs (with log_autovacuum_min_duration set) show lines like tuples: 0 removed, 1500000 remain, 1500000 are dead but not yet removable. "Dead but not yet removable" means something is holding an old snapshot. Also check replication slots and hot_standby_feedback on replicas, which hold xmin the same way.

#Step 4: End the session safely

Cancel won't help: an idle session isn't running a query. You need to terminate it, which rolls back its transaction:

SELECT pg_terminate_backend(12345);

Terminate all sessions idle in a transaction for more than 30 minutes, from a specific app:

SELECT pid, pg_terminate_backend(pid)
FROM   pg_stat_activity
WHERE  state = 'idle in transaction'
  AND  now() - state_change > interval '30 minutes'
  AND  application_name = 'billing-api';

Caution

Terminating rolls back any uncommitted work in that transaction. For a batch job that spent an hour writing, that work is lost. Check last_query and the application owner before killing long-running writers; idle readers are usually safe.

On managed services, use the same functions; rds_superuser / azure_pg_admin members can terminate other users' sessions.

#Step 5: Stop it happening

#Set a timeout

-- For an application role
ALTER ROLE billing_app SET idle_in_transaction_session_timeout = '5min';

-- Or database-wide
ALTER DATABASE billing SET idle_in_transaction_session_timeout = '10min';

New sessions get the setting; existing ones keep their old value until they reconnect. Sessions that exceed it are terminated with FATAL: terminating connection due to idle-in-transaction timeout. Make sure the app reconnects and retries.

PostgreSQL 17 also added transaction_timeout (limit on whole-transaction duration); check your version before using it.

#Protect deployments with lock_timeout

Make DDL give up instead of stalling the application:

SET lock_timeout = '5s';
ALTER TABLE orders ADD COLUMN discount_code text;

If it fails, retry in a loop or find the blocker. This one setting prevents most "deploy took the site down" incidents.

#Fix the application pattern

#Monitoring query for your dashboards

SELECT count(*) FILTER (WHERE state = 'idle in transaction')                                         AS idle_in_tx,
       max(now() - state_change) FILTER (WHERE state = 'idle in transaction')                         AS longest_idle_in_tx,
       max(age(backend_xmin))                                                                        AS oldest_xmin_age
FROM   pg_stat_activity;

Alert when longest_idle_in_tx exceeds a few minutes or oldest_xmin_age keeps rising.

#Prevent it next time

• Set idle_in_transaction_session_timeout for every application role and for human roles.
• Always use lock_timeout in migration scripts.
• Alert on idle-in-transaction age and xmin age, not only connection counts.
• Review ORM transaction settings in code review; "transaction per request" with slow external calls is the usual root cause.