Re: Introduce some randomness to autovacuum

Hi Sami,

On Thu, May 1, 2025 at 1:56 AM Sami Imseih <samimseih(at)gmail(dot)com> wrote:
>
> > Yes, it is masking the problem, but maybe a better way to think about it is that it is delaying the
> > performance impact, allowing more time for a manual intervention of the problematic table(s).
>
> I question how the user will gauge the success of setting the strategy
> to "random"? They may make
> it random by default, but fall into the same issues and revert it back
> to the default strategy.
>
> But also, the key as you mention is "manual intervention" which
> requires proper monitoring. I will
> argue that for the two cases that this proposal is seeking to correct,
> we already have good
> solutions that could be implemented by a user.
>
> Let's take the "spinning" case again. If a table has some sort of
> problem causing
> vacuum to error out, one can just disable autovacuum on a per-table
> level and correct
> the issue. Also, the xmin horizon being held back ( which is claimed
> to be the most common cause,
> and I agree with that ), well that one is just going to cause all your
> autovacuums to become
> useless.

Yeah, I tend to agree with you that the xmin horizon hold back will
make autovacuums to become useless for all tables.

But I have a question, let me quote Andres' comment on slack first:

```quote begin
It seems a bit silly to not just do some basic prioritization instead,
but perhaps we just need to reach for some basic stuff, given that
we seem unable to progress on prioritization.
```quote end

If randomness is not working, ISTM that the prioritization will not benefit
the "spinning" case too, am I right?

>
> Also, I do think the starvation problem has a good answer now that
> autovacuum_max_workers
> can be modified online. Maybe something can be done for autovacuum to
> auto-tune this
> setting to give more workers at times when it's needed. Not sure what
> that looks like,
> but it is more possible now that this setting does not require a restart.

Good to know, thanks.

One case I didn't mention is that some corruption due to vacuuming the
same table might starve other tables two, randomness gives other tables
some chances to be vacuumed. I do admit that multi vacuum workers
can eliminate this issue a little bit if the corrupted table's vacuum progress
lasts for some time, but I think randomness is much better.

>
> --
> Sami Imseih
> Amazon Web Services (AWS)

--
Regards
Junwang Zhao