MIME-Version: 1.0
References: <Z2DE4lDX4tHqNGZt@dev.null> <fa83a77bbca5da4d4864a49c8a27df323f23d28b.camel@cybertec.at>
 <CABwTF4U3xkF=ZRi2pztUDxohoN8h6XL10=QmTtuTXoMjzu5-zg@mail.gmail.com>
 <f82fc7fbb69a8c8000ad74ced50381257fe57838.camel@cybertec.at>
 <CA+TgmoZMXN19eorKdeiiFCv3AJFVaUAfkzRuamnt8A9U8uJSqg@mail.gmail.com>
 <Z7Tl2d7HrG1AQEOc@nathan> <CABwTF4XPc1_y=khhjErd=Oz8R20ZH05KigiAnMjjA+028QbohQ@mail.gmail.com>
 <Z8H-tHaYZ37lVZHb@nathan> <28773a66-fb88-41cf-a7ec-4216e6c91c94@oss.nttdata.com>
 <Z9ROT9v4rHjDYWNX@nathan> <CABwTF4WFp-oNSm6utik6g-1HZBxijTJBcuX7BKD8MYqza+KMnw@mail.gmail.com>
 <6f2f2167f4be09e6ca9251c8f69dfe01809d68be.camel@cybertec.at>
In-Reply-To: <6f2f2167f4be09e6ca9251c8f69dfe01809d68be.camel@cybertec.at>
From: Robert Treat <rob@xzilla.net>
Date: Sun, 16 Mar 2025 18:24:59 -0400
Message-ID: <CABV9wwNagobmRMCxs0dKqWmKXaQTUQj+oOOTw=fsPAgbBfVhew@mail.gmail.com>
Subject: Re: Disabling vacuum truncate for autovacuum
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: Gurjeet Singh <gurjeet@singh.im>, Nathan Bossart <nathandbossart@gmail.com>, 
	Andres Freund <andres@anarazel.de>, Will Storey <will@summercat.com>, 
	Fujii Masao <masao.fujii@oss.nttdata.com>, Robert Haas <robertmhaas@gmail.com>, 
	Postgres Hackers <pgsql-hackers@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CABV9wwNagobmRMCxs0dKqWmKXaQTUQj%2BoOOTw%3DfsPAgbBfVhew%40mail.gmail.com>
Precedence: bulk

On Sun, Mar 16, 2025 at 1:29=E2=80=AFAM Laurenz Albe <laurenz.albe@cybertec=
.at> wrote:
>
> On Sat, 2025-03-15 at 17:14 -0700, Gurjeet Singh wrote:
> > > One other difference in my version of the patch [0] is to call this G=
UC
> > > vacuum_truncate and have it apply to both autovacuum and VACUUM.  I d=
id
> > > this for the following reasons:
> >
> > +1 for the GUC name for the reasons you identified. But -1 for the beha=
viour
> > where the reloption and vacuum command's options overrides GUC.
> >
> > I'd like to bring our attention back to how this thread started. Will s=
tarted
> > the discussion by asking for a way to disable autovacuum's truncate beh=
aviour.
> > Even though the production outage he faced was due to manual vacuum, he=
 was
> > worried about the same behaviour that autovacuum may cause, especially =
since the
> > parameter old_snapshot_threshold is no longer available; old_snapshot_t=
hreshold
> > allowed sysadmins like Will to disable the truncation behaviour globall=
y. I
> > provided an anecdote where autovacuum's truncation behaviour had in fac=
t caused
> > a replication outage as well as the consequent application outage.
> >
> > The behaviour that is being proposed here does not prevent that situati=
on from
> > arising again. A sysadmin who's trying to prevent replication outage an=
d
> > a consequent application outage won't benefit from tuning vacuum_trunca=
te GUC,
> > because a reloption or VACUUM (TRUNCATE) command will override its beha=
viour,
> > and lead to an outage.
> >
> > We want this new GUC to give the sysadmin the power to override the per=
-relation
> > and per-command settings.
> >
> >
> > I guess what I'm looking for is a global switch that guarantees no rela=
tion
> > truncation will take place on the instance, so that the relevant replic=
ation
> > record is never emitted, and hence will never lead to a blocked replica=
tion on
> > the replica and never cause a consequent outage of applications connect=
ed to the
> > replica(s).
>
> Essentially, you are looking for something that reinstates the unintended=
 side
> effect of "old_snapshot_threshold" that some people relied on.
>
> I understand your reasoning.
> What I am worried about, and why I am against that, is the POLA violation=
 this
> constitutes.  I PostgreSQL, there usually are global settings that can be
> overridden by per-relation settings.  Doing it differently here would sur=
prise
> and confuse many users.
>

Agreed... I couldn't help when reading through this thread the same
thought that the normal way we do this is by trying to pick the
sensible default and then giving options to override it on a more
granular level.

> This is not the only way a user can do damage to the system by overriding=
 the
> administrator's settings.  Users can override all autovacuum settings and=
 even
> disable autovacuum on a table.  I don't think these settings are less dan=
gerous
> than VACUUM truncation.
>

Agreed. To the degree I am sympathetic to Gurjeet's concern, it sounds
more like he is trying to solve a socio-technical issue, which I think
is beyond something that we can guarantee help with; ie presuming we
provide a convenient way to disable this generally, if people are
going to go out of their way to do the thing they have been told not
to...

So if the general idea is a guc "vacuum_truncate" which sets a global
default for whether vacuums and autovacuums should do truncation, and
we then have the storage parameter which would override the global
setting. And to be clear, there is also the decision on whether the
VACUUM commands default should default to truncate=3Don (like the
existing behavior) or truncate =3D=3D vacuum_truncate guc, unless
explicitly set. I think the latter is probably the right way to go.

As an aside, thinking through a bunch of different scenarios, I think
I would actually be in favor of changing the default behavior to false
(I don't think it buys much for most workloads, and I'd love to see us
move towards defaults that minimize risk), but I suspect that may be a
bridge too far, at least in this release; but maybe down the line...
for now though I'd take an easy way for users to make it the default.


Robert Treat
https://xzilla.net