Re: another autovacuum scheduling thread

On Tue, 24 Mar 2026 at 11:27, Jim Nasby <jnasby(at)upgrade(dot)com> wrote:
>
> On Mon, Mar 23, 2026 at 4:01 PM Nathan Bossart <nathandbossart(at)gmail(dot)com> wrote:
>> Thanks. IMHO we should continue to focus on the main patch and get that
>> committed first.
>
>
> +1 ... for one thing if we're going to add a view meant for monitoring autovac decisions I'd like to think about ways to measure how many tables are "close" to being eligible for autovac. In particular, the scenario where you've just done an MVU via some form of logical, so now the freeze ages on all your tables are extremely similar.

+1 for main patch first. I do think a view would be useful as a
follow-up. However, which columns we put in that view might have some
influence on how the current patch should look. I think the view
should show the individual scores and the total score as the Max() of
the individual scores. If we didn't do that, it might be confusing to
the user which aspect of the score the final score is derived from.
That might mean that it'd be better to have
relation_needs_vacanalyze() output the scores individually, or perhaps
populate a struct that we pass in that gets allocated on the stack
during do_autovacuum(). That'd mean a bit less churn if we go with the
view containing individual scores.

I think it would be good to have the view show tables that are not
eligible for autovacuum too. It should be easy for users to filter
those out for cases where they're not needed. Doing that would make it
very easy for anyone who wanted to code up a script to run off-peak to
vacuum tables that might need attention on the next peak. Something
like:

SELECT 'VACUUM ' || vacrelid::regclass || ';' from
pg_stat_autovacuum_priority WHERE vacuum_score BETWEEN 0.75 AND 1.0
ORDER BY vacuum_score DESC;
\gexec

David