MIME-Version: 1.0
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>
 <y7xrf6c757b36v5hpythpxmldgbxpgyvobfnz2q6hs2m3lck4w@rv23wim7ok3o>
In-Reply-To: <y7xrf6c757b36v5hpythpxmldgbxpgyvobfnz2q6hs2m3lck4w@rv23wim7ok3o>
From: Melanie Plageman <melanieplageman@gmail.com>
Date: Mon, 20 Apr 2026 11:48:09 -0400
Message-ID: 
 <CAAKRu_arG0imObUfqtr8tjJzynq+dn3qk9kUP1+hdF3VmGms6g@mail.gmail.com>
Subject: Re: eliminate xl_heap_visible to reduce WAL (and eventually set VM
 on-access)
To: Andres Freund <andres@anarazel.de>
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>
Content-Type: multipart/mixed; boundary="000000000000769e8b064fe63a10"
Archived-At: 
 <https://www.postgresql.org/message-id/CAAKRu_arG0imObUfqtr8tjJzynq%2Bdn3qk9kUP1%2BhdF3VmGms6g%40mail.gmail.com>
Precedence: bulk

--000000000000769e8b064fe63a10
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Sat, Apr 18, 2026 at 12:33=E2=80=AFPM Andres Freund <andres@anarazel.de>=
 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 progr=
ess.
>
> But I'd not go there right now, that seems like a more complicated projec=
t
> (and we'd presumably want to do something roughly similar for the s_b cas=
e).

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

--000000000000769e8b064fe63a10
Content-Type: text/x-patch; charset="UTF-8";
	name="Make-local-buffers-pin-limit-more-conservative.patch"
Content-Disposition: attachment;
	filename="Make-local-buffers-pin-limit-more-conservative.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_mo7dcspf0>
X-Attachment-Id: f_mo7dcspf0

RnJvbSA3NTE3YTQ5OGNiMWNiYjIzZjYwMTg5MDE5YjNhNDZiMjdkYjM1ZjcyIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBNZWxhbmllIFBsYWdlbWFuIDxtZWxhbmllcGxhZ2VtYW5AZ21h
aWwuY29tPgpEYXRlOiBNb24sIDIwIEFwciAyMDI2IDExOjE0OjMxIC0wNDAwClN1YmplY3Q6IFtQ
QVRDSF0gTWFrZSBsb2NhbCBidWZmZXJzIHBpbiBsaW1pdCBtb3JlIGNvbnNlcnZhdGl2ZQpNSU1F
LVZlcnNpb246IDEuMApDb250ZW50LVR5cGU6IHRleHQvcGxhaW47IGNoYXJzZXQ9VVRGLTgKQ29u
dGVudC1UcmFuc2Zlci1FbmNvZGluZzogOGJpdAoKR2V0TG9jYWxQaW5MaW1pdCgpIGFuZCBHZXRB
ZGRpdGlvbmFsTG9jYWxQaW5MaW1pdCgpLCBjdXJyZW50bHkgaW4gdXNlCm9ubHkgYnkgdGhlIHJl
YWQgc3RyZWFtLCBwcmV2aW91c2x5IGFsbG93ZWQgYSBiYWNrZW5kIHRvIHBpbiBhbGwKbnVtX3Rl
bXBfYnVmZmVycyBsb2NhbCBidWZmZXJzLiBUaGlzIG1lYW50IHRoYXQgdGhlIHJlYWQgc3RyZWFt
IGNvdWxkCnVzZSBldmVyeSBhdmFpbGFibGUgbG9jYWwgYnVmZmVyIGZvciByZWFkLWFoZWFkLCBs
ZWF2aW5nIG5vbmUgZm9yIG90aGVyCmNvbmN1cnJlbnQgcGluLWhvbGRlcnMgbGlrZSBvdGhlciBy
ZWFkIHN0cmVhbXMgYW5kIHJlbGF0ZWQgYnVmZmVycyBsaWtlCnRoZSB2aXNpYmlsaXR5IG1hcCBi
dWZmZXIgbmVlZGVkIGR1cmluZyBvbi1hY2Nlc3MgcHJ1bmluZy4KCkNhcCB0aGUgbG9jYWwgcGlu
IGxpbWl0IHRvIG51bV90ZW1wX2J1ZmZlcnMgLyA0LCBwcm92aWRpbmcgc29tZQpoZWFkcm9vbS4g
VGhpcyBkb2Vzbid0IGd1YXJhbnRlZSB0aGF0IGFsbCBuZWVkZWQgcGlucyB3aWxsIGJlIGF2YWls
YWJsZQrigJQgZm9yIGV4YW1wbGUsIGEgYmFja2VuZCBjYW4gc3RpbGwgb3BlbiBtb3JlIGN1cnNv
cnMgdGhhbiB0aGVyZSBhcmUKYnVmZmVycyDigJQgYnV0IGl0IG1ha2VzIGl0IGxlc3MgbGlrZWx5
IHRoYXQgcmVhZC1haGVhZCB3aWxsIGV4aGF1c3QgdGhlCnBvb2wuCgpOb3RlIHRoYXQgdGhlc2Ug
ZnVuY3Rpb25zIGFyZSBub3QgbGltaXRlZCBieSBkZWZpbml0aW9uIHRvIHVzZSBpbiB0aGUKcmVh
ZCBzdHJlYW07IGhvd2V2ZXIsIHRoaXMgY2FwIHNob3VsZCBiZSBhcHByb3ByaWF0ZSBpbiBvdGhl
ciBjb250ZXh0cy4KCkF1dGhvcjogTWVsYW5pZSBQbGFnZW1hbiA8bWVsYW5pZXBsYWdlbWFuQGdt
YWlsLmNvbT4KUmVwb3J0ZWQtYnk6IEFsZXhhbmRlciBMYWtoaW4gPGV4Y2x1c2lvbkBnbWFpbC5j
b20+ClJldmlld2VkLWJ5OiBBbmRyZXMgRnJldW5kIDxhbmRyZXNAYW5hcmF6ZWwuZGU+CkRpc2N1
c3Npb246IGh0dHBzOi8vcG9zdGdyLmVzL20vOTc1MjlmNWEtZWMxMC00NmIxLWFiNTAtNDY1MzEy
NmM2ODg5JTQwZ21haWwuY29tCi0tLQogc3JjL2JhY2tlbmQvc3RvcmFnZS9idWZmZXIvbG9jYWxi
dWYuYyB8IDE1ICsrKysrKysrKysrKy0tLQogMSBmaWxlIGNoYW5nZWQsIDEyIGluc2VydGlvbnMo
KyksIDMgZGVsZXRpb25zKC0pCgpkaWZmIC0tZ2l0IGEvc3JjL2JhY2tlbmQvc3RvcmFnZS9idWZm
ZXIvbG9jYWxidWYuYyBiL3NyYy9iYWNrZW5kL3N0b3JhZ2UvYnVmZmVyL2xvY2FsYnVmLmMKaW5k
ZXggMzk2ZGE4NGIyNWMuLjI0ZWY5NWVjYjEwIDEwMDY0NAotLS0gYS9zcmMvYmFja2VuZC9zdG9y
YWdlL2J1ZmZlci9sb2NhbGJ1Zi5jCisrKyBiL3NyYy9iYWNrZW5kL3N0b3JhZ2UvYnVmZmVyL2xv
Y2FsYnVmLmMKQEAgLTMwNywxNiArMzA3LDI1IEBAIEdldExvY2FsVmljdGltQnVmZmVyKHZvaWQp
CiB1aW50MzIKIEdldExvY2FsUGluTGltaXQodm9pZCkKIHsKLQkvKiBFdmVyeSBiYWNrZW5kIGhh
cyBpdHMgb3duIHRlbXBvcmFyeSBidWZmZXJzLCBhbmQgY2FuIHBpbiB0aGVtIGFsbC4gKi8KLQly
ZXR1cm4gbnVtX3RlbXBfYnVmZmVyczsKKwkvKgorCSAqIEV2ZXJ5IGJhY2tlbmQgaGFzIGl0cyBv
d24gdGVtcG9yYXJ5IGJ1ZmZlcnMsIGJ1dCB3ZSBsZWF2ZSBoZWFkcm9vbSB0bworCSAqIGF2b2lk
IHJ1bm5pbmcgb3V0IG9mIHBpbnMgZm9yIGRpc2NyZXRpb25hcnkgZGVtYW5kLCBzdWNoIGFzIGZv
cgorCSAqIHJlYWQtYWhlYWQuCisJICovCisJcmV0dXJuIG51bV90ZW1wX2J1ZmZlcnMgLyA0Owog
fQogCiAvKiBzZWUgR2V0QWRkaXRpb25hbFBpbkxpbWl0KCkgKi8KIHVpbnQzMgogR2V0QWRkaXRp
b25hbExvY2FsUGluTGltaXQodm9pZCkKIHsKKwl1aW50MzIJCXRvdGFsID0gR2V0TG9jYWxQaW5M
aW1pdCgpOworCiAJQXNzZXJ0KE5Mb2NhbFBpbm5lZEJ1ZmZlcnMgPD0gbnVtX3RlbXBfYnVmZmVy
cyk7Ci0JcmV0dXJuIG51bV90ZW1wX2J1ZmZlcnMgLSBOTG9jYWxQaW5uZWRCdWZmZXJzOworCisJ
aWYgKE5Mb2NhbFBpbm5lZEJ1ZmZlcnMgPj0gdG90YWwpCisJCXJldHVybiAwOworCXJldHVybiB0
b3RhbCAtIE5Mb2NhbFBpbm5lZEJ1ZmZlcnM7CiB9CiAKIC8qIHNlZSBMaW1pdEFkZGl0aW9uYWxQ
aW5zKCkgKi8KLS0gCjIuNDMuMAoK
--000000000000769e8b064fe63a10--