Thread

Re: Report index currently being vacuumed in pg_stat_progress_vacuum

SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com> — 2026-05-04T04:53:04Z

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