Long-running and idle-in-transaction sessions in PostgreSQL: what they block and how to bound them

What it is

A transaction that began long ago keeps two things: every lock it acquired, released only at commit or rollback, and a snapshot, which fixes its backend_xmin. The documentation of idle_in_transaction_session_timeout states that even without significant locks an open transaction prevents vacuuming away recently-dead tuples that may be visible only to it, so remaining idle for a long time can contribute to table bloat. pg_stat_activity shows per session the state (active, idle, idle in transaction, idle in transaction (aborted)), xact_start and backend_xmin.

Why it matters

One session in idle in transaction is the common cause of three symptoms that look unrelated: tables that keep growing although autovacuum runs, a migration that hangs on ALTER TABLE and then blocks every query behind it, and transaction-ID wraparound warnings, for which the routine vacuuming chapter's list of remedies includes ending long-running open transactions found by age(backend_xmin). The client usually does not know it holds anything: a framework opened a transaction on the first query, then the code made an HTTP call or waited for a user.

How to apply

• Find them: SELECT pid, usename, state, now() - xact_start AS age, age(backend_xmin), left(query, 80) FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_start lists the oldest transactions first.
• Set idle_in_transaction_session_timeout per application role (ALTER ROLE app SET idle_in_transaction_session_timeout = '30s'); the session is terminated and the client sees a connection error, which is the right outcome for a forgotten transaction.
• Add transaction_timeout (PostgreSQL 17 and later) for roles whose transactions must be short, and statement_timeout for single statements; the documentation notes that a transaction_timeout shorter than or equal to the other two makes the longer ones irrelevant.
• Keep the defaults for reporting and migration roles that legitimately run long, and give them their own connections and pool.
• Set these per role or per session; the documentation advises against setting statement_timeout and transaction_timeout in postgresql.conf because that affects all sessions, including maintenance.
• Alert on the oldest xact_start and on age(backend_xmin) from monitoring, not only on bloat.

Pitfalls

Replication slots and prepared transactions hold back the xmin horizon in the same way and are not covered by these timeouts; check pg_replication_slots and pg_prepared_xacts as well. A standby with hot_standby_feedback exports its long queries to the primary. A pooler in transaction mode can surface a terminated server connection as a puzzling error on an unrelated client. Termination rolls work back; a batch job needs commits in chunks, not a longer timeout.