← Blog · Postgres · July 15, 2026 · 9 min read · By Team

Idle in Transaction in Postgres: What It Means, How to Find It, and Why Autovacuum Can't Save You

What idle in transaction means in Postgres, why it silently blocks autovacuum while dashboards stay green, the 60-second diagnosis SQL, and the one-line timeout that prevents it.

Idle in Transaction in Postgres: What It Means, How to Find It, and Why Autovacuum Can't Save You

Idle in transaction is a Postgres session state where a client has issued BEGIN, finished its last statement, and gone quiet without committing or rolling back. The connection is alive, the backend is waiting on the client, and — critically — the transaction is still holding a snapshot. That held snapshot is what pins the xmin horizon and quietly breaks autovacuum, even though every dashboard stays green.

Key Takeaway
Idle in transaction Postgres sessions don't burn CPU or throw errors — they hold a snapshot that freezes cleanup, and the damage only shows up days later as bloat, plan drift, or wraparound risk.

Why this matters

A single forgotten BEGIN on a long-lived pooled connection can block autovacuum across your busiest tables for hours or days. According to the Stackademic post-mortem from July 13, 2026, one idle session opened on a Tuesday blocked autovacuum until Thursday — no alerts fired, because CPU, error rate, and lock waits all looked normal. The visible symptom was gradually slower queries on a hot table; the actual cause was a snapshot that the database was not allowed to advance past.

This is the canonical silent failure: every component green, degradation accumulating for days. The fix is one line of configuration. Getting there requires understanding what the state actually is.

What idle in transaction actually means in Postgres

There are two "idle" states in pg_stat_activity:

There's also idle in transaction (aborted) — same thing, but the transaction hit an error and the client hasn't sent ROLLBACK yet. Same blocking behavior for cleanup.

The key mental model: Postgres cannot reclaim a row version that any open transaction might still need to see. A snapshot taken at BEGIN says "I might read the pre-update version of any row." As long as that snapshot is held, the dead tuple stays.

The mechanism: a held snapshot pins the xmin horizon

Every transaction gets a snapshot describing which other transactions were committed at its start. Autovacuum uses the oldest snapshot in the cluster — the xmin horizon — as the cutoff for which dead tuples are safe to remove. An idle-in-transaction session freezes that horizon at the moment it ran BEGIN.

The tell is characteristic and easy to misread:

Every knob you'd normally reach for — autovacuum_vacuum_scale_factor, autovacuum_naptime, more workers — cannot help. Autovacuum is running. It just isn't allowed to remove anything past the pinned horizon. This is a horizon problem, not a tuning problem.

Dashboards stay green because there's no CPU spike (the session is asleep), no error (the transaction is valid), and no lock storm (only the rows the transaction touched are locked). The system degrades on a slope no threshold catches.

Find idle in transaction in 60 seconds

Paste this during an incident. It surfaces the oldest open transaction, how long it's been idle, and what it last ran:

SELECT
  pid,
  usename,
  application_name,
  client_addr,
  state,
  wait_event_type,
  wait_event,
  xact_start,
  now() - xact_start        AS xact_age,
  now() - state_change      AS idle_for,
  left(query, 200)          AS last_query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
  AND xact_start IS NOT NULL
ORDER BY xact_start ASC
LIMIT 20;

Anything with xact_age over a few minutes on a transactional workload is suspect. Anything over an hour is the incident.

Confirm blast radius — who is blocked behind the locks this transaction holds:

SELECT
  blocked.pid       AS blocked_pid,
  blocked.usename   AS blocked_user,
  blocking.pid      AS blocking_pid,
  blocking.usename  AS blocking_user,
  blocking.state    AS blocking_state,
  now() - blocking.xact_start AS blocking_xact_age,
  left(blocked.query, 120)    AS blocked_query
FROM pg_stat_activity blocked
JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid) ON true
JOIN pg_stat_activity blocking ON blocking.pid = b.pid
WHERE blocked.wait_event_type = 'Lock'
ORDER BY blocking_xact_age DESC;

If a vacuum is running right now, watch it make (or fail to make) progress:

SELECT pid, datname, relid::regclass, phase,
       heap_blks_total, heap_blks_scanned, heap_blks_vacuumed
FROM pg_stat_progress_vacuum;

The decision tree: horizon first, blocking graph second, vacuum knobs third

When bloat or slow queries land on a table, resist the urge to tune. Go in this order:

  1. Horizon. Run the query above. Is there an idle-in-transaction session older than the vacuum interval you care about? If yes, that is the cause. Stop.
  2. Blocking graph. No idle-in-transaction older than a minute? Check pg_blocking_pids for a long-running query holding row locks the vacuum can't wait behind.
  3. Vacuum settings. Only if 1 and 2 are clean should you look at autovacuum_vacuum_scale_factor, cost limits, or worker count.

Skipping steps 1 and 2 is how teams spend a week tuning autovacuum before realizing the analytics job on the read replica is holding hot_standby_feedback on the primary.

Kill idle in transaction Postgres sessions correctly

Two functions, one right answer:

Use terminate for idle-in-transaction. Cancel is useless here.

-- Terminate every idle-in-transaction session older than 10 minutes.
SELECT pid, pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
  AND now() - xact_start > interval '10 minutes';

This is a manual bandage. The real fix is the timeout.

Where these sessions are born

They rarely come from a human at a psql prompt. In production the common sources are:

The escalation ladder

Held snapshot → bloat → then the compounding effects:

  1. Bloat on hot tables. Dead tuples accumulate; the table grows on disk.
  2. Index-only scans degrade. The visibility map can''t be marked all-visible for pages containing tuples the horizon still protects. Planners fall back to heap fetches.
  3. Plan drift. ANALYZE runs against inflated statistics; the planner chooses worse joins.
  4. Stalled freezing. VACUUM can''t advance relfrozenxid past the horizon.
  5. Wraparound risk. When age(datfrozenxid) approaches autovacuum_freeze_max_age, Postgres launches emergency anti-wraparound vacuums; if the horizon is still pinned, they cannot finish. At 2 billion transactions, the database refuses new writes to protect data integrity.

hot_standby_feedback = on is the replication cousin: a long query on a standby pins the horizon on the primary the same way. Same failure mode, different pid to hunt.

Prevention: idle_in_transaction_session_timeout

The single most valuable line of Postgres configuration for this class of failure. Set it, and Postgres closes any transaction idle longer than the timeout automatically. The client sees an error on its next statement — which is exactly what you want, because the alternative is silent bloat.

Cluster-wide default (conservative):

ALTER SYSTEM SET idle_in_transaction_session_timeout = ''5min'';
SELECT pg_reload_conf();

Tighter per role — analytics readers and BI service accounts almost never legitimately need a multi-minute open transaction:

ALTER ROLE analytics_reader SET idle_in_transaction_session_timeout = ''30s'';
ALTER ROLE bi_service       SET idle_in_transaction_session_timeout = ''30s'';

Check the current value:

SHOW idle_in_transaction_session_timeout;

SELECT rolname, rolconfig
FROM pg_roles
WHERE rolconfig::text LIKE ''%idle_in_transaction_session_timeout%'';

Pair it with statement_timeout for run-away queries and idle_session_timeout (Postgres 14+) for connections that never BEGIN but never disconnect either. The default for idle_in_transaction_session_timeout is 0 — off. Every cluster ships with this footgun loaded.

Monitoring wiring: alert on the horizon, not the symptom

Symptoms (bloat, slow queries) surface days late. Alert on the cause:

Common pitfalls

Key documents, evidence, and references

FAQ

What is idle in transaction?
A Postgres session state where a client has run BEGIN and one or more statements but hasn''t yet committed or rolled back, and isn''t currently sending a new statement. The connection is alive and a snapshot is held.

What is idle_in_transaction_session_timeout?
A Postgres GUC that terminates any transaction idle for longer than the configured duration. Defaults to 0 (off). Set it cluster-wide and tighten per role.

What does idle mean in pg_stat_activity?
Just idle — no active statement, no open transaction. Harmless. Different from idle in transaction, which does have an open transaction.

How does PostgreSQL handle transactions?
Every transaction gets a snapshot at BEGIN (in the default READ COMMITTED isolation, snapshots are actually taken per statement, but held resources like the xmin horizon apply for the transaction''s duration). Postgres won''t remove any row version any open transaction might still need. That''s why an idle-in-transaction session blocks cleanup until it commits, rolls back, or is terminated.

Where Operate fits

This is the failure class Operate is built to catch: every component green, degradation accumulating for days, resolved by a one-line config change nobody knew to make. Operate opens a case when an idle-in-transaction session crosses an age threshold or when n_dead_tup growth decouples from vacuum progress on a hot table. Its evidence-first RCA runs exactly this decision tree — horizon before knobs — with read-only database access, and drafts the ALTER SYSTEM or per-role timeout as a patch file a human reviews before it ships.

#postgres#autovacuum#reliability#runbook#sre