public inbox for [email protected]
help / color / mirror / Atom feedFrom: Melanie Plageman <[email protected]>
To: Andres Freund <[email protected]>
Cc: Alexander Lakhin <[email protected]>
Cc: Tomas Vondra <[email protected]>
Cc: David Rowley <[email protected]>
Cc: Kirill Reshke <[email protected]>
Cc: Chao Li <[email protected]>
Cc: Andrey Borodin <[email protected]>
Cc: Xuneng Zhou <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: Heikki Linnakangas <[email protected]>
Subject: Re: eliminate xl_heap_visible to reduce WAL (and eventually set VM on-access)
Date: Mon, 20 Apr 2026 11:48:09 -0400
Message-ID: <CAAKRu_arG0imObUfqtr8tjJzynq+dn3qk9kUP1+hdF3VmGms6g@mail.gmail.com> (raw)
In-Reply-To: <y7xrf6c757b36v5hpythpxmldgbxpgyvobfnz2q6hs2m3lck4w@rv23wim7ok3o>
References: <CAAKRu_ZrDadxmGepBwPZ03yAKnMxwsHYn8SK9Gg7VqigLLVUWg@mail.gmail.com>
<CAApHDvqAOeOwCKh9g0gfxWa040=Hyc7_oA=C59rjod8kXJDWyw@mail.gmail.com>
<CAAKRu_Yt76_HdfR6DtK_wtkSNSj9=VxSV_npt+6T2R=zTzp1Pg@mail.gmail.com>
<CAAKRu_atv6zA274m8Ysgbfn49c0NbdvHT7nXvd9kroZKnFq8Dg@mail.gmail.com>
<CAApHDvq_R-gNXu+06GQW6w_HaEMh1pezsyiCh7GNhgh+h0UqMw@mail.gmail.com>
<CAAKRu_YfoGTHNn0XxA+dCPj9hyO96vO4Eb+awRR6T8m22qC6ww@mail.gmail.com>
<[email protected]>
<CAAKRu_b=X-P26hB96i2vG40PhJB_5L=ARuNHV4SJngMPap8H3A@mail.gmail.com>
<yemtvnnnj4c6ehsmv6cu2x7h5i3nentpoeuqdtmyp3lnrw7ydu@7uwd2pogt5nv>
<CAAKRu_a_t6yHc94K6WkjUg_bDgXSK33LAYNACZFg-3RjrPPMcw@mail.gmail.com>
<y7xrf6c757b36v5hpythpxmldgbxpgyvobfnz2q6hs2m3lck4w@rv23wim7ok3o>
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
Attachments:
[text/x-patch] Make-local-buffers-pin-limit-more-conservative.patch (2.4K, 2-Make-local-buffers-pin-limit-more-conservative.patch)
download | inline diff:
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
view thread (143+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: eliminate xl_heap_visible to reduce WAL (and eventually set VM on-access)
In-Reply-To: <CAAKRu_arG0imObUfqtr8tjJzynq+dn3qk9kUP1+hdF3VmGms6g@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox