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.