Re: Overhauling "Routine Vacuuming" docs, particularly its handling of freezing

On Thu, May 4, 2023 at 3:18 PM samay sharma <smilingsamay@gmail.com> wrote:
> What do you think about the term "Exhaustion"? Maybe something like "XID allocation exhaustion" or "Exhaustion of
allocatableXIDs"? 

I use the term "transaction ID exhaustion" in the attached revision,
v4. Overall, v4 builds on the work that went into v2 and v3, by
continuing to polish the overhaul of everything related to freezing,
relfrozenxid advancement, and anti-wraparound autovacuum.

It would be nice if it was possible to add an animation/diagram a
little like this one: https://tuple-freezing-demo.angusd.com (this is
how I tend to think about the "transaction ID space".)

I feel that the patch that deals with freezing is really coming
together in v4. The main problem now is lack of detailed review --
though the freezing related patch is still not committable, it's
getting close now. (The changes to the docs covering freezing should
be committed separately from any further work on "25.2.1. Recovering
Disk Space". I still haven't done much there in v4, and those parts
clearly aren't anywhere near being committable. So, for now, they can
mostly be ignored.)

v4 also limits use of the term "wraparound" to places that directly
discuss anti-wraparound autovacuums (plus one place in xact.sgml,
where discussion of "true unsigned integer wraparound" and related
implementation details has been moved). Otherwise we use the term
"transaction ID exhaustion", which is pretty much the user-facing name
for "xidStopLimit". I feel that this is a huge improvement, for the
reason given to Greg earlier. I'm flexible on the details, but I feel
strongly that we should minimize use of the term wraparound wherever
it might have the connotation of "the past becoming the future". This
is not a case of inventing a new terminology for its own sake. If
anybody is skeptical I ask that they take a look at what I came up
with before declaring it a bad idea. I have made that as easy as
possible, by once again attaching a prebuilt routine-vacuuming.html.

I no longer believe that committing this patch series needs to block
on the patch that seeks to put things straight with single user mode
and xidStopLimit/transaction ID exhaustion (the one that John Naylor
is currently working on getting in shape), either (I'll explain my
reasoning if somebody wants to hear it).

Other changes in v4, compared to v3:

* Improved discussion of the differences between non-aggressive and
aggressive VACUUM.

Now mentions the issue of aggressive VACUUMs waiting for a cleanup
lock, including mention of the BufferPin wait event. This is the
second, minor difference between each kind of VACUUM. It matters much
less than the first difference, but it does merit a mention.

The discussion of aggressive VACUUM seems to be best approached by
starting with the mechanical differences, and only later going into
the consequences of those differences. (Particularly catch-up
freezing.)

* Explains "catch-up freezing" performed by aggressive VACUUMs directly.

"Catch-up" freezing is the really important "consequence" -- something
that emerges from how each type of VACUUM behaves over time. It is an
indirect consequence of the behaviors. I would like to counter the
perception that some users have about freezing only happening during
aggressive VACUUMs (or anti-wraparound autovacuums). But more than
that, talking about catch-up freezing seems essential because it is
the single most important difference.

* Much improved handling of the discussion of anti-wraparound
autovacuum, and how it relates to aggressive VACUUMs, following
feedback from Samay.

There is now only fairly minimal overlap in the discussion of
aggressive VACUUM and anti-wraparound autovacuuming. We finish the
discussion of aggressive VACUUM just after we start discussing
anti-wraparound autovacuum. This transition works well, because it
enforces the idea that anti-wraparound autovacuum isn't really special
compared to any other aggressive autovacuum. This was something that
Samay expressed particularly concern about: making anti-wraparound
autovacuums sound less scary. Though it's also a concern I had from
the outset, based on practical experience and interactions with people
that have much less knowledge of Postgres than I do.

* Anti-wraparound autovacuum is now mostly discussed as something that
happens to static or mostly-static tables.

This is related to the goal of making anti-wraparound autovacuums
sound less scary. Larger tables don't necessarily require any
anti-wraparound autovacuums these days -- we have the insert-driven
autovacuum trigger condition these days, so it's plausible (perhaps
even likely) that all aggressive VACUUMs against the largest
append-only tables can happen when autovacuum triggers VACUUMs to
processed recently inserted tuples.

This moves discussion of anti-wraparound av in the direction of:
"Anti-wraparound autovacuum is a special type of autovacuum. Its
purpose is to ensure that relfrozenxid advances when no earlier VACUUM
could advance it in passing — often because no VACUUM has run against
the table for an extended period."

* Added a couple of "Tips" about instrumentation that appears in the
server log whenever autovacuum reports on a VACUUM operation.

* Much improved "Truncating Transaction Status Information" subsection.

My explanation of the ways in which autovacuum_freeze_max_age can
affect the storage overhead of commit/abort status in pg_xact is much
clearer than it was in v3 -- pg_xact truncation is now treated as
something loosely related to the global config of anti-wraparound
autovacuum, which makes most sense.

It took a great deal of effort to find a structure that covered
everything, and that highlighted all of the important relationships
without going too far, while at the same time not being a huge mess.
That's what I feel I've arrived at with v4.

--
Peter Geoghegan