On Mon, 19 Oct, 2026, 17:13 Manu, <[email protected]> wrote:
> Hi Durga, > > > How to fix this permanently. > > Your output rules out a pinned transaction: the idle-in-transaction > sessions > have xid_age 9-22 (tiny), and restart_lsn is tracking confirmed_flush, so > nothing is stuck -- replication is simply behind by 60 GB. That is the good > news: there is no hidden holder to hunt. It is a throughput deficit, and > the > fix depends on *where* the deficit is. > > I reproduced your signature on 16.15 (hot-row counter, many small > transactions, walsender in catchup, sent ~= confirmed, spill/stream 0). > This > workload can be limited in two different places, and your snapshot cannot > tell which one is yours: > > 1. The subscriber's apply worker. A single apply worker applies these small > transactions serially; when the publisher has spare CPU, that one worker > is the ceiling. > 2. The publisher's walsender. On a CPU-saturated publisher the walsender > competes with your backends for CPU and cannot decode fast enough; the > backlog then does not drain while the load runs, and clears in seconds > once it stops. > > Tell them apart with top: the walsender pid on the source, and the > "logical replication apply worker" on the target. Whichever sits near 100% > of a core is the limit; one well below 100% while the host is busy is the > one > starved for CPU. Your sent_not_confirmed was 1297 MB (in my runs it was > kB), > which suggests the subscriber side is also behind -- so apply-bound is > likely, > possibly both. > > Why it matters -- the lever is different: > > - If the apply worker is the limit: streaming=parallel will not help. In > PG16 > parallel apply only covers transactions large enough to be streamed > (above > logical_decoding_work_mem); a flood of tiny transactions stays on one > worker > (I confirmed the count stays 1 with max_parallel_apply_workers_per_ > subscription=4). The levers are to lower the commit rate reaching that > table, or to split the tables across several subscriptions so multiple > apply > workers run in parallel. > > - If the walsender is starved: splitting makes it worse, because each > subscription runs its own walsender and each decodes the entire WAL. Here > the lever is to reduce WAL volume or free CPU on the publisher. > > Two structural notes, measured: > > - Splitting into N subscriptions does raise apply throughput (more > workers), > but each walsender re-decodes all the WAL (extra load on the publisher), > and > a transaction touching tables in two subscriptions is no longer applied > atomically on the subscriber. > > - That "update market set version=version+1" counter is about a fifth of > your > WAL bytes. If the target does not need it, excluding it from the > publication > frees apply capacity -- though it does not reduce the WAL the walsender > still > has to decode. > > One note on the "catchup" state: a logical walsender switches to > "streaming" > the first time it catches up and never switches back, so "catchup" only > means > it has not caught up once since it last started -- consistent with being > behind, not a separate fault. > > So run top on both processes first; that tells you whether to add apply > workers (split) or to relieve the publisher. Happy to share the test > details. > > Regards, > Manu > Hi Manu Now slog lag is very minimal. How did you rectify when same issue you faced rather than resync data from scratch. Regards Durga Mahesh >
