Re: Regarding huge logical slot lag between source and target

From: Manu <manuelreyesbravo(at)gmail(dot)com>
To: Durgamahesh Manne <maheshpostgres9(at)gmail(dot)com>
Cc: pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org>, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
Subject: Re: Regarding huge logical slot lag between source and target
Date: 2026-10-26 17:46:22
Message-ID: 179303678228.2339875.5229227136587270241@gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi Durga,

On Mon, Oct 26, 2026 at 10:25 AM Durgamahesh Manne
<maheshpostgres9(at)gmail(dot)com> wrote:
> Decoding is the single threaded which is unable to handle efficiently.

First a correction. Earlier I read your 1297 MB sent_not_confirmed as a
sign that the subscriber was behind. I tested that and it does not
hold. With a slow subscriber, a CPU-starved walsender and a slow link
alike, sent_lsn - flush_lsn stayed under 100 MB and 98% or more of the
lag sat before sent_lsn. A walsender stops reading WAL while it cannot
send, so the "not_yet_decoded" column I gave you grows whichever side
is slow. Neither number tells you where the bottleneck is.

Decoding is the limit only if the walsender sits near 100% of one
core. In my runs it averaged under 40% in every case.

What does tell you is CPU and wait_event of the two processes, sampled
a few times while the lag grows, plus top on the same pids. On the
target:

SELECT pid, wait_event FROM pg_stat_activity
WHERE backend_type = 'logical replication worker';

and on the source the same with backend_type = 'walsender'.

- Apply worker near 100% CPU, wait_event mostly empty: the subscriber
is the limit.
- Apply worker mostly in LogicalApplyMain (waiting for data) while the
lag is large: the source side is not feeding it. Then a walsender
near 100% CPU means decoding; low CPU on a busy source host means it
is starved for CPU; low CPU on a quiet host with wait_event mostly
WalSenderWriteData means the network.

> * Increase the bandwidth

That helps only in the last case. Through a link limited to 8 MB/s the
walsender used 6% CPU and was in WalSenderWriteData 93% of the time,
and the apply worker used 17% CPU, 82% in LogicalApplyMain. Doubling
the link to 16 MB/s gave 1.86x the drain rate. With the apply worker
at 97-98% CPU, or with a starved walsender, the link is not what
limits, and more bandwidth would not change the drain rate.

> * Hash partitioning on that table

With one subscription, no: all partitions still go through one
walsender and one apply worker. With the publisher idle it drained at
0.79x the rate of the plain table, apply worker at 98% in both.

With one subscription per partition (4 here) the apply side scales:
1.85x with the publisher idle. With the publisher saturated it still
fell behind, by 40 MB/s of WAL instead of 136-152 MB/s, but mostly
because the workload itself ran at about half the rate. Each of the 4
walsenders decoded every transaction, and a transaction that touches
rows in several partitions is applied in separate pieces on the target,
not atomically. It is the same split into several publications that you
said is not a permanent solution, plus rebuilding the table.

On Justin's point about REPLICA IDENTITY FULL: in 16 the apply worker
uses a btree index on the target if one is usable. In one run
with the primary key on both sides it applied as many transactions per
second as the default identity (15,115 vs 15,060 and 15,527). Without a
usable index it scans the table for every row, so it is worth checking
which case you have.

These come from a lab with 4 CPU threads per side, one run per case and
two for the partition layouts, so take the ratios rather than the
rates. Details are in the attached measurements.txt.

Regards,
Manu

Attachment Content-Type Size
measurements.txt text/plain 5.3 KB

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Justin 2026-10-26 21:26:36 Re: Regarding huge logical slot lag between source and target
Previous Message Adrian Klaver 2026-10-26 16:06:11 Re: Debugging --- ERROR: function does not exist