Re: snapshot too old issues, first around wraparound and then more.

On Wed, Jun 16, 2021 at 12:06 PM Andres Freund <andres(at)anarazel(dot)de> wrote:
> > I would think that it wouldn't really matter inside VACUUM -- it would
> > only really need to be either an opportunistic pruning or an
> > opportunistic index deletion thing -- probably both. Most of the time
> > VACUUM doesn't seem to end up doing most of the work of removing
> > garbage versions. It's mostly useful for "floating garbage", to use
> > the proper GC memory management term.
>
> I don't fully agree with this. For one, there are workloads where VACUUM
> removes the bulk of the dead tuples.

It's definitely much more important that VACUUM run often when non-HOT
updates are the norm, and there are lots of them. But that's probably
not going to be helped all that much by this technique anyway.

Mostly I'm just saying I'd start elsewhere and do heapam later. And
probably do VACUUM itself last of all, if that usefully cut scope.

> For another, slowing down VACUUM
> can cause a slew of follow-on problems, so being careful to not
> introduce new bottlenecks is important. And I don't think just doing
> this optimization as part of on-access pruning is reasonable
> solution. And it's not like making on-access pruning slower is
> unproblematic either.

I think that on-access pruning is much more important because it's the
only hope we have of keeping the original heap page intact, in the
sense that there are no non-HOT updates over time, though there may be
many HOT updates. And no LP_DEAD items ever accumulate. It's not so
much about cleaning up bloat as it is about *preserving* the heap
pages in this sense.

If in the long run it's impossible to keep the page intact in this
sense then we will still have most of our current problems. It might
not make that much practical difference if we simply delay the problem
-- we kinda have to prevent it entirely, at least for a given
workload. So I'm mostly concerned about keeping things stable over
time, at the level of individual pages.

> But as I said nearby, I think the hardest part is figuring out how to
> deal with ctid chains, not the efficiency of the xid->visibility lookup
> (or the collection of data necessary for that lookup).

Definitely true.

--
Peter Geoghegan