Message-ID: <6f2f2167f4be09e6ca9251c8f69dfe01809d68be.camel@cybertec.at>
Subject: Re: Disabling vacuum truncate for autovacuum
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Gurjeet Singh <gurjeet@singh.im>, Nathan Bossart
 <nathandbossart@gmail.com>,  Andres Freund <andres@anarazel.de>, Will
 Storey <will@summercat.com>
Cc: Fujii Masao <masao.fujii@oss.nttdata.com>, Robert Haas
	 <robertmhaas@gmail.com>, Postgres Hackers <pgsql-hackers@postgresql.org>
Date: Sun, 16 Mar 2025 06:29:17 +0100
In-Reply-To: <CABwTF4WFp-oNSm6utik6g-1HZBxijTJBcuX7BKD8MYqza+KMnw@mail.gmail.com>
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>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.54.3 (3.54.3-1.fc41) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/6f2f2167f4be09e6ca9251c8f69dfe01809d68be.camel%40cybertec.at>
Precedence: bulk

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 GUC
> > vacuum_truncate and have it apply to both autovacuum and VACUUM.  I did
> > this for the following reasons:
>=20
> +1 for the GUC name for the reasons you identified. But -1 for the behavi=
our
> where the reloption and vacuum command's options overrides GUC.
>=20
> I'd like to bring our attention back to how this thread started. Will sta=
rted
> the discussion by asking for a way to disable autovacuum's truncate behav=
iour.
> Even though the production outage he faced was due to manual vacuum, he w=
as
> worried about the same behaviour that autovacuum may cause, especially si=
nce the
> parameter old_snapshot_threshold is no longer available; old_snapshot_thr=
eshold
> allowed sysadmins like Will to disable the truncation behaviour globally.=
 I
> provided an anecdote where autovacuum's truncation behaviour had in fact =
caused
> a replication outage as well as the consequent application outage.
>=20
> The behaviour that is being proposed here does not prevent that situation=
 from
> arising again. A sysadmin who's trying to prevent replication outage and
> a consequent application outage won't benefit from tuning vacuum_truncate=
 GUC,
> because a reloption or VACUUM (TRUNCATE) command will override its behavi=
our,
> and lead to an outage.
>=20
> We want this new GUC to give the sysadmin the power to override the per-r=
elation
> and per-command settings.
>=20
>=20
> I guess what I'm looking for is a global switch that guarantees no relati=
on
> truncation will take place on the instance, so that the relevant replicat=
ion
> record is never emitted, and hence will never lead to a blocked replicati=
on on
> the replica and never cause a consequent outage of applications connected=
 to the
> replica(s).

Essentially, you are looking for something that reinstates the unintended s=
ide
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 t=
his
constitutes.  I PostgreSQL, there usually are global settings that can be
overridden by per-relation settings.  Doing it differently here would surpr=
ise
and confuse many users.

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

Yours,
Laurenz Albe