Re: Optimize truncation logic to reduce AccessExclusive lock impact

On Tue, 18 Mar 2025 at 19:04, Stepan Neretin <slpmcf(at)gmail(dot)com> wrote:
> We propose modifying the truncation condition in should_attempt_truncation to avoid unnecessary full buffer scans. The new formula ensures we only attempt truncation when we can free at least 3% of the relation size + 2 pages. This change prevents excessive truncation attempts on small tables while reducing the impact on larger relations. Previously, small tables could theoretically be truncated to a single page, but with this change, they may remain around 3 pages instead. We don't see this as a significant issue.

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.

As for the patch being proposed, it looks like you're maybe just
showing the answer to the question left in the comment below is "Yes".

/*
* Space/time tradeoff parameters: do these need to be user-tunable?
*
* To consider truncating the relation, we want there to be at least
* REL_TRUNCATE_MINIMUM or (relsize / REL_TRUNCATE_FRACTION) (whichever
* is less) potentially-freeable pages.
*/
#define REL_TRUNCATE_MINIMUM 1000
#define REL_TRUNCATE_FRACTION 16

David