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