Created at: 2025-01-08
2. SCANNING HEAP: VACUUM is currently scanning the heap. It will prune and defragment each page if required, and possibly perform freezing activity. The heap_blks_scanned column can be used to monitor the progress of the scan.
3. VACUUMING INDEXES: VACUUM is currently vacuuming the indexes. If a table has any indexes, this will happen at least once per vacuum, after the heap has been completely scanned. It may happen multiple times per vacuum if maintenance_work_mem (or, in the case of autovacuum, autovacuum_work_mem if set) is insufficient to store the number of dead tuples found.
4. VACUUMING HEAP: VACUUM is currently vacuuming the heap. Vacuuming the heap is distinct from scanning the heap, and occurs after each instance of vacuuming indexes. If heap_blks_scanned is less than heap_blks_total (see notes below), the system will return to scanning the heap after this phase is completed; otherwise, it will begin cleaning up indexes after this phase is completed.
5. CLEANING UP INDEXES: VACUUM is currently cleaning up indexes. This occurs after the heap has been completely scanned and all vacuuming of the indexes and the heap has been completed.
6. TRUNCATING HEAP: VACUUM is currently truncating the heap so as to return empty pages at the end of the relation to the operating system. This occurs after cleaning up indexes.
7. PERFORMING FINAL CLEANUP: VACUUM is performing final cleanup. During this phase, VACUUM will vacuum the free space map, update statistics in pg_class, and report statistics to the cumulative statistics system. When this phase is completed, VACUUM will end.
Notes:
Whenever VACUUM is running, the pg_stat_progress_vacuum view will contain one row for each backend (including autovacuum worker processes) that is currently vacuuming. The tables below describe the information that will be reported and provide information about how to interpret it. Progress for VACUUM FULL commands is reported via pg_stat_progress_cluster because both VACUUM FULL and CLUSTER rewrite the table, while regular VACUUM only modifies it in place.
pg_stat_progress_vacuum View
pid integer:
PPrroocceessss IIDD ooff bbaacckkeenndd..
datid oid:
OOIIDD ooff tthhee ddaattaabbaassee ttoo wwhhiicchh tthhiiss bbaacckkeenndd iiss ccoonnnneecctteedd..
datname name:
NNaammee ooff tthhee ddaattaabbaassee ttoo wwhhiicchh tthhiiss bbaacckkeenndd iiss ccoonnnneecctteedd..
relid oid:
OOIIDD ooff tthhee ttaabbllee bbeeiinngg vvaaccuuuummeedd..
phase text:
CCuurrrreenntt pprroocceessssiinngg pphhaassee ooff vvaaccuuuumm.. SSeeee TTaabbllee 2277..4466..
heap_blks_total bigint
TToottaall nnuummbbeerr ooff hheeaapp bblloocckkss iinn tthhee ttaabbllee.. TThhiiss nnuummbbeerr iiss rreeppoorrtteedd aass ooff tthhee bbeeggiinnnniinngg ooff tthhee ssccaann;; bblloocckkss aaddddeedd llaatteerr wwiillll nnoott bbee ((aanndd nneeeedd nnoott bbee)) vviissiitteedd bbyy tthhiiss VVAACCUUUUMM..
heap_blks_scanned bigint:
NNuummbbeerr ooff hheeaapp bblloocckkss ssccaannnneedd.. BBeeccaauussee tthhee vviissiibbiilliittyy mmaapp iiss uusseedd ttoo ooppttiimmiizzee ssccaannss,, ssoommee bblloocckkss wwiillll bbee sskkiippppeedd wwiitthhoouutt iinnssppeeccttiioonn;; sskkiippppeedd bblloocckkss aarree iinncclluuddeedd iinn tthhiiss ttoottaall,, ssoo tthhaatt tthhiiss nnuummbbeerr wwiillll eevveennttuuaallllyy bbeeccoommee eeqquuaall ttoo hheeaapp__bbllkkss__ttoottaall wwhheenn tthhee vvaaccuuuumm iiss ccoommpplleettee.. TThhiiss ccoouunntteerr oonnllyy aaddvvaanncceess wwhheenn tthhee pphhaassee iiss ssccaannnniinngg hheeaapp..
heap_blks_vacuumed bigint:
NNuummbbeerr ooff hheeaapp bblloocckkss vvaaccuuuummeedd.. UUnnlleessss tthhee ttaabbllee hhaass nnoo iinnddeexxeess,, tthhiiss ccoouunntteerr oonnllyy aaddvvaanncceess wwhheenn tthhee pphhaassee iiss vvaaccuuuummiinngg hheeaapp.. BBlloocckkss tthhaatt ccoonnttaaiinn nnoo ddeeaadd ttuupplleess aarree sskkiippppeedd,, ssoo tthhee ccoouunntteerr mmaayy ssoommeettiimmeess sskkiipp ffoorrwwaarrdd iinn llaarrggee iinnccrreemmeennttss..
index_vacuum_count bigint:
NNuummbbeerr ooff ccoommpplleetteedd iinnddeexx vvaaccuuuumm ccyycclleess..
max_dead_tuple_bytes bigint:
AAmmoouunntt ooff ddeeaadd ttuuppllee ddaattaa tthhaatt wwee ccaann ssttoorree bbeeffoorree nneeeeddiinngg ttoo ppeerrffoorrmm aann iinnddeexx vvaaccuuuumm ccyyccllee,, bbaasseedd oonn mmaaiinntteennaannccee__wwoorrkk__mmeemm..
dead_tuple_bytes bigint:
AAmmoouunntt ooff ddeeaadd ttuuppllee ddaattaa ccoolllleecctteedd ssiinnccee tthhee llaasstt iinnddeexx vvaaccuuuumm ccyyccllee..
num_dead_item_ids bigint:
NNuummbbeerr ooff ddeeaadd iitteemm iiddeennttiiffiieerrss ccoolllleecctteedd ssiinnccee tthhee llaasstt iinnddeexx vvaaccuuuumm ccyyccllee..
indexes_total bigint:
TToottaall nnuummbbeerr ooff iinnddeexxeess tthhaatt wwiillll bbee vvaaccuuuummeedd oorr cclleeaanneedd uupp.. TThhiiss nnuummbbeerr iiss rreeppoorrtteedd aatt tthhee bbeeggiinnnniinngg ooff tthhee vvaaccuuuummiinngg iinnddeexxeess pphhaassee oorr tthhee cclleeaanniinngg uupp iinnddeexxeess pphhaassee..
indexes_processed bigint:
NNuummbbeerr ooff iinnddeexxeess pprroocceesssseedd.. TThhiiss ccoouunntteerr oonnllyy aaddvvaanncceess wwhheenn tthhee pphhaassee iiss vvaaccuuuummiinngg iinnddeexxeess oorr cclleeaanniinngg uupp iinnddeexxeess..
SELECT
p.pid,
now() - a.xact_start AS duration,
coalesce(wait_event_type ||'.'|| wait_event, 'f') AS waiting,
CASE
WHEN a.query ~*'^autovacuum.*to prevent wraparound' THEN 'wraparound'
WHEN a.query ~*'^vacuum' THEN 'user'
ELSE
'regular'
END AS mode,
p.datname AS database,
p.relid::regclass AS table,
p.phase,
pg_size_pretty(p.heap_blks_total * current_setting('block_size')::int) AS table_size,
pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
pg_size_pretty(p.heap_blks_scanned * current_setting('block_size')::int) AS scanned,
pg_size_pretty(p.heap_blks_vacuumed * current_setting('block_size')::int) AS vacuumed,
round(100.0 * p.heap_blks_scanned / p.heap_blks_total, 1) AS scanned_pct,
round(100.0 * p.heap_blks_vacuumed / p.heap_blks_total, 1) AS vacuumed_pct,
p.index_vacuum_count,
round(100.0 * p.num_dead_tuples / p.max_dead_tuples,1) AS dead_pct
FROM pg_stat_progress_vacuum p
JOIN pg_stat_activity a using (pid)
ORDER BY now() - a.xact_start DESC;
Or simpler:
SELECT * FROM pg_stat_progress_vacuum
WHERE oid = 'my_sweet_table'::regclass;