Subject: Disabling vacuum truncate for autovacuum

From: Will Storey <[email-masked]>
Date: Mon, 16 Dec 2024 16:25:06 -0800

Hi!

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. This is of interest to me because I'm looking at tuning autovacuum
and getting rid of the cronjob, but I've realized relying on autovacuum
could be dangerous because of the truncates.

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). 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.

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?

I am also wondering if having an autovacuum setting to control it would be
a good idea for a feature. That would be simpler for me than altering all
my tables and help me avoid missing any (e.g. catalogs, new tables).

I might be worrying needlessly about this as maybe it is unlikely to
happen. I suppose it is workload dependent.

Thank you!

Will

---

From: Laurenz Albe <[email-masked]>
Date: Tue, 17 Dec 2024 08:30:19 +0100

On Mon, 2024-12-16 at 16:25 -0800, Will Storey wrote:
> as well.
> old_snapshot_threshold option).

Yes, you can only do that table by table.

> I'm thinking of altering all my tables to

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.

> any possibility of it happening). Are there any other things I might be

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

> a good idea for a feature.

I'm all for that.

Yours,
Laurenz Albe

---

From: Will Storey <[email-masked]>
Date: Tue, 17 Dec 2024 10:03:44 -0800

> alter and drop lots of objects, that shouldn't be necessary.

I see. That makes sense. I do have some things that use temporary tables as
well as some jobs that create/drop objects. They are not very frequent nor
are there a huge number objects involved, but I suppose it could still be
an issue. I'm not keen on altering the catalogs, but it sounds like if I
want to be very safe then I would need to.

> > a good idea for a feature.

I previously had old_snapshot_threshold enabled, which would have done this
anyway I believe, including for the catalog tables. That was convenient!

> Laurenz Albe

Thank you Laurenz! I've read a bunch of your writing and I've learned a lot
from you. I'm a big fan :-). Thank you for what you do!

---

From: Gurjeet Singh <[email-masked]>
Date: Thu, 23 Jan 2025 22:33:29 -0800

(moving discussion to -hackers, for patch-review)

> > replicated to a hot standby.

I can attest to one production outage caused by this behaviour of autovacuum.
The truncate operation performed by autovacuum, when being replayed on the
replica, was blocked by a query. Any new queries on that relation were blocked
by replication.

> Yes, you can only do that table by table.

That is unfortunate. Although doing so provides a granular control over which
relations one would like to exclude from truncation, it may not always be
desirable; a DBA/sysadmin may want to prevent this problem system-wide.

Also, this not really scalable since it requires that a maintenance operation
regularly connect to every database and apply this setting to all the relations,
for the fear that there may be new objects somewhere in the cluster since last
maintenance, which may cause this problem. It would be error prone, too,
considering that the list of databases in a cluster may change over time. And
then there's the added burden of monitoring the status of this maintenance
operation to ensure it's running successfully every time.

Turning on a system-wide setting that disables autovacuum truncation may look
like a heavy hammer, but in certain situations this may be preferable to the
risk of causing outage in production systems. It may be preferable to let the
system consume disk space by not truncating the tables, as opposed to running
the risk of blocked queries. Disk is cheap, and is possibly already being
monitored in a production system.

I understand Jeremy's contention upthread against adding such a feature at
global level, but I'm in favor of adding this feature since it prevents a sudden
and unpredictable impact on production systems, and instead leads to a gradual
escalation of the problem that can be monitored and addressed by a sysadmin/DBA
at a time that's convenient for them.

> > a good idea for a feature.

Please see attached an initial patch to disable truncation behaviour in
autovacuum. This patch retains the default behavior of autovacuum truncating
relations. The user is allowed to change the behaviour and disable relation
truncations system-wide by setting autovacuum_disable_vacuum_truncate = true.
Better parameter names welcome :-)

One additional improvement I can think of is to emit a WARNING or NOTICE message
that truncate operation is being skipped, perhaps only if the truncation
would've freed up space over a certain threshold.

Perhaps there's value in letting this parameter be specified at database level,
but I'm not able to think of a reason why someone would want to disable this
behaviour on just one database. So leaving the parameter context to be the same
as most other autovacuum parameters: SIGHUP.

Best regards,
Gurjeet
http://Gurje.et

---

From: Laurenz Albe <[email-masked]>
Date: Mon, 27 Jan 2025 10:55:34 +0100

> Better parameter names welcome :-)

I hope it is possible to override the global setting with the "vacuum_truncate"
option on an individual table.

My suggestion for the parameter name is "autovacuum_disable_truncate".

> would've freed up space over a certain threshold.

Interesting idea, but I think it is independent from this patch.

> as most other autovacuum parameters: SIGHUP.

I can imagine setting that on only a certain database.  Different databases
typically have different applications, which have different needs.

Eventually, the patch should have documentation and regression tests.

Yours,
Laurenz Albe

---

From: Robert Haas <[email-masked]>
Date: Mon, 27 Jan 2025 15:38:39 -0500

> My suggestion for the parameter name is "autovacuum_disable_truncate".

Then it would have a different name than the reloption, and the
opposite sense. In most cases, we've been able to keep those matching
-- autovacuum vs. autovacuum_enabled being, I believe, the only
current mismatch.

Also, how sure are we that turning this off globally is a solid idea?
Off-hand, it doesn't sound that bad: there are probably situations in
which autovacuum never truncates anything anyway just because the tail
blocks of the relations always contain at least 1 tuple. But we should
be careful not to add a setting that is far more likely to get people
into trouble than to get them out of it. It would be good to hear what
other people think about the risk vs. reward tradeoff in this case.

-- 
Robert Haas
EDB: http://www.enterprisedb.com