[Evergreen-general] Useful Postgres Query for Vacuum Progress

[Jason Boyer]

Hi, I was looking at some postgres vacuum-related stuff yesterday and learned about pg_stat_progress_vacuum. This view can give you more detail about what’s going on with a vacuum you start yourself or any auto vacuums that are running.

Since it’s largely just ids and counts, this query makes everything easier to read (note the joins on oid; those columns are normally hidden but I don’t believe there’s any other way to get from one place to the other):

select
  datname as database,
  pn.nspname as schema,
  pc.relname as table,
  pspv.phase,
  pspv.heap_blks_total,
  pspv.heap_blks_scanned,
  ((pspv.heap_blks_scanned::real / pspv.heap_blks_total::real) * 100)::numeric(6,2) as heap_scan_percent,
  pspv.heap_blks_vacuumed,
  ((pspv.heap_blks_vacuumed::real / pspv.heap_blks_total::real) * 100)::numeric(6,2) as heap_vacuum_percent,
  pspv.index_vacuum_count,
  pspv.max_dead_tuples,
  pspv.num_dead_tuples
from pg_stat_progress_vacuum pspv
  join pg_class pc on (pc.oid = pspv.relid)
  join pg_namespace pn on (pn.oid = pc.relnamespace)
;

The output looks like this:

-[ RECORD 1 ]-------+------------------
database            | datbasetho
schema              | action
table               | hold_copy_map
phase               | vacuuming indexes
heap_blks_total     | 42439
heap_blks_scanned   | 42439
heap_scan_percent   | 100.00
heap_blks_vacuumed  | 0
heap_vacuum_percent | 0.00
index_vacuum_count  | 0
max_dead_tuples     | 12349749
num_dead_tuples     | 64855
-[ RECORD 2 ]-------+------------------
database            | datbasetho
schema              | asset
table               | copy
phase               | vacuuming heap
heap_blks_total     | 115696
heap_blks_scanned   | 115696
heap_scan_percent   | 100.00
heap_blks_vacuumed  | 109963
heap_vacuum_percent | 95.04
index_vacuum_count  | 1
max_dead_tuples     | 33667536
num_dead_tuples     | 1032528


And the various phases of a vacuum are these; though I imagine it would be difficult to catch a couple of them in the act:

initializing
scanning heap
vacuuming indexes
vacuuming heap
cleaning up indexes
truncating heap
performing final cleanup

I hope this is helpful to someone; I thought it was interesting and handy if you want to know more about what’s going on.

Jason

-- 
Jason Boyer
Senior System Administrator
Equinox Open Library Initiative
phone:  +1 (877) Open-ILS (673-6457)
email:  JBoyer at EquinoxInitiative.org
web:  https://EquinoxInitiative.org/