Re: BUG: ReadStream look-ahead exhausts local buffers when effective_io_concurrency>=64 - Mailing list pgsql-hackers

On Fri, Jul 3, 2026 at 7:50 AM Xuneng Zhou <xunengzhou@gmail.com> wrote:
>
> Hi Induja,
>
> On Fri, Feb 27, 2026 at 7:55 PM Induja Sreekanthan <indujas@google.com> wrote:
> >
> > Hi,
> >
> > I found an issue where Postgres (with effective_io_concurrency of 64 or higher) runs out of local buffers during a
sequentialscan on a temporary table with TOAST data. 
> >
> > The issue occurs because the ReadStream look-ahead pins all the local buffers. This results in the TOAST index
look-upand TOAST page read being unable to find any available local buffers. The ReadStream's max_pinned_buffers can be
ashigh as the num_temp_buffers, depending on the effective_io_concurrency. 
> >
> > Here is a reproduction of the issue using the default temp_buffers setting and effective_io_concurrency=128:
> >
> > docker run --name my-postgres -e POSTGRES_PASSWORD=my-password -p 5432:5432 -d postgres:18 -c
effective_io_concurrency=128
> >
> > postgres=# CREATE TEMPORARY TABLE tmp_tbl1 (
> >     s_suppkey    NUMERIC NOT NULL,
> >     s_nationkey  NUMERIC,
> >     s_comment    VARCHAR(256),
> >     s_name       CHAR(256),
> >     s_address    VARCHAR(256),
> >     s_phone      TEXT,
> >     s_acctbal    NUMERIC,
> >     CONSTRAINT supplier_pk PRIMARY KEY (s_suppkey)
> > );
> > CREATE TABLE
> > postgres=# INSERT INTO tmp_tbl1 (s_suppkey, s_nationkey, s_comment, s_name, s_address, s_phone, s_acctbal)
> > SELECT
> >     ('1' || repeat('0', 2000) || i::text)::NUMERIC AS s_suppkey,
> >     ('5' || repeat('0', 2000) || floor(random() * 25)::text)::NUMERIC AS s_nationkey,
> >     md5(random()::text) || ' some comment' AS s_comment,
> >     'Supplier#' || LPAD(i::text, 9, '0') AS s_name,
> >     'Address-' || md5(i::text) AS s_address,
> >     repeat('P', 4096) || '-' || i::text || repeat('P', 2048) || 'fwoiefrr' ||
> >     repeat('fejwfelwkmfP', 4096) || '-' || i::text || repeat('fnwekjfmelkwf', 2048) AS s_phone,
> >     ('9' || repeat('9', 2000) || '.' || floor(random()*100)::text)::NUMERIC AS s_acctbal
> > FROM generate_series(1, 8000) AS i;
> > INSERT 0 8000
> > postgres=# SELECT * FROM tmp_tbl1;
> > ERROR:  no empty local buffer available
>
> 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.

The issue is gone in pg19 in further testing.

> > Attached is a patch that addresses this by limiting ReadStream's max_pinned_buffers for temp tables to 75% of the
availablelocal buffers. It also introduces a cap on max_ios for temp tables to DEFAULT_EFFECTIVE_IO_CONCURRENCY, to
accountfor 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.

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