The current implementation of pg_stat_progress_vacuum does not provide progress on which index is being vacuumed making it difficult for a user to determine if the "vacuuming indexes" phase is making progress. By exposing which index is being scanned as well as the total progress the scan has made for the current cycle, a user can make better estimations on when the vacuum will complete.
The proposed patch adds 4 new columns to pg_stat_progress_vacuum:
1. indrelid - the relid of the index being vacuumed
2. index_blks_total - total number of blocks to be scanned in the current cycle
3. index_blks_scanned - number of blocks scanned in the current cycle
4. leader_pid - if the pid for the pg_stat_progress_vacuum entry is a leader or a vacuum worker. This patch places an entry for every worker pid ( if parallel ) as well as the leader pid
Attached is the patch.
Here is a sample output of a parallel vacuum for table with relid = 16638
postgres=# select * from pg_stat_progress_vacuum ;
-[ RECORD 1 ]------+------------------
pid | 18180
datid | 13732
datname | postgres
relid | 16638
phase | vacuuming indexes
heap_blks_total | 5149825
heap_blks_scanned | 5149825
heap_blks_vacuumed | 3686381
index_vacuum_count | 2
max_dead_tuples | 178956969
num_dead_tuples | 142086544
indrelid | 0 <<-----
index_blks_total | 0 <<-----
index_blks_scanned | 0 <<-----
leader_pid | <<-----
-[ RECORD 2 ]------+------------------
pid | 1543
datid | 13732
datname | postgres
relid | 16638
phase | vacuuming indexes
heap_blks_total | 0
heap_blks_scanned | 0
heap_blks_vacuumed | 0
index_vacuum_count | 0
max_dead_tuples | 0
num_dead_tuples | 0
indrelid | 16646
index_blks_total | 3030305
index_blks_scanned | 2356564
leader_pid | 18180
-[ RECORD 3 ]------+------------------
pid | 1544
datid | 13732
datname | postgres
relid | 16638
phase | vacuuming indexes
heap_blks_total | 0
heap_blks_scanned | 0
heap_blks_vacuumed | 0
index_vacuum_count | 0
max_dead_tuples | 0
num_dead_tuples | 0
indrelid | 16651
index_blks_total | 2685921
index_blks_scanned | 2119179
leader_pid | 18180
Regards,
Sami Imseih
Database Engineer @ Amazon Web Services
