Re: Report index currently being vacuumed in pg_stat_progress_vacuum

Hi,

On Tue, Aug 25, 2026 at 9:34 AM Bharath Rupireddy
<bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:
>
> Hi,
>
> On Thu, Aug 20, 2026 at 4:10 PM Michael Paquier <michael(at)paquier(dot)xyz> wrote:
> >
> > On Wed, Aug 19, 2026 at 07:37:00PM -0700, Bharath Rupireddy wrote:
> > > I don't think a separate view is the right approach at least for two
> > > reasons. One, to know the progress of a vacuum one has to query two
> > > views and relate them. Two, since the leader itself participates in
> > > vacuuming indexes alongside workers (and will also for parallel heap
> > > vacuum), splitting the same command's progress into two views adds
> > > complexity. Keeping everything in a single view with one row per
> > > worker (as discussed upthread) is simpler. Some fields would be null
> > > on worker rows, but documenting this should be sufficient. That said,
> > > I'm open to hear more thoughts on this.
> >
> > Having a single progress view feels like the natural approach here,
> > for both the leader and the workers. The leader triggers the
> > existence of the workers, but both leader and workers may finish by
> > doing the same job as there could be usually little meaning for a
> > leader to stand idle, waiting for all the workers to do the work.
> > Such choices are implementation-agnostic, of course; we should not
> > lock ourselves.
>
> Thanks for reviewing. After thinking about this for a while, I still
> think one row per worker is the better choice, with the tradeoff of
> some fields being null on worker rows (similar to leader_pid in
> pg_stat_activity). The docs also say "the view will contain one row
> for each backend that is currently vacuuming," and in that sense,
> parallel workers launched for index vacuum are essentially doing
> vacuum too.
>
> Here's what I have. 0001 reports the current index being vacuumed, and
> 0002 reports the total blocks and done blocks for B-tree indexes. This
> helps track vacuum progress for large indexes in production.
>
> Please have a look.

Thanks Michael for the off-list chat. I agree that emitting leader_pid
via the vacuum progress report is information bloat, since one can
easily identify the workers for a given leader by looking at the
database OID and relation name, and if needed, can also join with
pg_stat_activity.leader_pid. So, I removed leader_pid in the 0001
patch.

Please find the attached v5 patches.

Here is some sample output that I captured:

-- Test 1: manual VACUUM, non-parallel index vacuum.
-- Take 1
pid | datid | datname | relid | phase |
heap_blks_scanned | current_index | index_blks_total | index_blks_done
| started_by
------+-------+----------+----------+-------------------+-------------------+---------------+------------------+-----------------+------------
5786 | 5 | postgres | test_vac | vacuuming indexes |
63695 | test_vac_idx1 | 27422 | 3706 | manual
-- Take 2
5786 | 5 | postgres | test_vac | vacuuming indexes |
63695 | test_vac_idx1 | 27422 | 10275 | manual

-- Test 2: manual VACUUM (PARALLEL 2), parallel index vacuum.
-- Take 1
pid | datid | datname | relid | phase |
heap_blks_scanned | current_index | index_blks_total | index_blks_done
| started_by
------+-------+----------+----------+-------------------+-------------------+---------------+------------------+-----------------+------------
8040 | 5 | postgres | test_vac | vacuuming indexes |
63695 | test_vac_idx1 | 27422 | 463 | manual
8372 | 5 | postgres | test_vac | vacuuming indexes |
0 | test_vac_idx3 | 27422 | 399 |
8373 | 5 | postgres | test_vac | vacuuming indexes |
0 | test_vac_idx2 | 27422 | 351 |
-- Take 2
8040 | 5 | postgres | test_vac | vacuuming indexes |
63695 | test_vac_idx1 | 27422 | 9855 | manual
8372 | 5 | postgres | test_vac | vacuuming indexes |
0 | test_vac_idx3 | 27422 | 9199 |
8373 | 5 | postgres | test_vac | vacuuming indexes |
0 | test_vac_idx2 | 27422 | 9145 |

-- Test 3: autovacuum, non-parallel index vacuum.
-- Take 1
pid | datid | datname | relid | phase |
heap_blks_scanned | current_index | index_blks_total | index_blks_done
| started_by
------+-------+----------+----------+-------------------+-------------------+---------------+------------------+-----------------+------------
9835 | 5 | postgres | test_vac | vacuuming indexes |
63695 | test_vac_idx1 | 27422 | 1023 |
autovacuum
-- Take 2
9835 | 5 | postgres | test_vac | vacuuming indexes |
63695 | test_vac_idx1 | 27422 | 3071 |
autovacuum

-- Test 4: autovacuum, parallel index vacuum (autovacuum_parallel_workers=2).
-- Take 1
pid | datid | datname | relid | phase |
heap_blks_scanned | current_index | index_blks_total | index_blks_done
| started_by
-------+-------+----------+----------+-------------------+-------------------+---------------+------------------+-----------------+------------
31309 | 5 | postgres | test_vac | vacuuming indexes |
63695 | test_vac_idx1 | 27422 | 459 |
autovacuum
650 | 5 | postgres | test_vac | vacuuming indexes |
0 | test_vac_idx2 | 27422 | 476 |
651 | 5 | postgres | test_vac | vacuuming indexes |
0 | test_vac_idx3 | 27422 | 474 |
-- Take 2
31309 | 5 | postgres | test_vac | vacuuming indexes |
63695 | test_vac_idx1 | 27422 | 2115 |
autovacuum
650 | 5 | postgres | test_vac | vacuuming indexes |
0 | test_vac_idx2 | 27422 | 2126 |
651 | 5 | postgres | test_vac | vacuuming indexes |
0 | test_vac_idx3 | 27422 | 2118 |

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