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;