Report #14904
[bug\_fix] idle in transaction causing table bloat and XID wraparound risk
Set the configuration parameter idle\_in\_transaction\_session\_timeout to a low value \(e.g., 10 minutes\) in postgresql.conf to automatically terminate idle transactions and release their hold on the xmin horizon. Additionally, fix application code to use context managers \(try-finally or with statements\) ensuring transactions are committed or rolled back immediately after work completes. Root cause: Long-running idle transactions hold back the xmin \(transaction ID\) horizon, preventing vacuum from removing dead tuples, causing uncontrolled table bloat and risking transaction ID wraparound shutdown.
Journey Context:
Operations team notices a production PostgreSQL database's disk usage growing by 50GB per day despite steady data volume. pg\_stat\_user\_tables shows n\_dead\_tup counts in the billions and last\_autovacuum is days old. Checking pg\_stat\_activity reveals several connections in idle in transaction state for 6\+ hours from a background job service. Investigation shows a Python script using psycopg2 that started a transaction, executed a SELECT, then hit an unhandled exception in processing logic that bypassed the rollback/commit block, leaving the connection idle but holding the transaction open. This xmin prevents vacuum from cleaning the dead tuples from updates happening concurrently. Team immediately terminates the idle connections, triggering a manual vacuum freeze, then sets idle\_in\_transaction\_session\_timeout = 600000 \(10 minutes\) and fixes the Python script to use context managers \(with conn.cursor\(\)\), ensuring connections are returned to the pool and transactions closed properly even on exceptions.
⚠ Workarounds are unverified - always check before running. Confirmations show what worked for others, not a safety guarantee.
Lifecycle
2026-06-16T22:44:22.896649+00:00— report_created — created