Subject: Re: Disabling vacuum truncate for autovacuum

From: Gurjeet Singh <[email-masked]>
Date: Thu, 27 Feb 2025 20:29:16 -0800

On Mon, Jan 27, 2025 at 1:55 AM Laurenz Albe <[email-masked]> wrote:
>
> 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.

Current patch behaviour is that if the autovacuum_vacuum_truncate is false, then
autovacuum will _not_ truncate any relations. If the parameter's value is true
(the default), then the relation's reloption will be honored.

A table-owner, or the database-owner, may enable truncation of a table, as they
may be trying to be nice and return the unused disk space back to the
OS/filesystem. But if the sysadmin/DBA (who is ultimately responsible for the
health of the entire db instance, as well as of any replicas of the db
instance),
wants to disable truncation across all databases to ensure that the replication
does not get stuck, then IMHO Postgres should empower the sysadmin to make
that decision, and override the relation-level setting enabled by the table-
owner or the database-owner.

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

Agreed. I'll consider writing a separate patch for this.

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

Makes sense. I don't think anything special needs to be done in the patch to
address this.

> Eventually, the patch should have documentation and regression tests.

Documentation added. Pointers on if, where, and what kind of tests to add will
be appreciated.

On Mon, Jan 27, 2025 at 12:38 PM Robert Haas <[email-masked]> wrote:
>
> On Mon, Jan 27, 2025 at 4:55 AM Laurenz Albe <[email-masked]> 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.

If we want to maintain the convention of autovacuum parameters names to be of
the format "autovacuum_<vacuum-option's-name>" then I believe the name
autovacuum_vacuum_truncate (boolean) would be better, as compared to my original
proposal (autovacuum_disable_vacuum_truncate), or Laurenz's proposal above. The
default value should be true, to match the current autovacuum behaviour.

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

Taking silence from others to be a sign of no opposition, I'm moving forward
with the patch.

On Tue, Feb 18, 2025 at 11:56 AM Nathan Bossart
<[email-masked]> wrote:
>
> On Mon, Jan 27, 2025 at 03:38:39 PM -0500, Robert Haas wrote:
> > Also, how sure are we that turning this off globally is a solid idea?

> In any case, it's
> already possible to achieve $SUBJECT with a trivial script that sets
> storage parameters on all tables, so IMHO it would be silly to withhold a
> global setting that does the same thing just on principle.

+1

For documentation of this GUC, I borrowed heavily from the relevant sections of
CREATE TABLE and VACUUM docs.

There are 3 ways I wrote one of the sentences in the docs. I picked the last
one, as it is concise and clearer than the others. If others feel a different
choice of words would be better, I'm all ears.

         If <literal>false</literal>, autovacuum will not perform the
         truncation, even if the <literal>vacuum_truncate</literal> option has
         been set to <literal>true</literal> for the table being processed.

         If <literal>false</literal>, autovacuum will not perform the
         truncation, and it ignores the <literal>vacuum_truncate</literal>
         option for the tables it processes.

         If <literal>false</literal>, autovacuum will not perform the truncation
         on any tables it vacuums. The <literal>vacuum_truncate</literal> option
         on the tables is ignored.

PS: Nathan, your latest email arrived as I was preparing this email and patch,
so this email and patch does not address concerns, if any, in your latest email.
I will try to respond to it soon.

Best regards,
Gurjeet
http://Gurje.et

---

From: Nathan Bossart <[email-masked]>
Date: Fri, 28 Feb 2025 12:21:40 -0600

> owner or the database-owner.

IIUC reloptions with corresponding GUCs typically override the GUC setting,
although autovacuum_enabled is arguably an exception.  If setting the GUC
to false overrides the relation-specific settings, it also becomes more
difficult to enable truncation for just a couple of tables, although that
might not be a popular use-case.  Furthermore, even if we do want the GUC
to override the reloption, it won't override VACUUM (TRUNCATE).

> Agreed. I'll consider writing a separate patch for this.

Perhaps it would be useful to say whether truncation was attempted in the
output of VACUUM (VERBOSE) and the autovacuum logs.

> Makes sense. I don't think anything special needs to be done in the patch to

Hm.  I was thinking PGC_USERSET might make sense for this one, but that was
only because I didn't see any technical reason to restrict it.  I don't
know whether limiting it accomplishes anything beyond making it more
cumbersome for users to choose their desired default truncation setting.

> I will try to respond to it soon.

Oops, sorry for the conflict.  I'm happy to take a step back and be the
reviewer/committer for this one.

-- 
nathan

---

From: Fujii Masao <[email-masked]>
Date: Thu, 6 Mar 2025 08:54:59 +0900

> to override the reloption, it won't override VACUUM (TRUNCATE).

+1 to having the reloption (if specified) override the GUC setting.
That is, I think that autovacuum_vacuum_truncate as defining
the default behavior for VACUUM truncation, and that the GUC should
only apply when neither the TRUNCATE option in VACUUM nor
the reloption is set.

Regards,

-- 
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION