MIME-Version: 1.0
References: <aQPSYD11NDoREZsg@nathan>
 <CAA5RZ0uo-nU9KqgXV5Tcf8aWWQkxsb5BDpkb-2Qfwbw-UVVaUA@mail.gmail.com>
 <CAA5RZ0tpV3PRHejTGG5-LSsqNKKV0qP=SDvurs8wj7pTk7jYJw@mail.gmail.com>
 <aQUYP1WjrEP3buQz@nathan>
 <CAApHDvqe7ee=vobWe4GVAt2gm_H6eiGNZeo_dEMptvHYAkibBA@mail.gmail.com>
 <CAApHDvoCFQxaQjUncTAtCRFAeANe2tpc-WCkJue=FaXRYOkV=Q@mail.gmail.com>
 <CAA5RZ0sw+9rEaW9taNpRZWvuLYMjRa9iibneGfB2ftNSUHT0Ww@mail.gmail.com>
 <CAApHDvq_j+GVqX_ZAmvn236Mgg5OYQ6_s9kVsyoo1tJa2RJ=2w@mail.gmail.com>
 <CAA5RZ0udjEYJupob5tv3286e28bMpajgsy+4nAbxg73YyigZFw@mail.gmail.com>
 <CAApHDvq9ecYSetXUhjMc7E_Jcc7gxxRkA0LosX9PP1-jM_Ak8A@mail.gmail.com>
 <aRNmEPQ18qZlLowV@nathan>
In-Reply-To: <aRNmEPQ18qZlLowV@nathan>
From: David Rowley <dgrowleyml@gmail.com>
Date: Wed, 12 Nov 2025 09:03:54 +1300
Message-ID: 
 <CAApHDvrtvMF3_W69hOUr2SnbizjC1jc68_Ca0nPYr=+VUkUkAw@mail.gmail.com>
Subject: Re: another autovacuum scheduling thread
To: Nathan Bossart <nathandbossart@gmail.com>
Cc: Sami Imseih <samimseih@gmail.com>, Robert Haas <robertmhaas@gmail.com>,
	Jeremy Schneider <schneider@ardentperf.com>, pgsql-hackers@postgresql.org
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CAApHDvrtvMF3_W69hOUr2SnbizjC1jc68_Ca0nPYr%3D%2BVUkUkAw%40mail.gmail.com>
Precedence: bulk

On Wed, 12 Nov 2025 at 05:36, Nathan Bossart <nathandbossart@gmail.com> wrote:
> From skimming the latest discussion, I gather we might want to consider
> re-sorting the list periodically.  Is the idea that we'll re-sort the
> remaining tables in the list, or that we'll basically restart
> do_autovacuum()?  If it's the latter, then we'll need to come up with some
> way to decide when to stop for the current database.  Right now, we just go
> through pg_class and call it a day.

I'm still trying to work out what Sami sees in the results that he
doesn't think is good. I resuggested he try coding up the periodic
refresh-the-list code to see if it makes the thing he sees better. I
was hoping that we could get away with not doing that for stage 1 of
this. My concern there is that these change-the-way-autovacuum-works
patches seems to blow up quickly as everyone chips in with autovacuum
problems they want fixed and expect the patch to do it all.

That said, the periodic refresh probably isn't too hard. I suspected
it was something like:

     /* when enough time has passed, refresh the list to ensure the
scores aren't too out-of-date */
    if (time is > lastcheck + autovacuum_naptime * <something>)
    {
        list_free_deep(tables_to_process);
       goto the_top;
    }
} // end of foreach(cell, tables_to_process)

Perhaps if the test cases we're going to give this involve lengthy
autovacuum runs, then we might need that patch sooner. I'm uncertain
if that's the case with Sami's test. There were some 50GB tables, so I
imagine some of the runs could take a long time, especially so when
running standard vacuum_cost_limit.

David