Re: Regarding huge logical slot lag between source and target

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