MIME-Version: 1.0
References: 
 <CAA5RZ0s4xjMrB-VAnLccC7kY8d0-4806-Lsac-czJsdA1LXtAw@mail.gmail.com>
 <CAHg+QDf2zWKtjt-_hp91a6RKe-qcYNaMpz80wBmZfCpUUduRxA@mail.gmail.com>
In-Reply-To: 
 <CAHg+QDf2zWKtjt-_hp91a6RKe-qcYNaMpz80wBmZfCpUUduRxA@mail.gmail.com>
From: SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com>
Date: Fri, 27 Mar 2026 21:19:55 -0700
Message-ID: 
 <CAHg+QDcGoV_hFcysQAFOGGJe2k2YSZ=1n30VDdcqo4OvCUEo2Q@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="000000000000bbf636064e0deeb3"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHg%2BQDcGoV_hFcysQAFOGGJe2k2YSZ%3D1n30VDdcqo4OvCUEo2Q%40mail.gmail.com>
Precedence: bulk

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

On Fri, Mar 27, 2026 at 9:14=E2=80=AFPM SATYANARAYANA NARLAPURAM <
satyanarlapuram@gmail.com> wrote:

>
> Hi Sami,
>
>
> On Fri, Mar 27, 2026 at 4:14=E2=80=AFPM Sami Imseih <samimseih@gmail.com>=
 wrote:
>
>> 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-VeJMauSf=
4kLa3A8LsK1tUyBNw%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 NUL=
L)
> + {
>
> 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
>

Additionally, do you expect this view to be available on the hot_Standby?
Because on a hot standby, the view only provides useful wraparound risk
data. All activity-based columns are blind. This should either be
documented, or the function should check RecoveryInProgress() and raise an
error/notice

Thanks,
Satya

>

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote g=
mail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Fri, Mar 27,=
 2026 at 9:14=E2=80=AFPM SATYANARAYANA NARLAPURAM &lt;<a href=3D"mailto:sat=
yanarlapuram@gmail.com">satyanarlapuram@gmail.com</a>&gt; wrote:<br></div><=
blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-l=
eft:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div dir=
=3D"ltr"><br><div>Hi Sami,</div><div><br></div></div><br><div class=3D"gmai=
l_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Fri, Mar 27, 2026 at 4:14=
=E2=80=AFPM Sami Imseih &lt;<a href=3D"mailto:samimseih@gmail.com" target=
=3D"_blank">samimseih@gmail.com</a>&gt; wrote:<br></div><blockquote class=
=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg=
b(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>Thanks for adding this. Applied the patch and the tests passe=
d. I haven&#39;t fully reviewed the patch but have a few comments below:</d=
iv><div><br></div><div>1. Please ass CFI in the function=C2=A0pg_stat_get_a=
utovacuum_priority, as the list of 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 c=
urrent approach pg_stat_get_autovacuum_priority does a full catalog scan wi=
thout any filters and can be expensive.</div><div><br></div><div>3. Please =
add tests for tables with autovacuum =3D off</div><div><br></div><div>4. Is=
 the=C2=A0view intended to be exposed to PUBLIC without any ACL restriction=
s?</div><div><br></div><div>5. Catalog version number needs to be increased=
</div><div><br></div><div>-#define CATALOG_VERSION_NO	202603241<br></div><d=
iv>+#define CATALOG_VERSION_NO	202603231</div></div></div></blockquote><div=
><br></div><div>Additionally, do you expect this view to be available on th=
e hot_Standby? Because on a hot standby, the view only provides useful wrap=
around risk data. All activity-based columns are blind. This should either =
be documented, or the function should check RecoveryInProgress() and raise =
an error/notice</div><div><br></div><div>Thanks,</div><div>Satya</div><bloc=
kquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:=
1px solid rgb(204,204,204);padding-left:1ex">
</blockquote></div></div>

--000000000000bbf636064e0deeb3--