Re: Disabling vacuum truncate for autovacuum

On Thu, 2025-01-23 at 22:33 -0800, Gurjeet Singh wrote:
> > > 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.
> 
> 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 :-)

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

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

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

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

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