How to fix idle in transaction connections in PostgreSQL
Quick answer: An
idle in transactionconnection ranBEGINbut never committed or rolled back. While it remains open, it holds any locks it took and pins thexminhorizon so autovacuum cannot clean up dead tuples. The result is lock waits and bloat. Find these connections inpg_stat_activityand limit them withidle_in_transaction_session_timeout, then fix the application so it commits promptly.
What the state actually means
Section titled “What the state actually means”A backend can have several states. The most problematic one is
idle in transaction:
active, running a query right now.idle, connected with no open transaction. This is harmless.idle in transaction, ranBEGIN, possibly ran some statements, and is now waiting with the transaction still open.idle in transaction (aborted), the same state after a statement errored; it is waiting forROLLBACK.
The usual cause is application code that opens a transaction and then does something slow inside it, such as an external API call, waiting for user input, or a long computation, instead of committing quickly.
Why it hurts
Section titled “Why it hurts”An open transaction consumes resources:
- It holds locks. Any row or table locks it acquired stay held until commit, so other queries can block behind it.
- It blocks vacuum cleanup. The transaction keeps an old snapshot, which pins the
xminhorizon. Autovacuum cannot remove dead tuples newer than that snapshot, so bloat builds up across the whole database while the transaction remains open. - It occupies a connection slot that other work may need under load.
How do I find them?
Section titled “How do I find them?”SELECT pid, state, now() - xact_start AS txn_open_for, now() - state_change AS idle_for, query AS last_queryFROM pg_stat_activityWHERE state LIKE 'idle in transaction%'ORDER BY xact_start;Match with LIKE 'idle in transaction%', not =. The second state,
idle in transaction (aborted), occurs when a statement errors and the
transaction remains open while waiting for a ROLLBACK. An equality check misses
it, even though it does the same damage.
The oldest xact_start is usually the most urgent because it has held its
snapshot and locks the longest. To end a specific offender:
SELECT pg_terminate_backend(<pid>);Why does AI-generated code leave transactions open?
Section titled “Why does AI-generated code leave transactions open?”Your AI coding agent writes code that opens a transaction and does the requested
work. That work may include a call to another service or an early return on an
error path that skips the commit or rollback. The code may look correct as
transactional code. In production, however, the external call may take two
seconds while the transaction holds locks and blocks vacuum. The cost appears
only at runtime under concurrency and is not visible in the source.
How do I keep idle-in-transaction from coming back?
Section titled “How do I keep idle-in-transaction from coming back?”-
Set a timeout so a stuck transaction ends on its own.
idle_in_transaction_session_timeout(since PostgreSQL 9.6) terminates any session that remains idle inside a transaction past the limit:ALTER ROLE app_user SET idle_in_transaction_session_timeout = '30s'; -
Commit or roll back as soon as the database work is done.
-
Never hold a transaction open across a network call or user think-time.
What does idle in transaction mean in PostgreSQL?
It means a connection opened a transaction with BEGIN, ran zero or more statements, and is now sitting idle without a COMMIT or ROLLBACK. The transaction is still open, so it keeps a snapshot and any locks it acquired.
Why is idle in transaction a problem?
The open transaction holds any locks it took, which can block other queries, and it pins the xmin horizon so autovacuum cannot remove dead tuples newer than its snapshot, which causes bloat. It also ties up a connection slot.
How do I find idle in transaction connections?
Query pg_stat_activity for state LIKE 'idle in transaction%', ordered by how long the transaction has been open (now() - xact_start). Use LIKE rather than an equality check so you also catch the idle in transaction (aborted) state, which does the same damage.
How do I automatically kill idle in transaction sessions?
Set idle_in_transaction_session_timeout (available since PostgreSQL 9.6). Any session idle inside a transaction longer than the timeout is terminated automatically. Set it at the server or role level to bound the damage a stuck transaction can do.