Re: Disabling vacuum truncate for autovacuum

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.
>
> 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 this
> 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 be
> a good idea for a feature.

I'm all for that.

Yours,
Laurenz Albe