On Wed, Aug 5, 2026 at 11:52 PM Melanie Plageman <[email protected]> wrote: > > On Fri, Jul 24, 2026 at 1:40 AM Xuneng Zhou <[email protected]> wrote: > > > > On Thu, Jul 23, 2026 at 11:08 PM Melanie Plageman > > <[email protected]> wrote: > > > > > > > > I wonder if in 20, we should move toward Andres' suggested formula > > > Max(1, (num_temp_buffers − NLocalPinnedBuffers) / 4) > > > instead of the existing > > > num_temp_buffers / 4 − NLocalPinnedBuffers > > > > > > Which would mean we got a quarter of available buffers instead of a > > > hard cap of a quarter of total buffers. > > > This wouldn't have been any better at solving the issue in this > > > thread, but I'm wondering if it is just a better formula in general > > > going forward (not for backpatching). > > > > I am not sure about that. In the existing formula, we set a hard-cap > > for the read streams at backend level. For the suggested one, the cap > > is removed so multiple successive streams could push the water level > > of pins toward the ceiling of temp buffers. I am unsure of the > > consequences of this. Adjusting the cap limit alone seems not resolve > > the competing nature between read stream and other needs. Reclaiming > > some pinned buffers might help, but it is a more complex mechanism > > which might not be a good fit in the bigger picture. > > I think the right approach is to drive out a change like this with > specific use cases or after seeing bottlenecks/problems in workloads. > Since we're not encountering those now, I think investigating how to > change this falls far down the priority list.
I agree with adjusting heuristic better based on real workloads. BTW, I wanna consult a potential nearby performance optimization. In the process of paired code reading with Sol before, it suggested lowering the cap of lookahead distance for synchronous mode. The suggestion somehow makes sense to me and it showed small consistent improvement(2%) in a preliminary benchmarking. Currently, the heuristic of lookahead distance is unified for three io modes. The distance can grow up to max_pinned_buffers, which may be pretty larger than io_combine_limit. For io_ worker and io_uring, that setting could make sense since it allows overlapping of multiple i/o operations to hide latency. However, for synchronous mode, that may seems not help a lot since reads are done by the consuming backend, so i/o cannot overlap with query execution. Once that distance is approaching io_combine_limit, the benefit of growing it further becomes marginal at the cost of the pinning and bookkeeping overhead. This is the case io_combine_limit for pg17, where the aio framework hasn't been introduced. After 18 is the status quo - max_pinned_buffers. Is there something missing in my understanding for this setting or is it a relatively low-priority micro-optimization? If it is the latter, do you think this is viable/worthwhile? I plan to start a new dedicated thread for this topic when the working thread of my mind gets preempted from other tasks and its context gets switched properly. -- Regards, Xuneng Zhou HighGo Software Co., Ltd.
