Hi, On Thu, Aug 13, 2026 at 8:48 PM Michael Paquier <[email protected]> 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
