Re: Add pg_stat_autovacuum_priority

public inbox for [email protected]  
help / color / mirror / Atom feed

From: Robert Treat <[email protected]>
To: Sami Imseih <[email protected]>
Cc: Bharath Rupireddy <[email protected]>
Cc: [email protected]
Cc: pgsql-hackers <[email protected]>
Subject: Re: Add pg_stat_autovacuum_priority
Date: Mon, 30 Mar 2026 13:13:06 -0400
Message-ID: <CABV9wwNBifXpOjxO9rGn1HHK=DG02qApVurWSHa+rDzPriK6pA@mail.gmail.com> (raw)
In-Reply-To: <CAA5RZ0vMp2B3UBUoqLVedy8G3u8_O8M11+Y5V7uZv3++CGYasg@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>
	<CABV9wwM3nRitsgUxeCF0ywbAaLZV5jWC-9tj6WKxkdmQkHRcWg@mail.gmail.com>
	<CAA5RZ0vMp2B3UBUoqLVedy8G3u8_O8M11+Y5V7uZv3++CGYasg@mail.gmail.com>

On Mon, Mar 30, 2026 at 11:17 AM Sami Imseih <[email protected]> wrote:
> > 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?
>
> Yes, that is possible. MAINTAIN is a per-object privilege granted on a relation,
> whereas pg_read_all_stats is a global role membership. They operate at
> different levels.
>
> I don't think one needs to have MAINTAIN permissions on the table to see the
> autovacuum score. DBA Monitoring users are usually separate from the DBA
> operational users.
>
> I think pg_read_all_stats is the right permission here and it should
> be implemented
> similar to how pg_get_shmem_allocations is done where the default permissions
> are pg_read_all_stats. pg_monitor inherits pg_read_all_stats so any
> user with this
> privilege will be able to access this view. A DBA is free to also add
> privileges to
> to other users if they wish.
>
> This is unlike other pg_stat_* views that have tuple level permission
> checks ( i.e.
> pg_stat_activity), but in those cases the permissions are needed to
> hide sensitive data.
> This is not the case here.
>

I don't think we are in disagreement here, I was just thinking about
it the other way round; someone might have MAINTAIN privileges on a
table and want to see what the relevant "autovacuum score" is before
taking action. If the solution for that is to give those roles
pg_read_all_stats, I guess that's ok, but there was probably a reason
the permissions were limited in the first place. *shrug*

> > > 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.
>
> Yeah, after sleeping on it I actually think the most common case will likely be
> ORDER BY score DESC LIMIT ... because you usually want to see where your
> table priority is relative to everything else in the database.
> For the rare case where someone wants to look up an individual table, the caller
> can just use a WHERE clause. So, we should just always do the full pg_class
> scan. I don't see why we need to complicate the c-function more than this.
>

I think we are also in agreement here, although based on my
experience, filtering out things like system and toast tables will be
common, but I don't see that changing what you said above.  On a
similar note, +1 to your changes in v2.


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: <CABV9wwNBifXpOjxO9rGn1HHK=DG02qApVurWSHa+rDzPriK6pA@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