Idle-in-Transaction Detection

Find leaking connections.

Overview

Idle-in-transaction is the PostgreSQL state where a connection has begun a transaction but is doing nothing while holding locks and table snapshots. Caught early, it is a one-line application fix; left alone, it becomes a database-wide cascade.

• Leaking connections. Application bugs leave transactions open. The connection holds locks and a snapshot until the transaction commits or aborts.
• Lock retention. Open transactions hold row and table locks. Other queries wait; throughput collapses.
• Snapshot retention. Open transactions prevent vacuum from cleaning up dead tuples. Table bloat grows silently.
• pg_stat_activity plus session timeout. pg_stat_activity exposes the leaking sessions; idle_in_transaction_session_timeout kills them server-side.

The approach

Three habits keep idle-in-transaction under control: monitor pg_stat_activity, set the server-side timeout, and fix the application-side connection-pool bug.

• Monitor pg_stat_activity. Alert on connections in idle-in-transaction state for more than a few seconds. The metric catches the leak before users feel it.
• Set the timeout. idle_in_transaction_session_timeout = 30s at the server level. Structural protection regardless of application bugs.
• Fix application bugs. Connection-pool transaction leaks usually trace to a missing commit or rollback path. The fix lives upstream of the database.
• Test under load plus document. Synthetic load tests surface leaks; per-application connection-pool config lives in the runbook.

Why this compounds

Each caught leak prevents one cascade-incident class. The team learns the pattern through repeated detection; new applications ship with the timeout from day one.

• Reduced lock contention. Released locks improve throughput across the database. p99 query time benefits.
• Better vacuuming. No long-running transactions blocking vacuum. Bloat shrinks; statistics stay fresh.
• Lower incident rate. Connection-leak incidents caught early stop being recurring outage shape.
• Year-one investment, year-two habit. The first detection is investment. Subsequent applications inherit the timeout and the monitoring.