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
From 556d8c4a8db1b1ee23f19631e8c94ac702a26042 Mon Sep 17 00:00:00 2001
From: Bharath Rupireddy <[email protected]>
Date: Fri, 17 Apr 2026 22:11:20 +0000
Subject: [PATCH v1] Report index currently being vacuumed in
pg_stat_progress_vacuum
When VACUUM is in the "vacuuming indexes" or "cleaning up indexes"
phase, there is no way to tell which specific index is being
processed. The 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 (btree, GIN, GiST, BRIN, HNSW,
etc.), since one cannot determine which index type or which specific
index is causing the delay.
This commit 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 value is set before each index
vacuum/cleanup begins and reset to 0 when all indexes have been
processed.
The column is reported for both the "vacuuming indexes" phase and
the "cleaning up indexes" phase.
When indexes are 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 processing, and leader_pid set to the PID of the
leader. For the leader process itself (or non-parallel vacuum),
leader_pid is NULL.
---
doc/src/sgml/monitoring.sgml | 26 ++++++++++++++++++++++++++
src/backend/access/heap/vacuumlazy.c | 16 ++++++++++++++++
src/backend/access/transam/parallel.c | 9 +++++++++
src/backend/catalog/system_views.sql | 4 +++-
src/backend/commands/vacuumparallel.c | 21 +++++++++++++++++++++
src/include/access/parallel.h | 1 +
src/include/commands/progress.h | 2 ++
src/test/regress/expected/rules.out | 4 +++-
8 files changed, 81 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 08d5b824552..0a7223608e6 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -7577,6 +7577,32 @@ FROM pg_stat_get_backend_idset() AS backendid;
</itemizedlist>
</para></entry>
</row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>current_index_relid</structfield> <type>oid</type>
+ </para>
+ <para>
+ If <command>VACUUM</command> is currently processing an index, this
+ column shows the OID of the index being vacuumed. The value is set
+ when the phase is <literal>vacuuming indexes</literal> or
+ <literal>cleaning up indexes</literal> and is reset to 0 when all
+ indexes have been processed. During parallel index vacuum, each
+ parallel worker row shows the index that particular worker is
+ processing.
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>leader_pid</structfield> <type>bigint</type>
+ </para>
+ <para>
+ Process ID of the leader process if this process is a parallel
+ vacuum worker; <literal>NULL</literal> if this process is the
+ leader itself or if the vacuum is not running in parallel.
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 39395aed0d5..ea040036251 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -3025,6 +3025,10 @@ lazy_vacuum_one_index(Relation indrel, IndexBulkDeleteResult *istat,
ivinfo.num_heap_tuples = reltuples;
ivinfo.strategy = vacrel->bstrategy;
+ /* Report which index we're currently processing */
+ pgstat_progress_update_param(PROGRESS_VACUUM_CURRENT_INDEX_RELID,
+ (int64) RelationGetRelid(indrel));
+
/*
* Update error traceback information.
*
@@ -3046,6 +3050,10 @@ lazy_vacuum_one_index(Relation indrel, IndexBulkDeleteResult *istat,
pfree(vacrel->indname);
vacrel->indname = NULL;
+ /* Reset the current index relid to avoid reporting a stale value */
+ pgstat_progress_update_param(PROGRESS_VACUUM_CURRENT_INDEX_RELID,
+ (int64) InvalidOid);
+
return istat;
}
@@ -3076,6 +3084,10 @@ lazy_cleanup_one_index(Relation indrel, IndexBulkDeleteResult *istat,
ivinfo.num_heap_tuples = reltuples;
ivinfo.strategy = vacrel->bstrategy;
+ /* Report which index we're currently processing */
+ pgstat_progress_update_param(PROGRESS_VACUUM_CURRENT_INDEX_RELID,
+ (int64) RelationGetRelid(indrel));
+
/*
* Update error traceback information.
*
@@ -3095,6 +3107,10 @@ lazy_cleanup_one_index(Relation indrel, IndexBulkDeleteResult *istat,
pfree(vacrel->indname);
vacrel->indname = NULL;
+ /* Reset the current index relid to avoid reporting a stale value */
+ pgstat_progress_update_param(PROGRESS_VACUUM_CURRENT_INDEX_RELID,
+ (int64) InvalidOid);
+
return istat;
}
diff --git a/src/backend/access/transam/parallel.c b/src/backend/access/transam/parallel.c
index 89e9d224eec..23a2e5eecf0 100644
--- a/src/backend/access/transam/parallel.c
+++ b/src/backend/access/transam/parallel.c
@@ -131,6 +131,15 @@ static dlist_head pcxt_list = DLIST_STATIC_INIT(pcxt_list);
/* Backend-local copy of data from FixedParallelState. */
static pid_t ParallelLeaderPid;
+/*
+ * Return the PID of the parallel group leader.
+ */
+pid_t
+GetParallelLeaderPid(void)
+{
+ return ParallelLeaderPid;
+}
+
/*
* List of internal parallel worker entry points. We need this for
* reasons explained in LookupParallelWorkerFunction(), below.
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 73a1c1c4670..22e9294bacc 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -1342,7 +1342,9 @@ CREATE VIEW pg_stat_progress_vacuum AS
CASE S.param13 WHEN 1 THEN 'manual'
WHEN 2 THEN 'autovacuum'
WHEN 3 THEN 'autovacuum_wraparound'
- ELSE NULL END AS started_by
+ ELSE NULL END AS started_by,
+ CAST(S.param14 AS oid) AS current_index_relid,
+ NULLIF(S.param15, 0) AS leader_pid
FROM pg_stat_get_progress_info('VACUUM') AS S
LEFT JOIN pg_database D ON S.datid = D.oid;
diff --git a/src/backend/commands/vacuumparallel.c b/src/backend/commands/vacuumparallel.c
index 979c2be4abd..613b379a3ca 100644
--- a/src/backend/commands/vacuumparallel.c
+++ b/src/backend/commands/vacuumparallel.c
@@ -36,6 +36,7 @@
#include "postgres.h"
#include "access/amapi.h"
+#include "access/parallel.h"
#include "access/table.h"
#include "access/xact.h"
#include "commands/progress.h"
@@ -1076,6 +1077,11 @@ parallel_vacuum_process_one_index(ParallelVacuumState *pvs, Relation indrel,
IndexBulkDeleteResult *istat = NULL;
IndexBulkDeleteResult *istat_res;
IndexVacuumInfo ivinfo;
+ const int progress_index[] = {
+ PROGRESS_VACUUM_PHASE,
+ PROGRESS_VACUUM_CURRENT_INDEX_RELID
+ };
+ int64 progress_val[2];
/*
* Update the pointer to the corresponding bulk-deletion result if someone
@@ -1112,6 +1118,13 @@ parallel_vacuum_process_one_index(ParallelVacuumState *pvs, Relation indrel,
RelationGetRelationName(indrel));
}
+ /* Report which index we're currently processing and the current phase */
+ progress_val[0] = (indstats->status == PARALLEL_INDVAC_STATUS_NEED_BULKDELETE)
+ ? PROGRESS_VACUUM_PHASE_VACUUM_INDEX
+ : PROGRESS_VACUUM_PHASE_INDEX_CLEANUP;
+ progress_val[1] = (int64) RelationGetRelid(indrel);
+ pgstat_progress_update_multi_param(2, progress_index, progress_val);
+
/*
* Copy the index bulk-deletion result returned from ambulkdelete and
* amvacuumcleanup to the DSM segment if it's the first cycle because they
@@ -1307,6 +1320,11 @@ parallel_vacuum_main(dsm_segment *seg, shm_toc *toc)
/* Prepare to track buffer usage during parallel execution */
InstrStartParallelQuery();
+ /* Register this worker for vacuum progress reporting */
+ pgstat_progress_start_command(PROGRESS_COMMAND_VACUUM, shared->relid);
+ pgstat_progress_update_param(PROGRESS_VACUUM_LEADER_PID,
+ (int64) GetParallelLeaderPid());
+
/* Process indexes to perform vacuum/cleanup */
parallel_vacuum_process_safe_indexes(&pvs);
@@ -1326,6 +1344,9 @@ parallel_vacuum_main(dsm_segment *seg, shm_toc *toc)
/* Pop the error context stack */
error_context_stack = errcallback.previous;
+ /* Unregister this worker from vacuum progress reporting */
+ pgstat_progress_end_command();
+
vac_close_indexes(nindexes, indrels, RowExclusiveLock);
table_close(rel, ShareUpdateExclusiveLock);
FreeAccessStrategy(pvs.bstrategy);
diff --git a/src/include/access/parallel.h b/src/include/access/parallel.h
index 60f857675e0..d329a2424bd 100644
--- a/src/include/access/parallel.h
+++ b/src/include/access/parallel.h
@@ -77,6 +77,7 @@ extern void ProcessParallelMessages(void);
extern void AtEOXact_Parallel(bool isCommit);
extern void AtEOSubXact_Parallel(bool isCommit, SubTransactionId mySubId);
extern void ParallelWorkerReportLastRecEnd(XLogRecPtr last_xlog_end);
+extern pid_t GetParallelLeaderPid(void);
extern void ParallelWorkerMain(Datum main_arg);
diff --git a/src/include/commands/progress.h b/src/include/commands/progress.h
index 2a12920c75f..f9f3e187718 100644
--- a/src/include/commands/progress.h
+++ b/src/include/commands/progress.h
@@ -31,6 +31,8 @@
#define PROGRESS_VACUUM_DELAY_TIME 10
#define PROGRESS_VACUUM_MODE 11
#define PROGRESS_VACUUM_STARTED_BY 12
+#define PROGRESS_VACUUM_CURRENT_INDEX_RELID 13
+#define PROGRESS_VACUUM_LEADER_PID 14
/* Phases of vacuum (as advertised via PROGRESS_VACUUM_PHASE) */
#define PROGRESS_VACUUM_PHASE_SCAN_HEAP 1
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index a65a5bf0c4f..00c0af6c24c 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2202,7 +2202,9 @@ pg_stat_progress_vacuum| SELECT s.pid,
WHEN 2 THEN 'autovacuum'::text
WHEN 3 THEN 'autovacuum_wraparound'::text
ELSE NULL::text
- END AS started_by
+ END AS started_by,
+ (s.param14)::oid AS current_index_relid,
+ NULLIF(s.param15, 0) AS leader_pid
FROM (pg_stat_get_progress_info('VACUUM'::text) s(pid, datid, relid, param1, param2, param3, param4, param5, param6, param7, param8, param9, param10, param11, param12, param13, param14, param15, param16, param17, param18, param19, param20)
LEFT JOIN pg_database d ON ((s.datid = d.oid)));
pg_stat_recovery| SELECT promote_triggered,
--
2.47.3
