MIME-Version: 1.0
References: 
 <CAA5RZ0s4xjMrB-VAnLccC7kY8d0-4806-Lsac-czJsdA1LXtAw@mail.gmail.com>
 <CALj2ACXcmYsoJXTYtuGhkxNVXV5jtTNfL-vB+sQq-jE9__N5AA@mail.gmail.com>
 <CAA5RZ0surzz41exF5QwecuFU8NqZVRR5aDnC6MObeEcsXhfu4Q@mail.gmail.com>
In-Reply-To: 
 <CAA5RZ0surzz41exF5QwecuFU8NqZVRR5aDnC6MObeEcsXhfu4Q@mail.gmail.com>
From: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>
Date: Sun, 29 Mar 2026 19:09:03 -0700
Message-ID: 
 <CALj2ACX+SRgv2RO9Oo4Me-zzMMjHVg8rf-MqvMRbp9=1ioWbsg@mail.gmail.com>
Subject: Re: Add pg_stat_autovacuum_priority
To: Sami Imseih <samimseih@gmail.com>
Cc: satyanarlapuram@gmail.com, 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/CALj2ACX%2BSRgv2RO9Oo4Me-zzMMjHVg8rf-MqvMRbp9%3D1ioWbsg%40mail.gmail.com>
Precedence: bulk

Hi,

On Sat, Mar 28, 2026 at 10:54=E2=80=AFAM Sami Imseih <samimseih@gmail.com> =
wrote:
>
> > 4. Is the view intended to be exposed to PUBLIC without any ACL restric=
tions?
>
> > 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.
>
> I think you're correct there. While the data is not sensitive, it
> should have more controlled usage. It's only taking an AccessShareLock,
> but you would not want anyone to be able to run this since it's
> doing real computation. I think requiring pg_read_all_stats
> is a good idea. Will do.

+1 for pg_read_all_stats.

> > 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?
>
> Yes, perhaps we should do this. So we can have a function called
> pg_stat_get_autovacuum_priority() that either takes a NULL or an OID
> to either return all the tables or just a single table.
> This is a similar usage pattern as pg_stat_get_subscription or
> pg_stat_get_activity.
>
> pg_stat_autovacuum_priority will be a view that wraps around the NULL
> variant of the function.
>
> The case where the OID is passed we just do a SearchSysCache1(RELOID,...)
> whereas the other case will do the full catalog scan.
>
> What do you think?

IMHO, we can have pg_stat_get_relation_autovacuum_priority defined as
a C function to give the autovacuum scoring as of the given moment for
the given table. It's easy for one to write a function to get scoring
for all the relations in a database. This keeps things simple yet
useful.

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