MIME-Version: 1.0
References: 
 <CAA5RZ0s4xjMrB-VAnLccC7kY8d0-4806-Lsac-czJsdA1LXtAw@mail.gmail.com>
In-Reply-To: 
 <CAA5RZ0s4xjMrB-VAnLccC7kY8d0-4806-Lsac-czJsdA1LXtAw@mail.gmail.com>
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Sat, 28 Mar 2026 02:18:34 -0700
Message-ID: 
 <CALj2ACXcmYsoJXTYtuGhkxNVXV5jtTNfL-vB+sQq-jE9__N5AA@mail.gmail.com>
Subject: Re: Add pg_stat_autovacuum_priority
To: Sami Imseih <samimseih@gmail.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CALj2ACXcmYsoJXTYtuGhkxNVXV5jtTNfL-vB%2BsQq-jE9__N5AA%40mail.gmail.com>
Precedence: bulk

Hi,

On Fri, Mar 27, 2026 at 4:14=E2=80=AFPM Sami Imseih <samimseih@gmail.com> w=
rote:
>
> 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.

Thanks for sending the patch.

+1 for the visibility into the new autovacuum scoring system and its
impact on prioritization. It would also be nice to add the computed
scores to pg_stat_progress_vacuum to show the current prioritization.
IMHO, we can get that change first. It's relatively smaller.

I quickly reviewed the v1 patches. Here are some comments:

1/
+ * pg_stat_get_autovacuum_priority
+ * Returns the autovacuum priority score for each relation in the
+ * current database.
+ *
Can we have the per-relation prioritization computation function in C
and provide a per-database computation function as a SQL function over
this per-relation function in system_functions.sql? This would
simplify things and also address the concern raised by Satya in this
thread about databases having a large number of relations. Would that
work?

2/ Do we need to revoke permissions on pg_stat_get_autovacuum_priority
for all and grant them to pg_monitor or similar? Especially since this
function loops over all the relations in a database, we may not want
everyone to be able to do this.

--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com