public inbox for [email protected]
help / color / mirror / Atom feedFrom: Nathan Bossart <[email protected]>
To: Gurjeet Singh <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: Laurenz Albe <[email protected]>
Cc: Postgres Hackers <[email protected]>
Cc: Will Storey <[email protected]>
Subject: Re: Disabling vacuum truncate for autovacuum
Date: Fri, 28 Feb 2025 12:21:40 -0600
Message-ID: <Z8H-tHaYZ37lVZHb@nathan> (raw)
In-Reply-To: <CABwTF4XPc1_y=khhjErd=Oz8R20ZH05KigiAnMjjA+028QbohQ@mail.gmail.com>
References: <[email protected]>
<[email protected]>
<CABwTF4U3xkF=ZRi2pztUDxohoN8h6XL10=QmTtuTXoMjzu5-zg@mail.gmail.com>
<[email protected]>
<CA+TgmoZMXN19eorKdeiiFCv3AJFVaUAfkzRuamnt8A9U8uJSqg@mail.gmail.com>
<Z7Tl2d7HrG1AQEOc@nathan>
<CABwTF4XPc1_y=khhjErd=Oz8R20ZH05KigiAnMjjA+028QbohQ@mail.gmail.com>
On Thu, Feb 27, 2025 at 08:29:16PM -0800, Gurjeet Singh wrote:
> On Mon, Jan 27, 2025 at 1:55 AM Laurenz Albe <[email protected]> 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
view thread (3+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Disabling vacuum truncate for autovacuum
In-Reply-To: <Z8H-tHaYZ37lVZHb@nathan>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox