Message-ID: <fa83a77bbca5da4d4864a49c8a27df323f23d28b.camel@cybertec.at>
Subject: Re: Disabling vacuum truncate for autovacuum
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Will Storey <will@summercat.com>, pgsql-general@lists.postgresql.org
Date: Tue, 17 Dec 2024 08:30:19 +0100
In-Reply-To: <Z2DE4lDX4tHqNGZt@dev.null>
References: <Z2DE4lDX4tHqNGZt@dev.null>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.54.2 (3.54.2-1.fc41) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/fa83a77bbca5da4d4864a49c8a27df323f23d28b.camel%40cybertec.at>
Precedence: bulk

On Mon, 2024-12-16 at 16:25 -0800, Will Storey wrote:
> I would like to disable vacuum's truncate behaviour for autovacuum.
> Previously I had an outage due to its access exclusive lock when it was
> replicated to a hot standby.
>=20
> When that outage happened it was from a VACUUM call in a cronjob rather
> than autovacuum. I now run such VACUUMs with TRUNCATE false which avoids
> the issue for these. However I've realized that autovacuum could cause th=
is
> as well.
>
> I believe the only way to disable this for autovacuum is by changing the
> vacuum_truncate storage parameters on tables. (Ignoring the now removed
> old_snapshot_threshold option).

Yes, you can only do that table by table.


> I'm thinking of altering all my tables to
> turn it off. Is this a horrible idea? I expect I would need to monitor
> tables for problematic growth, but that might be better than a surprise
> outage. I suppose the growth could cause an outage too, but I'm thinking =
it
> would be more controllable.

I don't see a problem with disabling VACUUM truncation for normal workloads=
.
Some applications, like volatile queue tables, might need the feature, but
I'd assume that to be the exception.

> Would I need to disable the settings on catalog tables too? (To rule out
> any possibility of it happening). Are there any other things I might be
> missing?

Potentially yes.  But unless you are using temporary tables or create,
alter and drop lots of objects, that shouldn't be necessary.

> I am also wondering if having an autovacuum setting to control it would b=
e
> a good idea for a feature.

I'm all for that.

Yours,
Laurenz Albe