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>
In-Reply-To: 
 <CAFBsxsE1TWidnMs=RMx1eb2QxqmNqdDaLLN5E7SP_v5_g2ZRwA@mail.gmail.com>
From: Peter Geoghegan <pg@bowt.ie>
Date: Tue, 21 Dec 2021 13:56:30 -0800
Message-ID: 
 <CAH2-Wzmt29ZUwXW+9ACvxMoN9AjSMuH_Oabk1_mJt78f58WdvQ@mail.gmail.com>
Subject: Re: do only critical work during single-user vacuum?
To: John Naylor <john.naylor@enterprisedb.com>
Cc: Masahiko Sawada <sawada.mshk@gmail.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"
Archived-At: 
 <https://www.postgresql.org/message-id/CAH2-Wzmt29ZUwXW%2B9ACvxMoN9AjSMuH_Oabk1_mJt78f58WdvQ%40mail.gmail.com>
Precedence: bulk

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

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