Re: Report reorder buffer size

Hi Masahiko,
Thanks for your inputs.

On Tue, Aug 26, 2025 at 2:17 AM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:

> On Wed, Aug 13, 2025 at 5:40 AM Ashutosh Bapat
> <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com> wrote:
>
> Thank you for starting the thread about this topic. This is one of the
> topics that I've been eager to address. Here are some random comments:
>

Glad that more people think there is a need for improving the reorder
buffer statistics.

> >
> > With the attached WIP patch, we can report, at a given point in time,
> > total size of reorder buffer i.e. sum of the size of all the changes
> > belonging to the transactions active in the reorder buffer whether
> > those changes are in memory, on disk or sent downstream as part of an
> > unfinished streamed transaction. By sampling this value at regular
> > intervals, one can observe the reorder buffer trendline as shown in
> > the plots attached.
>
> Alternative idea (or an additional metric) would be the high-watermark
> of memory usage. That way, users won't miss memory usage spikes that
> might be missed by sampling the total memory usage.

I think the trendline is important to make right trade-offs in case
setting logical_decoding_work_mem to maximum is not possible. With a
high watermark the question is how long does a high remain high? As
the load changes, a high that was once may become lower or higher than
the new high and soon irrelevant. Probably we could reset the
high-watermark every time the view is sampled, so that we provide a
trendline for the high-water mark as well. But I want to be cautious
about adding that complexity of tracking maxima accurately and then
maintain it forever. If sampling is frequent enough usually it will
capture a maxima and minima good enough for practical purposes. The
users will need to consider the trendline as approximate anyway since
the load will show slight variations over the time.

Please share your idea of reporting high-watermark.

> >
> > 2. Is the size of the reorder buffer enough or we want to also track
> > the size of changes on disk and the size of changes sent downstream as
> > part of unfinished streamed transactions separately? Also the number
> > of transactions being tracked by the reorder buffer?
>
> For the purpose of tuning logical_decoding_work_mem, the additional
> metrics and statistics we need might not be many. But in order to make
> logical decoding more visible for users for debugging or diagnosing
> purposes, more statistics like the number of transactions being
> tracked by the reorder buffer might be required.
>

Ok. We could add the total number of transactions in the reorder
buffer at a given point in time to the report easily. How about
subtransactions? How about prepared but not committed/aborted
transactions?

> We need to note that the actual size of changes sent downstream
> actually depends on logical decoding plugins. For instance, we have
> table, row, and column filters in logical replication cases. It might
> be worth considering providing such statistics too. The statistics
> like the number of changes filtered out by the table filters or the
> change-kind filter (e.g., only publishing INSERTs etc) might be
> helpful for users to confirm the effectiveness of the filters they
> set.

I have proposed this in [1]. Please check.

>
> > If we are going
> > to report so much statistics about the contents of the reorder buffer,
> > is it better to have a separate view pg_stat_reorder_buffer for the
> > same?
>
> Given logical decoding can be used also by regular backend processes,
> I guess that such dynamic metrics would fit a system view dedicated to
> logical decoding, say pg_stat_reorder_buffer or
> pg_stat_logical_decoding.

Hmm. That means we will have to reserve some area in the shared memory
with as many slots as the number of replication slots (since that is
the maximum number of reorder buffers that can be in the system) and
use each one to maintain the stats. I haven't yet checked whether we
could use the stats maintaining system for it, but it should be
doable.

--
Best Wishes,
Ashutosh Bapat