DBAtlas · Database operations atlas

Monitor the progress of running autovacuum/VACUUM operations

PostgreSQLMaintenance

Command

SELECT p.pid,
       a.backend_type,
       p.datname,
       p.relid::regclass AS table_name,
       p.phase,
       pg_size_pretty(p.heap_blks_total * current_setting('block_size')::bigint) AS table_size,
       round(100.0 * p.heap_blks_scanned / NULLIF(p.heap_blks_total, 0), 1) AS scanned_pct,
       round(100.0 * p.heap_blks_vacuumed / NULLIF(p.heap_blks_total, 0), 1) AS vacuumed_pct,
       p.index_vacuum_count,
       now() - a.xact_start AS running_for,
       a.wait_event_type || ':' || a.wait_event AS wait,
       left(a.query, 80) AS query
FROM pg_stat_progress_vacuum p
JOIN pg_stat_activity a USING (pid)
ORDER BY running_for DESC;

-- Busy autovacuum workers / upper limit
SELECT count(*) FILTER (WHERE backend_type = 'autovacuum worker') AS av_workers_busy,
       current_setting('autovacuum_max_workers') AS av_max_workers
FROM pg_stat_activity;

What it does

Shows, for every running VACUUM (autovacuum worker or manual), the table, phase, heap percentage scanned/vacuumed, number of index vacuum passes and wait event; plus the number of busy autovacuum workers.

When to use it

To estimate when a VACUUM on a large table will finish, whether all autovacuum workers are busy (tables waiting in the queue), and whether vacuum is waiting because of throttling.

Caution

index_vacuum_count > 1 means the dead tuple list did not fit in memory and indexes were scanned multiple times; increase maintenance_work_mem/autovacuum_work_mem (before PG17 at most 1GB is used for this). wait = Timeout:VacuumDelay is cost-based throttling (autovacuum_vacuum_cost_delay/cost_limit). Do not terminate an autovacuum whose query text says 'to prevent wraparound'; it restarts immediately. VACUUM FULL and CLUSTER appear in pg_stat_progress_cluster, not in this view.

Open in DBAtlas →

Related