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

Hi Melanie,

On Thu, Jul 23, 2026 at 11:08 PM Melanie Plageman
<melanieplageman(at)gmail(dot)com> wrote:
>
> On Thu, Jul 2, 2026 at 7:50 PM Xuneng Zhou <xunengzhou(at)gmail(dot)com> wrote:
> >
> > Thanks for reporting this issue. It smells similar to the bug reported
> > by Alexander earlier. [1] The root cause of them seems the same: we
> > give read stream too much budget for local buffer pins. The
> > fix(da6874635db by Melanie) is to cut the budget to 1/4.
> >
> > > Attached is a patch that addresses this by limiting ReadStream's max_pinned_buffers for temp tables to 75% of the available local buffers. It also introduces a cap on max_ios for temp tables to DEFAULT_EFFECTIVE_IO_CONCURRENCY, to account for multiple sequential scan look-aheads happening simultaneously.
> >
> > If that's the case, I'm wondering whether it makes sense to backpatch
> > this fix to 18. I tried to do this for the local tree and the
> > reproducer passed. That said, it might not be safe to do so for a
> > stable version. It would be helpful to hear Melanie's and Andres's
> > thoughts on this.
>
> da6874635db fixes the originally reported bug in this thread on 18. It
> makes sense to backpatch it to 18. I hadn't thought of the TOAST case.
> I'm thinking about whether backpatching to 17 makes sense or not. It
> would be much more difficult to hit an issue in 17, but I'll need to
> think more about it.
> I wonder if in 20, we should move toward Andres' suggested formula
> Max(1, (num_temp_buffers − NLocalPinnedBuffers) / 4)
> instead of the existing
> num_temp_buffers / 4 − NLocalPinnedBuffers
>
> Which would mean we got a quarter of available buffers instead of a
> hard cap of a quarter of total buffers.
> This wouldn't have been any better at solving the issue in this
> thread, but I'm wondering if it is just a better formula in general
> going forward (not for backpatching).

I am not sure about that. In the existing formula, we set a hard-cap
for the read streams at backend level. For the suggested one, the cap
is removed so multiple successive streams could push the water level
of pins toward the ceiling of temp buffers. I am unsure of the
consequences of this. Adjusting the cap limit alone seems not resolve
the competing nature between read stream and other needs. Reclaiming
some pinned buffers might help, but it is a more complex mechanism
which might not be a good fit in the bigger picture.

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