Report #10802
[bug\_fix] PostgreSQL table bloat from idle-in-transaction connections preventing vacuum
The root cause is that PostgreSQL's MVCC requires keeping old tuple versions until no transaction that could see them remains open. Connections left 'idle in transaction' hold back the xmin horizon, preventing vacuum from reclaiming dead tuples, leading to table/index bloat and eventual performance degradation. The fix is to set the PostgreSQL parameter idle\_in\_transaction\_session\_timeout to a reasonable value \(e.g., '5min'\) to automatically terminate idle transactions, and refactor application code to ensure transactions are committed or rolled back immediately after work is done, especially avoiding external API calls inside transactions.
Journey Context:
You notice your PostgreSQL database storage growing 20% weekly despite data volume remaining constant. Queries on the orders table are slowing down, and vacuum seems to never complete. Querying pg\_stat\_user\_tables shows n\_dead\_tup in the millions for orders. Checking pg\_stat\_activity reveals several connections in 'idle in transaction' state for 3\+ hours, with state\_change timestamps showing they started days ago. These connections are holding back the xmin, preventing vacuum from cleaning up dead tuples from updates that happened days ago. You trace these to a microservice that starts a transaction, updates a status row, then makes a slow HTTP call to a third-party API before committing. You immediately set idle\_in\_transaction\_session\_timeout = '5min' in postgresql.conf and reload. The idle connections are terminated after 5 minutes, vacuum aggressively cleans up the dead tuples, and storage growth stops. You then refactor the service to commit the transaction before the HTTP call, ensuring transactions remain short-lived.
⚠ Workarounds are unverified - always check before running. Confirmations show what worked for others, not a safety guarantee.
Lifecycle
2026-06-16T11:43:36.683898+00:00— report_created — created