On Fri, Jul 3, 2026 at 11:54 AM Xuneng Zhou <[email protected]> wrote: > > Hi Feike, > > On Wed, May 6, 2026 at 8:48 PM Feike Steenbergen > <[email protected]> wrote: > > > > > > > > On Wed, 6 May 2026 at 14:24, Feike Steenbergen <[email protected]> > > 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.
