MIME-Version: 1.0
References: 
 <CALj2ACUgwSchK6jQ2CdKLBWUADTOE_zKdTff2Zg3E6hOuXKv-w@mail.gmail.com>
In-Reply-To: 
 <CALj2ACUgwSchK6jQ2CdKLBWUADTOE_zKdTff2Zg3E6hOuXKv-w@mail.gmail.com>
From: SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com>
Date: Sun, 3 May 2026 21:53:04 -0700
Message-ID: 
 <CAHg+QDfEfBgQ4d-ByBx4ToCQMaB83=i-+aSBROfh6cqoGG74Ew@mail.gmail.com>
Subject: Re: Report index currently being vacuumed in pg_stat_progress_vacuum
To: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000005a14fd0650f6b556"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHg%2BQDfEfBgQ4d-ByBx4ToCQMaB83%3Di-%2BaSBROfh6cqoGG74Ew%40mail.gmail.com>
Precedence: bulk

--0000000000005a14fd0650f6b556
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi,

On Sun, May 3, 2026 at 7:01=E2=80=AFPM 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 specifi=
c
> 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 th=
e
> 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 =3D v.relid
> LEFT JOIN pg_class ic
>     ON ic.oid =3D v.current_index_relid
> WHERE v.relid =3D $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

--0000000000005a14fd0650f6b556
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">Hi,</div><br><div class=3D"gmail_quote gm=
ail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Sun, May 3, 2=
026 at 7:01=E2=80=AFPM Bharath Rupireddy &lt;<a href=3D"mailto:bharath.rupi=
reddyforpostgres@gmail.com">bharath.rupireddyforpostgres@gmail.com</a>&gt; =
wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0=
px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=
=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr">Hi,<br><br>When VACUUM is in the=
 &quot;vacuuming indexes&quot; or &quot;cleaning up indexes&quot; phase, th=
ere is currently no easy way to tell which specific index is being processe=
d. The progress report view shows indexes_total and indexes_processed count=
ers, but not which index is actively being worked on.<br><br>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 d=
elay.<br><br>Please find the attached patch adds a new column current_index=
_relid to pg_stat_progress_vacuum that reports the OID of the index current=
ly being vacuumed or cleaned up. The column is reported for both the &quot;=
vacuuming indexes&quot; phase and the &quot;cleaning up indexes&quot; phase=
.<br><br>When indexes are being vacuumed in parallel, each parallel worker =
emits its own row in pg_stat_progress_vacuum with current_index_relid set t=
o the index it is currently processing, and leader_pid pointing to the lead=
er process.<br><br>Appreciate any feedback. Thank you!<br><br>[1] Example o=
utput:<br><br><font face=3D"monospace">=C2=A0pid =C2=A0| datname =C2=A0| re=
lid | table_name | =C2=A0 =C2=A0 =C2=A0 phase =C2=A0 =C2=A0 =C2=A0 | starte=
d_by | current_index_relid | =C2=A0index_name =C2=A0 | leader_pid <br>-----=
-+----------+-------+------------+-------------------+------------+--------=
-------------+---------------+------------<br>=C2=A01420 | postgres | 16395=
 | vac_test =C2=A0 | vacuuming indexes | autovacuum | =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 16398 | vac_test_idx1 | =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0<br>=C2=A01421 | postgres | 16395 | vac_test =C2=A0 | vacuumin=
g indexes | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0=
 =C2=A0 =C2=A0 =C2=A0 =C2=A0 16399 | vac_test_idx2 | =C2=A0 =C2=A0 =C2=A0 1=
420<br>=C2=A01423 | postgres | 16395 | vac_test =C2=A0 | vacuuming indexes =
| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 16400 | vac_test_idx3 | =C2=A0 =C2=A0 =C2=A0 1420<br>(=
3 rows)<br></font><br><font face=3D"monospace">=C2=A0pid =C2=A0| datname =
=C2=A0| relid | table_name | =C2=A0 =C2=A0 =C2=A0 phase =C2=A0 =C2=A0 =C2=
=A0 | started_by | current_index_relid | =C2=A0index_name =C2=A0 | leader_p=
id <br>------+----------+-------+------------+-------------------+---------=
---+---------------------+---------------+------------<br>=C2=A01346 | post=
gres | 16395 | vac_test =C2=A0 | vacuuming indexes | manual =C2=A0 =C2=A0 |=
 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 16398 | vac_test_idx1 | =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0<br>(1 row)</font><br><br>[2]<br><font fa=
ce=3D"monospace">SELECT v.pid, v.datname, v.relid, c.relname AS table_name,=
<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0v.phase, v.started_by, v.current_index_relid=
,<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0COALESCE(ic.relname, &#39;&#39;) AS index_n=
ame, v.leader_pid<br>FROM pg_stat_progress_vacuum v<br>JOIN pg_class c<br>=
=C2=A0 =C2=A0 ON c.oid =3D v.relid<br>LEFT JOIN pg_class ic<br>=C2=A0 =C2=
=A0 ON ic.oid =3D v.current_index_relid<br>WHERE v.relid =3D $tbl_oid<br>OR=
DER BY<br>=C2=A0 =C2=A0 v.leader_pid,<br>=C2=A0 =C2=A0 v.pid;</font></div><=
/div></div></blockquote><div><br></div><div>Bharath, thanks for the patch! =
A few comments:</div><div><br></div><div>=C2=A0(1) Do we need a global API?=
 Can we add a leader_pid field in PVShared?</div><div><br></div><div>+pid_t=
<br>+GetParallelLeaderPid(void)<br>+{<br>+	return ParallelLeaderPid;<br>+}<=
/div><div><br></div><div>(2):=C2=A0 Looks like current_index_relid is not c=
leared when we leave the index phases.As a result, once any index has been =
processed,=C2=A0</div><div>pg_stat_progress_vacuum.current_index_relid keep=
s reporting that relid through vacuuming heap, truncating heap, cleaning up=
 indexes.=C2=A0</div><div>This will be confusing to the user. Something lik=
e below:</div><div><br></div><div>1795819|vacuuming heap|0/0|16392|t1_pkey|=
LEADER</div><div><br></div><div>(3)=C2=A0leader_pid type should be integer =
type similar to pg_Stat_activity?</div><div><br></div><div>Thanks,</div><di=
v>Satya</div></div></div>

--0000000000005a14fd0650f6b556--