Skip to main content
RunBook Academy

← All break/fix scenarios in PostgreSQL

advancedpg-idle-in-transaction~40 min

Every table in the cluster started bloating, and autovacuum was working perfectly

Reported symptoms

  • ●Disk usage on db-prod-03 rises from 58 to 74 per cent over four days with no change in row counts anywhere in the cluster
  • ●The growth is not concentrated: roughly a hundred tables across six schemas are all larger, including several that receive fewer than a thousand writes a day
  • ●Query latency on the two busiest tables has roughly doubled, and plans that used index-only scans now report large Heap Fetches values
  • ●autovacuum_count in pg_stat_user_tables is increasing normally and last_autovacuum is recent on every affected table, so autovacuum appears to be running
  • ●A manual VACUUM VERBOSE on the largest affected table completes in ninety seconds and frees nothing
  • ●The team increases autovacuum_max_workers from three to eight and lowers autovacuum_vacuum_scale_factor to 0.02 cluster-wide, and the growth continues at the same rate
  • ●Somebody proposes VACUUM FULL on the six largest tables during the next maintenance window

Evidence

  • · VACUUM VERBOSE reports tuples: 0 removed, 4188122 remain, 3947551 are dead but not yet removable, and a removable cutoff many transactions behind the current one
  • · pg_stat_activity shows one session with state idle in transaction, an xact_start four days old, and a backend_xmin whose age is over eleven million transactions
  • · The session query column reads SELECT count(*) FROM orders WHERE status = 4; and its state_change is the same four-day-old timestamp
  • · The session usename is a named developer account, its application_name is psql, and its client_addr is inside the office VPN range
  • · pg_replication_slots is empty and pg_prepared_xacts is empty, so neither slots nor prepared transactions are holding the horizon
  • · The oldest backend_xmin across all sessions equals the removable cutoff reported by VACUUM, which identifies that session as the sole constraint
  • · pgstattuple on the largest affected table reports tuple_percent of 11.4 and free_percent of 4.2, so the space is occupied by dead tuples rather than reusable free space
  • · idle_in_transaction_session_timeout is 0 on this cluster and on every other cluster in the estate
Diagnosis and resolutionclick to reveal

Root cause

A single session had an open transaction for four days, and that transaction's snapshot prevented vacuum from removing any row version newer than it — across every table in the cluster. Vacuum may only remove a dead row version if no transaction that is still running could need to see it. It establishes that boundary by taking the oldest snapshot held by any session on the cluster and refusing to remove anything newer. That boundary is a single cluster-wide value. It is not per table, per database or per schema, because vacuum cannot know which tables an open transaction might go on to read. So one forgotten `BEGIN` froze cleanup everywhere. Autovacuum was not failing. It ran on schedule, scanned the tables, found millions of dead tuples, correctly determined it was not permitted to remove them, and reported exactly that in a line everybody read past: **"0 removed, 3947551 are dead but not yet removable"**. Every remedy the team applied made the situation slightly worse. Raising `autovacuum_max_workers` and lowering the scale factor made autovacuum run more often, and each run did the same scanning work and removed the same nothing, adding I/O to a server already under pressure. The index-only scan regression has the same single cause. Those scans depend on the visibility map, vacuum is what maintains it, and vacuum could not set a page all-visible while dead tuples on it were unremovable. `Heap Fetches` rose, and the queries slowed, for the same reason the tables grew. The session itself was not doing anything. It was a developer's `psql`, left open on a Thursday afternoon after a `BEGIN` that was never committed or rolled back, sitting idle inside a transaction over a weekend.

Remediation

Identify the constraint before touching anything. One query answers it: ```sql SELECT pid, usename, application_name, client_addr, state, now() - xact_start AS xact_age, backend_xmin, age(backend_xmin) AS xmin_age, left(query, 60) AS last_query FROM pg_stat_activity WHERE backend_xmin IS NOT NULL OR (state LIKE 'idle in transaction%' AND xact_start IS NOT NULL) ORDER BY xact_start; ``` Check the two other holders of the horizon in the same breath, because the fix differs for each: ```sql SELECT slot_name, active, restart_lsn, xmin FROM pg_replication_slots; SELECT gid, prepared, owner FROM pg_prepared_xacts; ``` Here both are empty and a single session is responsible. Terminate it. `pg_cancel_backend` will return true and do nothing — there is no statement running to cancel — so `pg_terminate_backend` is the correct tool: ```sql SELECT pg_terminate_backend(<pid>); ``` Confirm the horizon moved, then vacuum. The horizon advancing is what makes the next vacuum effective; without checking it you cannot tell whether the termination achieved anything. Vacuum the largest tables explicitly rather than waiting for autovacuum to reach them, and read `VACUUM VERBOSE` output rather than assuming: the line to see is "N removed" with a non-zero N and "0 are dead but not yet removable". Revert the autovacuum changes made during the incident. They were applied against a misdiagnosis, they increase load on every table in the cluster, and leaving them in place makes the next capacity conversation harder. Do not run `VACUUM FULL`. It would have worked, at the cost of an AccessExclusiveLock on each table for the duration of a full rewrite, and it treats the symptom while the cause is still connected.

Verification

`SELECT count(*) FROM pg_stat_activity WHERE state LIKE 'idle in transaction%' AND now() - xact_start > interval '5 minutes'` returns zero. `VACUUM (VERBOSE)` on a previously affected table reports a non-zero "removed" and "0 are dead but not yet removable", and the "removable cutoff" is close to the current transaction id rather than millions behind it. `n_dead_tup` in `pg_stat_user_tables` falls on the affected tables, and stops rising. An `EXPLAIN ANALYZE` of one of the regressed queries shows `Heap Fetches: 0` again once vacuum has re-established the visibility map, and the execution time returns to its previous range. Table sizes do **not** fall. Vacuum makes space reusable inside the file rather than returning it to the filesystem, so the correct outcome is that growth stops and the files stay where they are. Expecting the disk to recover is how this gets misdiagnosed a second time.

Prevention

**Set `idle_in_transaction_session_timeout` cluster-wide.** This is the single change that closes the class. A value in the minutes is generous for any legitimate application transaction and catches every forgotten `psql`. It terminates the session, logs the reason, and leaves a record naming the cause. **Alert on transaction age, not on table size.** Disk growth is a lagging indicator that fires days late. `max(now() - xact_start)` across `pg_stat_activity` fires in minutes, and so does `max(age(backend_xmin))`. **Read the "dead but not yet removable" line.** It is in every `VACUUM VERBOSE` and in every `log_autovacuum_min_duration` entry, and it distinguishes "vacuum cannot keep up" from "vacuum is not permitted to act" — two problems with opposite fixes. **Monitor all three holders of the horizon together.** Long transactions, replication slots, and prepared transactions produce identical symptoms. A single dashboard panel showing the oldest of the three prevents diagnosing the wrong one. **Give developers a role with a short timeout.** Interactive access is legitimate; an interactive session holding a transaction over a weekend is not. A per-role `idle_in_transaction_session_timeout` costs nothing and applies exactly where the risk is.

Reported symptoms

Disk usage on db-prod-03 goes from 58% to 74% over four days. No row count anywhere in the cluster has changed materially.

The growth is not concentrated in one place. About a hundred tables across six schemas are all larger, including several that take fewer than a thousand writes a day and one that is written to weekly.

Latency on the two busiest tables has roughly doubled. Plans that used to show index-only scans now report large Heap Fetches values.

pg_stat_user_tables shows autovacuum_count increasing and last_autovacuum recent on every affected table. A manual VACUUM VERBOSE on the largest table runs for ninety seconds and frees nothing.

The team raises autovacuum_max_workers from 3 to 8 and drops autovacuum_vacuum_scale_factor to 0.02 cluster-wide. The growth continues at exactly the same rate. Somebody proposes VACUUM FULL on the six largest tables in the next window.

Evidence provided

Read-only / Safevacuum ran, did its work, and was not allowed to remove anything
$ psql -c "VACUUM (VERBOSE) orders;"
INFO:  vacuuming "app.public.orders"
INFO:  finished vacuuming "app.public.orders": index scans: 0
pages: 0 removed, 512044 remain, 512044 scanned (100.00% of total)
tuples: 0 removed, 4188122 remain, 3947551 are dead but not yet removable
removable cutoff: 118904221, which was 11284993 XIDs old when operation ended
avg read rate: 88.412 MB/s, avg write rate: 0.004 MB/s
system usage: CPU: user: 3.11 s, system: 1.02 s, elapsed: 89.44 s

Illustrative output

Read-only / Safeone session, four days old
$ psql -c "SELECT pid, usename, state, now()-xact_start AS xact_age, age(backend_xmin) AS xmin_age, left(query,45) AS last_query FROM pg_stat_activity WHERE state LIKE 'idle in transaction%' ORDER BY xact_start;"
  pid  |  usename   |        state        |    xact_age     | xmin_age |                 last_query                 
-------+------------+---------------------+-----------------+----------+--------------------------------------------
41182 | j.okonkwo  | idle in transaction | 4 days 02:17:51 | 11284993 | SELECT count(*) FROM orders WHERE status = 4;

Illustrative output

pg_replication_slots is empty. pg_prepared_xacts is empty. idle_in_transaction_session_timeout is 0.

pgstattuple on the largest table reports tuple_percent of 11.4 and free_percent of 4.2.

Work the evidence before reading on

  1. autovacuum_count is rising and last_autovacuum is recent. Is autovacuum failing?
  2. The vacuum reports 0 removed and 3947551 are dead but not yet removable. Which of those two numbers is the diagnosis?
  3. A hundred tables across six schemas are affected. What kind of cause has that reach?
  4. free_percent is 4.2 and tuple_percent is 11.4. Where is the rest of the file?

Root cause

One snapshot, one cluster-wide horizon

Vacuum may only remove a dead row version if no running transaction could still need to see it. It establishes that boundary from the oldest snapshot held anywhere on the cluster and refuses to remove anything newer.

That boundary is one number. It is not per table, per database or per schema, because vacuum cannot know which tables an open transaction might go on to read.

Session 41182 had held a snapshot for four days. Every dead row version created in those four days, in every table, in every database, was protected by it.

The slow queries have the same cause

An index-only scan avoids reading the heap by consulting the visibility map — one bit per page saying “every tuple here is visible to everyone”. Vacuum is what sets those bits, and it cannot set a page all-visible while it contains dead tuples it is not allowed to remove.

So the bits went stale, index-only scans degraded into heap fetches, and Heap Fetches rose. The latency regression and the disk growth are the same incident.

The space is dead rows, not free space

pgstattuple separates them, and the distinction points at the fix:

  • tuple_percent = 11.4 — live rows.
  • free_percent = 4.2 — reusable space.
  • The remaining ~84% is dead tuples that vacuum could not reclaim.

If this were ordinary bloat, free_percent would be high — the space would have been reclaimed and would be waiting for reuse. It is not. Nothing has been reclaimed at all.

Resolution

Terminate the session. pg_cancel_backend will return true and change nothing, because there is no statement running to cancel — the session is idle, holding a transaction.

SELECT pg_terminate_backend(41182);

Confirm the horizon actually moved before doing anything else:

SELECT max(age(backend_xmin)) AS oldest_snapshot FROM pg_stat_activity;

Then vacuum the largest tables explicitly and read the output:

VACUUM (VERBOSE) orders;

The line to see is a non-zero “removed” and 0 are dead but not yet removable. Anything else means something is still holding the horizon.

Revert the autovacuum changes. They were made against a misdiagnosis, they raise load on every table in the cluster, and leaving them in place distorts the next capacity discussion.

Verification

No session has been idle in transaction for more than a few minutes.

VACUUM (VERBOSE) on a previously affected table reports a non-zero removed count, 0 are dead but not yet removable, and a removable cutoff close to the current transaction id.

n_dead_tup falls on the affected tables and stops rising.

EXPLAIN ANALYZE of a regressed query shows Heap Fetches: 0 and the previous execution time.

Table sizes do not fall, and that is correct.

Prevention

Set idle_in_transaction_session_timeout. Minutes, cluster-wide. This single setting closes the class, and it logs the reason when it acts:

FATAL:  terminating connection due to idle-in-transaction timeout

Alert on transaction age, not disk usage. max(now() - xact_start) and max(age(backend_xmin)) fire in minutes. Disk fires in days.

Put “dead but not yet removable” on the dashboard. It is the line that distinguishes “autovacuum cannot keep up” from “autovacuum is not allowed to act”, and the two have opposite remedies.

Watch all three horizon holders on one panel, so that nobody diagnoses the wrong one.

Give interactive roles a short timeout. The access is legitimate; the weekend-long transaction is not, and a per-role setting costs nothing.