Re: Add pg_stat_autovacuum_priority

Hi Sami,

On Fri, Mar 27, 2026 at 4:14 PM Sami Imseih <samimseih(at)gmail(dot)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

Thanks,
Satya