Re: Report index currently being vacuumed in pg_stat_progress_vacuum

Hi,

On Wed, Sep 9, 2026 at 12:01 PM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>
> I'm studying the patch and discussion so I might be missing something,
> but let me share my thoughts on this patch:
>
> I think it's fine for a progress view to have an entry per backend,
> including parallel workers, so more than one row for a single command.
> If we ever implement parallel index rebuilding for REINDEX TABLE,
> where different workers rebuild different indexes, having one entry
> per worker in pg_stat_progress_create_index seems the straightforward
> thing to do. A dedicated view for workers, say
> pg_stat_progress_create_index_worker, would end up with mostly the
> same columns as pg_stat_progress_create_index.

Thanks for sharing the thoughts.

> I can also see Sami's point that the patch makes many of the existing
> columns of pg_stat_progress_vacuum no-op. But I'm not sure a dedicated
> worker view really avoids that. If a user wants the progress of one
> vacuum command they would join the two views, get one row per
> participant anyway, and the leader's heap columns would be repeated on
> every row. That's better than reporting them as 0, but the user still
> has to know which columns are command-level and which are
> backend-local. It might be worth clarifying the actual query and its
> output for each approach and comparing them.

Here's the sample output [1] with the two approaches, and yes, the
separate view for workers has repeated columns when joined to get the
progress report of a single vacuum command.

That said, I would like to mention an interesting and closely related
discussion on single view vs. separate view to show wait event info of
different processes in pg_stat_activity. The alignment there (almost
unanimously) was to go with the single view even if some of the
columns are not meaningful for certain processes (for example, most of
the pg_stat_activity columns are not applicable to auxiliary processes
such as the checkpointer, background writer, autovacuum launcher,
etc.): https://www.postgresql.org/message-id/CA%2BTgmoYES5nhkEGw9nZXU8\_FhA8XEm8NTm3-SO%2B3ML1B81Hkww%40mail.gmail.com.

As suggested upthread by Michael, I have merged the two patches (index
being vacuumed and index progress report) into a single patch since
they are closely related and there is no strong reason to keep them
separate. I also attached a 0002 patch that removes the now-unused
IndexVacuumInfo.report_progress field. Please find the attached v6
patch.

Note that I haven't added tests for pg_stat_progress_vacuum. It looks
like we have lived without them so far. I'm happy to add a simple
progress report test with parallel index vacuuming (I haven't checked,
but we may need an injection point to hold the parallel workers during
index vacuuming).

[1]
-- One row per worker
select * from pg_stat_progress_vacuum;

pid | datid | datname | relid | phase |
heap_blks_total | heap_blks_scanned | heap_blks_vacuumed |
index_vacuum_count | max_dead_tuple_bytes | dead_tuple_bytes |
num_dead_item_ids | indexes_total | indexes_processed | delay_time |
mode | started_by | current_index_relid | index_blks_total |
index_blks_done
-------+-------+----------+-------+-------------------+-----------------+-------------------+--------------------+--------------------+----------------------+------------------+-------------------+---------------+-------------------+------------+--------+------------+---------------------+------------------+-----------------
24598 | 5 | postgres | 16408 | vacuuming indexes |
16667 | 12862 | 0 | 0 |
1048576 | 1310720 | 352797 |
4 | 0 | 0 | normal | manual |
16413 | 5487 | 303
24600 | 5 | postgres | 16408 | vacuuming indexes |
0 | 0 | 0 | 0 |
0 | 0 | 0 | 0
| 0 | 0 | (null) | (null) |
16416 | 1754 | 130
24601 | 5 | postgres | 16408 | vacuuming indexes |
0 | 0 | 0 | 0 |
0 | 0 | 0 | 0
| 0 | 0 | (null) | (null) |
16414 | 5487 | 335
24602 | 5 | postgres | 16408 | vacuuming indexes |
0 | 0 | 0 | 0 |
0 | 0 | 0 | 0 |
0 | 0 | (null) | (null) |
16415 | 5487 | 291
(4 rows)

-- Separate views for leader and worker vacuum progress
select * from pg_stat_progress_vacuum v
left join pg_stat_progress_vacuum_worker w
on w.leader_pid = v.pid;

pid | datid | datname | relid | phase |
heap_blks_total | heap_blks_scanned | heap_blks_vacuumed |
index_vacuum_count | max_dead_tuple_bytes | dead_tuple_bytes |
num_dead_item_ids | indexes_total | indexes_processed | delay_time |
mode | started_by | current_index_relid | index_blks_total |
index_blks_done | pid | leader_pid | datid | datname | relid |
phase | current_index_relid | index_blks_total |
index_blks_done
-------+-------+----------+-------+-------------------+-----------------+-------------------+--------------------+--------------------+----------------------+------------------+-------------------+---------------+-------------------+------------+--------+------------+---------------------+------------------+-----------------+-------+------------+-------+----------+-------+-------------------+---------------------+------------------+-----------------
27109 | 5 | postgres | 16384 | vacuuming indexes |
16667 | 12862 | 0 | 0 |
1048576 | 1310720 | 205920 |
4 | 0 | 0 | normal | manual |
16389 | 5487 | 1577 | 27110 | 27109 |
5 | postgres | 16384 | vacuuming indexes | 16392 |
1754 | 1070
27109 | 5 | postgres | 16384 | vacuuming indexes |
16667 | 12862 | 0 | 0 |
1048576 | 1310720 | 205920 |
4 | 0 | 0 | normal | manual |
16389 | 5487 | 1577 | 27111 | 27109 |
5 | postgres | 16384 | vacuuming indexes | 16390 |
5487 | 1583
27109 | 5 | postgres | 16384 | vacuuming indexes |
16667 | 12862 | 0 | 0 |
1048576 | 1310720 | 205920 |
4 | 0 | 0 | normal | manual |
16389 | 5487 | 1577 | 27112 | 27109 |
5 | postgres | 16384 | vacuuming indexes | 16391 |
5487 | 1599
(3 rows)

--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com