Report index currently being vacuumed in pg_stat_progress_vacuum

Hi,

On Sun, May 3, 2026 at 7:01 PM Bharath Rupireddy <
bharath.rupireddyforpostgres@gmail.com> wrote:

Hi,

When VACUUM is in the "vacuuming indexes" or "cleaning up indexes" phase,
there is currently no easy way to tell which specific index is being
processed. The progress report view shows indexes_total and
indexes_processed counters, but not which index is actively being worked on.

This makes it difficult to debug slow or stuck autovacuum workers on
tables with multiple indexes of different types (btree, GIN, GiST, BRIN,
HNSW, etc.), since one cannot determine which index type or which specific
index is causing the delay.

Please find the attached patch adds a new column current_index_relid to
pg_stat_progress_vacuum that reports the OID of the index currently being
vacuumed or cleaned up. The column is reported for both the "vacuuming
indexes" phase and the "cleaning up indexes" phase.

When indexes are being vacuumed in parallel, each parallel worker emits
its own row in pg_stat_progress_vacuum with current_index_relid set to the
index it is currently processing, and leader_pid pointing to the leader
process.

Appreciate any feedback. Thank you!

[1] Example output:

pid | datname | relid | table_name | phase | started_by |
current_index_relid | index_name | leader_pid

------+----------+-------+------------+-------------------+------------+---------------------+---------------+------------
1420 | postgres | 16395 | vac_test | vacuuming indexes | autovacuum |
16398 | vac_test_idx1 |
1421 | postgres | 16395 | vac_test | vacuuming indexes | |
16399 | vac_test_idx2 | 1420
1423 | postgres | 16395 | vac_test | vacuuming indexes | |
16400 | vac_test_idx3 | 1420
(3 rows)

pid | datname | relid | table_name | phase | started_by |
current_index_relid | index_name | leader_pid

------+----------+-------+------------+-------------------+------------+---------------------+---------------+------------
1346 | postgres | 16395 | vac_test | vacuuming indexes | manual |
16398 | vac_test_idx1 |
(1 row)

[2]
SELECT v.pid, v.datname, v.relid, c.relname AS table_name,
v.phase, v.started_by, v.current_index_relid,
COALESCE(ic.relname, '') AS index_name, v.leader_pid
FROM pg_stat_progress_vacuum v
JOIN pg_class c
ON c.oid = v.relid
LEFT JOIN pg_class ic
ON ic.oid = v.current_index_relid
WHERE v.relid = $tbl_oid
ORDER BY
v.leader_pid,
v.pid;

Bharath, thanks for the patch! A few comments:

(1) Do we need a global API? Can we add a leader_pid field in PVShared?

+pid_t
+GetParallelLeaderPid(void)
+{
+ return ParallelLeaderPid;
+}

(2): Looks like current_index_relid is not cleared when we leave the index
phases.As a result, once any index has been processed,
pg_stat_progress_vacuum.current_index_relid keeps reporting that relid
through vacuuming heap, truncating heap, cleaning up indexes.
This will be confusing to the user. Something like below:

1795819|vacuuming heap|0/0|16392|t1_pkey|LEADER

(3) leader_pid type should be integer type similar to pg_Stat_activity?

Thanks,
Satya

Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com> wrote:

When VACUUM is in the "vacuuming indexes" or "cleaning up indexes" phase, there is currently no easy way to tell which specific index is
being processed. The progress report view shows indexes_total and indexes_processed counters, but not which index is actively being worked
on.

This makes it difficult to debug slow or stuck autovacuum workers on tables with multiple indexes of different types (btree, GIN, GiST, BRIN,
HNSW, etc.), since one cannot determine which index type or which specific index is causing the delay.

Please find the attached patch adds a new column current_index_relid to pg_stat_progress_vacuum that reports the OID of the index
currently being vacuumed or cleaned up. The column is reported for both the "vacuuming indexes" phase and the "cleaning up indexes"
phase.

When indexes are being vacuumed in parallel, each parallel worker emits its own row in pg_stat_progress_vacuum with current_index_relid
set to the index it is currently processing, and leader_pid pointing to the leader process.

Appreciate any feedback. Thank you!

This problem seems to be similar to what I noticed when workign on the REPACK
command: progress reporting of index build needs to be disabled if the build
is part of REPACK, otherwise the index build can overwrite the counters of
REPACK (whether the overwriting actually happens or not is another question).

The solution I suggest is to allow progress tracking of a "sub-command" - see
the attached patch. Wouldn't that also resolve your problem? (My plan is to
incorporate this in the series of REPACK enhancements soon.)

--
Antonin Houska
Web: https://www.cybertec-postgresql.com

Hi,

Appreciate any feedback. Thank you!

I think it is valuable to show the index being processed. There is
really no other easy way to get this information except for pstack,
etc. I am +1 for the idea.

However, I am not sure that having a separate row for every parallel
worker is the right approach. The pg_stat_progress_* views are designed
to show progress per row. Each row represents one command with
meaningful progress counters (heap_blks_scanned, indexes_total,
indexes_processed, etc.). A parallel worker row would only show
current_index_relid and leader_pid with no actual progress information
of its own. That is status, not progress, and it does not fit the
view. Also, many columns would remain empty or redundant with the
leader's row.

Instead, could we aggregate the parallel worker information into the
leader's row. For example, an array of worker PIDs in one column and an
array of index relids in another?

--
Sami Imseih
Amazon Web Services (AWS)

Hi,

On Tue, May 5, 2026 at 10:54 AM Sami Imseih <samimseih@gmail.com> wrote:

I think it is valuable to show the index being processed. There is
really no other easy way to get this information except for pstack,
etc. I am +1 for the idea.

Thanks for reviewing this!

However, I am not sure that having a separate row for every parallel
worker is the right approach. The pg_stat_progress_* views are designed
to show progress per row. Each row represents one command with
meaningful progress counters (heap_blks_scanned, indexes_total,
indexes_processed, etc.). A parallel worker row would only show
current_index_relid and leader_pid with no actual progress information
of its own. That is status, not progress, and it does not fit the
view. Also, many columns would remain empty or redundant with the
leader's row.

Instead, could we aggregate the parallel worker information into the
leader's row. For example, an array of worker PIDs in one column and an
array of index relids in another?

Thanks for the review. I read f1889729 and it looks like the
preference was to keep one command = one row, with workers feeding the
leader's row rather than showing up as separate rows. I want to stay
with that approach.

I considered having the leader set up a DSA that workers write into,
shown as an extra column on the leader's row. But that means new
shared memory whose handle has to be stored somewhere other backends
can find it, attached by the reader, and freed safely while a
monitoring query might still be reading it - that's a lot of work for
a small amount of per-worker data.

The simpler approach is to have workers report the index they're
currently on into their own st_progress_param[] slots, which already
exist per backend, along with their leader's pid. The view then groups
the worker entries under the matching leader's pid and shows only the
leader rows, so no new shared memory is needed. The current index oid
column has to be added to that array anyway for the non-parallel case,
which needs it just as much, and the parallel workers already have
their own slots to report into, so there's nothing extra to set up.
One thing to note is that pg_stat_get_progress_info('VACUUM') itself
would still return the worker rows, and the grouping happens in the
pg_stat_progress_vacuum view instead. I prefer keeping it in the view
rather than the function. The function is shared by all the progress
commands and only deals with raw params, while the leader pid grouping
is specific to VACUUM. This way the shared function stays unchanged
and the other progress views are not affected.

While I'm here, in the "vacuuming indexes" phase I also want to report
the total index pages to scan and the pages scanned so far, for the
index currently being vacuumed. On a large index this phase can run
for a long time with no way to tell whether it's making progress.

Does this direction sound reasonable, or do you see a reason to prefer
a different approach?

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

Hi,

The simpler approach is to have workers report the index they're
currently on into their own st_progress_param[] slots, which already
exist per backend, along with their leader's pid. The view then groups
the worker entries under the matching leader's pid and shows only the
leader rows, so no new shared memory is needed.

Right, that is what I am thinking also.

One thing to note is that pg_stat_get_progress_info('VACUUM') itself
would still return the worker rows, and the grouping happens in the
pg_stat_progress_vacuum view instead.

Yes, that would be the best way. Do the aggregation on the SQL level
using array_agg.

While I'm here, in the "vacuuming indexes" phase I also want to report
the total index pages to scan and the pages scanned so far, for the
index currently being vacuumed. On a large index this phase can run
for a long time with no way to tell whether it's making progress.

This was brought up when the "index progress" columns were being worked on, and
knowing the total was not possible for all index types [1]/messages/by-id/CAH2-Wz=3JGBty=3tXoBoEYYwQNd7fXJuN9oPcnBAj3JYroBv3w@mail.gmail.com

Does this direction sound reasonable, or do you see a reason to prefer
a different approach?

Yes, I think the SQL level aggregation is sane.

[1]: /messages/by-id/CAH2-Wz=3JGBty=3tXoBoEYYwQNd7fXJuN9oPcnBAj3JYroBv3w@mail.gmail.com

--
Sami Imseih
Amazon Web Services (AWS)

Hi,

See the attached v2.

It aggregates on the leader (if parallel) at the SQL level with two new
columns: index_vacuum_pids and index_vacuum_oids. These are pid and oid
arrays respectively and are order-aligned. The leader is listed first when
it is itself processing an index; otherwise one of the workers is,
obviously, first.
For a serial vacuum, each array is a single value. The array fields will be
NULL if no index is currently being processed.

Using array typed columns is new for the pg_stat_progress_* views, none of
them expose arrays today, or any other pg_stat_* views. It's a bit unusual here,
but I think it's the natural fit.

Now, with this, parallel workers now register their own progress entry
so they show up as additional rows in pg_stat_get_progress_info('VACUUM').
The progress_vacuum view filters them out, but any code that reads the function
directly will now see an extra (mostly zeros) row per worker.
pg_stat_get_progress_info()
is not meant to be used directly, hence it's not documented for direct
use, so I think this is OK.

I also noticed in v1 that we weren't resetting the index after the vacuum
completed, and the index was being set in the wrong place. I fixed that. I
also removed the unnecessary typecast for InvalidOid.

Lastly, with this approach we no longer need the external function to
retrieve the leader pid.

What do you think?

--
Sami

Hi,

On Fri, Jul 17, 2026 at 12:24 PM Sami Imseih <samimseih@gmail.com> wrote:

See the attached v2.

It aggregates on the leader (if parallel) at the SQL level with two new
columns: index_vacuum_pids and index_vacuum_oids. These are pid and oid
arrays respectively and are order-aligned. The leader is listed first when
it is itself processing an index; otherwise one of the workers is,
obviously, first.
For a serial vacuum, each array is a single value. The array fields will be
NULL if no index is currently being processed.

Using array typed columns is new for the pg_stat_progress_* views, none of
them expose arrays today, or any other pg_stat_* views. It's a bit unusual here,
but I think it's the natural fit.

Thanks for the v2 patch, Sami!

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.

Here's how the sample output looks.

Index vacuum:

pid | phase | index_vacuum_pids | index_vacuum_oids
------+-------------------+-------------------+-------------------
4955 | vacuuming indexes | {4955} | {16478}
(1 row)

Parallel index vacuum:

pid | phase | index_vacuum_pids | index_vacuum_oids
------+-------------------+-----------------------+---------------------------
5765 | vacuuming indexes | {5765,5768,5769,5770} | {16478,16479,16480,16481}
(1 row)

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

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.

Thanks for the updates in v3.

It turns out, to my surprise, that leader_pid can be NULL if the user
querying pg_stat_progress_vacuum does not have proper privileges, either
pg_read_all_stats or membership in the role running the vacuum.

Here is the case. "foo" is created with

```
CREATE ROLE foo LOGIN;
GRANT CONNECT ON DATABASE postgres TO foo;
```

A superuser aggregates correctly on leader_pid, because the workers
emit leader_pid in pg_stat_activity.

```
pid | phase | index_vacuum_pids | index_vacuum_oids
-------+-------------------+---------------------+---------------------
23492 | vacuuming indexes | {23492,23570,23571} | {16389,16390,16391}
(1 row)

pid | leader_pid | backend_type | state | query
-------+------------+-----------------+--------+------------------------------
23492 | | client backend | active | VACUUM (PARALLEL 4) vac_demo;
23570 | 23492 | parallel worker | active | VACUUM (PARALLEL 4) vac_demo;
23571 | 23492 | parallel worker | active | VACUUM (PARALLEL 4) vac_demo;
(3 rows)
```

But "foo" cannot, because leader_pid is NULL for this user, so the
aggregation falls apart and each worker emits its own row in
pg_stat_progress_vacuum.

pid | phase | index_vacuum_pids | index_vacuum_oids
-------+-------+-------------------+-------------------
23492 | | |
23570 | | |
23571 | | |
(3 rows)

I think for this patch we should drop the reliance on pg_stat_activity
and have pg_stat_get_progress_info() emit leader_pid directly and
unconditionally. The aggregation in the view then works regardless of
the caller's
privileges. It is the same lockGroupLeader value pg_stat_activity
already computes.

```
 pg_stat_get_progress_info(PG_FUNCTION_ARGS)
 {
-#define PG_STAT_GET_PROGRESS_COLS      PGSTAT_NUM_PROGRESS_PARAM + 3
+#define PG_STAT_GET_PROGRESS_COLS      PGSTAT_NUM_PROGRESS_PARAM + 4
        int                     num_backends = pgstat_fetch_stat_numbackends();
        int                     curr_backend;
        char       *cmd = text_to_cstring(PG_GETARG_TEXT_PP(0));
@@ -373,6 +373,7 @@ pg_stat_get_progress_info(PG_FUNCTION_ARGS)
        {
                LocalPgBackendStatus *local_beentry;
                PgBackendStatus *beentry;
+               PGPROC     *proc;
                Datum           values[PG_STAT_GET_PROGRESS_COLS] = {0};
                bool            nulls[PG_STAT_GET_PROGRESS_COLS] = {0};
                int                     i;
@@ -391,6 +392,23 @@ pg_stat_get_progress_info(PG_FUNCTION_ARGS)
                values[0] = Int32GetDatum(beentry->st_procpid);
                values[1] = ObjectIdGetDatum(beentry->st_databaseid);

+               proc = BackendPidGetProc(beentry->st_procpid);
+               if (proc != NULL && proc->lockGroupLeader != NULL &&
+                       proc->lockGroupLeader->pid != beentry->st_procpid)
+                       values[PGSTAT_NUM_PROGRESS_PARAM + 3] =
+                               Int32GetDatum(proc->lockGroupLeader->pid);
+               else
+                       values[PGSTAT_NUM_PROGRESS_PARAM + 3] =
Int32GetDatum(0);
+
```

That leaves a more interesting question in my mind, which is why
pg_stat_activity puts leader_pid behind permissions at all. It should be
treated just like pid.

There is probably a larger discussion around what should and should not
be permission controlled in pg_stat_activity, and I could not find a
consistent rule. For example, we do not permission control application_name,
which is user controlled free text, yet we do permission control
query_id, which
is not permission controlled elsewhere such as pg_stat_statements. We probably
need a separate thread to clearly lay out the principles for this.

As far as this patch goes, I don't think it should be blocked and it should
continue to emit the leader_pid, but with the idea I shared above
instead of joining with pg_stat_activity.

thoughts?

--
Sami Imseih
Amazon Web Services (AWS)

Hi,

On Wed, Aug 12, 2026 at 2:45 PM Sami Imseih <samimseih@gmail.com> wrote:

Thanks for the updates in v3.

Thanks for taking a look at it.

It turns out, to my surprise, that leader_pid can be NULL if the user
querying pg_stat_progress_vacuum does not have proper privileges, either
pg_read_all_stats or membership in the role running the vacuum.

Nice catch!

I think for this patch we should drop the reliance on pg_stat_activity
and have pg_stat_get_progress_info() emit leader_pid directly and
unconditionally. The aggregation in the view then works regardless of
the caller's privileges.

That's one option. There's another option that I originally proposed
upthread, which is to track the leader_pid directly in the progress
report.

That leaves a more interesting question in my mind, which is why
pg_stat_activity puts leader_pid behind permissions at all. It should be
treated just like pid.

Yes, I looked at the commit (b025f32e0) and the discussion. I think
one of the main reasons was to not let unprivileged users take
ProcArrayLock and scan over the entire PGPROC array via
BackendPidGetProc().

To summarize, we have three options:

1/ Make pg_stat_get_progress_info() report the leader_pid like
pg_stat_get_activity() does.
2/ Make pg_stat_get_activity() itself report the leader_pid just like the pid.
3/ Track the leader_pid via a new progress report param (like the v1
did upthread).

(1) and (2) will let unprivileged users take ProcArrayLock and scan
the entire PGPROC array. (3) although it eats up a new slot in the
progress report, gives the leader pid almost for free. I prefer (3)
for its simplicity and without any additional risks.

Adding Michael Paquier to the thread for any thoughts on this.

There is probably a larger discussion around what should and should not
be permission controlled in pg_stat_activity, and I could not find a
consistent rule. For example, we do not permission control application_name,
which is user controlled free text, yet we do permission control
query_id, which
is not permission controlled elsewhere such as pg_stat_statements. We probably
need a separate thread to clearly lay out the principles for this.

The rule here seems simple. The pid or leader_pid by itself is not
something that requires permission controls, it is what the users will
do to get it that matters. I think this applies to all other params as
well.

Thoughts?

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

Hi,

3/ Track the leader_pid via a new progress report param (like the v1

did upthread).

Yes, this seems like the best way. Have the workers report their leader.

(1) and (2) will let unprivileged users take ProcArrayLock and scan
the entire PGPROC array. (3) although it eats up a new slot in the
progress report, gives the leader pid almost for free. I prefer (3)
for its simplicity and without any additional risks.

Adding Michael Paquier to the thread for any thoughts on this.

There is probably a larger discussion around what should and should not
be permission controlled in pg_stat_activity, and I could not find a
consistent rule. For example, we do not permission control

application_name,

which is user controlled free text, yet we do permission control
query_id, which
is not permission controlled elsewhere such as pg_stat_statements. We

probably

need a separate thread to clearly lay out the principles for this.

The rule here seems simple. The pid or leader_pid by itself is not
something that requires permission controls, it is what the users will
do to get it that matters. I think this applies to all other params as
well.

Thoughts?

I agree. At least the leader_pid should not be permission controlled and we
should
be able to perform the aggregation as we do in v3- at the sql level. Other
fields like
relid, phase, etc. sit behind permission controls and should remain that
way. If there
is different opinion for those fields, that is a separate discussion.

WDYT?

--
Sami

On Thu, Aug 13, 2026 at 07:47:06PM -0500, Sami Imseih wrote:

I agree. At least the leader_pid should not be permission controlled and we
should
be able to perform the aggregation as we do in v3- at the sql level. Other
fields like
relid, phase, etc. sit behind permission controls and should remain that
way. If there
is different opinion for those fields, that is a separate discussion.

WDYT?

That's debatable perhaps, but the leader PID is in the same kind of
category as the wait events: no information derived from a PGPROC
entry should be viewable except for a role with pg_read_all_stats
privileges or if a role is a member of the role whose information is
queried.
--
Michael

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

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]/messages/by-id/CAD21AoAEfCNv-GgaDheDJ+s-p_Lv1H24AiJeNoPGCmZNSwL1YA@mail.gmail.com 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]/messages/by-id/CAH2-Wz=3JGBty=3tXoBoEYYwQNd7fXJuN9oPcnBAj3JYroBv3w@mail.gmail.com.

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]: /messages/by-id/CAD21AoAEfCNv-GgaDheDJ+s-p_Lv1H24AiJeNoPGCmZNSwL1YA@mail.gmail.com
[2]: /messages/by-id/CAH2-Wz=3JGBty=3tXoBoEYYwQNd7fXJuN9oPcnBAj3JYroBv3w@mail.gmail.com

--
Sami Imseih
Amazon Web Services (AWS)

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

My hesitation is this is not really a "progress" view, since it's not showing
progress related data. Just thought I'll put this out there for discussion
as well.

--
Sami

Hi,

On Thu, Aug 13, 2026 at 8:48 PM Michael Paquier <michael@paquier.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

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.

I will look at the rest of the points later in detail, but it does sound
like to me that a new worker view will be a better place to hold extra per
worker ( or leader ) details and the current view will remain high
level/progress data. Unused progress fields do not sound right to me, and I
also worry we will bloat the existing view over time if we want to add more
per worker fields.

--
Sami

Hi,

Thanks Sami for the thoughts. I addressed most of these upthread [1]/messages/by-id/CALj2ACX6gyBmQfaqbCsycDmaSPbq1=iPJw1OqUT+aLqaKjW8dQ@mail.gmail.com,
responding to the remaining ones here.

[1]: /messages/by-id/CALj2ACX6gyBmQfaqbCsycDmaSPbq1=iPJw1OqUT+aLqaKjW8dQ@mail.gmail.com

On Fri, Aug 14, 2026 at 2:26 PM Sami Imseih <samimseih@gmail.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] /messages/by-id/CAD21AoAEfCNv-GgaDheDJ+s-p_Lv1H24AiJeNoPGCmZNSwL1YA@mail.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] /messages/by-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

On Wed, Aug 19, 2026 at 07:37:00PM -0700, Bharath Rupireddy wrote:

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.

Having a single progress view feels like the natural approach here,
for both the leader and the workers. The leader triggers the
existence of the workers, but both leader and workers may finish by
doing the same job as there could be usually little meaning for a
leader to stand idle, waiting for all the workers to do the work.
Such choices are implementation-agnostic, of course; we should not
lock ourselves.
--
Michael

On Wed, Aug 19, 2026 at 07:37:00PM -0700, Bharath Rupireddy wrote:

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.

Having a single progress view feels like the natural approach here,
for both the leader and the workers. The leader triggers the
existence of the workers, but both leader and workers may finish by
doing the same job as there could be usually little meaning for a
leader to stand idle, waiting for all the workers to do the work.
Such choices are implementation-agnostic, of course; we should not
lock ourselves.

This sounds to me that we are ok with breaking the principle design of the
progress views, at least how I understand it, which is that we only have
one row associated with the backend from which the user issued command.
Background workers that are started as a result of that command, and are
transient during the life of the command, don’t fit into that.

Also, If we include workers in a separate row, we will need to mix stats
that are aggregate of the entire command with stats that are specific to
the work that leader done on its own. what would heap_blks_* refer to in
this case? The aggregate of heap blocks accessed by the leader and workers
-or— just the blocks accessed by the leader?

I think monitoring tools/users consuming this view can better deal with the
separation of worker level details ( worker here is also the leader ) in
one view and the aggregate/high level information in the separate existing
view(s) much better than having to deal with documented caveats about what
the stats mean.

--
Sami