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] -- Sami Imseih Amazon Web Services (AWS)
v1-0002-Add-pg_stat_autovacuum_priority-view.patch
Description: Binary data
v1-0001-Add-force_scores-option-to-relation_needs_vacanal.patch
Description: Binary data
