Hi Sami, On Thu, May 1, 2025 at 1:56 AM Sami Imseih <samims...@gmail.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