Horton
Menu

Re: Add pg_stat_autovacuum_priority

Sami Imseih <samimseih@gmail.com>

View thread
From: Sami Imseih <samimseih@gmail.com>
To: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>, satyanarlapuram@gmail.com
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2026-03-28T17:53:52Z
Lists: pgsql-hackers 
Hi,

Thank you all for the review and comments!

> 1. Please add CFI in the function pg_stat_get_autovacuum_priority, as the list of tables can be very long
good catch. Will add.


> 3. Please add tests for tables with autovacuum = off
Yes, I will add this test as well.


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


> 5. Catalog version number needs to be increased
This will be left to the committer at the time.

> 2. Should we add filtering? The current approach pg_stat_get_autovacuum_priority
> does a full catalog scan without any filters and can be expensive.

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

--
Sami

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Fix double-free in pg_stat_autovacuum_scores.

  2. Add LOG_NEVER error level code.

  3. Add pg_stat_autovacuum_scores system view.

  4. Remove recheck_relation_needs_vacanalyze().

  5. Add elevel parameter to relation_needs_vacanalyze().

  6. Teach relation_needs_vacanalyze() to always compute scores.

  7. Refactor relation_needs_vacanalyze().

  8. Add rudimentary table prioritization to autovacuum.

  9. Fix per-relation memory leakage in autovacuum.

  10. Fix recently-understood problems with handling of XID freezing, particularly