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
