Skip to content

How to fix idle in transaction connections in PostgreSQL

Last updated

View as Markdown

Quick answer: An idle in transaction connection ran BEGIN but never committed or rolled back. While it sits there it holds any locks it took and pins the xmin horizon so autovacuum cannot clean up dead tuples, quietly causing lock waits and bloat. Find them in pg_stat_activity and cap them with idle_in_transaction_session_timeout, then fix the app to commit promptly.

What the state actually means

Section titled “What the state actually means”

A backend can be in several states. The dangerous one is idle in transaction:

  • active, running a query right now.
  • idle, connected, no open transaction. Harmless.
  • idle in transaction, ran BEGIN, maybe some statements, and is now waiting with the transaction still open. Harmful.
  • idle in transaction (aborted), same, but a statement errored; it is waiting for ROLLBACK.

The usual cause is application code that opens a transaction and then does something slow inside it, an external API call, waiting on user input, a long compute, instead of committing quickly.

Why it hurts

Section titled “Why it hurts”

An open transaction is not free:

  • 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 xmin horizon: autovacuum cannot remove dead tuples newer than that snapshot, so bloat builds up across the whole database while it sits there.
  • It occupies a connection slot you may badly 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_query
FROM pg_stat_activity
WHERE state LIKE 'idle in transaction%'
ORDER BY xact_start;

Match with LIKE 'idle in transaction%', not =. There is a second state.idle in transaction (aborted), where a statement errored and the transaction is sitting open waiting for a ROLLBACK, and an equality check silently misses it, even though it does the same damage.

The oldest xact_start is doing the most damage. It is 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 (Claude Code, Cursor) writes code that opens a transaction and does the requested work, and sometimes that work includes a call to another service, or an early return on an error path that skips the commit/rollback. It reads as correct, transactional code. What it cannot see is that in production that external call takes two seconds while the transaction holds locks and blocks vacuum. The cost is a runtime, concurrency property, invisible 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 dies on its own.idle_in_transaction_session_timeout (since PostgreSQL 9.6) terminates any session 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.

FAQ

Section titled “FAQ”
  • What does idle in transaction mean?
    A connection opened a transaction with BEGIN, ran some statements, and is now idle without COMMIT or ROLLBACK, the transaction is still open.
  • Why is it a problem?
    It holds locks (blocking others) and pins the xmin horizon so autovacuum cannot clean up dead tuples (causing bloat), and it ties up a connection.
  • How do I find them?
    pg_stat_activity where state LIKE 'idle in transaction%' (use LIKE, not =, so you also catch idle in transaction (aborted)), ordered by now() - xact_start.
  • How do I kill them automatically?
    Set idle_in_transaction_session_timeout (since PostgreSQL 9.6) at the server or role level; sessions idle in a transaction past the limit are terminated.
Section titled “Related”