On Sat, Apr 18, 2026 at 12:33 PM Andres Freund <[email protected]> wrote: > > I guess I could see an argument for doing something more complicated for temp > buffers than num_temp_buffers / 4, e.g. > Min(1, (num_temp_buffers - NLocalPinnedBuffers) / 4) > so that we get more conservative the more scans are concurrently in progress. > > But I'd not go there right now, that seems like a more complicated project > (and we'd presumably want to do something roughly similar for the s_b case).
With shared buffers, while it is true you'd ideally leave the backend headroom for other read streams etc, it won't error out the way the temp table case does unless we've actually pinned all shared buffers. It will simply slow down the read ahead of the competing read streams. Attached is what I'm thinking of committing. - Melanie
From 7517a498cb1cbb23f60189019b3a46b27db35f72 Mon Sep 17 00:00:00 2001 From: Melanie Plageman <[email protected]> Date: Mon, 20 Apr 2026 11:14:31 -0400 Subject: [PATCH] Make local buffers pin limit more conservative MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit GetLocalPinLimit() and GetAdditionalLocalPinLimit(), currently in use only by the read stream, previously allowed a backend to pin all num_temp_buffers local buffers. This meant that the read stream could use every available local buffer for read-ahead, leaving none for other concurrent pin-holders like other read streams and related buffers like the visibility map buffer needed during on-access pruning. Cap the local pin limit to num_temp_buffers / 4, providing some headroom. This doesn't guarantee that all needed pins will be available — for example, a backend can still open more cursors than there are buffers — but it makes it less likely that read-ahead will exhaust the pool. Note that these functions are not limited by definition to use in the read stream; however, this cap should be appropriate in other contexts. Author: Melanie Plageman <[email protected]> Reported-by: Alexander Lakhin <[email protected]> Reviewed-by: Andres Freund <[email protected]> Discussion: https://postgr.es/m/97529f5a-ec10-46b1-ab50-4653126c6889%40gmail.com --- src/backend/storage/buffer/localbuf.c | 15 ++++++++++++--- 1 file changed, 12 insertions(+), 3 deletions(-) diff --git a/src/backend/storage/buffer/localbuf.c b/src/backend/storage/buffer/localbuf.c index 396da84b25c..24ef95ecb10 100644 --- a/src/backend/storage/buffer/localbuf.c +++ b/src/backend/storage/buffer/localbuf.c @@ -307,16 +307,25 @@ GetLocalVictimBuffer(void) uint32 GetLocalPinLimit(void) { - /* Every backend has its own temporary buffers, and can pin them all. */ - return num_temp_buffers; + /* + * Every backend has its own temporary buffers, but we leave headroom to + * avoid running out of pins for discretionary demand, such as for + * read-ahead. + */ + return num_temp_buffers / 4; } /* see GetAdditionalPinLimit() */ uint32 GetAdditionalLocalPinLimit(void) { + uint32 total = GetLocalPinLimit(); + Assert(NLocalPinnedBuffers <= num_temp_buffers); - return num_temp_buffers - NLocalPinnedBuffers; + + if (NLocalPinnedBuffers >= total) + return 0; + return total - NLocalPinnedBuffers; } /* see LimitAdditionalPins() */ -- 2.43.0
