Re: Report index currently being vacuumed in pg_stat_progress_vacuum

Thanks for the feedback, Michael!

The view already reports indexes_total and indexes_processed, how many
indexes are done. These columns add which ones are in progress, and by
which PID.

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

I put the PID and OID in the leader's row as two position-aligned arrays
precisely to keep the shape of the view, one row per command. The docs
describe pg_stat_progress_vacuum as "one row for each backend (including
autovacuum worker processes) that is currently vacuuming". I read that
as one row for the backend that launched the operation. A parallel
worker isn't running a command, it's a consequence of the configuration,
so it belongs in the leader's row, not a row of its own.

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

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}
```

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}
```

On naming, we should probablt call the columns vacuum_pids and vacuum_oids
rather than index_vacuum_*, since the phase says what they are for.
So this doesn't hamstring us, and it doesn't need a row per worker.

> 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].

Also, reusing heap_blks_* isn't the right interface for it. They hold
how far the heap got, and even though they stop advancing once we're
vacuuming indexes, a user still wants to know at any given moment just
how far the table has been vacuumed. Double purposing the same field to
count index blocks would overwrite it, which makes monitoring this field
impractical.

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.

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

This isn't really about mapping a leader to its workers. A JOIN with
pg_stat_activity relates the PIDs, but it can't tell you which index
each worker is on, and that's what these two columns add.

[1] https://www.postgresql.org/message-id/flat/CAD21AoAEfCNv-GgaDheDJ%2Bs-p_Lv1H24AiJeNoPGCmZNSwL1YA%40mail.gmail.com
[2] https://www.postgresql.org/message-id/CAH2-Wz=3JGBty=3tXoBoEYYwQNd7fXJuN9oPcnBAj3JYroBv3w@mail.gmail.com

--
Sami Imseih
Amazon Web Services (AWS)