Skip to main content
RunBook Academy

← All break/fix scenarios in PostgreSQL

intermediatepg-bloat~40 min

A VACUUM FULL was run to reclaim disk space during business hours and every query on the table stopped for eleven minutes

Reported symptoms

  • ●Disk usage on the primary reached 88 percent and the orders table accounted for most of the growth
  • ●An engineer ran VACUUM FULL orders at 11:20 to reclaim the space
  • ●Within seconds every query touching orders stopped returning, including simple primary-key lookups
  • ●The application connection pool filled and new requests began timing out across services that do not use the orders table
  • ●The VACUUM FULL completed after eleven minutes and everything resumed
  • ●Disk usage did fall, from 88 percent to 61 percent, so the operation is regarded by some as a success
  • ●A plain VACUUM had been run the previous week and freed no disk space at all, which is why VACUUM FULL was chosen

Evidence

  • · pgstattuple on the table before the operation showed table_len 77766656, tuple_percent 14.48, dead_tuple_percent 14.49 and free_percent 62.06
  • · After a plain VACUUM the same table showed dead_tuple_percent 0 and free_percent 82.86, with table_len unchanged at 77766656
  • · pg_locks during the incident showed one AccessExclusiveLock not granted, behind an idle in transaction session holding AccessShareLock, with further readers queued behind the AccessExclusiveLock
  • · The queued readers were all waiting with wait_event_type Lock and wait_event relation
  • · The VACUUM FULL itself was blocked for most of the eleven minutes and did its actual work in 159 milliseconds on a comparable table
  • · pgstatindex on the primary key showed avg_leaf_density 36.17 and leaf_fragmentation 39.97 before the rewrite and 89.95 and 0 after
  • · The table has a steady write workload and returned to a similar free_percent within three weeks
  • · REINDEX INDEX CONCURRENTLY on a comparable index completed in 23 seconds against an active reader without blocking it
Diagnosis and resolutionclick to reveal

Root cause

There are two separate faults here and it is worth keeping them apart, because the team was right about the first one. The bloat was real. `pgstattuple` reported 62 percent free space in a 74 MB heap holding 11 MB of live tuples. Sequential scans were reading four pages for every one that mattered. Something needed to be done. The remedy was the fault. `VACUUM FULL` rewrites the table into a new file and takes `ACCESS EXCLUSIVE` on it for the whole operation. That lock conflicts with everything, including `AccessShareLock` — the lock every plain `SELECT` takes. So the moment `VACUUM FULL` requested its lock, every subsequent reader queued behind it. The eleven minutes were mostly not rewriting. `VACUUM FULL` could not start, because an `idle in transaction` session was already holding `AccessShareLock` on the table. `VACUUM FULL` waited for that session, and every query that arrived afterwards waited for `VACUUM FULL`. One forgotten session, one heavy lock request, and a queue that grows for as long as the session stays open. The actual rewrite, measured on a comparable table, took 159 milliseconds. The blast radius reached services that do not use `orders` because the connection pool filled with blocked sessions. A lock on one table became a connection shortage for everything sharing the pool. The reason `VACUUM FULL` was chosen is also worth naming, because it is a reasonable inference from a real observation. The plain `VACUUM` the previous week did free the space — `free_percent` went from 62.06 to 82.86 — and it did not shrink the file, which is the only thing anybody could see. Plain vacuum makes space reusable *inside* the file. That is the correct behaviour and it is exactly what a table with a steady write workload needs, because the space will be reused. It is invisible to `df`.

Remediation

Stop the bleeding first. If a `VACUUM FULL` is running and a queue has formed, cancel it — the queue drains immediately: ```sql SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE query LIKE 'VACUUM FULL%'; ``` Then look at what it was waiting for, because that is the thing that turned a 159-millisecond operation into an eleven-minute outage: ```sql SELECT a.pid, a.state, a.wait_event_type, a.wait_event, now() - a.state_change AS in_state_for, left(a.query, 60) AS query FROM pg_stat_activity a WHERE a.pid IN (SELECT pid FROM pg_locks WHERE relation = 'orders'::regclass) ORDER BY a.state_change; ``` Measure the bloat before deciding whether anything further is needed. Configuration and `df` are not measurements of bloat; `pgstattuple` is: ```sql CREATE EXTENSION IF NOT EXISTS pgstattuple; SELECT * FROM pgstattuple('orders'); SELECT * FROM pgstatindex('orders_pkey'); ``` `pgstattuple` scans the whole table, which is expensive on a large one — use `pgstattuple_approx` for a sampled estimate when the table is big. Now choose the instrument by what you actually need: - **The space will be reused by ongoing writes.** Do nothing beyond a plain `VACUUM`, and correct the autovacuum settings so the table does not reach 62 percent free again. This is the right answer far more often than it is chosen. - **The index is the problem.** `REINDEX INDEX CONCURRENTLY` rebuilds without blocking readers or writers. On a comparable index it took 23 seconds against an active reader and never blocked it. - **The disk genuinely must be returned to the filesystem**, and the table will not regrow. Then a rewrite is warranted — and `pg_repack` performs one without a long exclusive lock, which is what `VACUUM FULL` cannot do. If `VACUUM FULL` is genuinely the right tool — a table that has permanently shrunk, in a maintenance window, on a cluster where a full stop on that table is acceptable — then run it with a `lock_timeout` so it fails instead of forming a queue: ```sql SET lock_timeout = '5s'; VACUUM FULL orders; ``` Failing fast and retrying is nearly always better than blocking, because a blocked `ACCESS EXCLUSIVE` request blocks everybody behind it.

Verification

No session is waiting on a lock against the table: ```sql SELECT count(*) FROM pg_stat_activity WHERE wait_event_type = 'Lock' AND state = 'active'; ``` Application latency and connection count return to normal, including for the services that do not use this table. `pgstattuple` reports the state you intended. After a rewrite, expect a high `tuple_percent` and a low `free_percent` — measured on a comparable table, 85.65 and 0.55 respectively, against 14.48 and 62.06 before. Index density is restored, if that was the goal: ```sql SELECT avg_leaf_density, leaf_fragmentation, index_size FROM pgstatindex('orders_pkey'); ``` A rebuilt index measured 89.95 percent density and 0 fragmentation, against 36.17 and 39.97 before. Three weeks later, measure again. The comparable table returned to a similar `free_percent` within three weeks under a steady write workload. If yours does the same, the rewrite was not the fix — the autovacuum settings are — and repeating the rewrite quarterly is a treadmill, not a remedy.

Prevention

**Never run `VACUUM FULL` on a live table during business hours.** It takes `ACCESS EXCLUSIVE` and every reader queues behind it. This belongs in the runbook as a prohibition, in those words, because it is an obvious-looking answer with a non-obvious cost. **Understand what plain `VACUUM` does**, so its result does not look like a failure: it makes space reusable inside the file and does not shrink the file. On a table with steady writes that is the desirable outcome, and `df` cannot show it. **Measure bloat with `pgstattuple`, not with `df`.** And note that `dead_tuple_percent` alone understates it: on the measured table, 1,000,000 updates produced only 200,057 counted dead tuples but 62 percent free space, because opportunistic pruning had already converted most dead tuples into free space. `free_percent` is where the space went. **Prefer `REINDEX ... CONCURRENTLY` for index bloat**, which is the majority of visible bloat on many workloads. It does not block — though it does *wait* for older transactions to finish, so a long-running transaction will delay it. **Prefer `pg_repack` for heap rewrites** where the space must genuinely be returned. **Set `lock_timeout` before any statement that needs a heavy lock.** A failed statement is recoverable; a lock queue during business hours is an outage. **Alert on `idle in transaction` sessions.** One of them turned a 159-millisecond operation into eleven minutes, and it will do the same to your next migration. **Fix the autovacuum settings rather than repeating the rewrite.** A table that reaches 62 percent free space is not being vacuumed often enough for its churn rate.

Reported symptoms

Disk usage on the primary reached 88 percent, and the orders table accounted for most of the growth.

An engineer ran VACUUM FULL orders at 11:20. Within seconds every query touching orders stopped returning — including simple primary-key lookups.

The connection pool filled, and requests began timing out across services that do not use the orders table.

The operation completed after eleven minutes and everything resumed. Disk usage fell from 88 percent to 61 percent, so some regard it as a success.

A plain VACUUM had been run the previous week and freed no disk space at all, which is why VACUUM FULL was chosen.

Evidence provided

Read-only / Safethe bloat was real: 62 percent free space in a 74 MB heap
$ psql -c "SELECT * FROM pgstattuple('orders');"
 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent 
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
77766656 |      200000 |  11261952 |         14.48 |           200057 |       11265091 |              14.49 |   48262032 |        62.06
Read-only / Safewhat the plain VACUUM actually did — and why nobody could see it
$ psql -c "VACUUM orders;" -c "SELECT * FROM pgstattuple('orders');"
 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent 
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
77766656 |      200000 |  11261952 |         14.48 |                0 |              0 |                  0 |   64438200 |        82.86

And the eleven minutes:

Read-only / Safeone idle session, one heavy lock request, and a queue
$ psql -c "SELECT pid, state, wait_event_type, wait_event, query FROM pg_stat_activity ..."
  pid  |        state        | wait_event_type | wait_event |            query             
-------+---------------------+-----------------+------------+------------------------------
11253 | idle in transaction | Client          | ClientRead | SELECT count(*) FROM orders;
11269 | active              | Lock            | relation   | VACUUM FULL orders;
11287 | active              | Lock            | relation   | SELECT count(*) FROM orders;
11293 | active              | Lock            | relation   | SELECT count(*) FROM orders;

pid  |        mode         | granted 
-------+---------------------+---------
11253 | AccessShareLock     | t
11269 | AccessExclusiveLock | f
11287 | AccessShareLock     | f
11293 | AccessShareLock     | f

The rewrite itself, measured on a comparable table, took 159 milliseconds.

Work the evidence before reading on

  1. The plain VACUUM moved free_percent from 62.06 to 82.86 and left table_len unchanged. Was it a failure?
  2. VACUUM FULL ran for eleven minutes. How much of that was rewriting?
  3. Why did services that never touch orders time out?
  4. What would you have run instead, and how would you have known it was the right choice?

Root cause

Two faults, and the team was right about the first

The bloat was real: 62 percent free space in a heap holding 11 MB of live tuples. Sequential scans were reading four pages for every useful one. Something needed doing.

The remedy was the fault

VACUUM FULL rewrites the table into a new file and holds ACCESS EXCLUSIVE for the whole operation. That conflicts with everything, including the AccessShareLock that every plain SELECT takes.

Unrelated services timed out because the connection pool filled with blocked sessions. A lock on one table became a connection shortage for everything sharing the pool.

Why VACUUM FULL looked like the answer

The plain VACUUM the previous week did free the space — 62.06 to 82.86 percent — and did not shrink the file, which is the only thing anybody could see.

Resolution

Stop the bleeding. Cancelling drains the queue immediately:

SELECT pg_cancel_backend(pid) FROM pg_stat_activity
WHERE query LIKE 'VACUUM FULL%';

Then find what it was waiting for — that is what turned 159 milliseconds into eleven minutes:

SELECT a.pid, a.state, a.wait_event_type, a.wait_event,
       now() - a.state_change AS in_state_for,
       left(a.query, 60) AS query
FROM pg_stat_activity a
WHERE a.pid IN (SELECT pid FROM pg_locks WHERE relation = 'orders'::regclass)
ORDER BY a.state_change;

Measure before deciding. df is not a measurement of bloat:

CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('orders');
SELECT * FROM pgstatindex('orders_pkey');

pgstattuple scans the whole table; use pgstattuple_approx on a large one.

Then choose the instrument by what you need:

  • The space will be reused by ongoing writes. A plain VACUUM, plus autovacuum settings that stop the table reaching 62 percent free again. This is the right answer far more often than it is chosen.
  • The index is the problem. REINDEX INDEX CONCURRENTLY — measured at 23 seconds against an active reader, without blocking it.
  • The disk must genuinely be returned and the table will not regrow. A rewrite is warranted, and pg_repack performs one without the long exclusive lock.

If VACUUM FULL really is right — a permanently shrunken table, in a window, where a full stop on that table is acceptable — bound it:

SET lock_timeout = '5s';
VACUUM FULL orders;

Failing fast beats blocking, because a blocked ACCESS EXCLUSIVE request blocks everybody behind it.

Verification

Nothing is waiting on a lock:

SELECT count(*) FROM pg_stat_activity
WHERE wait_event_type = 'Lock' AND state = 'active';

Latency and connection count return to normal, including for the services that do not use this table.

pgstattuple reports the state you intended. After a rewrite on the measured table: tuple_percent 85.65 and free_percent 0.55, against 14.48 and 62.06 before.

Index density is restored, if that was the goal — 89.95 percent density and 0 fragmentation after a rebuild, against 36.17 and 39.97 before:

SELECT avg_leaf_density, leaf_fragmentation, index_size FROM pgstatindex('orders_pkey');

Prevention

Never run VACUUM FULL on a live table during business hours. Put that in the runbook in those words; it is an obvious-looking answer with a non-obvious cost.

Understand what plain VACUUM does, so its result does not look like a failure.

Measure bloat with pgstattuple, not df, and read free_percent rather than dead_tuple_percent.

Prefer REINDEX ... CONCURRENTLY for index bloat — the majority of visible bloat on many workloads. It does not block, though it does wait for older transactions, so a long-running transaction will delay it.

Prefer pg_repack for heap rewrites where space must genuinely be returned.

Set lock_timeout before any statement needing a heavy lock.

Alert on idle in transaction. One of those turned 159 milliseconds into eleven minutes, and it will do the same to your next migration.

Fix the autovacuum settings rather than repeating the rewrite. A table reaching 62 percent free space is not being vacuumed often enough for its churn rate.