On Fri, Jul 3, 2026 at 7:50 AM Xuneng Zhou <[email protected]> wrote: > > Hi Induja, > > On Fri, Feb 27, 2026 at 7:55 PM Induja Sreekanthan <[email protected]> wrote: > > > > Hi, > > > > I found an issue where Postgres (with effective_io_concurrency of 64 or > > higher) runs out of local buffers during a sequential scan 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-up and TOAST page read being > > unable to find any available local buffers. The ReadStream's > > max_pinned_buffers can be as high 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 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. -- Regards, Xuneng Zhou HighGo Software Co., Ltd.
