Re: New GUC autovacuum_max_threshold ?

On Wed, Jan 08, 2025 at 07:01:53PM -0500, Robert Treat wrote:
> To be frank, this patch feels like a solution in search of a problem,
> and as I read back through the thread, it isn't clear what problem
> this is intended to fix.

Thanks for sharing your thoughts. FWIW I've heard various strategies over
the years for ensuring large tables are vacuumed more often, including
per-table settings for autovacuum_vacuum_threshold, etc. At its core, this
patch is intended to ensure larger tables are handled by default. But I do
think it's also important to consider _why_ folks want to vacuum larger
tables more often. More on that below...

> Is the patch supposed to help with wraparound prevention?
> autovac_freeze_max_age already covers that, and when it doesn't
> vacuum_failsafe_age helps out.

Not really, although I certainly don't think it hurts matters in that
department. In any case, while autovacuum_freeze_max_age and
vacuum_failsafe_age are incredibly important backstops for wraparound
issues, it's probably not great to rely on them too much for bloat, etc.

> A couple of people mentioned issues around hitting the index wall when
> vacuuming large tables, but we believe that problem is mostly resolved
> due to radix based tid storage, so this doesn't solve that. (To the
> degree you don't think v17 has baked into enough production workloads
> to be sure, I'd agree, but that's also an argument against doing more
> work that might not be needed)

Agreed.

> Maybe the hope is that this setting will cause vacuum to run more
> often to help ameliorate i/o work from freeze vacuums kicking in, but
> I suspect that Melanie's nearby work on eager vacuuming is a smarter
> solution towards this problem (warning, it also may want to add more
> gucs), so I think we're not solving that, and in fact might be
> undercutting it.

I haven't paid enough attention to the eager freezing work to have an
opinion on this point.

> I guess that means this is supposed to help with bloat management? but
> only on large tables? I guess because you run vacuums more often?

Right. I think Robert Haas explained it well [0] [1].

> Except that the adages of running vacuums more often don't apply as
> cleanly to large tables, because those tables typically come with
> large indexes, and while we have a lot of machinery in place to help
> with repeated scans of the heap, that same machinery doesn't exist for
> scanning the indexes, which gives you sort of an exponential curve
> around vacuum times as table size (but actually index size) grows
> larger. On the upside, this does mean we're less likely to see a 50x
> boost in vacuums on large tables that some seemed concerned about, but
> on the downside its because we're probably going to increase the
> probability of vacuum worker starvation.

IIUC your concern is that instead of incurring one gigantic vacuum every
once in a while, we are incurring multiple medium vacuums more often, to
the point that we are spending significantly more time vacuuming a table
than before. Is that right? If so, I'm curious what you think about the
discussion upthread on this point [2].

> But getting back to goals, if your goal is to help with bloat
> management, trying to tie that to a number that doesn't cleanly map to
> the meta information of the table in question is a poor way to do it.
> Meaning, to the degree that you are skeptical that vacuuming based on
> 20% of the rows of a table might not really be 20% of the size of the
> table, it's certainly going to be a closer map than 100million rows in
> a n number of tables of unknown (but presumably greater than
> 500million?) numbers of rows of unknown sizes. And again, we have a
> means to tackle these bloat cases already; lowering
> vacuum_scale_factor.

I disagree on this point. I think the fact that folks are forced to make
per-table adjustments to parameters like vacuum_scale_factor and are
participating in vigorous discussions like this one indicates that the
existing system isn't sufficient (or at least isn't sufficient by default).
That's not to say that adding a hard cap is perfect, either, but I don't
think we should let perfect be the enemy of good, especially not at this
stage of v18 development.

[0] https://postgr.es/m/CA%2BTgmoY4BENJyYcnU2eLFYZ73MZOb72WymNBH7vug6DtA%2BCZZw%40mail.gmail.com
[1] https://youtu.be/RfTD-Twpvac?&t=1979
[2] https://postgr.es/m/20240507211702.GA2720371%40nathanxps13

--
nathan