Add pg_stat_autovacuum_priority

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.

[1] [https://www.postgresql.org/message-id/CAApHDvqQN-B2sQov8nsfZOmx-VeJMauSf4kLa3A8LsK1tUyBNw%40mail.gmail.com]

--
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

Responses

Browse pgsql-hackers by date

  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