Scenario
A manual or autovacuum VACUUM is running on a large table — use pg_stat_progress_vacuum to estimate progress and watch for stalls. Diagnose it SELECT p.pid, p.datname, n.nspname || '.' || c.relname AS table_name, p.phase, p.heap_blks_total, p.heap_blks_scanned,…
Investigation Path
A manual or autovacuum VACUUM is running on a large table — use pg_stat_progress_vacuum to estimate progress and watch for stalls.
Diagnose it
SELECT p.pid,
p.datname,
n.nspname || '.' || c.relname AS table_name,
p.phase,
p.heap_blks_total,
p.heap_blks_scanned,
p.heap_blks_vacuumed,
round(
100.0 * p.heap_blks_vacuumed
/ NULLIF(p.heap_blks_total, 0),
1) AS pct_done,
p.index_vacuum_count,
p.indexes_total, -- PostgreSQL 12+
p.indexes_processed, -- PostgreSQL 12+
p.dead_tuple_bytes, -- PostgreSQL 16+
p.num_dead_item_ids, -- PostgreSQL 16+
p.mode, -- PostgreSQL 17+
now() - a.query_start AS running_for
FROM pg_stat_progress_vacuum p
JOIN pg_stat_activity a ON a.pid = p.pid
JOIN pg_class c ON c.oid = p.relid
JOIN pg_namespace n ON n.oid = c.relnamespace;The phase column cycles through: initializing → scanning heap →
vacuuming indexes → vacuuming heap → cleaning up indexes → truncating heap → performing
final cleanup. A VACUUM stuck on vacuuming indexes for a long time usually
means many indexes need cleanup — check indexes_total vs indexes_processed.
Why it happens
pg_stat_progress_vacuum was introduced in PostgreSQL 9.6. Column availability
varies: indexes_total/indexes_processed since PG12;
dead_tuple_bytes/num_dead_item_ids since PG16; mode
since PG17. Always check column availability against your PostgreSQL version.
How to fix it
If VACUUM is running unusually slowly, it may be throttled by vacuum_cost_delay.
For a one-off manual VACUUM where speed matters:
SET vacuum_cost_delay = 0; -- disable throttle for this session only
VACUUM (VERBOSE, ANALYZE) schema.tablename;Prevent it next time
For tables that take hours to vacuum, tune per-table autovacuum settings so routine vacuum
runs happen more frequently on smaller dead-tuple budgets — each run is faster and less
disruptive than one massive cleanup.
Related & next steps
Career Impact
This scenario builds production judgment and operational confidence under pressure.