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>
 <CAFBsxsHCBWTJx5j6zKogh5-_wuggcHJT7g+5_TQVMyKvgCT4Fw@mail.gmail.com>
 <CAFBsxsE1TWidnMs=RMx1eb2QxqmNqdDaLLN5E7SP_v5_g2ZRwA@mail.gmail.com>
 <CAH2-Wzmt29ZUwXW+9ACvxMoN9AjSMuH_Oabk1_mJt78f58WdvQ@mail.gmail.com>
In-Reply-To: 
 <CAH2-Wzmt29ZUwXW+9ACvxMoN9AjSMuH_Oabk1_mJt78f58WdvQ@mail.gmail.com>
From: Masahiko Sawada <sawada.mshk@gmail.com>
Date: Wed, 22 Dec 2021 11:39:00 +0900
Message-ID: 
 <CAD21AoCN_T1kdnnNNyzO3yVHd+HxOeV=2FP_Qh5vDM=38q=Yjg@mail.gmail.com>
Subject: Re: do only critical work during single-user vacuum?
To: Peter Geoghegan <pg@bowt.ie>
Cc: John Naylor <john.naylor@enterprisedb.com>,
 Andres Freund <andres@anarazel.de>,
	"Bossart, Nathan" <bossartn@amazon.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAD21AoCN_T1kdnnNNyzO3yVHd%2BHxOeV%3D2FP_Qh5vDM%3D38q%3DYjg%40mail.gmail.com>
Precedence: bulk

On Wed, Dec 22, 2021 at 6:56 AM Peter Geoghegan <pg@bowt.ie> wrote:
>
> On Tue, Dec 21, 2021 at 1:31 PM John Naylor
> <john.naylor@enterprisedb.com> wrote:
> > 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.
>
> 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.)

+1

>
> 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.

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=E2=80=99s 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,

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