Message-ID: <28773a66-fb88-41cf-a7ec-4216e6c91c94@oss.nttdata.com>
Date: Thu, 6 Mar 2025 08:54:59 +0900
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Disabling vacuum truncate for autovacuum
Content-Language: en-US
To: Nathan Bossart <nathandbossart@gmail.com>,
 Gurjeet Singh <gurjeet@singh.im>
Cc: Robert Haas <robertmhaas@gmail.com>,
 Laurenz Albe <laurenz.albe@cybertec.at>,
 Postgres Hackers <pgsql-hackers@postgresql.org>,
 Will Storey <will@summercat.com>
References: <Z2DE4lDX4tHqNGZt@dev.null>
 <fa83a77bbca5da4d4864a49c8a27df323f23d28b.camel@cybertec.at>
 <CABwTF4U3xkF=ZRi2pztUDxohoN8h6XL10=QmTtuTXoMjzu5-zg@mail.gmail.com>
 <f82fc7fbb69a8c8000ad74ced50381257fe57838.camel@cybertec.at>
 <CA+TgmoZMXN19eorKdeiiFCv3AJFVaUAfkzRuamnt8A9U8uJSqg@mail.gmail.com>
 <Z7Tl2d7HrG1AQEOc@nathan>
 <CABwTF4XPc1_y=khhjErd=Oz8R20ZH05KigiAnMjjA+028QbohQ@mail.gmail.com>
 <Z8H-tHaYZ37lVZHb@nathan>
From: Fujii Masao <masao.fujii@oss.nttdata.com>
In-Reply-To: <Z8H-tHaYZ37lVZHb@nathan>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/28773a66-fb88-41cf-a7ec-4216e6c91c94%40oss.nttdata.com>
Precedence: bulk


On 2025/03/01 3:21, Nathan Bossart wrote:
> 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).

+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