skip to content

In PostgreSQL, how do you read pg_stat_progress_vacuum to tell where a long-running vacuum is spending its time?

level: seniorimportance: nice to knowfreq 30%

answer

  1. one row per vacuuming backend
  2. phase names the current pass
  3. index_vacuum_count above one
  4. join pg_stat_activity on pid
  5. delay_time needs a setting

basics

~10 s

Join pg_stat_progress_vacuum to pg_stat_activity on pid, then read phase, heap_blks_scanned against heap_blks_total, index_vacuum_count (above 1 means dead-tuple memory filled) and, in PostgreSQL 18, delay_time for time spent throttled.

solid answer

~40 s

`pg_stat_progress_vacuum` has one row per backend running `VACUUM`, autovacuum workers included; `relid::regclass` names the table, and a join to `pg_stat_activity` on `pid` adds the query text and wait event. `phase` says which pass it is in: `scanning heap` (compare `heap_blks_scanned` with `heap_blks_total`), `vacuuming indexes` (`indexes_processed` of `indexes_total`), `vacuuming heap`, `cleaning up indexes`, `truncating heap`. An `index_vacuum_count` above 1, or `dead_tuple_bytes` close to `max_dead_tuple_bytes`, means the dead-tuple store filled and every index is being scanned again — a memory sizing problem. In PostgreSQL 18, `delay_time` (with `track_cost_delay_timing` on) gives milliseconds slept by cost throttling; if it is a large share of elapsed time, the throttle is the bottleneck. `VACUUM FULL` reports in `pg_stat_progress_cluster` instead.

go deeper

for a junior

Recall that PostgreSQL has a view, pg_stat_progress_vacuum, showing each running vacuum and its current phase, including autovacuum workers.

for a middle

Explain the phases in order and the key columns, and how to join the view to pg_stat_activity for the table name, wait event and runtime.

for a senior

Read it as a diagnosis: repeated index cycles mean memory, VacuumDelay or delay_time means throttling, a long truncating heap phase means a lock wait, and know the 17 and 18 column changes.

for a principal

Decide what your monitoring keeps from a view that forgets each run when it ends, and which alerts belong on run duration versus on dead-tuple trends.

## What the view is `pg_stat_progress_vacuum` is one of PostgreSQL's **progress-reporting views**. While any backend runs a plain `VACUUM`, including **autovacuum workers**, the view holds one row for it. When the vacuum ends, the row disappears, so the view is for watching a run in flight; finished runs are read from the autovacuum log and the per-table statistics views. `VACUUM FULL` is not reported here, because it rewrites the table like `CLUSTER`; it reports in `pg_stat_progress_cluster`. The view carries no table name or query text, so join it: ```sql SELECT p.pid, p.relid::regclass AS table_name, p.phase, round(100.0 * p.heap_blks_scanned / nullif(p.heap_blks_total, 0), 1) AS pct_scanned, p.index_vacuum_count, p.indexes_processed, p.indexes_total, p.delay_time, a.wait_event_type, a.wait_event, now() - a.xact_start AS running_for FROM pg_stat_progress_vacuum AS p JOIN pg_stat_activity AS a USING (pid); ``` ## The columns that matter | Column | Meaning | What to look for | |---|---|---| | `phase` | current pass | which pass is slow | | `heap_blks_total` | heap blocks when the scan began | the denominator | | `heap_blks_scanned` | blocks scanned, including all-visible blocks skipped | progress of the first pass | | `heap_blks_vacuumed` | blocks cleaned in the heap pass | jumps over pages with nothing dead | | `index_vacuum_count` | completed index vacuum cycles | above 1 means memory ran out | | `max_dead_tuple_bytes` / `dead_tuple_bytes` | dead-tuple store capacity and fill | how close the next forced index cycle is | | `num_dead_item_ids` | dead item identifiers since the last index cycle | backlog size | | `indexes_total` / `indexes_processed` | indexes in the current index phase | which index pass you are in | | `delay_time` | milliseconds slept by cost-based delay | share of time spent throttled | ## The phases in order 1. **`initializing`** — preparing to scan; normally brief. 2. **`scanning heap`** — reading the table and collecting dead tuples. `heap_blks_scanned` rises toward `heap_blks_total`; pages the visibility map marks all-visible are skipped yet counted, so the counter can jump. 3. **`vacuuming indexes`** — removing index entries for the collected dead tuples, one index after another. 4. **`vacuuming heap`** — marking those dead line pointers unused. If the scan was cut short because memory filled, vacuum goes back to `scanning heap` and the cycle repeats. 5. **`cleaning up indexes`** — final index maintenance once the whole heap is scanned. 6. **`truncating heap`** — cutting empty pages off the end of the file, which needs a brief `ACCESS EXCLUSIVE` lock. 7. **`performing final cleanup`** — updating the free space map and the `pg_class` statistics. ## Three readings and what they mean - **Many index cycles.** `index_vacuum_count` of 3 or 4 on one run means every index was scanned that many times, which often costs more than the heap work. Give the vacuum more memory for dead tuples (`autovacuum_work_mem` or `maintenance_work_mem`; sizing belongs to the memory-tuning topic) or vacuum the table more often so less piles up. Since PostgreSQL 17 vacuum stores dead tuples compactly and is no longer silently capped at 1 GB of that memory. - **Slow scan with sleeping.** A worker stuck in `scanning heap` whose `wait_event` is often `VacuumDelay`, or whose `delay_time` is a large share of `running_for`, is throttled rather than I/O-bound; raise its cost limit. - **Stuck at the end.** A long time in `truncating heap` with `wait_event` `VacuumTruncate` means vacuum is waiting for the lock to cut tail pages; the table's `vacuum_truncate` storage parameter can turn that step off. ## Version notes - **PostgreSQL 17** renamed `max_dead_tuples` to `max_dead_tuple_bytes` and `num_dead_tuples` to `num_dead_item_ids`, added `dead_tuple_bytes`, and added `indexes_total` and `indexes_processed`. Monitoring queries written for 16 break on these names. - **PostgreSQL 18** added `delay_time`. It stays at `0` unless `track_cost_delay_timing` is on; that setting is off by default because timing calls can be expensive on some platforms. For a manual `VACUUM (PARALLEL n)`, it includes the time parallel workers slept. ## Reading the joined row The `pg_stat_activity` side of the join adds context the progress view lacks: - `backend_type` is `autovacuum worker` for autovacuum runs and `client backend` for a manual `VACUUM`. - An autovacuum's `query` reads `autovacuum: VACUUM public.user_sessions`, with `ANALYZE` added when it does both, and ends in `(to prevent wraparound)` for an anti-wraparound run, which is not cancelled by conflicting lock requests. - `wait_event_type` and `wait_event` show what the backend is waiting on right now, which a single snapshot of the counters cannot tell you. Sample the query every minute or so: the rate at which `heap_blks_scanned` rises, not one value, tells you when the run will finish. ## Where this sits The progress view answers 'what is this run doing now'. Whether a table needs vacuuming, how often it got it, and how many dead tuples remain afterwards come from the cumulative statistics views, which are the monitoring topic.

  • Why does heap_blks_scanned in pg_stat_progress_vacuum sometimes jump forward in big steps?
    Pages the visibility map marks all-visible are skipped without being read, but they are still added to `heap_blks_scanned` so the counter ends equal to `heap_blks_total`. A mostly static table therefore looks fast in places and slow where pages actually changed.
  • index_vacuum_count reaches 4 on a large table in PostgreSQL 18; what do you change?
    Each extra cycle is a full pass over every index because the dead-tuple store filled. Give autovacuum more memory through `autovacuum_work_mem` (or `maintenance_work_mem` when it is -1), or lower the table's trigger so each run has fewer dead tuples to collect; since 17 the 1 GB cap no longer applies.

  • pg_stat_progress_vacuum also shows the progress of VACUUM FULL.
  • A delay_time of zero proves the vacuum was never throttled.
  • index_vacuum_count above 1 just means the table has several indexes.
  • heap_blks_vacuumed should equal heap_blks_total when a vacuum finishes.
  • Autovacuum workers do not appear in the progress view, only manual VACUUM.

Assumes PostgreSQL 18: delay_time is new in 18, and 17 renamed max_dead_tuples and num_dead_tuples to max_dead_tuple_bytes and num_dead_item_ids.