Every table in a database was bloating at once. Not one hot table — all of them. Autovacuum was running constantly and recovering almost nothing. The metrics made no sense until I looked at the connection list and found a session that had been idle in transaction for eleven hours.
It was a developer's debugger, paused on a breakpoint, with an open transaction behind it, on a connection pointed at production. That one paused transaction was holding the line that vacuum is not allowed to cross.
An idle in transaction session is a transaction that ran at least one statement, did not commit or roll back, and is now just sitting there. It looks harmless. It is one of the most destructive states a PostgreSQL connection can be in.
Why one open transaction hurts everything
PostgreSQL can only clean up a dead tuple once no transaction could still need to see it. The oldest running transaction defines that boundary — the xmin horizon. VACUUM will not remove any row version newer than the oldest transaction still in progress.
An idle-in-transaction session holds a transaction open, so it pins that horizon in the past. Now vacuum across the entire database cannot reclaim any dead tuple created after that transaction started. Every table accumulates dead rows that vacuum dutifully visits and refuses to remove. That is why the whole database bloats from one idle session.
It also holds locks
Beyond the vacuum horizon, an open transaction keeps whatever locks it acquired. If it ran an UPDATE or took an explicit lock before going idle, every other transaction that needs that resource waits — potentially for the entire eleven hours. A paused debugger can block a migration, a deploy, or a user's checkout indefinitely.
Finding the offenders
The connection state lives in pg_stat_activity. You want sessions whose state is exactly idle in transaction (or the more dangerous idle in transaction (aborted)), sorted by how long they have been sitting there.
SELECT pid,
state,
now() - state_change AS idle_for,
now() - xact_start AS xact_age,
left(query, 60) AS last_query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
ORDER BY xact_start;The setting that ends the problem
PostgreSQL can terminate these automatically. idle_in_transaction_session_timeout kills any session that stays idle inside a transaction longer than the limit. Setting it to something like 5–15 minutes means a forgotten transaction cleans itself up instead of bloating the database overnight.
Set it globally as a safety net, and you can set it tighter per role for interactive users who are more likely to wander off mid-transaction.
-- Global safety net
ALTER SYSTEM SET idle_in_transaction_session_timeout = '10min';
SELECT pg_reload_conf();
-- Tighter for human/interactive roles
ALTER ROLE analyst SET idle_in_transaction_session_timeout = '2min';Fix it in the application too
The timeout is a backstop, not a cure. The real fix is application discipline: do not open a transaction and then do slow work (an HTTP call, user think-time, a debugger). Keep transactions short, commit or roll back promptly, and make sure your connection pool resets transaction state when handing a connection back.
A good rule: a transaction should be open only while you are actively running SQL. The moment you are waiting on anything else, you should not be inside one.
- An idle-in-transaction session pins the xmin horizon and blocks vacuum database-wide.
- It also holds any locks it acquired until it ends.
- Find them in pg_stat_activity by state and xact_start.
- Set idle_in_transaction_session_timeout as a global safety net.
- Keep application transactions short; never wait on I/O or humans inside one.
The practical standard
The best PostgreSQL performance work is boring in the right way. Name the failure mode, capture the before plan or metric, make one change, and compare the exact same signal afterward. Anything else is just a more confident guess.