Re: Add pg_stat_autovacuum_priority

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

>
> Hi Sami,
>
>
> On Fri, Mar 27, 2026 at 4:14 PM 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=# 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-VeJMauSf4kLa3A8LsK1tUyBNw%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 = heap_getnext(relScan, ForwardScanDirection)) != 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 = 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

>