Re: Report index currently being vacuumed in pg_stat_progress_vacuum

Hi,

On Thu, Aug 13, 2026 at 8:48 PM Michael Paquier <michael(at)paquier(dot)xyz> wrote:
>
> On Tue, Aug 04, 2026 at 03:20:00PM -0700, Bharath Rupireddy wrote:
> > I spent some more time thinking about using arrays here, and about the
> > one-row-per-command policy. I still think emitting the index OIDs and
> > worker PIDs as position-aligned arrays (like the existing
> > pg_stats.most_common_vals/most_common_freqs columns) is the simple
> > solution. I appreciate any thoughts or other ways here.
>
> Exposing the information of an index a worker is processing is a good
> idea, but I think that this choice lacks a long-term vision. I think
> that we should expose one row for each worker rather than an array of
> PIDs and index OIDs in the row of a leader.
> The main issue for me is
> the granularity of the information provided, where it would actually
> make sense to provide more information for each worker.

The position-aligned array will only grow in the future, making it
hard to add more per-worker information later (index blocks total vs
done for parallel index vacuum, heap blocks total vs done for parallel
heap vacuum, per index dead rows cleaned up and deduped, etc.).

> Choosing how
> an index clean works in vacuum for parallel workers is an
> implementation choice, where we could think about approaches like:
> - Distribute the workload of one index across N workers (for a 1TB
> index, spawn N workers each sharing 1/N TB of data to clean)
> - Have each worker do one index.
> - Or more strategies, etc.
> My point is not the strategy or the design we choose, which could vary
> depending on an index AM. It's that for any design, any strategy or
> any index AM, at the end it is going to be way more important for the
> end-user how *each* individual worker behaves.

I found ab0dfc961 which adds AM-agnostic and AM-specific fields for
the indexes supported in core (see below). When multiple indexes want
a common thing to be reported, we can add such a thing to the core and
let the specific index AM report that information.

> One thing could be for
> example reusing heap_blks_total and heap_blks_scanned for indexes, so
> as it is possible how much each worker has done (let's perhaps rename
> them).

heap_blks_* cannot be reused for tracking index blocks total and
scanned, because those values must be retained across multi-pass index
vacuuming (which triggers when the dead-TID store fills). Although
this is rare after the radix-tree based TID store optimizations, it's
still possible. The leader itself does index vacuuming in the serial
case, so overwriting those fields would corrupt the heap progress
that's still needed.

I came across two AM-specific fields (used for BTree and GIN for now):
PROGRESS_SCAN_BLOCKS_TOTAL/DONE, added by ab0dfc961 for create index
progress reporting. For example, btvacuumscan reports index progress
during concurrent BTree index creation/recreation cases, something
like the following:

phase | blocks_done | blocks_total
----------------------------------+-------------+--------------
index validation: scanning index | 959 | 27422
index validation: scanning index | 13350 | 27422
index validation: scanning index | 25973 | 27422

This, combined with per-worker vacuum progress reporting, lets us
report index vacuum progress nicely for BTree. Although this only
covers BTree for now (others will continue to report as NULL), it's a
good starting point since the majority of indexes are BTree. It also
gives visibility into how the index vacuum is progressing towards its
goal and lets one estimate the vacuum finish time (along with
heap_blks_*), particularly with hundreds of GBs and TBs of indexes at
scale.

> Being able to map a leader with its worker is an information
> already provided by pg_stat_activity, adding this information in the
> progress view seems unnecessary for me to add here as a JOIN is
> already able to solve that anyway. If extra SQL knowledge is
> necessary, that's more a documentation problem to me, adding more
> fields for data that's already available is just more information
> bloat.

Although we could get leader_pid almost for free in the parallel index
vacuum cases, I agree that having it there is not only information
bloat but also eats up a fixed progress reporting slot in shared
memory (we only have 20, and I expect that to grow in the future). We
can leave a note in the docs that leader_pid being NULL in
pg_stat_activity, especially with roles not having pg_read_all_stats
or roles not owning the backends, means they won't see the worker
rows.

In short, I tend to agree with having one row per worker in the vacuum
progress report, joining pg_stat_activity's leader_pid for simpler
usability, extensibility, and less information bloat, along with doc
changes to explain this. One concern is that some progress fields
would be null on worker rows, but documenting this should be
sufficient. I could be missing something here, so I would like to hear
some thoughts before coming up with a patch.

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