liveBook · Manning

Idle_in_transaction_session_timeout

As explained in PostgreSQL Mistakes and How to Avoid Them

idle_in_transaction_session_timeout is a PostgreSQL setting that terminates a session after it remains idle while holding an open transaction for the configured duration.

An application can begin a transaction, read data, and then remain idle while doing other work. The open transaction can retain locks and prevent VACUUM from removing recently dead rows, causing table bloat, wasted disk space, and eventually transaction ID wraparound risk. section 6.5.1

 Client A               | Client B
------------------------+-----------------------
 BEGIN;                 |
 SELECT * FROM mytable; |
 (...)                  |
                        | ALTER TABLE mytable
                        | ADD description text;
                        | *hangs*

Shows how an idle transaction in one client can make a concurrent ALTER TABLE hang. section 6.5.1

Set idle_in_transaction_session_timeout to terminate sessions that remain idle inside an open transaction longer than the configured number of milliseconds. This limits lock retention and VACUUM blockage, but the duration must allow legitimate post-BEGIN processing, and the application must tolerate the resulting disconnection. section 6.5.1

How PostgreSQL Mistakes and How to Avoid Them covers it

How deep it goes
Explains timeout behavior, lock and VACUUM consequences, and application tradeoffs without detailing configuration syntax.
Examples
Uses a client that begins a transaction and reads mytable while another client’s ALTER TABLE hangs.
What it assumes
Assumes familiarity with PostgreSQL transactions, locks, and VACUUM behavior.
What you can do after
Configure a timeout that limits idle transactions while preserving legitimate processing and handling session disconnects.
How it connects
Connects transactions with locks, VACUUM, autovacuum, table bloat, and transaction ID wraparound.
Cover of PostgreSQL Mistakes and How to Avoid Them
liveBookmanning.com
Read PostgreSQL Mistakes and How to Avoid Them on liveBook Get the book at manning.com

Related concepts

This explainer was generated by Manning's knowledge graph from the text of PostgreSQL Mistakes and How to Avoid Them. The section links point to the parts of the book it draws on.