Thread: Improving the "Routine Vacuuming" docs

Recent work on VACUUM and relfrozenxid advancement required that I
update the maintenance.sgml VACUUM documentation ("Routine
Vacuuming"). It was tricky to keep things current, due in part to
certain structural problems. Many of these problems are artifacts of
how the document evolved over time.

"Routine Vacuuming" ought to work as a high level description of how
VACUUM keeps the system going over time. The intended audience is
primarily DBAs, so low level implementation details should either be
given much less prominence, or not even mentioned. We should keep it
practical -- without going too far in the direction of assuming that
we know the limits of what information might be useful.

My high level concerns are:

* Instead of discussing FrozenTransactionId (and then explaining how
that particular magic value is not really used anymore anyway), why
not describe freezing in terms of the high level rules?

Something along the lines of the following seems more useful: "A tuple
whose xmin is frozen (and xmax is unset) is considered visible to
every possible MVCC snapshot. In other words, the transaction that
inserted the tuple is treated as if it ran and committed at some point
that is now *infinitely* far in the past."

It might also be useful to describe freezing all of a live tuple's
XIDs as roughly the opposite process as completely physically removing
a dead tuple. It follows that we don't necessarily need to freeze
anything to advance relfrozenxid (especially not on Postgres 15).

* The general description of how the XID space works similarly places
way too much emphasis on low level details that are of very little
relevance.

These details would even seem totally out of place if I was the
intended audience. The problem isn't really that the information is
too technical. The problem is that we emphasize mechanistic stuff
while never quite explaining the point of it all.

Currently, "25.1.5. Preventing Transaction ID Wraparound Failures"
says this, right up-front:

"But since transaction IDs have limited size (32 bits) a cluster that
runs for a long time (more than 4 billion transactions) would suffer
transaction ID wraparound"

This is way too mechanistic. We totally muddle things by even
mentioning 4 billion XIDs in the first place. It seems like a
confusing artefact of a time before freezing was invented, back when
you really could have XIDs that were more than 2 billion XIDs apart.

This statement has another problem: it's flat-out untrue. The
xidStopLimit stuff will reliably kick in at about 2 billion XIDs.

* The description of wraparound sounds terrifying, implying that data
corruption can result.

The alarming language isn't proportionate to the true danger
(something I complained about in a dedicated thread last year [1]).

* XID space isn't really a precious resource -- it isn't even a
resource at all IMV.

ISTM that we should be discussing wraparound as an issue about the
maximum *difference* between any two unfrozen XIDs in a
cluster/installation.

Talking about an abstract-sounding XID space seems to me to be quite
counterproductive. The logical XID space is practically infinite,
after all. We should move away from the idea that physical XID space
is a precious resource. Sure, users are often concerned that the
xidStopLimit mechanism might kick-in, effectively resulting in an
outage. That makes perfect sense. But it doesn't follow that XIDs are
precious, and implying that they are intrinsically valuable just
confuses matters.

First of all, physical XID space is usually abundantly available. A
"distance" of ~2 billion XIDs is a vast distance in just about any
application (barring those with pathological problems, such as a
leaked replication slot). Second of all, Since the amount of physical
freezing required to be able to advance relfrozenxid by any given
amount (amount of XIDs) varies enormously, and is not even predictable
for a given table (because individual tables don't get their own
physical XID space), the age of datfrozenxid predicts very little
about how close we are to having the dreaded xidStopLimit mechanism
kick in. We do need some XID-wise slack, but that's just a way of
absorbing shocks -- it's ballast, usually only really needed for one
or two very large tables.

Third of all, and most importantly, the whole idea that we can just
put off freezing indefinitely and actually reduce the pain (rather
than having a substantial increase in problems) seems to have just
about no basis in reality, at least once you get into the tens of
millions range (though usually well before that).

Why should you be better off if all of your freezing occurs in one big
balloon payment? Sometimes getting into debt for a while is useful,
but why should it make sense to keep delaying freezing? And if it
doesn't make sense, then why does it still make sense to treat XID
space as a precious resource?

* We don't cleanly separate discussion of anti-wraparound autovacuums,
and aggressive vacuums, and the general danger of wraparound (by which
I actually mean the danger of having the xidStopLimit stop limit kick
in).

I think that we should move towards a world in which we explicitly
treat the autovacuum anti-wraparound criteria as not all that
different to any of the standard criteria (so we probably still have
the behavior with autovacuums not being cancellable, but it would be a
dynamic thing that didn't depend on the original reason why
autovacuum.c launched an autovacuum worker). But even now we aren't
clear enough about the fact that anti-wraparound autovacuums really
aren't all that special. Which makes them seem scarier than they
should be.

[1] https://postgr.es/m/CAH2-Wzk_FxfJvs4TnUtj=DCsokbiK0CxfjZ9jjrfSx8sTWkeUg@mail.gmail.com
-- 
Peter Geoghegan

On Tue, Apr 12, 2022 at 4:24 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
> I've attached some off-the-cuff thoughts on reworking the first three paragraphs and the note.
>
> It's hopefully useful for providing perspective if nothing else.

More perspective is definitely helpful.

> I'm assuming and caring only about visible rows when I'm reading this section. Maybe we need to make that explicit -
onlyxmin matters (and the invisible frozen flag)?
 

The statement "only xmin matters" is true in spirit. If xmax needed to
be frozen then we'd actually remove the whole tuple instead (unless it
was a MultiXact). Alternatively, if it looked like xmax needed to be
frozen, but the XID turned out to have been from an aborted xact, then
we'd clear the XID from xmax instead.

Freezing tends to lag the removal of dead tuples, but that's just an
optimization. If you set vacuum_freeze_min_age to 0 then freezing and
dead tuple removal happen in tandem (actually, we can remove tuples
inserted by an XID after VACUUM's OldestXmin/removal cutoff when the
inserting xact aborts, but VACUUM makes no real promises about XIDs >=
OldestXmin anyway).

>> It might also be useful to describe freezing all of a live tuple's
>> XIDs as roughly the opposite process as completely physically removing
>> a dead tuple. It follows that we don't necessarily need to freeze
>> anything to advance relfrozenxid (especially not on Postgres 15).
>
>
> I failed to pickup on how this and "mod-2^32" math interplay, and I'm not sure I care when reading this.  It made
moresense to consider "shortest path" along the "circle".
 

It would probably be possible to teach the system to deal with
coexisting XIDs that are close to a full ~4 billion XIDs apart. We'd
have to give up on the mod-2^32 comparison stuff in functions like
TransactionIdPrecedes(), and carefully keep track of things per-table,
and carry that context around a lot more. I certainly don't think that
that's a good idea (if 2 billion XIDs wasn't enough, why should 4
billion XIDs be?), but it does seem feasible.

My point is this: there is nothing particularly intuitive or natural
about the current ~2 billion XID limit, even if you already know that
XIDs are generally represented on disk as 32-bit unsigned integers.
And so the fact is that we are already asking users to take it on
faith that there are truly good reasons why the system cannot tolerate
any scenario in which two unfrozen XIDs are more than about ~2 billion
XIDs apart. Why not just admit that, and then deem the XID comparison
rules out of scope for this particular chapter of the docs?

*Maybe* it's still useful to discuss why things work that way in code
like TransactionIdPrecedes(), but that's a totally different
discussion -- it doesn't seem particularly relevant to the design of
VACUUM, no matter the audience. Most DBAs will just accept that the
"XID distance" limit/invariant is about ~2 billion XIDs for esoteric
implementation reasons, with some vague idea of why it must be so
(e.g., "32-bit integers don't have enough space"). They will be no
worse off for it.

(Bear in mind that mod-2^32 comparison stuff was only added when
freezing/wraparound was first implemented back in 2001, by commit
bc7d37a525.)

>> Currently, "25.1.5. Preventing Transaction ID Wraparound Failures"
>> says this, right up-front:
>>
>> "But since transaction IDs have limited size (32 bits) a cluster that
>> runs for a long time (more than 4 billion transactions) would suffer
>> transaction ID wraparound"
>
>
> I both agree and disagree - where I settled (as of now) is reflected in the patch.

I just don't think that you need to make it any more complicated than
this: physical XID values are only meaningful when compared to other
XIDs from the same cluster. The system needs to make sure that no two
XIDs can ever be more than about 2 billion XIDs apart, and here's how
you as a DBA can help the system to make sure of that.

Discussion of the past becoming the future just isn't helpful, because
that simply cannot ever happen on any version of Postgres from the
last decade. Freezing is more or less an overhead of storing data in
Postgres long term (even medium term) -- that is the simple reality.
We should say so.

> I am wondering, for the more technical details, is there an existing place to send xrefs, do you plan to create one,
oris it likely unnecessary?
 

I might end up doing that, but just want to get a general sense of how
other hackers feel about it for now.

-- 
Peter Geoghegan

On Tue, Apr 12, 2022 at 5:53 PM Peter Geoghegan <pg@bowt.ie> wrote:
> My high level concerns are:
>
> * Instead of discussing FrozenTransactionId (and then explaining how
> that particular magic value is not really used anymore anyway), why
> not describe freezing in terms of the high level rules?
>
> Something along the lines of the following seems more useful: "A tuple
> whose xmin is frozen (and xmax is unset) is considered visible to
> every possible MVCC snapshot. In other words, the transaction that
> inserted the tuple is treated as if it ran and committed at some point
> that is now *infinitely* far in the past."

I agree with this idea.

> * The description of wraparound sounds terrifying, implying that data
> corruption can result.
>
> The alarming language isn't proportionate to the true danger
> (something I complained about in a dedicated thread last year [1]).

I mostly agree with this, but not entirely. The section needs some
rephrasing, but xidStopLimit doesn't apply in single-user mode, and
relfrozenxid and datfrozenxid values can and do get corrupted. So it's
not a purely academic concern.

> * XID space isn't really a precious resource -- it isn't even a
> resource at all IMV.

I disagree with this. Usable XID space is definitely a resource, and
if you're in the situation where you care deeply about this section of
the documentation, it's probably one in short supply. Being careful
not to expend too many XIDs while fixing the problems that have cause
you to be short of safe XIDs is *definitely* a real thing.

> * We don't cleanly separate discussion of anti-wraparound autovacuums,
> and aggressive vacuums, and the general danger of wraparound (by which
> I actually mean the danger of having the xidStopLimit stop limit kick
> in).

I think it is wrong to conflate wraparound with xidStopLimit.
xidStopLimit is the final defense against an actual wraparound, and
like I say, an actual wraparound is quite possible if you put the
system in single user mode and then do something like this:

backend> VACUUM FULL;

Big ouch.

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

On Wed, Apr 13, 2022 at 8:40 AM Robert Haas <robertmhaas@gmail.com> wrote:
> > Something along the lines of the following seems more useful: "A tuple
> > whose xmin is frozen (and xmax is unset) is considered visible to
> > every possible MVCC snapshot. In other words, the transaction that
> > inserted the tuple is treated as if it ran and committed at some point
> > that is now *infinitely* far in the past."
>
> I agree with this idea.

Cool. Maybe I should write a doc patch just for this part, then.

What do you think of the idea of relating freezing to removing tuples
by VACUUM at this point? This would be a basis for explaining how
freezing and tuple removal are constrained by the same cutoff. A very
old snapshot can hold up cleanup, but it can also hold up freezing to
the same degree (it's just not as obvious because we are less eager
about freezing by default).

> > The alarming language isn't proportionate to the true danger
> > (something I complained about in a dedicated thread last year [1]).
>
> I mostly agree with this, but not entirely. The section needs some
> rephrasing, but xidStopLimit doesn't apply in single-user mode, and
> relfrozenxid and datfrozenxid values can and do get corrupted. So it's
> not a purely academic concern.

I accept the distinction that you want to make is valid. More on that below.

> > * XID space isn't really a precious resource -- it isn't even a
> > resource at all IMV.
>
> I disagree with this. Usable XID space is definitely a resource, and
> if you're in the situation where you care deeply about this section of
> the documentation, it's probably one in short supply. Being careful
> not to expend too many XIDs while fixing the problems that have cause
> you to be short of safe XIDs is *definitely* a real thing.

I may have gone too far with this metaphor. My point was mostly that
XID space has a highly unpredictable cost (paid in freezing).

Perhaps we can agree on some (or even all) of the following specific points:

* We shouldn't mention "4 billion XIDs" at all.

* We should say that the issue is an issue of distances between
unfrozen XIDs. The maximum distance that can ever be allowed to emerge
between any two unfrozen XIDs in a cluster is about 2 billion XIDs.

* We don't need to say anything about how XIDs are compared, normal vs
permanent XIDs, etc.

* The system takes drastic intervention to prevent this implementation
restriction from becoming a problem, starting with anti-wraparound
autovacuums. Then there's the failsafe. Finally, there's the
xidStopLimit mechanism, our last line of defense.

> I think it is wrong to conflate wraparound with xidStopLimit.
> xidStopLimit is the final defense against an actual wraparound, and
> like I say, an actual wraparound is quite possible if you put the
> system in single user mode and then do something like this:

I forget to emphasize one aspect of the problem that seems quite
important: the document itself seems to conflate the xidStopLimit
mechanism with true wraparound. At least I thought so. Last year's
thread on this subject ('What is "wraparound failure", really?') was
mostly about that confusion. I personally found that very confusing,
and I doubt that I'm the only one.

There is no good reason to use single user mode anymore (a related
problem with the docs is that we still haven't made that point). And
the pg_upgrade bug that led to invalid relfrozenxid values was
flagrantly just a bug (adding a WARNING for this recently, in commit
e83ebfe6). So while I accept that the distinction you're making here
is valid, maybe we can fix the single user mode doc bug too, removing
the need to discuss "true wraparound" as a general phenomenon. You
shouldn't ever see it in practice anymore. If you do then either
you've done something that "invalidated the warranty", or you've run
into a legitimate bug.

-- 
Peter Geoghegan

On Wed, Apr 13, 2022 at 12:34 PM Peter Geoghegan <pg@bowt.ie> wrote:
> What do you think of the idea of relating freezing to removing tuples
> by VACUUM at this point? This would be a basis for explaining how
> freezing and tuple removal are constrained by the same cutoff. A very
> old snapshot can hold up cleanup, but it can also hold up freezing to
> the same degree (it's just not as obvious because we are less eager
> about freezing by default).

I think something like that could be useful, if we can find a way to
word it sufficiently clearly.

> Perhaps we can agree on some (or even all) of the following specific points:
>
> * We shouldn't mention "4 billion XIDs" at all.
>
> * We should say that the issue is an issue of distances between
> unfrozen XIDs. The maximum distance that can ever be allowed to emerge
> between any two unfrozen XIDs in a cluster is about 2 billion XIDs.
>
> * We don't need to say anything about how XIDs are compared, normal vs
> permanent XIDs, etc.
>
> * The system takes drastic intervention to prevent this implementation
> restriction from becoming a problem, starting with anti-wraparound
> autovacuums. Then there's the failsafe. Finally, there's the
> xidStopLimit mechanism, our last line of defense.

Those all sound pretty reasonable. There's a little bit of doubt in my
mind about the third one; I think it could possibly be useful to
explain that the XID space is circular and 0-2 are special, but maybe
not.

> > I think it is wrong to conflate wraparound with xidStopLimit.
> > xidStopLimit is the final defense against an actual wraparound, and
> > like I say, an actual wraparound is quite possible if you put the
> > system in single user mode and then do something like this:
>
> I forget to emphasize one aspect of the problem that seems quite
> important: the document itself seems to conflate the xidStopLimit
> mechanism with true wraparound. At least I thought so. Last year's
> thread on this subject ('What is "wraparound failure", really?') was
> mostly about that confusion. I personally found that very confusing,
> and I doubt that I'm the only one.

OK.

> There is no good reason to use single user mode anymore (a related
> problem with the docs is that we still haven't made that point). And

Agreed.

> the pg_upgrade bug that led to invalid relfrozenxid values was
> flagrantly just a bug (adding a WARNING for this recently, in commit
> e83ebfe6). So while I accept that the distinction you're making here
> is valid, maybe we can fix the single user mode doc bug too, removing
> the need to discuss "true wraparound" as a general phenomenon. You
> shouldn't ever see it in practice anymore. If you do then either
> you've done something that "invalidated the warranty", or you've run
> into a legitimate bug.

I think it is probably important to discuss this, but along the lines
of: it is possible to bypass all of these safeguards and cause a true
wraparound by running in single-user mode. Don't do that. There's no
wraparound situation that can't be addressed just fine in multi-user
mode, and here's how to do that. In previous releases, we used to
sometimes recommend single user mode, but that's no longer necessary
and not a good idea, so steer clear.

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

On Wed, Apr 13, 2022 at 1:25 PM Robert Haas <robertmhaas@gmail.com> wrote:
> On Wed, Apr 13, 2022 at 12:34 PM Peter Geoghegan <pg@bowt.ie> wrote:
> > What do you think of the idea of relating freezing to removing tuples
> > by VACUUM at this point? This would be a basis for explaining how
> > freezing and tuple removal are constrained by the same cutoff.

> I think something like that could be useful, if we can find a way to
> word it sufficiently clearly.

What if the current "25.1.5. Preventing Transaction ID
Wraparound Failures" section was split into two parts? The first part
would cover freezing, the second part would cover
relfrozenxid/relminmxid advancement.

Freezing can sensibly be discussed before introducing relfrozenxid.
Freezing is a maintenance task that makes tuples self-contained
things, suitable for long term storage. Freezing makes tuples not rely
on transient transaction metadata (mainly clog), and so is an overhead
of storing data in Postgres long term.

That's how I think of it, at least. That definition seems natural to me.

> Those all sound pretty reasonable.

Great.

I have two more things that I see as problems. Would be good to get
your thoughts here, too. They are:

1. We shouldn't really be discussing VACUUM FULL here at all, except
to say that it's out of scope, and probably a bad idea.

You once wrote about the problem of how VACUUM FULL is perceived by
users (VACUUM FULL doesn't mean "VACUUM, but better"), expressing an
opinion of VACUUM FULL that I agree with fully. The docs definitely
contributed to that problem.

2. We don't go far enough in emphasizing the central role of autovacuum.

Technically the entire section assumes that its primary audience are
those users that have opted to not use autovacuum. This seems entirely
backwards to me.

We should make it clear that technically autovacuum isn't all that
different from running your own VACUUM commands, because that's an
important part of understanding autovacuum. But that's all. ISTM that
anybody that *entirely* opts out of using autovacuum is just doing it
wrong (besides, it's kind of impossible to do it anyway, what with
anti-wraparound autovacuum being impossible to disable).

There is definitely a role for using tools like cron to schedule
off-hours VACUUM operations, and that's still worth pointing out
prominently. But that should be a totally supplementary thing, used
when the DBA understands that running VACUUM off-hours is less
disruptive.

> There's a little bit of doubt in my
> mind about the third one; I think it could possibly be useful to
> explain that the XID space is circular and 0-2 are special, but maybe
> not.

I understand the concern. I'm not saying that this kind of information
doesn't have any business being in the docs. Just that it has no
business being in this particular chapter of the docs. In fact, it
doesn't even belong in "III. Server Administration". If it belongs
anywhere, it should be in some chapter from "VII. Internals".

Discussing it here just seems inappropriate (and would be even if it
wasn't how we introduce discussion of wraparound). It's really only
tangentially related to VACUUM anyway. It seems like it should be
covered when discussing the heapam on-disk representation.

> I think it is probably important to discuss this, but along the lines
> of: it is possible to bypass all of these safeguards and cause a true
> wraparound by running in single-user mode. Don't do that. There's no
> wraparound situation that can't be addressed just fine in multi-user
> mode, and here's how to do that. In previous releases, we used to
> sometimes recommend single user mode, but that's no longer necessary
> and not a good idea, so steer clear.

Yeah, that should probably happen somewhere.

On the other hand...why do we even need to tolerate wraparound in
single-user mode? I do see some value in reserving extra XIDs that can
be used in single-user mode (apparently single-user mode can be used
in scenarios where you have buggy event triggers, things like that).
But that in itself does not justify allowing single-user mode to
exceed xidWrapLimit.

Why shouldn't single-user mode also refuse to allocate new XIDs when
we reach xidWrapLimit (as opposed to when we reach xidStopLimit)?

Maybe there is a good reason to believe that allowing single-user mode
to corrupt the database is the lesser evil, but if there is then I'd
like to know the reason.


--
Peter Geoghegan

On Thu, Apr 14, 2022 at 5:03 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:

> I would be on board with having the language of the entire section written with the assumption that autovacuum is
enabled,with a single statement upfront that this is the case.  Most of the content remains as-is but we remove a
non-trivialnumber of sentences and fragments of the form "The autovacuum daemon, if enabled, will..." and "For those
notusing autovacuum,..." 

+1

The second one goes on to say "a typical approach...", which seems to
imply there are plenty of installations that hum along happily with
autovacuum disabled. If there are, I haven't heard of any. (Aside: In
my experience, it's far more common for someone to disable autovacuum
on 118 tables via reloptions for who-knows-what-reason and without
regard for the consequences). Also:

- "Some database administrators will want to supplement or replace the
daemon's activities with manually-managed VACUUM commands". I'm not
sure why we go as far as to state that *replacing* is an option to
consider.

- " you will need to use VACUUM FULL, or alternatively CLUSTER or one
of the table-rewriting variants of ALTER TABLE."

If you follow the link to the ALTER TABLE documentation, there is no
easy-to-find info on what these table-rewriting variants might be.
Within that page there are two instances of the text "one of the forms
of ALTER TABLE that ...", but again no easy-to-find advice on what
those might be. Furthermore, I don't recall there being an ALTER TABLE
that rewrites the table with no other effects (*). So if you find
yourself *really* needing to VACUUM FULL or CLUSTER, which primary
effect of ALTER TABLE should they consider, in order to get the side
effect of rewriting the table? Why are we mentioning ALTER TABLE here
at all?

> If the basic content is deemed worthy of preservation, relocating all of those kinds of hints and whatnot to a single
"supplementingor disabling auto-vacuum" section. 

I think any mention of disabling should be in a context where it is
not assumed to be normal, i.e. exceptional situations. Putting it in a
section heading makes it too normal. Here's how I think about this: do
we have a section heading anywhere on disabling fsync? I know it's not
the same, but that's how I think about it.

(*) Should there be? As alluded to upthread, VACUUM FULL is a terrible
name for what it does as of 9.0. I continue to encounter admins who
have a weekly script that runs database-wide VACUUM FULL, followed by
REINDEX. Or, upon asking someone to run a manual vacuum on a table,
will echo what they think they heard: "okay, so run a full vacuum". I
would prefer these misunderstandings to get a big fat syntax error if
they are carried out.

--
John Naylor
EDB: http://www.enterprisedb.com