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. > 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. [1] https://postgr.es/m/97529f5a-ec10-46b1-ab50-4653126c6889%40gmail.com -- Regards, Xuneng Zhou HighGo Software Co., Ltd.
