idle_in_transaction_session_timeout

Definition PG 18 manual

Terminate any session that has been idle (that is, waiting for a client query) within an open transaction for longer than the specified amount of time. If this value is specified without units, it is taken as milliseconds. A value of zero (the default) disables the timeout.

This option can be used to ensure that idle sessions do not hold locks for an unreasonable amount of time. Even when no significant locks are held, an open transaction prevents vacuuming away recently-dead tuples that may be visible only to this transaction; so remaining idle for a long time can contribute to table bloat. See Section 24.1 for more details.

How it works

idle_in_transaction_session_timeout sets the maximum allowed idle time between queries, when in a transaction. 0 disables the timeout. It terminates sessions idle inside an open transaction, releasing locks and old snapshots that can block vacuum cleanup and cause bloat.

idle_in_transaction_session_timeout is a USER-context setting. An authorized role can change it for a session, while ALTER ROLE or ALTER DATABASE can establish a default for future sessions.

Timeouts overlap: the first applicable deadline wins, while client, pooler, TCP, and server cancellation behavior determines whether work is retried, canceled, or the session is closed.

Operational considerations

Changing idle_in_transaction_session_timeout in one session and assuming role defaults, database defaults, or other pooled sessions changed with it.

Forgetting that zero usually disables the timeout or delegates behavior to the operating system.

Setting overlapping deadlines without deciding which layer owns retries, cancellation, and connection disposal.

Changing idle_in_transaction_session_timeout globally without a rollback plan and a client or operational compatibility test.

Workload guidance

OLAP: Analytical work usually needs a larger or job-specific idle_in_transaction_session_timeout; preserve a finite guardrail for abandoned work without killing legitimate long scans.

OLTP: Set idle_in_transaction_session_timeout from the service latency and failure budget, preferably per role or application. Test retries and cancellation paths before enforcing a cluster-wide value.

SMALL: Use a conservative finite idle_in_transaction_session_timeout only when the client or operating system handles termination correctly; verify that maintenance still has a dedicated exception.