MIME-Version: 1.0
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>
 <CABV9wwNBifXpOjxO9rGn1HHK=DG02qApVurWSHa+rDzPriK6pA@mail.gmail.com>
 <CAA5RZ0uiBCiQDUfA5sbc50gybWx1YES3HfLKMd9gv661YpJyRg@mail.gmail.com>
 <acvqclvQI7-tHTJY@nathan>
In-Reply-To: <acvqclvQI7-tHTJY@nathan>
From: Sami Imseih <samimseih@gmail.com>
Date: Tue, 31 Mar 2026 11:15:35 -0500
Message-ID: 
 <CAA5RZ0v1Pa_NqfQbOZ+jzGL09+rJ36XiemE4o9WZObW=hiKzgQ@mail.gmail.com>
Subject: Re: Add pg_stat_autovacuum_priority
To: Nathan Bossart <nathandbossart@gmail.com>
Cc: Robert Treat <rob@xzilla.net>,
	Bharath Rupireddy <bharath.rupireddyforpostgres@gmail.com>,
 satyanarlapuram@gmail.com,
	pgsql-hackers <pgsql-hackers@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CAA5RZ0v1Pa_NqfQbOZ%2BjzGL09%2BrJ36XiemE4o9WZObW%3DhiKzgQ%40mail.gmail.com>
Precedence: bulk

> + * force_scores set to true forces the computation of a score. This is useful for
> + * tools that wish to inspect scores outside of the do_vacuum() path.
>
> I'm of two minds about this new function parameter.  On one hand, I see the
> utility of forcing score calculations even when autovacuum is disabled.  On
> the other hand, when autovacuum is disabled, the scores are actually 0.0,
> and it's probably a good idea to report exactly what autovacuum workers
> see.

I went back and forth on this. Showing 0.0 when autovacuum is disabled
would reflect what autovacuum workers actually see, but I think the more
useful behavior is to always compute the score based on the table's actual
state. This way, a DBA who has disabled autovacuum on a table can still
see that its score is climbing and needs attention. The view shows need,
not eligibility. This will also make the view more useful for maintenance
jobs that wish to supplement autovacuum by looking at high scores
and triggering a manual vacuum for those tables.

> I also see that we're not forcing the computation of the (M)XID
> scores.  Is that intentional?

hmm, the force_score does not need to be in the force_vacuum path
because the score is calculated there naturally when the table is in
need of force_vacuum. The force_score is there to ensure that
we are not existing early in the autovacuum disabled case.

> I wonder if we can rework this function to always calculate the scores,
> even if autovacuum is disabled or !force_vacuum.  This way, both paths are
> doing the exact same thing and reporting the same scores.

I prefer that we still calculate the score as if autovacuum is enabled
for the reason above. I do think one potential middle ground is to have
needs_analyze, needs_vacuum, eligible_analyze, eligible_vacuum
fields to differentiate. I just rather not hide a score because a/v
is disabled on a table.

> +     <row>
> +      <entry role="catalog_table_entry"><para role="column_definition">
> +       <structfield>vacuum_dead_score</structfield> <type>double precision</type>
> +      </para>
> +      <para>
> +       Score component based on the estimated number of dead tuples
> +       needing removal by vacuum.
> +      </para></entry>
> +     </row>
>
> I think we should make sure the column names align with the names given to
> the new parameters [0] and the new "Autovacuum Prioritization" section in
> the docs [1].

I will look into this in the next rev.

--
Sami