Re: Introduce some randomness to autovacuum - Mailing list pgsql-hackers

From Nathan Bossart
Subject Re: Introduce some randomness to autovacuum
Date
Msg-id aBJBrTqfPLVylCP-@nathan
Whole thread Raw
In response to Introduce some randomness to autovacuum  (Junwang Zhao <zhjwpku@gmail.com>)
Responses Re: Introduce some randomness to autovacuum
Re: Introduce some randomness to autovacuum
List pgsql-hackers
On Fri, Apr 25, 2025 at 10:02:49PM +0800, Junwang Zhao wrote:
> After watching Robert's talk[1] on autovacuum and participating in the related
> workshop yesterday, it appears that people are inclined to use prioritization
> to address the issues highlighted in Robert's presentation. Here I list two
> of the failure modes that were discussed.
> 
> - Spinning. Running repeatedly on the same table but not accomplishing
> anything useful.
> - Starvation. autovacuum can't vacuum everything that needs vacuuming.
> - ...
> 
> The prioritization way needs some basic stuff that postgres doesn't have now.
> 
> I had a random thought that introducing some randomness might help
> mitigate some of the issues mentioned above. Before performing vacuum
> on the collected tables, we could rotate the table_oids list by a random
> number within the range [0, list_length(table_oids)]. This way, every table
> would have an equal chance of being vacuumed first, thus no spinning and
> starvation.
> 
> Even if there is a broken table that repeatedly gets stuck, this random
> approach would still provide opportunities for other tables to be vacuumed.
> Eventually, the system would converge.

First off, thank you for thinking about this problem and for sharing your
thoughts.  Adding randomness to solve this is a creative idea.

That being said, I am -1 for this proposal.  Autovacuum parameters and
scheduling are already quite complicated, and making it nondeterministic
would add an additional layer of complexity (and may introduce its own
problems).  But more importantly, IMHO it masks the problems instead of
solving them more directly, and it could mask future problems, too.  It'd
probably behoove us to think about the known problems more deeply and to
craft more targeted solutions.

-- 
nathan



pgsql-hackers by date:

Previous
From: Robert Treat
Date:
Subject: Re: Doc: fix the rewrite condition when executing ALTER TABLE ADD COLUMN
Next
From: Japin Li
Date:
Subject: Fix outdated comments for IndexInfo