Hi Sami,
On Fri, Mar 27, 2026 at 4:14 PM Sami Imseih <[email protected]> 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
