MIME-Version: 1.0
References: 
 <CAA5RZ0s4xjMrB-VAnLccC7kY8d0-4806-Lsac-czJsdA1LXtAw@mail.gmail.com>
In-Reply-To: 
 <CAA5RZ0s4xjMrB-VAnLccC7kY8d0-4806-Lsac-czJsdA1LXtAw@mail.gmail.com>
From: SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com>
Date: Fri, 27 Mar 2026 21:14:01 -0700
Message-ID: 
 <CAHg+QDf2zWKtjt-_hp91a6RKe-qcYNaMpz80wBmZfCpUUduRxA@mail.gmail.com>
Subject: Re: Add pg_stat_autovacuum_priority
To: Sami Imseih <samimseih@gmail.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000a94c0b064e0dd9c0"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHg%2BQDf2zWKtjt-_hp91a6RKe-qcYNaMpz80wBmZfCpUUduRxA%40mail.gmail.com>
Precedence: bulk

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

Hi Sami,


On Fri, Mar 27, 2026 at 4:14=E2=80=AFPM Sami Imseih <samimseih@gmail.com> w=
rote:

> Hi,
>
> This is a quick follow-up to the commit d7965d65f which
> introduced autovacuum prioritization based on a score that
> is the Max of several components, such as vacuum
> thresholds, xid age, etc.
>
> It was also discussed in that thread [1] that we will need
> a view to expose the priority scores, per table in a view.
> This will allow a user to introspect what the autovacuum
> launcher will prioritize next as well as verify tuning
> efforts for autovacuum prioritization; the latter case
> likely being rare.
>
> So after spending time on this today, I am proposing a view
> that returns a line for each relation with information
> about if the table needs autovacuum/autoanalyze, as well as
> scores of each component and the Max score. It looks like
> the below:
>
> ```
> postgres=3D# select * FROM pg_stat_autovacuum_priority;
> -[ RECORD 1 ]-----+----------------------------
> relid             | 16410
> schemaname        | public
> relname           | av_priority_test
> needs_vacuum      | f
> needs_analyze     | f
> wraparound        | f
> score             | 0
> xid_score         | 0
> mxid_score        | 0
> vacuum_dead_score | 0
> vacuum_ins_score  | 0
> analyze_score     | 0
> ```
>
> The function essentially calls relation_needs_vacanalyze()
> with some setup work, such as scanning the catalog with an
> AccessShareLock, etc. and emits the result of this call.
>
> To make this work 0001 introduces a small change to
> relation_needs_vacanalyze() to take in a boolean to force
> the calculation of the score (even if autovacuum is
> disabled for the relation).
>
> 0002 introduces the view with documentation and testing in
> vacuum.c (xid age and mxid age scores are not tested as
> they require xid_wraparound to consume enough XIDs to
> trigger a score, which will cost too much time for a
> regression test).
>
> Find the attached taking the first attempt at this view.
>
> [1] [
> https://www.postgresql.org/message-id/CAApHDvqQN-B2sQov8nsfZOmx-VeJMauSf4=
kLa3A8LsK1tUyBNw%40mail.gmail.com
> ]
>


Thanks for adding this. Applied the patch and the tests passed. I haven't
fully reviewed the patch but have a few comments below:

1. Please ass CFI in the function pg_stat_get_autovacuum_priority, as the
list of tables can be very long

+ while ((tuple =3D heap_getnext(relScan, ForwardScanDirection)) !=3D NULL)
+ {

2. Should we add filtering? The current approach
pg_stat_get_autovacuum_priority does a full catalog scan without any
filters and can be expensive.

3. Please add tests for tables with autovacuum =3D off

4. Is the view intended to be exposed to PUBLIC without any ACL
restrictions?

5. Catalog version number needs to be increased

-#define CATALOG_VERSION_NO 202603241
+#define CATALOG_VERSION_NO 202603231

Thanks,
Satya

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

<div dir=3D"ltr"><div dir=3D"ltr"><br><div>Hi Sami,</div><div><br></div></d=
iv><br><div class=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" cl=
ass=3D"gmail_attr">On Fri, Mar 27, 2026 at 4:14=E2=80=AFPM Sami Imseih &lt;=
<a href=3D"mailto:samimseih@gmail.com">samimseih@gmail.com</a>&gt; wrote:<b=
r></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex=
;border-left:1px solid rgb(204,204,204);padding-left:1ex">Hi,<br>
<br>
This is a quick follow-up to the commit d7965d65f which<br>
introduced autovacuum prioritization based on a score that<br>
is the Max of several components, such as vacuum<br>
thresholds, xid age, etc.<br>
<br>
It was also discussed in that thread [1] that we will need<br>
a view to expose the priority scores, per table in a view.<br>
This will allow a user to introspect what the autovacuum<br>
launcher will prioritize next as well as verify tuning<br>
efforts for autovacuum prioritization; the latter case<br>
likely being rare.<br>
<br>
So after spending time on this today, I am proposing a view<br>
that returns a line for each relation with information<br>
about if the table needs autovacuum/autoanalyze, as well as<br>
scores of each component and the Max score. It looks like<br>
the below:<br>
<br>
```<br>
postgres=3D# select * FROM pg_stat_autovacuum_priority;<br>
-[ RECORD 1 ]-----+----------------------------<br>
relid=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 16410<br>
schemaname=C2=A0 =C2=A0 =C2=A0 =C2=A0 | public<br>
relname=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| av_priority_test<br>
needs_vacuum=C2=A0 =C2=A0 =C2=A0 | f<br>
needs_analyze=C2=A0 =C2=A0 =C2=A0| f<br>
wraparound=C2=A0 =C2=A0 =C2=A0 =C2=A0 | f<br>
score=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 0<br>
xid_score=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| 0<br>
mxid_score=C2=A0 =C2=A0 =C2=A0 =C2=A0 | 0<br>
vacuum_dead_score | 0<br>
vacuum_ins_score=C2=A0 | 0<br>
analyze_score=C2=A0 =C2=A0 =C2=A0| 0<br>
```<br>
<br>
The function essentially calls relation_needs_vacanalyze()<br>
with some setup work, such as scanning the catalog with an<br>
AccessShareLock, etc. and emits the result of this call.<br>
<br>
To make this work 0001 introduces a small change to<br>
relation_needs_vacanalyze() to take in a boolean to force<br>
the calculation of the score (even if autovacuum is<br>
disabled for the relation).<br>
<br>
0002 introduces the view with documentation and testing in<br>
vacuum.c (xid age and mxid age scores are not tested as<br>
they require xid_wraparound to consume enough XIDs to<br>
trigger a score, which will cost too much time for a<br>
regression test).<br>
<br>
Find the attached taking the first attempt at this view.<br>
<br>
[1] [<a href=3D"https://www.postgresql.org/message-id/CAApHDvqQN-B2sQov8nsf=
ZOmx-VeJMauSf4kLa3A8LsK1tUyBNw%40mail.gmail.com" rel=3D"noreferrer" target=
=3D"_blank">https://www.postgresql.org/message-id/CAApHDvqQN-B2sQov8nsfZOmx=
-VeJMauSf4kLa3A8LsK1tUyBNw%40mail.gmail.com</a>]<br></blockquote><div><br><=
/div><div><br class=3D"gmail-Apple-interchange-newline">Thanks for adding t=
his. Applied the patch and the tests passed. I haven&#39;t fully reviewed t=
he patch but have a few comments below:</div><div><br></div><div>1. Please =
ass CFI in the function=C2=A0pg_stat_get_autovacuum_priority, as the list o=
f tables can be very long</div><div><br></div><div>+	while ((tuple =3D heap=
_getnext(relScan, ForwardScanDirection)) !=3D NULL)<br>+	{</div><div><br></=
div><div>2. Should we add filtering? The current approach pg_stat_get_autov=
acuum_priority does a full catalog scan without any filters and can be expe=
nsive.</div><div><br></div><div>3. Please add tests for tables with autovac=
uum =3D off</div><div><br></div><div>4. Is the=C2=A0view intended to be exp=
osed to PUBLIC without any ACL restrictions?</div><div><br></div><div>5. Ca=
talog version number needs to be increased</div><div><br></div><div>-#defin=
e CATALOG_VERSION_NO	202603241<br></div><div>+#define CATALOG_VERSION_NO	20=
2603231</div><div><br></div><div>Thanks,</div><div>Satya=C2=A0</div></div><=
/div>

--000000000000a94c0b064e0dd9c0--