Обсуждение: Report index currently being vacuumed in pg_stat_progress_vacuum
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 Rupireddy
Amazon Web Services: https://aws.amazon.com
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 Rupireddy
Amazon Web Services: https://aws.amazon.com
Вложения
Re: Report index currently being vacuumed in pg_stat_progress_vacuum
От
SATYANARAYANA NARLAPURAM
Дата:
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;
+}
+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 whichspecific index is > being processed. The progress report view shows indexes_total and indexes_processed counters, but not which index is activelybeing 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 ofthe index > currently being vacuumed or cleaned up. The column is reported for both the "vacuuming indexes" phase and the "cleaningup 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)