On Thu, Aug 6, 2026 at 11:34 AM Xuneng Zhou <[email protected]> wrote: > > 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
Brain fart, this is inaccurate even for temp buffers after the stricter budget. > 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.
