Re: Report index currently being vacuumed in pg_stat_progress_vacuum

Hi,

Thanks Sami for the thoughts. I addressed most of these upthread [1],
responding to the remaining ones here.

[1] https://www.postgresql.org/message-id/CALj2ACX6gyBmQfaqbCsycDmaSPbq1%3DiPJw1OqUT%2BaLqaKjW8dQ%40mail.gmail.com

On Fri, Aug 14, 2026 at 2:26 PM Sami Imseih <samimseih(at)gmail(dot)com> wrote:
>
> The two arrays already roll up what we care about, which PID is on which
> index, into the leader's row, and the approach we pick only changes
> whether an OID repeats.
>
> Today, index vacuuming is one PID per OID. An index is claimed in full by
> one process and never scanned by two at once, so the OIDs are distinct.
>
> ```
> leader_pid | vacuum_pids | vacuum_oids
> ------------+---------------+---------------------
> 100 | {100,200,300} | {10000,10001,10002}
> ```

Right, and this holds only for BTree. Some other index AM might
implement intra-parallel index vacuum (vacuuming one index with
multiple workers), in which case OIDs would repeat.

> More than one PID on a single OID, whether that's distributing one index
> across workers as you describe, or parallel heap vacuum [1] which is
> still being discussed, is handled by repeating the OID. The phase says
> what the OID is, here a table's relid rather than an index.
>
> ```
> leader_pid | phase | vacuum_pids | vacuum_oids
> ------------+----------------+-------------------+-----------------------
> 100 | vacuuming heap | {100,200,300,400} | {9000,9000,9000,9000}
> ```
>
> [1] https://www.postgresql.org/message-id/flat/CAD21AoAEfCNv-GgaDheDJ%2Bs-p_Lv1H24AiJeNoPGCmZNSwL1YA%40mail.gmail.com

I haven't thought about parallel heap vacuum in depth yet and will do
that as part of that thread. Quick thoughts. One row per worker lets
us report heap_blks_* per worker naturally. Alternatively, we could
report a single overall value in the leader's heap_blks_*, the way
parallel CREATE INDEX does today, tracking total scan progress across
all workers rather than any one worker's share.

> > 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).
>
> Right, there's a need here. For some indexes we know how far we need to
> scan, like btree, and for some we can't, like GIN. This was discussed
> before for index scan progress [2].
>
> [2] https://www.postgresql.org/message-id/CAH2-Wz=3JGBty=3tXoBoEYYwQNd7fXJuN9oPcnBAj3JYroBv3w@mail.gmail.com

Correct, but today BTree scan progress is supported via create index
progress reporting (see my response upthread around
PROGRESS_SCAN_BLOCKS_TOTAL/DONE).

> > If we do expose per-worker progress, a separate view is the better home.
> > Most columns here are the leader's or shared, so a per-worker row would
> > be mostly empty anyway.
>
> A different idea other than aggregating the pids and oid's into a list
> as is currently
> being proposed, would be to have a "pg_stat_progress_vacuum_worker" view,
> which initially will be 4 columns:
>
> "pid"
> "leader_pid"
> "phase"
> "oid"
>
> and it will have a row for every worker ( or leader ) and the "oid" they are
> processing, which could be a index ( or a heap if we get to that point of
> parallel heap vacuum ).

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.

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