Optimize truncation logic to reduce AccessExclusive lock impact

Hi hackers,

We've encountered an issue where autovacuum holds an AccessExclusive lock
for an extended period when attempting to truncate heap tables. The root
cause appears to be the way autovacuum handles block truncation,
specifically when scanning shared buffers during smgrtruncate. This issue
is particularly painful for large shared buffer configurations (e.g., 1.8
TiB with NUMA and multiple workers), and pg_statistic is among the most
affected tables.

When autovacuum detects freeable blocks at the tail of a relation, it
attempts to truncate them. It acquires an AccessExclusive lock and scans
all shared buffers to remove the corresponding blocks. With a large shared
buffer configuration (e.g., 1.8 TiB, 200 million blocks, and multiple NUMA
nodes), this process is slow and significantly impacts performance.

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.

The idea for this patch was proposed by Yuriy Sokolov.

There is also an open discussion about optimizing the shared buffer
traversal to minimize the impact of AccessExclusive locks. This patch is a
step toward improving the situation.

The patch is attached to this message.

Best regards, Stepan Neretin.