Monitor PostgreSQL Vacuum Progress with pg_stat_progress_vacuum

calendar May 16, 2026 · 4 min read · pg_stat_progress_vacuum vacuum autovacuum performance monitoring administration  ·
Share on: linkedin copy

Tracking Active Vacuum and Autovacuum Workers in PostgreSQL

PostgreSQL vacuum is the maintenance process that removes dead tuples left behind by updates and deletes, reclaims storage, and prevents transaction ID wraparound. On active databases, autovacuum runs continuously in the background, but knowing whether it is actually making progress — and on which tables — requires querying the pg_stat_progress_vacuum view.

Introduced in PostgreSQL 9.6, pg_stat_progress_vacuum exposes one row per active vacuum worker, including both manual VACUUM commands and autovacuum background workers. The view shows the current phase, block counts scanned and vacuumed, and dead tuple statistics. This is the primary tool for answering the operational question: is vacuum keeping up, and if not, where is it stuck?

Sample Code

 1SELECT
 2    p.pid,
 3    p.datname,
 4    p.relid::regclass                   AS table_name,
 5    p.phase,
 6    p.heap_blks_total,
 7    p.heap_blks_scanned,
 8    p.heap_blks_vacuumed,
 9    CASE p.heap_blks_total
10        WHEN 0 THEN 0
11        ELSE ROUND(
12            100.0 * p.heap_blks_vacuumed
13                  / p.heap_blks_total, 1
14        )
15    END                                 AS pct_vacuumed,
16    p.index_vacuum_count,
17    p.num_dead_tuples,
18    p.max_dead_tuples,
19    NOW() - a.xact_start               AS elapsed
20FROM pg_stat_progress_vacuum p
21JOIN pg_stat_activity a ON a.pid = p.pid
22ORDER BY elapsed DESC NULLS LAST;

Notes: Requires PostgreSQL 9.6 or later. Superuser or pg_monitor role required to see all workers; non-superusers see only their own sessions. Returns no rows when no vacuum is currently running.

Code Breakdown

  • pg_stat_progress_vacuum — one row per active VACUUM worker; reset when the worker finishes
  • p.relid::regclass — casts the relation OID to a human-readable schema.table name
  • p.phase — current vacuum phase: initializing, scanning heap, vacuuming indexes, vacuuming heap, cleaning up indexes, truncating heap, or performing final cleanup
  • p.heap_blks_total / scanned / vacuumed — 8 KB blocks: total in the table, how many have been scanned, how many have been vacuumed
  • pct_vacuumed — derived completion percentage based on blocks vacuumed vs. total
  • p.index_vacuum_count — number of completed index vacuum passes; large tables with many indexes may require multiple passes
  • p.num_dead_tuples — dead tuples accumulated in the current pass
  • p.max_dead_tuples — ceiling before PostgreSQL must flush dead tuples and start an index pass (controlled by maintenance_work_mem)
  • NOW() - a.xact_start — time the vacuum worker has been running

Key Points

  • If pct_vacuumed is not advancing over repeated samples, the vacuum worker may be blocked by an idle-in-transaction session holding an old snapshot
  • num_dead_tuples approaching max_dead_tuples repeatedly means maintenance_work_mem is too small; the worker must make extra index passes
  • A table stuck in vacuuming indexes for a long time usually indicates a large or bloated index that needs attention
  • index_vacuum_count > 1 on every run signals that maintenance_work_mem should be increased so the worker can process all dead tuples in a single heap pass
  • No rows in the view during known bloat growth means autovacuum is not keeping up — check autovacuum_vacuum_scale_factor and worker count settings

Insights and Explanations

To find which sessions are blocking vacuum from advancing, check for idle-in-transaction connections holding old transaction snapshots:

 1SELECT
 2    pid,
 3    datname,
 4    usename,
 5    state,
 6    NOW() - xact_start      AS xact_age,
 7    LEFT(query, 80)         AS last_query
 8FROM pg_stat_activity
 9WHERE state = 'idle in transaction'
10ORDER BY xact_age DESC NULLS LAST;

To check tables that autovacuum is skipping or deferring, query pg_stat_user_tables for last vacuum timestamps and dead tuple counts:

 1SELECT
 2    schemaname,
 3    relname,
 4    n_dead_tup,
 5    last_autovacuum,
 6    last_vacuum
 7FROM pg_stat_user_tables
 8WHERE n_dead_tup > 1000
 9ORDER BY n_dead_tup DESC
10LIMIT 20;

If a specific table is accumulating dead tuples faster than autovacuum can clear them, run a manual VACUUM VERBOSE <table> during a low-traffic window to force a full pass and observe the output.

Additional Considerations

  • Permissions: superuser or pg_monitor role (PostgreSQL 10+) required to see all vacuum workers
  • Version compatibility: pg_stat_progress_vacuum available from PostgreSQL 9.6; the max_dead_tuples column was added in PostgreSQL 13
  • maintenance_work_mem: controls max_dead_tuples; increasing this reduces index passes and speeds large vacuums; set per session with SET maintenance_work_mem = '1GB' before a manual VACUUM
  • Autovacuum vs. manual: both appear in this view; distinguish by querying a.query from pg_stat_activity — autovacuum workers show autovacuum: VACUUM <table>

References

Posts in this series