Feedback-ID: id4a34324:Fastmail
Date: Sat, 18 Apr 2026 12:33:26 -0400
From: Andres Freund <andres@anarazel.de>
To: Melanie Plageman <melanieplageman@gmail.com>
Cc: Alexander Lakhin <exclusion@gmail.com>, Tomas Vondra <tomas@vondra.me>,
	David Rowley <dgrowleyml@gmail.com>, Kirill Reshke <reshkekirill@gmail.com>,
	Chao Li <li.evan.chao@gmail.com>, Andrey Borodin <x4mmm@yandex-team.ru>,
	Xuneng Zhou <xunengzhou@gmail.com>, Robert Haas <robertmhaas@gmail.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
 Heikki Linnakangas <hlinnaka@iki.fi>
Subject: Re: eliminate xl_heap_visible to reduce WAL (and eventually set VM
 on-access)
Message-ID: <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>
 <97529f5a-ec10-46b1-ab50-4653126c6889@gmail.com>
 <CAAKRu_b=X-P26hB96i2vG40PhJB_5L=ARuNHV4SJngMPap8H3A@mail.gmail.com>
 <yemtvnnnj4c6ehsmv6cu2x7h5i3nentpoeuqdtmyp3lnrw7ydu@7uwd2pogt5nv>
 <CAAKRu_a_t6yHc94K6WkjUg_bDgXSK33LAYNACZFg-3RjrPPMcw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CAAKRu_a_t6yHc94K6WkjUg_bDgXSK33LAYNACZFg-3RjrPPMcw@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/y7xrf6c757b36v5hpythpxmldgbxpgyvobfnz2q6hs2m3lck4w%40rv23wim7ok3o>
Precedence: bulk

Hi,

On 2026-04-16 16:21:35 -0400, Melanie Plageman wrote:
> On Sun, Apr 5, 2026 at 11:49 AM Andres Freund <andres@anarazel.de> wrote:
> >
> > I think we should probably just have GetLocalPinLimit() return something
> > considerably smaller than num_temp_buffers, e.g. num_temp_buffers / 4 or
> > so.
> 
> I think num_temp_buffers / 4 seems reasonable for GetLocalPinLimit().
> We'd also need to make this change in GetAdditionalLocalPinLimit().

Right.


> We will likely see an impact on performance impact because this will
> keep the readahead distance substantially lower for temp table cases
> with only one read stream.

I don't think it's likely to be practically relevant. Unless you use a toy
sized temp_buffers - in which case readahead won't be a relevant performance
bottleneck - the pinned limit will often even be higher than with shared
buffers (due to the shared buffers pin limit being divided by
MaxBackends). Even at the default temp_buffers=1024/8MB, the limit would be
256. That's quite high for a single scan in such a tiny pool.


> > There always may be more than one scan going on, so we can't ever promise that
> > there's at least a certain number of pins available. The main goal of the
> > shared pin limit is to prevent one backend from pinning disproportionally much
> > of s_b.  And for that eventually scaling down to just needing 1-2 pins per
> > scan is sufficient.
> 
> With the last sentence "And for that eventually scaling down to just
> needing 1-2 pins per scan is sufficient." -- how do you mean to relate
> that to what we will do with local buffers case?

Just explaining why we shouldn't see these limits as hard limits that may
never be exceeded, but as caps that make problems less likely.

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).

Greetings,

Andres Freund