Why can a long-running or idle-in-transaction session make PostgreSQL slow?
Vacuum can only remove dead rows that no open transaction might still need. One session that sits idle in transaction holds back the cleanup horizon for the whole database, so bloat grows and queries slow down.
Explain it simply
Imagine a shared notice board where old notices can be removed only when nobody who started reading before they were replaced is still reading. If one person starts reading, then goes for lunch without finishing, none of the old notices can be thrown away.
In PostgreSQL, a transaction holds a snapshot. Dead rows newer than the oldest snapshot must be kept. A developer who runs BEGIN, a SELECT, and then leaves the terminal open ties up the cleanup for every table.
Example
SELECT pid, usename, state, xact_start,
now() - xact_start AS open_for, left(query, 40) AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start;
-- stop the offender
SELECT pg_terminate_backend(12345);
-- stop it happening again
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '60s';Application bugs that open a transaction and then call a slow external service (for example a payment gateway) before committing are the classic cause.
What to say in the interview
"VACUUM cannot clean rows an old snapshot might still see, so one idle-in-transaction session causes bloat everywhere. I find it in pg_stat_activity and set idle_in_transaction_session_timeout, and I keep transactions short in the application."
Common mistakes
- Holding a transaction open while waiting for a user or an API call.