Re: Add pg_stat_autovacuum_priority

> On Sun, Mar 29, 2026 at 10:09 PM Bharath Rupireddy
> <bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:
> > On Sat, Mar 28, 2026 at 10:54 AM Sami Imseih <samimseih(at)gmail(dot)com> 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.

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

Attached v2 implements it as above.

A few other things in v2:

1/ I set autovacuum_enabled = OFF in the tests. This will make
sure the test is both stable and will also test that the score is
returned even in
the case where autovacuum is disabled.

2/ Moved pg_stat_autovacuum_priority to the end of the docs in
"Monitoring Database Activity".

3/ Also added a mention of the extremely high score values when failsafe
is triggered [1]

[1] https://www.postgresql.org/message-id/abGP87A3JPIXDG2I%40nathan

--
Sami