public inbox for [email protected]
help / color / mirror / Atom feedFrom: Robert Treat <[email protected]>
To: Bharath Rupireddy <[email protected]>
Cc: Sami Imseih <[email protected]>
Cc: [email protected]
Cc: pgsql-hackers <[email protected]>
Subject: Re: Add pg_stat_autovacuum_priority
Date: Mon, 30 Mar 2026 08:56:12 -0400
Message-ID: <CABV9wwM3nRitsgUxeCF0ywbAaLZV5jWC-9tj6WKxkdmQkHRcWg@mail.gmail.com> (raw)
In-Reply-To: <CALj2ACX+SRgv2RO9Oo4Me-zzMMjHVg8rf-MqvMRbp9=1ioWbsg@mail.gmail.com>
References: <CAA5RZ0s4xjMrB-VAnLccC7kY8d0-4806-Lsac-czJsdA1LXtAw@mail.gmail.com>
<CALj2ACXcmYsoJXTYtuGhkxNVXV5jtTNfL-vB+sQq-jE9__N5AA@mail.gmail.com>
<CAA5RZ0surzz41exF5QwecuFU8NqZVRR5aDnC6MObeEcsXhfu4Q@mail.gmail.com>
<CALj2ACX+SRgv2RO9Oo4Me-zzMMjHVg8rf-MqvMRbp9=1ioWbsg@mail.gmail.com>
On Sun, Mar 29, 2026 at 10:09 PM Bharath Rupireddy
<[email protected]> wrote:
> On Sat, Mar 28, 2026 at 10:54 AM Sami Imseih <[email protected]> wrote:
> >
> > > 4. Is the view intended to be exposed to PUBLIC without any ACL restrictions?
> >
> > > 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.
>
Is there a gap here where someone may have been granted MAINTAIN on a
relation but they do not have 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.
>
I don't have a strong opinion on the above, but I do suspect that the
most common way people will interact with this is by querying against
the view with a WHERE clause, so optimizing for that case seems
important.
Robert Treat
https://xzilla.net
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], [email protected], [email protected]
Subject: Re: Add pg_stat_autovacuum_priority
In-Reply-To: <CABV9wwM3nRitsgUxeCF0ywbAaLZV5jWC-9tj6WKxkdmQkHRcWg@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