Re: another autovacuum scheduling thread

On Sat, 1 Nov 2025 at 14:50, David Rowley <dgrowleyml@gmail.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