Re: another autovacuum scheduling thread

On Sat, 1 Nov 2025 at 14:50, David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
> If we logged the score, we could do the "unpatched" test with the
> patched code, just with commenting out the
> list_sort(tables_to_process, TableToProcessComparator); It'd then be
> interesting to zero the log_auto*_min_duration settings and review the
> order differences and how high the scores got. Would the average score
> be higher or lower with patched version? I'd guess lower since the
> higher scoring tables would tend to get vacuumed later with the
> unpatched version and their score would be even higher by the time
> autovacuum got to them. I think if the average score has gone down at
> the point that the vacuum starts, then that's a very good thing. Maybe
> we'd need to write a patch to recalculate the "tables_to_process" List
> after a table is vacuumed and autovacuum_naptime has elapsed for us to
> see this, else the priorities might have become too outdated. I'd
> expect that to be even more true when vacuum_cost_limit is configured
> too low.

I'm not yet sure how meaningful it is, but I tried adding the
following to recheck_relation_needs_vacanalyze():

elog(LOG, "Performing autovacuum of table \"%s\" with score = %f",
get_rel_name(relid), score);

then after grepping the logs and loading the data into a table and performing:

select case patched when true then 'v7' else 'master' end as
patched,case when left(tab, 11) = 'table_batch' then 'table_batch_*'
when left(tab,6) = 'table_' then 'table_*' else 'other' end,
avg(score) as avg_Score,count(*) as count from autovac where score>0
and score<2000 group by rollup(1,2) order by 2,1;

with vacuum_cost_limit = 5000, I got:

patched | case | avg_score | count
---------+---------------+--------------------+-------
master | other | 2.004997014705882 | 68
v7 | other | 1.9668087323943668 | 71
master | table_* | 1.196698981375357 | 1396
v7 | table_* | 1.2134741693430646 | 1370
master | table_batch_* | 2.1887380086206902 | 116
v7 | table_batch_* | 1.8882025693430664 | 137
master | | 1.3043197367088595 | 1580
v7 | | 1.3059485323193893 | 1578
| | 1.3051336187460454 | 3158

It would still be good to do the rate limiting as there's more work
being done in the patched version. Seems to be about 1.1% more rows in
batch_tables and 0.48% more updates in the numbered_tables in the
patched version.

David