| From: | Sami Imseih <samimseih(at)gmail(dot)com> |
|---|---|
| To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
| Subject: | Add pg_stat_autovacuum_priority |
| Date: | 2026-03-27 23:14:14 |
| Message-ID: | CAA5RZ0s4xjMrB-VAnLccC7kY8d0-4806-Lsac-czJsdA1LXtAw@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
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.
--
Sami Imseih
Amazon Web Services (AWS)
| Attachment | Content-Type | Size |
|---|---|---|
| v1-0002-Add-pg_stat_autovacuum_priority-view.patch | application/octet-stream | 19.5 KB |
| v1-0001-Add-force_scores-option-to-relation_needs_vacanal.patch | application/octet-stream | 7.0 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Heikki Linnakangas | 2026-03-27 23:17:13 | Re: Better shared data structure management and resizable shared data structures |
| Previous Message | Tomas Vondra | 2026-03-27 23:13:40 | Re: Changing the state of data checksums in a running cluster |