On Fri, Mar 27, 2026 at 9:14 PM SATYANARAYANA NARLAPURAM < [email protected]> wrote:
> > 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 > 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 >
