Message-ID: <fb49cf06-ee2b-4e47-9156-40273165d5fb@postgrespro.ru>
Date: Fri, 21 Mar 2025 14:50:22 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Optimize truncation logic to reduce AccessExclusive lock impact
From: Yura Sokolov <y.sokolov@postgrespro.ru>
To: David Rowley <dgrowleyml@gmail.com>, Stepan Neretin <slpmcf@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
References: 
 <CA+Yyo5Trs9x03-sc4PpmEXPY9K_B_jbEbNd01RbF+vOxs3zVUA@mail.gmail.com>
 <CAApHDvrfngqCpQ09LKdr-BnJEVHW0=wAhxfRBnw=HJ2645cJPg@mail.gmail.com>
 <0be607a5-20f6-4894-bb22-412e3c784bc2@postgrespro.ru>
Content-Language: en-US
In-Reply-To: <0be607a5-20f6-4894-bb22-412e3c784bc2@postgrespro.ru>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/fb49cf06-ee2b-4e47-9156-40273165d5fb%40postgrespro.ru>
Precedence: bulk

19.03.2025 19:05, Yura Sokolov wrote:
> 19.03.2025 05:09, David Rowley wrote:
>> On Tue, 18 Mar 2025 at 19:04, Stepan Neretin <slpmcf@gmail.com> wrote:
>>> We propose modifying the truncation condition in should_attempt_truncation to avoid unnecessary full buffer scans. 
>> I'm just following this code through. Looking at
>> DropRelationBuffers(), d6ad34f34 added an optimisation so that we can
>> skip scanning all of shared buffers while in recovery (where
>> smgrnblocks_cached() can return a non-InvalidBlockNumber value), which
>> the following code might decide to lookup the buffers one-by-one
>> rather than scanning the entire buffer pool:
>>
>> if (BlockNumberIsValid(nBlocksToInvalidate) &&
>>     nBlocksToInvalidate < BUF_DROP_FULL_SCAN_THRESHOLD)
>>     {
>>         for (j = 0; j < nforks; j++)
>>             FindAndDropRelationBuffers(rlocator.locator, forkNum[j],
>>                 nForkBlock[j], firstDelBlock[j]);
>>         return;
>> }
>>
>> While I don't know this code too well, from a quick look, it seem to
>> me that since DropRelationBuffers() is being called from
>> smgrtruncate() in this case, smgrtruncate() has access to old_nblocks
>> so it knows the old size of the relation. Couldn't we do something
>> like have another version of DropRelationBuffers() which accepts a
>> parameter for the old number of blocks and uses that instead of
>> calling smgrnblocks_cached()?  That would allow the
>> FindAndDropRelationBuffers() optimisation to be used in cases where
>> the buffers to truncate is small or shared buffers is large.
> 
> Yes, it will work for smaller relations.
> But it doesn't fix issue with large relations:
> - imho, there is no big need to truncate 2TB table to release every 8MB of
> free space, but it happens now.

After thinking a bit, I realized I was not quite right: if large table is
truncated on small number of pages, and we know old size and new one, we
can precisely remove them from shared buffers.

Exactly as you've described, excuse me for not understanding you clearly
for the first time.

But the question still remains: should we use this possibility with huge
tables, or we should just truncate them rarely?

-- 
regards
Yura Sokolov aka funny-falcon