In PostgreSQL, a VACUUM FULL has run for an hour; how do you use pg_stat_progress_cluster to tell how far along it is, or whether it has started?
answer
- one view for two commands
- phase names the stage
- blocks scanned over blocks total
- no row until the lock is held
- a Lock wait in pg_stat_activity
basics
~20 spg_stat_progress_cluster has a row per backend running CLUSTER or VACUUM FULL: phase names the stage, heap_blks_scanned over heap_blks_total gives scan progress, and index_rebuild_count counts rebuilt indexes. No row on an active session means it still waits for its lock.
solid answer
~40 s`VACUUM FULL` reports in `pg_stat_progress_cluster`, not `pg_stat_progress_vacuum`, one row per backend with `command = 'VACUUM FULL'`. Its phases run `initializing`, `seq scanning heap`, `swapping relation files`, `rebuilding index` and `performing final cleanup`; it never sorts. During the scan, `heap_blks_scanned / heap_blks_total` is the fraction of the old table read, and `heap_tuples_written` trails `heap_tuples_scanned` by the dead tuples being dropped. During the index stage, compare `index_rebuild_count` with the number of indexes on the table. If the session is `active` in `pg_stat_activity` with `wait_event_type = 'Lock'` but has no progress row, it has not started at all: it is waiting for `ACCESS EXCLUSIVE`, and every later query on that table is queued behind it, which is usually the bigger problem.
code
sql · 17 lines-- Progress of running CLUSTER and VACUUM FULL commands
SELECT p.pid, p.relid::regclass AS table_name, p.command, p.phase,
round(100.0 * p.heap_blks_scanned / nullif(p.heap_blks_total, 0), 1)
AS pct_heap_scanned,
p.index_rebuild_count,
(SELECT count(*) FROM pg_index AS i WHERE i.indrelid = p.relid)
AS index_total
FROM pg_stat_progress_cluster AS p;
-- A VACUUM FULL with no progress row is still waiting for its lock
SELECT a.pid, a.state, a.wait_event_type, a.wait_event,
now() - a.query_start AS waiting_for
FROM pg_stat_activity AS a
WHERE a.query ILIKE 'vacuum%full%'
AND a.pid <> pg_backend_pid()
AND NOT EXISTS (SELECT 1 FROM pg_stat_progress_cluster AS p
WHERE p.pid = a.pid);go deeper
Recall that VACUUM FULL and CLUSTER report progress in pg_stat_progress_cluster, and that the phase column names the stage the command has reached.
Explain the VACUUM FULL phases and how to turn heap_blks_scanned, heap_blks_total and index_rebuild_count into a progress estimate.
Recognise the dangerous state: no progress row plus a Lock wait in pg_stat_activity means the rewrite is queued and is blocking every later query on the table.
Make rewrites observable by policy: require a lock_timeout, progress checks and a cancel rule in the runbook before anyone starts a VACUUM FULL in production.
## One view for two commands `VACUUM FULL` and `CLUSTER` share their implementation, so they share a progress view. Plain `VACUUM` reports in `pg_stat_progress_vacuum`; a backend running `VACUUM FULL` reports in **`pg_stat_progress_cluster`** instead, with one row per backend. | Column | What it tells you | |---|---| | `pid`, `datname`, `relid` | Which backend and which table (`relid::regclass` gives the name) | | `command` | `CLUSTER` or `VACUUM FULL` | | `phase` | The current stage | | `cluster_index_relid` | The index used for an index scan, otherwise zero | | `heap_tuples_scanned`, `heap_tuples_written` | Tuples read and tuples copied so far | | `heap_blks_total`, `heap_blks_scanned` | Table size in blocks, as of the start of the sequential scan, and blocks read so far | | `index_rebuild_count` | Indexes rebuilt so far | ## The phases | Phase | `VACUUM FULL` | `CLUSTER` | |---|---|---| | `initializing` | Yes, briefly | Yes, briefly | | `seq scanning heap` | Yes: reads and writes in one pass | With the sort method | | `index scanning heap` | Never | With the index-scan method | | `sorting tuples` | Never | With the sort method | | `writing new heap` | Never | With the sort method | | `swapping relation files` | Yes | Yes | | `rebuilding index` | Yes | Yes | | `performing final cleanup` | Yes | Yes | `VACUUM FULL` never sorts: it copies tuples as it scans, so `heap_tuples_scanned` and `heap_tuples_written` advance together during `seq scanning heap`, and the gap between them is the dead tuples being dropped. ## Estimating how far along it is 1. During **`seq scanning heap`**, `heap_blks_scanned / heap_blks_total` is the fraction of the old table read. This is usually the longest stage. 2. During **`swapping relation files`** the new heap is complete; this stage is short. 3. During **`rebuilding index`**, compare `index_rebuild_count` with the number of indexes on the table. The view has no finer counter, so one large index can sit at the same count for a long time. 4. **`performing final cleanup`** means it is about to finish. For a `CLUSTER` using an index scan the block counters do not advance; compare `heap_tuples_scanned` with `pg_class.reltuples` for a rough fraction instead. ## No row at all: still waiting for the lock The progress row appears only once the command holds its **`ACCESS EXCLUSIVE`** lock. If `pg_stat_activity` shows the session as `active` with `wait_event_type = 'Lock'` and `wait_event = 'relation'`, and there is no row for it in `pg_stat_progress_cluster`, the rewrite has not started: it is queued behind sessions that hold a lock on the table. That is the most dangerous state of all. Lock requests are granted in queue order, so every new query on the table, even a plain `SELECT`, now waits behind the waiting `VACUUM FULL`; an hour of waiting can mean an hour of blocked traffic on the payroll ledger. Finding exactly which sessions block it is a lock-monitoring job; the decision here is whether to cancel the `VACUUM FULL` with `pg_cancel_backend()` or to end the blockers. ## Practical notes - `VACUUM FULL` with no table name processes tables one after another; `relid` changes as it moves on, and the counters start again for each table. - `heap_blks_total` is read when the sequential scan starts, so it describes the old, bloated table, which is the right denominator. - Cancelling in any phase rolls back the whole rewrite: the new files are discarded and the old table stays as it was, so a late cancel throws away all the work done. - Poll the view every few minutes rather than guessing from elapsed time: a rising `heap_blks_scanned` shows progress, and a stuck `index_rebuild_count` usually just means a big index is being built. - Permissions shape what you see. Any role sees the `pid` and database of every row, but `relid` and all the counters are null for sessions of other roles unless you are a superuser, have the privileges of `pg_read_all_stats`, or are a member of the session's role. A monitoring login that sees only nulls lacks that role rather than finding the view broken. - `VACUUM (FULL, VERBOSE)` prints its summary of removable and nonremovable row versions only when each table is done; while it runs, the view is the only live signal.
- If a PostgreSQL VACUUM FULL is cancelled in the rebuilding index phase, what is left of its work?Nothing. The rewrite is one transaction: cancelling it discards the new heap and the indexes already rebuilt, and the old, bloated table stays exactly as it was. All the time spent is lost, so late in the run it is usually better to let it finish unless it is blocking something critical.
- Why does a PostgreSQL VACUUM FULL that is still waiting for its lock block queries that arrive after it, even read-only ones?Lock requests on a table are granted in queue order. A waiting `ACCESS EXCLUSIVE` request conflicts with every mode, so later `ACCESS SHARE` requests from `SELECT` queue behind it rather than overtaking it. Setting a `lock_timeout` in the session before starting the `VACUUM FULL` bounds that wait, so it fails instead of stalling the table.
- VACUUM FULL progress shows up in pg_stat_progress_vacuum like any vacuum
- No row in pg_stat_progress_cluster means the VACUUM FULL is almost done
- VACUUM FULL sorts the table, so the sorting tuples phase is the one to watch
- A waiting VACUUM FULL is harmless because it has not locked anything yet
- Cancelling VACUUM FULL late keeps the part already rewritten