[Evergreen-general] Useful Postgres Query for Vacuum Progress

Jason Boyer jboyer at equinoxinitiative.org
Fri Jan 8 10:58:23 EST 2021

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):

  datname as database,
  pn.nspname as schema,
  pc.relname as table,
  ((pspv.heap_blks_scanned::real / pspv.heap_blks_total::real) * 100)::numeric(6,2) as heap_scan_percent,
  ((pspv.heap_blks_vacuumed::real / pspv.heap_blks_total::real) * 100)::numeric(6,2) as heap_vacuum_percent,
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:

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 Boyer
Senior System Administrator
Equinox Open Library Initiative
phone:  +1 (877) Open-ILS (673-6457)
email:  JBoyer at EquinoxInitiative.org
web:  https://EquinoxInitiative.org/

More information about the Evergreen-general mailing list