On Sun, Jan 14, 2024 at 6:01 AM vignesh C <vignes...@gmail.com> wrote: > > On Sat, 10 Jun 2023 at 07:57, James Coleman <jtc...@gmail.com> wrote: > > > > I've previously noted in "Add last commit LSN to > > pg_last_committed_xact()" [1] that it's not possible to monitor how > > many bytes of WAL behind a logical replication slot is (computing such > > is obviously trivial for physical slots) because the slot doesn't need > > to replicate beyond the last commit. In some cases it's possible for > > the current WAL location to be far beyond the last commit. A few > > examples: > > > > - An idle server with checkout_timeout at a lower value (so lots of > > empty WAL advance). > > - Very large transactions: particularly insidious because committing a > > 1 GB transaction after a small transaction may show almost zero time > > lag even though quite a bit of data needs to be processed and sent > > over the wire (so time to replay is significantly different from > > current lag). > > - A cluster with multiple databases complicates matters further, > > because while physical replication is cluster-wide, the LSNs that > > matter for logical replication are database specific. > > > > Since we don't expose the most recent commit's LSN there's no way to > > say "the WAL is currently 1250, the last commit happened at 1000, the > > slot has flushed up to 800, therefore there are at most 200 bytes > > replication needs to read through to catch up. > > > > In the aforementioned thread [1] I'd proposed a patch that added a SQL > > function pg_last_commit_lsn() to expose the most recent commit's LSN. > > Robert Haas didn't think the initial version's modifications to > > commit_ts made sense, and a subsequent approach adding the value to > > PGPROC didn't have strong objections, from what I can see, but it also > > didn't generate any enthusiasm. > > > > As I was thinking about how to improve things, I realized that this > > information (since it's for monitoring anyway) fits more naturally > > into the stats system. I'd originally thought of exposing it in > > pg_stat_wal, but that's per-cluster rather than per-database (indeed, > > this is a flaw I hadn't considered in the original patch), so I think > > pg_stat_database is the correct location. > > > > I've attached a patch to track the latest commit's LSN in pg_stat_database. > > I have changed the status of commitfest entry to "Returned with > Feedback" as Aleksander's comments have not yet been resolved. Please > feel free to post an updated version of the patch and update the > commitfest entry accordingly.
Thanks for reminding me; I'd lost track of this patch. Regards, James Coleman
