Skip to main content

How to fix idle in transaction connections in PostgreSQL

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 can't 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​

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's 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​

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 can't 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?​

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's 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's 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?​

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 can't 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?​

  • 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.

How DBGorilla helps​

DBGorilla connects read-only and gives your AI coding agent (Claude Code, Cursor) the live session picture — which connections are idle in transaction, how long they've held their snapshot, what locks they hold — so it can trace a stuck transaction back to the code path that opened it and never committed. It surfaces and explains; it does not terminate sessions or change server settings for you. Get started free →

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 can't 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.