public inbox for [email protected]  

help / color / mirror / Atom feed

From: SATYANARAYANA NARLAPURAM <[email protected]>
To: Sami Imseih <[email protected]>
Cc: pgsql-hackers <[email protected]>
Subject: Re: Add pg_stat_autovacuum_priority
Date: Fri, 27 Mar 2026 21:14:01 -0700
Message-ID: <CAHg+QDf2zWKtjt-_hp91a6RKe-qcYNaMpz80wBmZfCpUUduRxA@mail.gmail.com> (raw)
In-Reply-To: <CAA5RZ0s4xjMrB-VAnLccC7kY8d0-4806-Lsac-czJsdA1LXtAw@mail.gmail.com>
References: <CAA5RZ0s4xjMrB-VAnLccC7kY8d0-4806-Lsac-czJsdA1LXtAw@mail.gmail.com>

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.gma...
> ]
>


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

view thread (60+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Add pg_stat_autovacuum_priority
  In-Reply-To: <CAHg+QDf2zWKtjt-_hp91a6RKe-qcYNaMpz80wBmZfCpUUduRxA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox