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

On Wed, Jun 16, 2021 at 10:04 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I remember that Heikki was fooling with a patch that reduced snapshots
> to LSNs. If we got that done, it'd be practical to expose complete
> info about backends' snapshot state in a lot of cases (i.e., anytime
> you had less than N live snapshots).
>
> Of course, there's still the question of how VACUUM could cheaply
> apply such info to decide what could be purged.

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.

It's not just because opportunistic techniques are where the real work
of removing garbage is usually done these days. It's also because
opportunistic techniques are triggered in response to an immediate
problem, like an overfull heap page or an imminent page split that
we'd like to avoid -- they can actually see what's going on at the
local level in a way that doesn't really work inside VACUUM.

This also means that they can cycle through strategies for a page,
starting with the cheapest and coarsest grained cleanup, progressing
to finer grained cleanup. You only really need the finer grained
cleanup when the coarse grained cleanup (simple OldestXmin style
cutoff) fails. And even then you only need to use the slowpath when
you have a pretty good idea that it'll actually be useful -- you at
least know up front that there are a bunch of RECENTLY_DEAD tuples
that very well might be freeable once you use the slow path.

We can leave the floating garbage inside heap pages that hardly ever
get opportunistic pruning behind for VACUUM. We might even find that
an advanced strategy that does clever things in order to cleanup
intermediate versions isn't actually needed all that often (it's
executed perhaps orders of magnitude less frequently than simple
opportunistic pruning is executed) -- even when the clever technique
really helps the workload. Technically opportunistic pruning might be
99%+ effective, even when it doesn't look like it's effective to
users. The costs in this area are often very nonlinear. It can be very
counterintuitive.

--
Peter Geoghegan