An idle-in-transaction session has started a PostgreSQL transaction, is not executing a query, and has neither committed nor rolled back. In pg_stat_activity, it appears as idle in transaction. The dangerous pattern is simple: a client runs BEGIN, performs work, then leaves the connection open while the transaction remains alive. section 6.5.1
This state is not harmless inactivity. PostgreSQL’s MVCC visibility rules rely on transaction IDs, while transactions can retain locks acquired by earlier statements. A session that merely read a table can therefore continue to affect work that arrives later, even though no query is currently running. section 6.5.1 section 6.13
A read that blocks a schema change
Client A begins a transaction and runs SELECT * FROM mytable, acquiring an ACCESS SHARE lock. If Client A then goes idle without ending the transaction, Client B’s ALTER TABLE ... ADD description text waits because ACCESS EXCLUSIVE conflicts with every lock mode. A later Client C query can also wait behind the pending schema change. Once Client A ends and commits, the ALTER TABLE completes and Client C can proceed. section 6.5.1
Client A | Client B | Client C
------------------------+-----------------------+------------------------
BEGIN; | |
SELECT * FROM mytable; | |
(...) | |
| ALTER TABLE mytable |
| ADD description text; |
| *hangs* |
| | SELECT 1 FROM mytable;
| | *hangs*
The three-client timeline makes the blocking chain visible: an idle reader holds its transaction open, a DDL request waits, and later access queues behind that request. section 6.5.1
Long-lived idle transactions can also hold back the XID horizon. That can defer freezing of old rows, interfere with autovacuum, and contribute to bloat or more serious transaction-ID-wraparound pressure. Idle-in-transaction sessions and long-running queries may additionally cause blocking that surfaces as application delays or errors. section 6.6.1 section 6.13
Avoid the state by treating transaction completion as part of every request or operational task:
- Do not leave a client connected after BEGIN without promptly committing or rolling back. section 6.5.1
- Avoid keeping connections that do little work; when connections must be retained, use a pooler aware of transaction semantics, such as PgBouncer in Transaction Mode. section 6.13
- Check pg_stat_activity for sessions reported as idle in transaction when investigating unexpected waits. section 6.5.1
The practical rule is to keep transaction scope no larger than the database work it protects. A completed SELECT does not mean its transaction has stopped affecting concurrency; only COMMIT or ROLLBACK releases the session from that state. section 6.5.1
