MIME-Version: 1.0
References: 
 <CAFBsxsFL5LoWanSLDjt_SjS9=FU_9XWi22iHMexRS2SubkX-Vw@mail.gmail.com>
 <CAH2-Wz=Z5Jjd1y8kCfXd6e-GSvKiL8XEmxoJJFNHcekM4aw-Vg@mail.gmail.com>
 <CAH2-Wzn3pE8rNzBxh-Qpmq=zxFWFpoC7LWLbyDg_1XZ3wJOtUA@mail.gmail.com>
 <CAFBsxsG-VDhXYa25uERfDGD4dO_AZ3d+Dv2NF+DJBADZiXejpw@mail.gmail.com>
 <CAH2-Wznop1jM3j6E1xTcG--kFQ1hW5cMmM8B1965Mz+uUg6HSg@mail.gmail.com>
 <3F7FD826-36EF-4991-AB37-81675AFEBF54@amazon.com>
 <0544901A-0FC5-46B5-801B-F30AC0B81A19@amazon.com>
 <CAH2-Wz=9mfmSL5QDG9GmXt2Av8NBvNSo+CF7FPW3g80awHab7Q@mail.gmail.com>
 <DB4D9904-146A-4B21-8362-99F885C489E1@amazon.com>
 <CAH2-Wz=Eb-jrhFK6=_WNT6V1JAK-qFOTu_4-yOpAhkEES4VeNA@mail.gmail.com>
 <20211221034630.maoteglpjqpgh62g@alap3.anarazel.de>
 <CAD21AoCz8=-jBTUBcQSveOMCLkJ=yUw5TYtfVPWKTXiLuan_CQ@mail.gmail.com>
 <CAH2-Wzn+PJ99oJRnN32ojvRpgH4xjmTOJZbt+GTHpDWN655Adg@mail.gmail.com>
 <CAD21AoAXHZ0J-Xd0RfP_VpkUnGv0G_gCbwt3aCuy6weOKNaHNw@mail.gmail.com>
In-Reply-To: 
 <CAD21AoAXHZ0J-Xd0RfP_VpkUnGv0G_gCbwt3aCuy6weOKNaHNw@mail.gmail.com>
From: John Naylor <john.naylor@enterprisedb.com>
Date: Tue, 21 Dec 2021 13:36:20 -0400
Message-ID: 
 <CAFBsxsHCBWTJx5j6zKogh5-_wuggcHJT7g+5_TQVMyKvgCT4Fw@mail.gmail.com>
Subject: Re: do only critical work during single-user vacuum?
To: Masahiko Sawada <sawada.mshk@gmail.com>
Cc: Peter Geoghegan <pg@bowt.ie>, Andres Freund <andres@anarazel.de>,
	"Bossart, Nathan" <bossartn@amazon.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CAFBsxsHCBWTJx5j6zKogh5-_wuggcHJT7g%2B5_TQVMyKvgCT4Fw%40mail.gmail.com>
Precedence: bulk

On Tue, Dec 21, 2021 at 3:56 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
> On Tue, Dec 21, 2021 at 1:53 PM Peter Geoghegan <pg@bowt.ie> wrote:
> >
> > On Mon, Dec 20, 2021 at 8:40 PM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> > > BTW a vacuum automatically enters failsafe mode under the situation
> > > 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.
>
> 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.

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.

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