Re: Add index scan progress to pg_stat_progress_vacuum

Here is a V2 attempt of the patch to include a new view called pg_stat_progress_vacuum_worker. Also, scans for index cleanups will also have an entry in the new view.

- here is the new view which reports an entry for every worker ( or leader ) that is doing index vacuum/index cleanup work.
postgres=# select * from pg_stat_progress_vacuum_worker ;
-[ RECORD 1 ]------+------
pid | 29355
leader_pid | 26501
indrelid | 16391
index_blks_total | 68894
index_blks_scanned | 35618

- the view can be joined with pg_stat_progress_vacuum. Sample output below

postgres=# select a.*, b.phase, b.heap_blks_total, b.heap_blks_scanned from pg_stat_progress_vacuum_worker a full outer join pg_stat_progress_vacuum b on a.pid = b.pid ;
pid | leader_pid | indrelid | index_blks_total | index_blks_scanned | phase | heap_blks_total | heap_blks_scanned
-------+------------+----------+------------------+--------------------+---------------------+-----------------+-------------------
26667 | 26667 | 16391 | 9165 | 401 | cleaning up indexes | 20082 | 20082
(1 row)

postgres=# select a.*, b.phase, b.heap_blks_total, b.heap_blks_scanned from pg_stat_progress_vacuum_worker a full outer join pg_stat_progress_vacuum b on a.pid = b.pid ;
-[ RECORD 1 ]------+------------------
pid | 26501
leader_pid | 26501
indrelid | 16393
index_blks_total | 145107
index_blks_scanned | 11060
phase | vacuuming indexes
heap_blks_total | 165375
heap_blks_scanned | 165375
-[ RECORD 2 ]------+------------------
pid | 28982
leader_pid | 26501
indrelid | 16392
index_blks_total | 47616
index_blks_scanned | 11861
phase | vacuuming indexes
heap_blks_total | 0
heap_blks_scanned | 0
-[ RECORD 3 ]------+------------------
pid | 28983
leader_pid | 26501
indrelid | 16391
index_blks_total | 56936
index_blks_scanned | 9138
phase | vacuuming indexes
heap_blks_total | 0
heap_blks_scanned | 0

On 12/15/21, 4:10 PM, "Bossart, Nathan" <bossartn(at)amazon(dot)com> wrote:

On 12/1/21, 3:02 PM, "Imseih (AWS), Sami" <simseih(at)amazon(dot)com> wrote:
> 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.

+1

> 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

nitpick: Shouldn't index_blks_scanned be index_blks_vacuumed? IMO it
is more analogous to heap_blks_vacuumed.

No, What is being tracked is the number of index blocks scanned from the total index blocks. The block will be scanned regardless if it will be vacuumed or not.

This will tell us which indexes are currently being vacuumed and the
current progress of those operations, but it doesn't tell us which
indexes have already been vacuumed or which ones are pending vacuum.
I think such information is necessary to truly understand the current
progress of vacuuming indexes, and I can think of a couple of ways we
might provide it:

1. Make the new columns you've proposed return arrays. This isn't
very clean, but it would keep all the information for a given
vacuum operation in a single row. The indrelids column would be
populated with all the indexes that have been vacuumed, need to
be vacuumed, or are presently being vacuumed. The other index-
related columns would then have the associated stats and the
worker PID (which might be the same as the pid column depending
on whether parallel index vacuum was being done). Alternatively,
the index column could have an array of records, each containing
all the information for a given index.
2. Create a new view for just index vacuum progress information.
This would have similar information as 1. There would be an
entry for each index that has been vacuumed, needs to be
vacuumed, or is currently being vacuumed. And there would be an
easy way to join with pg_stat_progress_vacuum (e.g., leader_pid,
which again might be the same as our index vacuum PID depending
on whether we were doing parallel index vacuum). Note that it
would be possible for the PID of these entries to be null before
and after we process the index.
3. Instead of adding columns to pg_stat_progress_vacuum, adjust the
current ones to be more general, and then add new entries for
each of the indexes that have been, need to be, or currently are
being vacuumed. This is the most similar option to your current
proposal, but instead of introducing a column like
index_blks_total, we'd rename heap_blks_total to blks_total and
use that for both the heap and indexes. I think we'd still want
to add a leader_pid column. Again, we have to be prepared for
the PID to be null in this case. Or we could just make the pid
column always refer to the leader, and we could introduce a
worker_pid column. That might create confusion, though.

I wish option #1 was cleaner, because I think it would be really nice
to have all this information in a single row. However, I don't expect
much support for a 3-dimensional view, so I suspect option #2
(creating a separate view for index vacuum progress) is the way to go.
The other benefit of option #2 versus option #3 or your original
proposal is that it cleanly separates the top-level vacuum operations
and the index vacuum operations, which are related at the moment, but
which might not always be tied so closely together.

Option #1 is not clean as you will need to unnest the array to make sense out of it. It will be too complex to use.
Option #3 I am reluctant to spent time looking at this option. It's more valuable to see progress per index instead of total.
Option #2 was one that I originally designed but backed away as it was introducing a new view. Thinking about it a bit more, this is a cleaner approach.
1. Having a view called pg_stat_progress_vacuum_worker to join with pg_stat_progress_vacuum is clean
2. No changes required to pg_stat_progress_vacuum
3. I’ll lean towards calling the view " pg_stat_progress_vacuum_worker" instead of " pg_stat_progress_vacuum_index", to perhaps allow us to track other items a vacuum worker may do in future releases. As of now, only indexes are vacuumed by workers.
I will rework the patch for option #2

Nathan