idle_in_transaction_session_timeout
Kills sessions idle inside open transactions. Default 0 = never. Without it, forgotten transactions block VACUUM and cause bloat.
Tier 1 — Lab-Proven — Measured behavior and safety boundaries are available below.
What We Observed
mechanism
Terminates sessions that have been idle inside an open transaction for too long. Idle-in-transaction sessions hold row snapshots, preventing VACUUM from removing dead tuples.
from iit effect
idle_in_transaction_session_timeout terminates a session left idle inside an open BEGIN. (idle_in_txn_reached=1 = it actually went idle-in-transaction; alive_after=0 = server killed it)
timeout=5s idle~2s | idle_in_txn_reached=1 alive_after=1 | survived timeout=2s idle~5s | idle_in_txn_reached=0 alive_after=0 | TERMINATED timeout=3s idle~6s | idle_in_txn_reached=1 alive_after=0 | TERMINATED
Exact server-side message (from log):
2026-06-20 19:00:36.349 UTC [10924] FATAL: terminating connection due to idle-in-transaction timeout
(Below the timeout the idle-in-transaction session lives; past it the server terminates it with FATAL 'terminating connection due to idle-in-transaction timeout' and rolls it back.)
from iit danger
=== A) No idle transaction: UPDATE all 100k rows (100k dead versions), then VACUUM === pages: 0 removed, 5883 remain, 5883 scanned (100.00% of total) tuples: 100000 removed, 100000 remain, 0 are dead but not yet removable removable cutoff: 1540, which was 0 XIDs old when operation ended index scan needed: 2942 pages from table (50.01% of total) had 100000 dead item identifiers removed pages: 0 removed, 0 remain, 0 scanned (100.00% of total) tuples: 0 removed, 0 remain, 0 are dead but not yet removable removable cutoff: 1540, which was 0 XIDs old when operation ended index scan not needed: 0 pages from table (100.00% of total) had 0 dead item identifiers removed
^ all dead versions removable -> reclaimed
=== B) Idle-in-transaction holder open (pid 10783, timeout DISABLED). UPDATE again, VACUUM === 10783 | idle in transaction | 1540 pages: 0 removed, 5883 remain, 5883 scanned (100.00% of total) tuples: 0 removed, 200000 remain, 100000 are dead but not yet removable removable cutoff: 1540, which was 1 XIDs old when operation ended index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed pages: 0 removed, 0 remain, 0 scanned (100.00% of total) tuples: 0 removed, 0 remain, 0 are dead but not yet removable removable cutoff: 1540, which was 1 XIDs old when operation ended index scan not needed: 0 pages from table (100.00% of total) had 0 dead item identifiers removed
^ dead versions NOT removable -- the idle txn's snapshot pins the xmin horizon = future bloat
=== C) Holder released (as idle_in_transaction_session_timeout would force). VACUUM again === pages: 0 removed, 5883 remain, 5883 scanned (100.00% of total) tuples: 100000 removed, 100000 remain, 0 are dead but not yet removable removable cutoff: 1541, which was 0 XIDs old when operation ended index scan needed: 2941 pages from table (49.99% of total) had 99982 dead item identifiers removed pages: 0 removed, 0 remain, 0 scanned (100.00% of total) tuples: 0 removed, 0 remain, 0 are dead but not yet removable removable cutoff: 1541, which was 0 XIDs old when operation ended index scan not needed: 0 pages from table (100.00% of total) had 0 dead item identifiers removed
^ the SAME dead versions are now removable -> reclaimed; the guard would have done this for you
Reproduced Danger
Sizing / Safe Range
Workload Guidance
Unlock Incident Pro
The free sections tell you what to do. Pro is the measured proof — the exact failure signature, safe-ceiling formula, and per-workload defaults, each captured on real PG 14–18 runs.
- Reproduced Danger
- Sizing / Safe Range
- Workload Guidance
idle_in_transaction_session_timeout sets how long a session may sit IDLE while it still has an open transaction (a BEGIN that hasn't been committed or rolled back) before the server forcibly terminates the connection. It targets one specific bad state: a client that ran BEGIN, did some work, and then walked away — leaving the transaction open but the session doing nothing. That state is dangerous because an open transaction holds a snapshot (and any locks it took), and a held snapshot PINS the database's xmin horizon, which is the cutoff below which VACUUM is allowed to remove dead row versions. While the idle transaction sits there, VACUUM cannot clean up dead tuples newer than that snapshot anywhere in the database, so bloat accumulates and autovacuum spins without making progress. The default is 0, which means the guard is OFF — an idle-in-transaction session can sit forever. Setting it to a positive number of milliseconds tells the server to kill such a session after that long, rolling back its transaction and releasing its snapshot and locks so the xmin horizon can advance again. It is purely a watchdog on the IDLE-IN-TRANSACTION state; it does not affect a session that is idle without an open transaction (that is idle_session_timeout), nor a long-RUNNING query (that is statement_timeout), nor the total length of a transaction (that is transaction_timeout, new in PG 17).
- name
idle_in_transaction_session_timeout
- setting default
0 (disabled)
- unit
ms
- vartype
integer
- category
Client Connection Defaults / Statement Behavior
- context
user
- min val
0
- max val
2147483647
- enumvals
(n/a — integer ms)
- boot val
0
- reset val
0
- pending restart
false
- short desc
Sets the maximum allowed idle time between queries, when in a transaction.
- ground truth note
Captured literally from pg_settings on PG 14-18 (-18) on 2026-06-20. setting=0, boot_val=0, reset_val=0 on EVERY version (default_changed=false) — the guard ships DISABLED. context=user (session-changeable, no reload/restart). vartype=integer, unit=ms, min_val=0 (0 disables), max_val=2147483647. category='Client Connection Defaults / Statement Behavior', short_desc='Sets the maximum allowed idle time between queries, when in a transaction.' Identical across 14, 15, 16, 17, 18. The related transaction_timeout (whole-transaction cap) is NEW in PG 17; idle_in_transaction_session_timeout itself has existed and been unchanged since well before 14.
Rock-stable across versions: default=0 (disabled), min=0, max=2147483647, context=user, vartype=integer, unit=ms on PG 14, 15, 16, 17 and 18 — no version cliff for THIS knob (default_changed=false). The FAMILY did change: transaction_timeout (a cap on the whole transaction's duration, which also catches idle-in-transaction time) was ADDED in PG 17, so on 17/18 you have a second, stronger guard available. statement_timeout and idle_session_timeout are present and unchanged across 14-18. Per-version detail in _guc_matrix_machine.
context=user: set it per session with SET idle_in_transaction_session_timeout = '30s'; (effective immediately for the current session), per role/database with ALTER ROLE/DATABASE . SET (the usual way to apply it broadly without a restart — e.g. ALTER DATABASE app SET idle_in_transaction_session_timeout='60s'), or cluster-wide via postgresql.conf / ALTER SYSTEM + reload. CONTEXT PROOF (captured literal, identical on and): context=user, so a session SET is accepted with no wrong-context error. BOUNDARY PROOF (captured literal, identical on and): a negative value is rejected at set time -- ALTER SYSTEM SET idle_in_transaction_session_timeout = -1; -> ERROR: -1 ms is outside the valid range for parameter "idle_in_transaction_session_timeout" (0 ms .. 2147483647 ms). The valid range is 0 . 2147483647 ms; 0 DISABLES the guard (the default). The value is in milliseconds but accepts unit suffixes like '30s', '5min'.
Managed providers (Azure Database for PostgreSQL Flexible Server, Amazon RDS / Aurora, Cloud SQL, Crunchy, Supabase, Neon, Heroku) all expose idle_in_transaction_session_timeout as a server parameter, default 0, and because context=user you can also set it per-session/role/database without provider involvement (ALTER DATABASE . SET is the usual deployment). Several managed platforms recommend or pre-set a non-zero value precisely to protect their fleets from idle-in-transaction bloat. Recommended posture: set a sensible positive value (sized above legitimate think time) per database, monitor pg_stat_activity for long-lived 'idle in transaction' sessions and low backend_xmin, and on PG 17+ add transaction_timeout for a total-duration cap. Do not rely on the default 0.
Common Pitfalls
- Leaving the default 0 in production — a forgotten BEGIN in an app/pooler connection pins the xmin horizon indefinitely and bloats tables despite healthy autovacuum.
- Setting it too low — it terminates LEGITIMATE transactions that pause between statements (external calls, user think time), rolling back real work and surfacing as 'server closed the connection unexpectedly'.
- Confusing it with idle_session_timeout — that one targets idle sessions WITHOUT a transaction and has no bloat effect; this one targets idle-IN-transaction sessions, which are the ones that hold back VACUUM.
- Expecting it to bound a LONG-RUNNING query — it only fires when the session is IDLE; an active query is governed by statement_timeout, and total transaction time by transaction_timeout (PG 17+).
- Setting it globally and forgetting admin/maintenance sessions — a DBA running a careful multi-step manual transaction can be killed mid-way; loosen it per-role for admins.
- Thinking it removes existing bloat — it only stops a forgotten transaction from causing MORE; you still need VACUUM (or VACUUM FULL/pg_repack for severe bloat) to reclaim space already lost.
Evidence and References
- PostgreSQL docs: Runtime Config — Client Connection Defaults / Statement Behavior (idle_in_transaction_session_timeout — terminate a session idle within an open transaction longer than the limit; 0 disables; related idle_session_timeout, statement_timeout, transaction_timeout).
- PostgreSQL docs: Routine Vacuuming / VACUUM (how the xmin horizon and 'dead but not yet removable' tuples work — why a held snapshot blocks dead-tuple removal).
- PostgreSQL docs: pg_stat_activity (state='idle in transaction', backend_xmin — diagnosing a session that is pinning the horizon).
- PostgreSQL 17 release notes: addition of transaction_timeout (a hard cap on total transaction duration, complementing idle_in_transaction_session_timeout).
- Lab capture 2026-06-20: — watchdog terminates idle-in-txn past the timeout with 'FATAL: terminating connection due to idle-in-transaction timeout' ; idle REPEATABLE-READ txn with backend_xmin=1540 blocked VACUUM (100000 'dead but not yet removable', removable cutoff 1540) until released ; boundary min=0 disables ; pg_settings ground truth PG 14-18 (-18).
Technical Reference
- Category
- Client Connection Defaults / Statement Behavior
- Type
- integer (ms (milliseconds; 0 disables))
- Blast radius
- database
- Severity
- Moderate
- Context
- user
- Versions
- 14, 15, 16, 17, 18
- Reviewed
- 2026-06-20
Severity scale: 1 Minimal · 2 Low · 3 Moderate · 4 High · 5 Critical.