Subject: do only critical work during single-user vacuum?

From: John Naylor <[email-masked]>
Date: Thu, 9 Dec 2021 15:28:18 -0400

When a user must shut down and restart in single-user mode to run
vacuum on an entire database, that does a lot of work that's
unnecessary for getting the system online again, even without
index_cleanup. We had a recent case where a single-user vacuum took
around 3 days to complete.

Now that we have a concept of a fail-safe vacuum, maybe it would be
beneficial to skip a vacuum in single-user mode if the fail-safe
criteria were not met at the beginning of vacuuming a relation. This
is not without risk, of course, but it should be much faster than
today and once up and running the admin would have a chance to get a
handle on things. Thoughts?

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

---

From: "Bossart, Nathan" <[email-masked]>
Date: Thu, 9 Dec 2021 20:32:53 +0000

> handle on things. Thoughts?

Would the --min-xid-age and --no-index-cleanup vacuumdb options help
with this?

Nathan

---

From: Peter Geoghegan <[email-masked]>
Date: Thu, 9 Dec 2021 13:04:43 -0800

On Thu, Dec 9, 2021 at 11:28 AM John Naylor
<[email-masked]> wrote:
> criteria were not met at the beginning of vacuuming a relation.

Obviously the main goal of the failsafe is to not get into this
situation in the first place. But it's still very reasonable to ask
"what happens when the failsafe even fails at that?". This was
something that we considered directly when working on the feature.

There is a precheck that takes place before any other work, which
ensures that we won't even start off any of the nonessential tasks the
failsafe skips (e.g., index vacuuming). The precheck works like any
other check -- it checks if relfrozenxid is dangerously old. (We won't
even bother trying to launch parallel workers when this precheck
triggers, which is another reason to have it that Mashahiko pointed
out during development.)

Presumably there is no need to specifically check if we're running in
single user mode when considering if we need to trigger the failsafe
-- which, as you say, we won't do. It shouldn't matter, because
anybody running single-user mode just to VACUUM must already be unable
to allocate new XIDs outside of single user mode. That condition alone
will trigger the failsafe.

That said, it would be very easy to add a check for single user mode.
It didn't happen because we weren't aware of any specific need for it.
Perhaps there is an argument for it.

-- 
Peter Geoghegan

---

From: Peter Geoghegan <[email-masked]>
Date: Thu, 9 Dec 2021 13:12:50 -0800

> something that we considered directly when working on the feature.

Oh, I think I misunderstood. Your concern is for the case where the
DBA runs a simple "VACUUM" in single-user mode; you want to skip over
tables that don't really need to advance relfrozenxid, automatically.

I can see an argument for something like that, but I think that it
should be a variant of VACUUM. Or maybe it could be addressed with a
better user interface; single-user mode should prompt the user about
what exact VACUUM command they ought to run to get things going.

-- 
Peter Geoghegan

---

From: Andres Freund <[email-masked]>
Date: Thu, 9 Dec 2021 14:08:39 -0800

Hi,

> handle on things. Thoughts?

What if the user tried to reclaim space by vacuuming (via truncation)? Or is
working around some corruption or such?  I think this is too much magic.

That said, having a VACUUM "selector" that selects the oldest tables could be
quite useful. And address this usecase both for single-user and normal
operation.

Another thing that might be worth doing is to update relfrozenxid earlier. We
definitely should update it before doing truncation (that can be quite
expensive). But we probably should do it even before the final
lazy_cleanup_all_indexes() pass - often that'll be the only pass, and there's
really no reason to delay relfrozenxid advancement till after that.

Greetings,

Andres Freund

---

From: John Naylor <[email-masked]>
Date: Thu, 9 Dec 2021 19:53:40 -0400

> tables that don't really need to advance relfrozenxid, automatically.

Right.

> better user interface;

> quite useful. And address this usecase both for single-user and normal

All good points.

[Peter again]
> single-user mode should prompt the user about

The current message is particularly bad in its vagueness because some
users immediately reach for VACUUM FULL, which quite logically seems
like the most complete thing to do.

---

From: Peter Geoghegan <[email-masked]>
Date: Thu, 9 Dec 2021 16:34:53 -0800

> like the most complete thing to do.

You mean the GetNewTransactionId() error, about single-user mode? Why
do we need to use single-user mode at all? I'm pretty sure that the
reason is "as an escape hatch", but I wonder what that really means.

***Thinks***

I suppose that it might be a good idea to make sure that autovacuum
cannot run, because in general autovacuum might need to allocate an
XID (for autoanalyze), and locking all that down in exactly the right
way might not be a very good use of our time.

But even still, why not have some variant of single-user mode just for
this task? Something that's easy to use when the DBA is rudely
awakened at 4am -- something a little bit like a big red button that
fixes the exact problem of XID exhaustion, in a reasonably targeted
way? I don't think that this needs to involve the VACUUM command
itself.

The current recommendation to do a whole-database VACUUM doesn't take
a position on how old the oldest datfrozenxid has to be in order to
become safe again, preferring to "make a conservative recommendation"
-- which is what a database-level VACUUM really is. But that doesn't
seem helpful at all. In fact, it's not even conservative. We could
easily come up with a reasonable definition of "datfrozenxid that's
sufficiently new to make it safe to come back online and allocate XIDs
again". Perhaps something based on the current
autovacuum_freeze_max_age (and autovacuum_multixact_freeze_max_age)
settings, with sanity checks.

We could then apply this criteria in new code that implements this
"big red button" (maybe this is a new option for the postgres
executable, a little like --single?). Something that's reasonably
targeted, and dead simple to use.

-- 
Peter Geoghegan

---

From: "Bossart, Nathan" <[email-masked]>
Date: Fri, 10 Dec 2021 01:05:42 +0000

> Would the --min-xid-age and --no-index-cleanup vacuumdb options help

Sorry, I'm not sure what I was thinking.  Of coure you cannot use
vacuumdb in single-user mode.  But I think something like
--min-xid-age in VACUUM is what you are looking for.

Nathan

---

From: "Bossart, Nathan" <[email-masked]>
Date: Fri, 10 Dec 2021 01:06:33 +0000

> targeted, and dead simple to use.

+1

Nathan

---

From: "Bossart, Nathan" <[email-masked]>
Date: Fri, 10 Dec 2021 01:11:57 +0000

>> targeted, and dead simple to use.

As Andres noted, such a feature might be useful during normal
operation, too.  Perhaps the vacuumdb --min-xid-age stuff should be
moved to a new VACUUM option.

Nathan

---

From: Peter Geoghegan <[email-masked]>
Date: Thu, 9 Dec 2021 17:25:53 -0800

> moved to a new VACUUM option.

I was thinking of something like pg_import_system_collations() for
this: a function that's built-in, and can be called in single user
mode, that nevertheless doesn't make any assumptions about how it may
be called. Nothing stops a superuser from calling
pg_import_system_collations() themselves, outside of initdb. That
isn't particularly common, but it works in the way you'd expect it to
work. It's easy to test.

I imagine that this new function (to handle maintenance tasks in the
event of a wraparound emergency) would output information about its
progress. For example, it would make an up-front decision about which
tables needed to be vacuumed in order for the current DB's
datfrozenxid to be sufficiently new, before it started anything (with
handling for edge-cases with many tables, perhaps). It might also show
the size of each table, and show another line for each table that has
been processed so far, as a rudimentary progress indicator.

We could still have a separate option for the postgres executable,
just to invoke single-user mode and call this function. It would
mostly just be window dressing, of course, but that still seems
useful.

--
Peter Geoghegan

---

From: Andres Freund <[email-masked]>
Date: Thu, 9 Dec 2021 17:56:16 -0800

Hi,

On 2021-12-09 16:34:53 -0800, Peter Geoghegan wrote:
> this task?

> Something that's easy to use when the DBA is rudely

I think we should move *away* from single user mode, rather than the
opposite. It's a substantial code burden and it's hard to use.

I don't think single user mode is a good fit for this anyway - it's inherently
focussed on connecting to a single database. But wraparound issues often
involve more than one database (often just because of shared catalogs).

Also, requiring a restart will often exascerbate the problem - the cache will
be cold, there's no walwriter, etc, making the vacuum slower. Making vacuum
not consume an xid seems like a lot more promising - and quite doable. Then
there's no need to restart at all.

Greetings,

Andres Freund

---

From: Peter Geoghegan <[email-masked]>
Date: Thu, 9 Dec 2021 18:32:12 -0800

> opposite. It's a substantial code burden and it's hard to use.

I wouldn't say that this is moving closer to single user mode.

> involve more than one database (often just because of shared catalogs).

I don't disagree with any of that. My suggestions were based on the
assumption that it might be unrealistic to expect somebody to spend a
huge amount of time on this, given that (in a certain sense) it's
never really supposed to be used. Even a very simple approach would be
a big improvement.

> there's no need to restart at all.

I didn't give too much consideration to what it would take to keep the
system partially online, without introducing excessive complexity.
Maybe it wouldn't be that hard to teach the system to stop allocating
XIDs, while still allowing autovacuum workers to continue to get the
system functioning again. With the av workers taking a particular
emphasis on doing whatever work is required for the system to be able
to allocate XIDs again -- but not too much more (not until things are
back to normal). Now the plan is starting to get ambitious relative to
how often it'll be seen by users, though.

-- 
Peter Geoghegan

---

From: "Bossart, Nathan" <[email-masked]>
Date: Fri, 10 Dec 2021 04:41:07 +0000

> been processed so far, as a rudimentary progress indicator.

I like the idea of having a built-in function that does the bare
minimum to resolve wraparound emergencies, and I think providing some
sort of simple progress indicator (even if rudimentary) would be very
useful.  I imagine the decision logic could be pretty simple.  If
we're only interested in getting the cluster out of a wraparound
emergency, we can probably just look for all tables with an age over
~2B.

Nathan

---

From: Peter Geoghegan <[email-masked]>
Date: Mon, 20 Dec 2021 17:17:26 -0800

> sort of simple progress indicator (even if rudimentary) would be very

If John doesn't have time to work on this during the Postgres 15
cycle, and if nobody else picks it up, then we should at least do the
bare minimum here: force the use of the failsafe in single user mode
(regardless of the age of relfrozenxid/relminmxid, which in general
might not be that old in tables where VACUUM might need to do a lot of
work). Attached quick and dirty patch shows what this would take. If
nothing else, it seems natural to define running any VACUUM in single
user mode as an emergency.

This is really the least we could do -- it's much better than nothing,
but still really lazy^M^M^M^M conservative. I haven't revised the
assumption that the user should do a top-level "VACUUM" in databases
that can no longer allocate XIDs due to wraparound, despite the fact
that we could do far better with moderate effort. Although it might
make sense to commit something like the attached as part of a more
worked out solution (assuming it didn't fully remove single user mode
from the equation, which would be better still).

-- 
Peter Geoghegan

---

From: Andres Freund <[email-masked]>
Date: Mon, 20 Dec 2021 19:46:30 -0800

Hi,

> user mode as an emergency.

As I said before I think this is a bad idea. I'm fine with adding a vacuum
parameter forcing failsafe mode. And perhaps a hint to suggest it in single
user mode. But forcing it is a bad idea - single user isn't just used for
emergencies (c.f. initdb, which this patch would regress) and not every
emergency making single user mode useful is related to wraparound.

Greetings,

Andres Freund

---

From: Masahiko Sawada <[email-masked]>
Date: Tue, 21 Dec 2021 13:40:12 +0900

+1

BTW a vacuum automatically enters failsafe mode under the situation
where the user has to run a vacuum in the single-user mode, right?

Regards,

-- 
Masahiko Sawada
EDB:  https://www.enterprisedb.com/

---

From: Peter Geoghegan <[email-masked]>
Date: Mon, 20 Dec 2021 20:52:39 -0800

> where the user has to run a vacuum in the single-user mode, right?

Only for the table that had the problem. Maybe there are no other
tables that a database level "VACUUM" will need to spend much time on,
or maybe there are, and they will make it take much much longer (it
all depends).

The goal of the patch is to make sure that when we're in single user
mode, we'll consistently trigger the failsafe, for every VACUUM
against every table -- not just the table (or tables) whose
relfrozenxid is very old. That's still naive, but much less naive than
simply telling users to VACUUM the whole database in single user mode
while vacuuming indexes, etc.

--
Peter Geoghegan

---

From: Masahiko Sawada <[email-masked]>
Date: Tue, 21 Dec 2021 16:56:07 +0900

> while vacuuming indexes, etc.

I understand the patch, thank you for the explanation!

I remember Simon proposed a VACUUM command option[1], called
FAST_FREEZE, to turn off index cleanup and heap truncation. Now that
we have failsafe mechanism probably we can have a VACUUM command
option to turn on failsafe mode instead.

Regards,

[1] https://commitfest.postgresql.org/32/2908/

---

From: John Naylor <[email-masked]>
Date: Tue, 21 Dec 2021 13:36:20 -0400

> option to turn on failsafe mode instead.

I've been thinking a bit more about this, and I see two desirable
goals of anti-wraparound vacuum in single-user mode:

1. Get out of single-user mode as quickly as possible.

2. Minimize the catch-up work we have to do once we're out.

Currently, a naive vacuum does as much work as possible and leaves a
bunch of WAL streaming and archiving work for later, so that much is
easy to improve upon and we don't have to be terribly sophisticated.
Keeping in mind Andres' point that we don't want to force possibly
unwanted behavior just because we're in single-user mode, it makes
sense to have some kind of option that has the above two goals.
Instead of a boolean, it seems like the new option should specify some
age below which VACUUM will skip the table entirely, and above which
will enter fail-safe mode. As mentioned earlier, the shutdown hint
could spell out the exact command. With this design, it would specify
the fail-safe default, or something else, to use with the option. That
seems doable for v15 -- any thoughts on that approach?

In standard operation, the above goals could be restated as "advance
xmin as quickly as possible" and "generate as little future
'work/debt' as possible, whether dirty pages or WAL". There are some
more sophisticated things we can do in this regard, but something like
the above could also be useful in normal operation. In fact, that
"normal" could be just after we restarted after doing the bare-minimum
in single-user mode, and want to continue freezing and keep things
under control.

---

From: John Naylor <[email-masked]>
Date: Tue, 21 Dec 2021 17:35:05 -0400

I wrote:

> the fail-safe default, or something else, to use with the option.

On second thought, we don't really need another number here. We could
simply go by the existing failsafe parameter, and if the admin wants a
different value, it's already possible to specify
vacuum_failsafe_age/vacuum_multixact_failsafe_age in a session,
including in single-user mode. Perhaps a new boolean called
FAILSAFE_ONLY. If no table is specified, then when generating the list
of tables, include only those with relfrozenxid/relminmxid greater
than their failsafe thresholds.

---

From: Peter Geoghegan <[email-masked]>
Date: Tue, 21 Dec 2021 13:56:30 -0800

> than their failsafe thresholds.

That's equivalent to the quick and dirty patch I wrote (assuming that
the user actually uses this new FAILSAFE_ONLY thing).

But if we're going to add a new option to the VACUUM command (or
something of similar scope), then we might as well add a new behavior
that is reasonably exact -- something that (say) only *starts* a
VACUUM for those tables whose relfrozenxid age currently exceeds half
the autovacuum_freeze_max_age for the table (usually taken from the
GUC, sometimes taken from the reloption), which also forces the
failsafe. And with similar handling for
relminmxid/autovacuum_multixact_freeze_max_age.

In other words, while triggering the failsafe is important, simply *not
starting* VACUUM for relations where there is really no need for it is
at least as important. We shouldn't even think about pruning or
freezing with these tables. (ISTM that the only thing that might be a
bit controversial about any of this is my definition of "safe", which
seems like about the right trade-off to me.)

This new command/facility should probably not be a new flag to the
VACUUM command, as such. Rather, I think that it should either be an
SQL-callable function, or a dedicated top-level command (that doesn't
accept any tables). The only reason to have this is for scenarios
where the user is already in a tough spot with wraparound failure,
like that client of yours. Nobody wants to force the failsafe for one
specific table. It's not general purpose, at all, and shouldn't claim
to be.

--
Peter Geoghegan

---

From: John Naylor <[email-masked]>
Date: Tue, 21 Dec 2021 19:02:12 -0400

> the user actually uses this new FAILSAFE_ONLY thing).

Equivalent but optional.

> freezing with these tables.

Right, not starting where not necessary is crucial for getting out of
single-user mode as quickly as possible. We're in agreement there.

> (ISTM that the only thing that might be a

It seems reasonable to want to start back up and not immediately have
anti-wraparound vacuums kick in. On the other hand, it's not good to
do work while unable to monitor progress, and while more WAL is piling
up. I'm not sure where the right trade off is.

> specific table. It's not general purpose, at all, and shouldn't claim

Makes sense, I'll have a think about what that would look like.

---

From: Masahiko Sawada <[email-masked]>
Date: Wed, 22 Dec 2021 11:39:00 +0900

+1

> specific table. It's not general purpose, at all, and shouldn't claim

Even not in the situation where the database has to run as the
single-user mode to freeze tuples, I think there would be some use
cases where users want to run vacuum (in failsafe mode) on tables with
relfrozenxid/relminmxid greater than their failsafe thresholds before
falling into that situation. I think it’s common that users are
somehow monitoring relfrozenxid/relminmxid and want to manually run
vacuum on them rather than relying on autovacuums. --min-xid-age
option and --min-mxid-age option of vacuumdb command would be good
examples. So I think this new command/facility might not necessarily
need to be specific to single-user mode.

Regards,

---

From: John Naylor <[email-masked]>
Date: Wed, 22 Dec 2021 11:07:49 -0400

> need to be specific to single-user mode.

If we want to leave open the possibility to specify these parameters,
a SQL-callable function seems like the way to go. And even if we
don't, a function is fine.

---

From: Peter Geoghegan <[email-masked]>
Date: Wed, 22 Dec 2021 13:33:28 -0800

> need to be specific to single-user mode.

It wouldn't be specific to single-user mode, since that is not really
special. It's only special in a way that's quite artificial (it can
continue to allocate XIDs past the point where we usually deem it
unsafe).

So, I think we agree; this new emergency vacuuming feature shouldn't
be restricted to single-user mode in any way, and shouldn't care about
whether we're in single user mode or not when run. OTOH, it probably
will be presented as something that is typically run in single user
mode, in situations like the one John's customer found themselves in
-- disastrous, unpleasant situations. It's not just a good policy
(that makes testing easy). The same kind of problem can easily be
caught a little earlier, before the system actually becomes unable to
allocate new XIDs (when not in single-user mode) -- that's quite
likely, and almost as scary.

As I said before, ISTM that the important thing is to have something
dead simple -- something that is easy to use when woken at 4am, when
the DBA is tired and stressed. Something that makes generic choices,
that are not way too conservative, but also don't risk making the
problem worse instead of better.

-- 
Peter Geoghegan

---

From: John Naylor <[email-masked]>
Date: Thu, 31 Mar 2022 16:51:05 +0700

On Wed, Mar 16, 2022 at 4:48 AM Peter Geoghegan <[email-masked]> wrote:
>
> On Wed, Feb 16, 2022 at 12:43 AM John Naylor
> > I'll put some effort in finding any way that it might not be robust.
> > After that, changing the message and docs is trivial.
>
> It would be great to be able to totally drop the idea of using
> single-user mode before Postgres 15 feature freeze. How's that going?

Unfortunately, I was distracted from this work for a time, and just as
I had intended to focus on it during March, I was out sick for 2-3
weeks. I gather from subsequent discussion that a full solution goes
beyond just a new warning message and documentation. Either way I'm
not quite prepared to address this in time for v15.

> I suggest that we apply the following patch as part of that work. It
> adds one last final failsafe check at the point that VACUUM makes a
> final decision on rel truncation.

That is one thing that was in the back of my mind, and it seems
reasonable to me.

---

From: Justin Pryzby <[email-masked]>
Date: Mon, 27 Jun 2022 14:36:09 -0500

On Thu, Feb 03, 2022 at 01:05:50PM -0500, Robert Haas wrote:
> On Thu, Dec 9, 2021 at 8:56 PM Andres Freund <[email-masked]> wrote:
> Yes. This thread seems to be largely devoted to the topic of making
> single-user vacuum work better, but I don't see anyone asking the
> question "why do we have a message that tells people to vacuum in
> single user mode in the first place?". It's basically bad advice,

> The correct thing to do would be to remove
> the hint as bad advice that we never should have offered in the first
> place. And so here. We should not try to make vacuum in single
> user-mode work better or differently, or at least that shouldn't be
> our primary objective. We should just stop telling people to do it. We
> should probably add messages and documentation *discouraging* the use
> of single user mode for recovering from wraparound trouble, exactly
> the opposite of what we do now. There's nothing we can do in
> single-user mode that we can't do equally well in multi-user mode. If
> people try to fix wraparound problems in multi-user mode, they still
> have read-only access to their database, they can use parallelism,
> they can use command line utilities like vacuumdb, and they can use
> psql which has line editing and allows remote access and is a way
> nicer user experience than running postgres --single. We need a really
> compelling reason to tell people to give up all those advantages, and
> there is no such reason. It makes just as much sense as telling people
> to deal with wraparound problems by angering a live anaconda.

By chance, I came across this prior thread which advocated the same thing in a
initially (rather than indirectly as in this year's thread).

https://www.postgresql.org/message-id/flat/CAMT0RQTmRj_Egtmre6fbiMA9E2hM3BsLULiV8W00stwa3URvzA%40mail.gmail.com
|We should stop telling users to "vacuum that database in single-user mode"

---

From: Peter Geoghegan <[email-masked]>
Date: Mon, 27 Jun 2022 13:36:36 -0700

> initially (rather than indirectly as in this year's thread).

Revisiting this topic reminded me that PostgreSQL 14 (the first
version that had the wraparound failsafe mechanism controlled by
vacuum_failsafe_age) has been a stable release for 9 months now. As of
today I am still not aware of even one user that ran into the failsafe
mechanism in production. It might well have happened by now, of
course, but I am not aware of any specific case. Perhaps this will
change soon enough -- maybe somebody else will read this and enlighten
me.

To me the fact that the failsafe seems to seldom kick-in in practice
suggests something about workload characteristics in general: that it
isn't all that common for users to try to get away with putting off
freezing until a table attains an age that is significantly above 1
billion XIDs.

When people talk about things like 64-bit XIDs, I tend to wonder: if 2
billion XIDs wasn't enough, why should 4 billion or 8 billion be
enough? *Maybe* the system can do better by getting even further into
debt than it can today, but you can't expect to avoid freezing
altogether (without significant work elsewhere). My general sense is
that freezing isn't a particularly good thing to try to do lazily --
even if we ignore the risk of an eventual wraparound failure.

-- 
Peter Geoghegan