Thread
-
BUG: ReadStream look-ahead exhausts local buffers when effective_io_concurrency>=64
Induja Sreekanthan <indujas@google.com> — 2026-02-24T16:33:03Z
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 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. Regards, Induja Sreekanthan -
Re: BUG: ReadStream look-ahead exhausts local buffers when effective_io_concurrency>=64
Eduard Stepanov <crtxcz@gmail.com> — 2026-03-12T11:13:38Z
Hi Induja! I've encountered the same issue but in a different context, so I came up with an alternative solution: dynamic buffer_limit check in read_stream_look_ahead() In read_stream_look_ahead(), I dynamically check the actual remaining pin budget via GetAdditionalLocalPinLimit(). This adds a third condition to the main while loop and re-checks after each pin operation. Additionally, when the budget is exhausted, the pending read is started immediately rather than waiting for io_combine_limit accumulation. The attached patch includes changes to src/backend/storage/aio/read_stream.c and adds two additional tests to temp.sql. Best regards, Eduard Stepanov Tantor Labs LLC On Tue, 10 Mar 2026 at 23:22, 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 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 > > 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. > > Regards, > Induja Sreekanthan -
Re: BUG: ReadStream look-ahead exhausts local buffers when effective_io_concurrency>=64
Feike Steenbergen <feikesteenbergen@gmail.com> — 2026-05-06T12:24:38Z
Hi, all, > On Tue, 10 Mar 2026 at 23:22, 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 sequential scan on a temporary table with TOAST data. > > Just to confirm, I have a production db (20TB, high concurrency, lot's of updates/merges) that is a heavy user of temp tables running into this as well with io_method='io_uring'. This started after having upgraded PostgreSQL 18 (from 17). For now, the problem disappears again when switching to io_method='worker' Thank you for providing the patches, kind regards, Feike Steenbergen
-
Re: BUG: ReadStream look-ahead exhausts local buffers when effective_io_concurrency>=64
Feike Steenbergen <feikesteenbergen@gmail.com> — 2026-05-06T12:47:46Z
On Wed, 6 May 2026 at 14:24, Feike Steenbergen <feikesteenbergen@gmail.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. Feike
-
Re: BUG: ReadStream look-ahead exhausts local buffers when effective_io_concurrency>=64
Xuneng Zhou <xunengzhou@gmail.com> — 2026-07-02T23:50:38Z
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 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. -
Re: BUG: ReadStream look-ahead exhausts local buffers when effective_io_concurrency>=64
Xuneng Zhou <xunengzhou@gmail.com> — 2026-07-03T01:24:19Z
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 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. -
Re: BUG: ReadStream look-ahead exhausts local buffers when effective_io_concurrency>=64
Xuneng Zhou <xunengzhou@gmail.com> — 2026-07-03T03:54:31Z
Hi Feike, On Wed, May 6, 2026 at 8:48 PM Feike Steenbergen <feikesteenbergen@gmail.com> wrote: > > > > On Wed, 6 May 2026 at 14:24, Feike Steenbergen <feikesteenbergen@gmail.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.. -- Regards, Xuneng Zhou HighGo Software Co., Ltd.
-
Re: BUG: ReadStream look-ahead exhausts local buffers when effective_io_concurrency>=64
Xuneng Zhou <xunengzhou@gmail.com> — 2026-07-07T08:27:56Z
On Fri, Jul 3, 2026 at 11:54 AM Xuneng Zhou <xunengzhou@gmail.com> wrote: > > Hi Feike, > > On Wed, May 6, 2026 at 8:48 PM Feike Steenbergen > <feikesteenbergen@gmail.com> wrote: > > > > > > > > On Wed, 6 May 2026 at 14:24, Feike Steenbergen <feikesteenbergen@gmail.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.