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: [email protected] web: https://EquinoxInitiative.org/ _______________________________________________ Evergreen-general mailing list [email protected] http://list.evergreen-ils.org/cgi-bin/mailman/listinfo/evergreen-general
