Thread: 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

On Mon, Jan 27, 2025 at 4:55 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> 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