Re: BUG: ReadStream look-ahead exhausts local buffers when effective_io_concurrency>=64

On Fri, Jul 3, 2026 at 11:54 AM Xuneng Zhou <xunengzhou(at)gmail(dot)com> wrote:
>
> Hi Feike,
>
> On Wed, May 6, 2026 at 8:48 PM Feike Steenbergen
> <feikesteenbergen(at)gmail(dot)com> wrote:
> >
> >
> >
> > On Wed, 6 May 2026 at 14:24, Feike Steenbergen <feikesteenbergen(at)gmail(dot)com> wrote:
> > > For now, the problem disappears again when switching to io_method='worker'
> >
> > I spoke too soon it seems. It happens less frequently, but we do still have the issue even with io_method='worker'.
> > Reverting to io_method='sync' as I would expect that will solve the issue.
>
> Interestingly, I wonder why this variance would occur in production.
> This is the more interesting part of this thread. I did some
> investigation & experiment, pg 19 passed the reproducer even if
> da6874635db is removed. A series of improvements made in April may
> contribute to this. Will return soon to share the findings..

I think we cannot expect that different I/O methods will expose the
bug differently since the root cause of this failure stays the same
regardless of the I/O methods being selected. That's what happened in
my pg18 testing with the producer -- it failed deterministically with
all three methods.

1) Separate but not independent

Currently, the I/O stack has two moving pieces that matter here:

- The read stream decides what to read next and how far ahead: it
tries to pin a window of buffers ahead of its consumer via lookahead
heuristics, so that by the time the caller wants a block, the read has
long been issued or even completed, and the buffer can be returned
with less waiting.
- The AIO submission layer decides who would execute the read: the
backend itself (sync), a fleet of dedicated I/O worker processes, or
the kernel via io_uring.

These two mechanisms are separate -- but not independent. The coupling
of them matters. The look-ahead window size is not fixed; it's
auto-tuned with feedback. The size starts at one block and adjusts
based on the signal/status of the consumed buffer: if the buffer was a
cache hit, the size decays by one; if I/O needed, the size doubles, up
to a ceiling. The signal that drives the controller is generated by
the fill machinery -- the very subsystem io_metod selects. The signal
seems deceptively simple at first glance, but it becomes complicated
as we look into it -- varies across versions/io methods/buffer types.
So better look at the simple and coherent part first -- how to
encounter this bug.

As said, we have a ceiling for the look-ahead window, it is set as:
max_pinned_buffers = (effective_io_concurrency + 1) x
io_combine_limit, and being clamped several times after. For shared
buffers, the clamping strategy is effective as we let seqscan uses a
small ring and one backend gets only a fair share of shared_buffers.
However, for temp Buffers, the pin max_pinned_buffers is capped only
by GetLocalPinLimit() which returns num_temp_buffers. All of it, which
is over-generous in a dangerous way. Let's say with the defaults
(temp_buffers=1024, io_combine_limit=16), any effective_io_concurrency
of 64 or more makes the formula exceed 1024, so the clamp cap is
exactly on the whole pool. That means a single sequential scan's
look-ahead is allowed to pin every local buffer the backend has. It
will be a problem if extra buffer needs from the backend arrives
simultaneously.

This is how the tragedy of the reproducer happened, 'tmp_tbl1'
occupies about 1,333 heap blocks, larger than the default
temp_buffers' pool of 1,024 blocks, which is ample for driving the
lookahead window to its ceiling in a cold-miss run. The "something
else" arrives on every single row -- each tuple carries a TOASTed
column that must be detoasted for output, and the TOAST fetch goes
through plain ReadBuffer, which has no pin-limit awareness and no
fallback; it just errors. The collision was caught in a backtrace:
printtup --> detoast_attr --> toast_fetch_datum --> … -->
GetLocalVictimBuffer, at the precise moment the added log recorded
NLocalPinnedBuffers=1024, num_temp_buffers=1024. Every buffer pinned;
the 1025th request was fatal.

This coincidence condition here is why production and lab could behave
differently. The reproducer failed deterministically in cold runs of
pg18 since it ensures both conditions are true -- the look ahead
window ramp-up to its ceiling and the extra buffer needs is there at
that timing. However, those two conditions can be hard to guarantee in
a changing production workload.

2) Three engines, two pools

To be continued...

--
Regards,
Xuneng Zhou
HighGo Software Co., Ltd.