Re: Report reorder buffer size

On Tue, Sep 23, 2025 at 6:04 PM Andres Freund <andres(at)anarazel(dot)de> wrote:
>
> Hi,
>
> On 2025-09-23 17:45:19 +0530, Ashutosh Bapat wrote:
> > On Tue, Sep 23, 2025 at 11:30 AM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> > >
> > > On Wed, Aug 27, 2025 at 9:36 PM Ashutosh Bapat
> > > <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com> wrote:
> > > >
> > > > Ok. I am interested in knowing how you think we should keep track of
> > > > the high watermark.
> > > >
> > > > Do we keep it updated as new maxima are found? And keep reporting the
> > > > last high watermark seen till a new maxima is reached? With that,
> > > > users would end up setting logical_decoding_work_mem (and hence
> > > > provisioning for it) at a higher value, even if the workload changes
> > > > so that the reorder buffer never reaches that high watermark. So, I
> > > > think we should reset the high watermark. Do you think so? If yes,
> > > > When do we do that? How often do we reset it?
> > >
> > > I think it ultimately depends on use cases but I imagine that users
> > > can reset the high watermark value after adjusting
> > > logical_decoding_work_mem, and see how the new value works, then
> > > adjust the parameter and reset the value again, ... repeating.
> > >
> > > > Tracking maxima and minima of other parameters in pg_stat_replication
> > > > like replication lag might also help users e.g. to tune their
> > > > networks. But we don't track their watermarks. External tools are used
> > > > for that. I was envisioning something like that for reorder buffer
> > > > size as well.
> > >
> > > I think that high watermark value would be a new type of statistics we
> > > collect. As far as I know pg_stat_statement collects similar values
> > > but there is no precedent in the core.
> > >
> >
> > Thanks for pointing me to pg_stat_statements. I see we store
> > cumulative statistics like min and max plan and execution times for
> > queries there. We maintain cumulative statistics about logical
> > decoding/replication in pg_stat_replication_slot. I think the high
> > watermark fits there. It also has functionality to reset the
> > statistics which can be used to reset the high watermark along with
> > other statistics.
>
> I think pg_stat_statement is an anti-example, if anything. It's been so
> overloaded with barely useful fields that it got so so slow that it starts to
> cause contention even in just moderately busy workloads.
>
> Let's make this a minimal change, instead of over-complicating this beyond
> usefulness.

Thanks Bertrand, Masahiko and Andres for your thoughts.

Summarising the discussion so far, it seems we agree that reporting
the current size of reorder buffer (including the spilled and streamed
transactions) in pg_stat_replication is useful.Including the sizes of
changes from spilled as well as streamed transactions allows users to
see how reorder buffer size would trend if logical_decoding_work_mem
were to be infinite. If it's sampled frequently enough, the trendline
can be used to find an optimal value of logical_decoding_work_mem
considering various trade-offs.

We did consider other metrics like number of transactions in the
reorder buffer and highest size of reorder buffer. But the opinions
about their usefulness differ. Hence defering them for now.

I will work on producing a complete patch next.

--
Best Wishes,
Ashutosh Bapat