Re: Report index currently being vacuumed in pg_stat_progress_vacuum

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.
>
> Please find attached the v3 patch. It ensures the current index is
> reset after each index (so vacuuming heap and truncating heap show
> NULL arrays with no stale relid), fixes the docs for the type of
> index_vacuum_pids, adds a note in the docs about the arrays being
> position-aligned, and rewords the commit message a bit.

+ ELSE NULL END AS started_by,
+ I.index_vacuum_pids AS index_vacuum_pids,
+ I.index_vacuum_oids AS index_vacuum_oids
FROM pg_stat_get_progress_info('VACUUM') AS S
- LEFT JOIN pg_database D ON S.datid = D.oid;
+ LEFT JOIN pg_database D ON S.datid = D.oid
+ LEFT JOIN pg_stat_activity A ON S.pid = A.pid,
+ LATERAL (

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. 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. 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). 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.
--
Michael