MIME-Version: 1.0
References: 
 <CABV9wwNBifXpOjxO9rGn1HHK=DG02qApVurWSHa+rDzPriK6pA@mail.gmail.com>
 <CAA5RZ0uiBCiQDUfA5sbc50gybWx1YES3HfLKMd9gv661YpJyRg@mail.gmail.com>
 <CALj2ACVnQRS=T8SY2zegfft=Nku1n6w5609JPdd9jqiM+G_gGg@mail.gmail.com>
 <acwTxpz3Toxt0ty8@nathan>
 <CAA5RZ0uVZLsX_k33QoWt8iRes73jyqs+EjypL9+1wW1v8NsYbg@mail.gmail.com>
 <ac00otrpVnWzZ_i4@nathan>
 <CAA5RZ0vFtDKAOZALeLp3vLyhWcLntaqS-Oh36Xxgx5_8sYeSKQ@mail.gmail.com>
 <CAA5RZ0tOJjUuJidUQYAeoUD4VGjOSjuB7ox4dtLAsyoCZqSN+Q@mail.gmail.com>
 <ac2ODZSOjWNzueHZ@nathan>
 <CAA5RZ0uVg0L3vVvkOiHaY9ZJ+K9ZcsyQH0TVQ5rXSd-ZEeVFNg@mail.gmail.com>
 <ac_vLKVgs4RYK1P-@nathan>
In-Reply-To: <ac_vLKVgs4RYK1P-@nathan>
From: Sami Imseih <samimseih@gmail.com>
Date: Fri, 3 Apr 2026 14:13:16 -0500
Message-ID: 
 <CAA5RZ0sCRjH3xkHFdSXnKysdMZXFyaS_094+K-O_rr4Fkmwc=Q@mail.gmail.com>
Subject: Re: Add pg_stat_autovacuum_priority
To: Nathan Bossart <nathandbossart@gmail.com>
Cc: Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>,
 Robert Treat <rob@xzilla.net>,
	satyanarlapuram@gmail.com, pgsql-hackers <pgsql-hackers@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CAA5RZ0sCRjH3xkHFdSXnKysdMZXFyaS_094%2BK-O_rr4Fkmwc%3DQ%40mail.gmail.com>
Precedence: bulk

> Alright, I've been preparing these for commit.  Most changes are cosmetic,
> but there are a couple of bigger ones I should note:

Thanks!

> * I added a prerequisite patch for relation_needs_vacanalyze() that saves a
> level of indentation on a chunk of code.  This simplifies 0001 (now 0002) a
> bit.

I like this this.

> * I noticed that if autovacuum decides to force a vacuum for
> anti-wraparound purposes, it might also decide to analyze the table even if
> autovacuum is disabled for it.  AFAICT this is accidental, but since it's
> behaved this way since commit 48188e1621 (2006) [0], I am slightly worried
> that this bug may have become a feature.  In 0002, I separated this edge
> case in the code and added a comment, and I intend to start a new thread
> about removing it.

hmm yeah, I think this just needs to be documented clearly. I always
thought it was expected for auto-analyze to run in this case, and I don't
see why it shouldn't. If this needs to be clarified in docs, we should
do that in a separate discussion.

> * I removed the booleans in the view in favor of just noting that scores >=
> 1.0 means the table needs processing.  IMHO trying to distinguish
> needs_vacuum from do_vacuum is just going to confuse folks more than
> anything, and IIUC this information is redundant with "score >= 1.0",
> anyway.

That's fine by me.

> * I renamed the view to pg_autovacuum_scores.  While some of the
> information in the view depends on cumulative statistics, not all of it
> does, and what does is quite heavily modified from the original stats.  So,
> I didn't think the pg_stat_* prefix was appropriate, although I can see how
> reasonable people might disagree.

Initially I thought about moving this away from the cumulative stats section,
but this view does need to lookup relation stats and if relation stats
are reset,
the same rules will apply to this view.

Also not all views under "cumulative stats" are necessarily cumulative.
Some just show real-time data; pg_stat_activity, pg_stat_progress_*, etc.

This view does not have precedent in the type of work it does, but I do
really think it belongs under pg_stat_*, and not be too far away conceptually
from the vacuum stats in pg_stat_all_tables.

> * I considered whether to make the backing function per-table and
> ultimately decided against it.  The initialization logic is a bit
> expensive, and I assume most folks will be interested in the full picture
> of the current database.  Maybe we could add a per-table function down the
> road, but I don't see any strong need for that for now.

Yes, I did not proceed with this since the common use-case will be comparing
tables in a broader context. I don't see a string single table use-case at this
point.

Besides the above, I have one comment on 0005:

Where it says "indicate the table needs analyzing" or "needs processing"
or "needs vacuuming", we should instead say "may need". Since the
actually processing depends on the thresholds or force vacuum conditions,
but no need to go into that level of detail in the row descriptions. That is
all explained in the existing autovacuum prioritization docs.

--
Sami Imseih
Amazon Web Services (AWS)