Re: Eager page freeze criteria clarification

On Mon, Oct 2, 2023 at 11:37 AM Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
> If no vacuuming against pgbench_accounts is strictly better than some
> vacuuming (unless it's just to advance relfrozenxid, which can't be
> avoided), then to what extent is Melanie's freezing patch obligated to
> not make the situation worse? I'm not saying that it doesn't matter at
> all; just that there needs to be a sense of proportion.

I agree. I think Melanie's previous test results showed no measurable
performance regression but a significant regression in WAL volume. I
don't remember how much of a regression it was, but it was enough to
make me think that the extra WAL volume could probably be turned into
a performance loss by adjusting the test scenario (a bit less
hardware, or a bandwidth-constrained standby connection, etc.). I
think I'd be OK to accept a small amount of additional WAL, though. Do
you see it differently?

> Whatever your priorities might be, workload-wise, it could still be
> useful to recognize useless freezing as part of a broader problem of
> useless (or at least dubious) work performed by VACUUM -- especially
> with pgbench-like workloads. The utility of freezing is a lot easier
> to measure than the utility of pruning, but why should we assume that
> pruning isn't already just as much of a problem? (Maybe that's not a
> problem that particularly interests you right now; I'm bringing it up
> because it seems possible that putting it in scope could somehow
> clarify what to do about freezing.)

I wonder how much useless work we're doing in this area today. I mean,
most pruning in that workload gets done by HOT rather than by vacuum,
because vacuum simply can't keep up, but I don't think it's worthless
work: if it wasn't done in the background by VACUUM, it would happen
in the foreground anyway very soon after. I don't have a good feeling
for how much index cleanup we end up doing in a pgbench workload, but
it seems to me that if we don't do index cleanup at least now and
then, we'll lose the ability to recycle line pointers in the wake of
non-HOT updates, and that seems bad. Maybe that's rare in pgbench
specifically, or maybe it isn't, but it seems like you'd only have to
change the workload a tiny bit for that to become a real problem.

--
Robert Haas
EDB: http://www.enterprisedb.com