Re: Disabling vacuum truncate for autovacuum - Mailing list pgsql-hackers

On Thu, Feb 27, 2025 at 08:29:16PM -0800, Gurjeet Singh wrote:
> On Mon, Jan 27, 2025 at 1:55 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>> 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.

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

>> > 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 it would be useful to say whether truncation was attempted in the
output of VACUUM (VERBOSE) and the autovacuum logs.

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

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.

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

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

-- 
nathan