Hi Durga,
On Mon, Oct 26, 2026 at 10:25 AM Durgamahesh Manne
<[email protected]> 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
Logical replication lag: where is the bottleneck? -- PostgreSQL 16.15
=====================================================================
Lab measurements, one host. Not a benchmark: the host had other load,
so absolute rates are only comparable within this file. Partition
layouts were run twice in alternating rounds (a, b); the network and
REPLICA IDENTITY FULL cases once each.
Setup
-----
- PostgreSQL 16.15 (REL_16_STABLE 6ceac2e1250), -O2, no assertions.
Publisher and subscriber on one host, unix sockets.
- Publisher and pgbench pinned to 4 hardware threads, subscriber to
another 4. logical_decoding_work_mem=64MB. Subscriptions:
binary=true, streaming=on, copy_data=false.
- One hot table, 1M rows: hot(id bigint PRIMARY KEY, k bigint, v bigint,
pad text). Workload, 16 clients, autocommit, primary key unchanged:
UPDATE hot SET k = k + 1, v = :a WHERE id BETWEEN :a AND :a + 19;
About 5 kB of decoded data per transaction in every layout.
- Layouts: flat = plain table, 1 publication, 1 subscription.
hash4/1 = hash partitioned in 4, 1 publication on the root,
1 subscription. hash4/4 = 4 publications with one partition each,
4 subscriptions. publish_via_partition_root = false in all.
- Backlog built with the subscriptions disabled, then they are enabled:
"idle" = load stopped (publisher has spare CPU), "busy" = the same
load keeps running for 120 s (publisher CPU saturated).
- Every 2 s: pg_current_wal_lsn, slot confirmed_flush_lsn, sent_lsn and
flush_lsn from pg_stat_replication, CPU of the walsender(s) and apply
worker(s) from /proc. Every 0.25 s: wait_event of the walsender(s)
and apply worker(s) from pg_stat_activity (empty = "CPU").
- "consumed" = advance of confirmed_flush_lsn of the slowest slot, in
MB of WAL per second. Rows and sum(k) matched publisher vs subscriber
after every drained run.
1. Publisher idle: the single apply worker is the limit
-------------------------------------------------------
layout consumed MB/s (a / b) apply CPU walsender CPU
flat 66.0 / 68.6 97% / 98% 29% / 30%
hash4/1 52.7 / 53.6 98% / 98% 23% / 24%
hash4/4 123.2 / 126.4 365% / 375% 143% / 145%
(4 workers) (4 walsenders)
Relative to flat: hash4/1 0.79x, hash4/4 1.85x.
The subscriber had 4 hardware threads, so 4 apply workers were capped
near 4 x 95%.
Wait events, flat: apply CPU 99%; walsender WalSenderWriteData 65-70%.
2. Publisher saturated: nothing drains, the walsender gets too little CPU
------------------------------------------------------------------------
layout consumed MB/s generated MB/s net MB/s pgbench tps
flat a 46.8 182.4 -135.6 44,161
flat b 44.4 196.0 -151.6 47,541
hash4/1 a 34.4 119.8 -85.5 27,587
hash4/1 b 33.7 123.8 -90.1 28,195
hash4/4 a 59.4 99.6 -40.2 23,158
hash4/4 b 59.8 98.8 -39.0 22,993
Apply worker CPU 65-70% (flat, hash4/1), 148-151% for 4 (hash4/4).
Apply worker waiting for data (LogicalApplyMain): flat 22% / 34%,
hash4/1 31% / 32%, hash4/4 63% / 64% of probes.
Walsender CPU 28-30% (flat), 23-24% (hash4/1), about 24% each of 4.
The hash-partitioned table also lowered the publisher's own throughput
in this workload (each 20-row range touches all 4 partitions).
In hash4/4, each of the 4 slots decoded every transaction: total_txns
per slot 1,592,727 to 1,595,815 (pg_stat_replication_slots, run a).
3. Bandwidth-limited link (proxy between subscriber and publisher)
------------------------------------------------------------------
link consumed MB/s walsender apply worker
8 MB/s 10.6 6% CPU, 17% CPU,
WalSenderWriteData LogicalApplyMain 82%
93%
16 MB/s 19.7 12% CPU, 29% CPU,
WalSenderWriteData LogicalApplyMain 73%
90%
Doubling the link gave 1.86x, because here the link was the limit.
4. Where the lag shows up in pg_stat_replication
------------------------------------------------
In every run above (apply-bound, walsender-bound, link-bound), 98% or
more of the unconfirmed WAL was between pg_current_wal_lsn and sent_lsn,
and sent_lsn - flush_lsn never exceeded 96 MB. A logical walsender
advances sent_lsn as it decodes and stops reading WAL while it cannot
send (walsender.c, XLogSendLogical and ProcessPendingWrites), so a slow
link or a slow subscriber also shows up as "not yet sent".
One transaction updating all 1M rows (253 MB decoded), subscriber caught
up before it: sent_lsn - flush_lsn peaked at 54 MB with
logical_decoding_work_mem=4GB (not streamed) and at 12 MB with 64MB
(streamed, stream_txns=1).
5. REPLICA IDENTITY FULL, primary key present on both sides (one run)
--------------------------------------------------------------------
Publisher idle, flat layout: 15,115 transactions/s applied, vs 15,060
and 15,527 with the default replica identity. Decoded data per
transaction 8,520 vs 5,060 bytes. In 16 the apply worker uses a usable
btree index on the subscriber for REPLICA IDENTITY FULL
(FindUsableIndexForReplicaIdentityFull, relation.c); without one it
scans the table for every updated row.
