Re: Report reorder buffer size

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.

Greetings,

Andres Freund