On Tue, Sep 23, 2025 at 11:30 AM Masahiko Sawada <sawada.m...@gmail.com> wrote: > > On Wed, Aug 27, 2025 at 9:36 PM Ashutosh Bapat > <ashutosh.bapat....@gmail.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. > > > > > > > > > > > > > > > > > > > > > 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? > > > > > > For debugging or diagnosing purposes, this information might be > > > useful, but I'm not sure we need this information of logical decodings > > > that are running. > > > > I was not clear. Sorry. I meant reporting the number of > > subtransactions and prepared transactions in the reorder buffer (not > > the actual subtransactions or prepared transactions themselves). Do > > you think that reporting just the number of transactions in the > > reorder buffer is enough? Do you think that reporting the number of > > subtransactions and the number of prepared transactions is not > > required? > > Given that collecting new statistics and updating the statistics > real-time require costs, it's ultimately rewards vs. costs. In terms > of additional costs, I guess tracking the number of prepared > transactions in the reorderbuffer would not be costly, but I'm not > sure about the number of subtransactions. I personally would avoid > collecting these statistics unless there are explicit use cases. > I agree. I think it's not clear whether statistics about number of transactions currently active in the reorder buffer is useful. So let's leave it. If needed we will add it at a later point in time. I will submit a complete patch soon. -- Best Wishes, Ashutosh Bapat
