Hi,
On Thu, Aug 6, 2026 at 6:02 PM Hayato Kuroda (Fujitsu)
<[email protected]> wrote:
>
> Dear Bharath,
Thanks a lot for providing thoughts.
> > The narrower the row, the more rows fit on a page and the bigger the saving.
> > The WAL reduction is the main benefit - less I/O, fewer WAL files, faster
> > crash
> > recovery, and less WAL to ship for cascading replication. The apply-time
> > change
> > is small (under 5%). My hypothesis is that the > other per-row work in the
> > apply
> > path outweighs the WAL write, so fewer WAL records alone doesn't move it
> > much.
> > I'll also test with streaming=off to see how that compares. (I also have
> > small-scale results from 1 to 100K rows showing the gain grows with row
> > count;
> > happy to share if useful.)
>
> Hmm. Actually I'm bit unclear the outcome wins the complexity of
> adding codes. The benefit might be specific: apply-time cannot be
> improved and the amount of WAL cannot be reduced for large tuples. But
> it's good to measure the recovery time before and after the patching.
That's a good metric to have numbers for. I did a benchmark and as
expected found that multi-insert apply on the subscriber writes fewer
WAL records (56x fewer at 50B rows, 23x at 128B rows) and less WAL
(2.68x at 50B, 1.48x at 128B), which speeds up crash recovery by 6.3x
and 3.2x respectively, and standby catch-up (physical standby
connected to subscriber) by 5.3x and 3.2x. Apply time does not change
because the per-row work in the apply path outweighs the WAL write.
Recovery improves more than the WAL reduction alone because the
remaining WAL also replays faster per byte (the WAL replay rate row),
since each record carries fixed redo overhead and there are far fewer
records.
Results (100M rows, average of 2 runs), see [1] [2] [3] for test setup
and server logs:
Row size Metric HEAD PATCHED Gain
50 B crash-recovery time 27.3 s 4.4 s 6.3x
standby catch-up 29.6 s 5.6 s 5.3x
restart-to-ready 31.5 s 8.7 s 3.6x
WAL replay rate 326 MB/s 759 MB/s 2.3x
WAL generated 8.86 GB 3.31 GB 2.68x
WAL records 100.9 M 1.8 M 56x
logical decode time 168.2 s 162.9 s 1.03x
apply time 133 s 134 s ~1.0x
128 B crash-recovery time 33.3 s 10.4 s 3.2x
standby catch-up 34.7 s 10.8 s 3.2x
restart-to-ready 45.5 s 22.6 s 2.0x
WAL replay rate 509 MB/s 1118 MB/s 2.2x
WAL generated 16.96 GB 11.45 GB 1.48x
WAL records 102.7 M 4.5 M 23x
logical decode time 291.7 s 289.8 s 1.01x
apply time 197 s 198 s ~1.0x
> BTW, your patch looks like table_multi_insert() is called even if only
> a few tuples are accumulated. I'm not familiar with the TableAM, but
> are there any overheads that to use instead of tuple_insert()?
Multi-insert with only a few tuples costs about the same as
single-insert calls. The only difference is that tuples that fit on
one page are written as one WAL record instead of one per tuple. Apply
time is unchanged between HEAD and the patch, including the wide-row
case where there is little to batch, so there is no measurable
overhead. If you have a specific case in mind, I'm happy to benchmark
it.
> > Currently the subscriber batches single inserts (this patch) and does a
> > multi-insert. Another idea is to add a new message type for multi-insert on
> > the
> > publisher when decoding XLOG_HEAP2_MULTI_INSERT, with the subscriber using
> > multi-insert only on that path. That keeps things simpler, but it only kicks
> in when the publisher used multi-insert itself (e.g. COPY FROM), and it
> requires
> > protocol changes.
>
> I think it has enough motivation to introduce MULTI_INSERT replication
> message. It might be narrower window to apply, but it's ensured that
> there are lots of tuples to be inserted.
I agree. Having a new logical decoding message for multi-insert seems
a good choice here as it keeps the design simple (especially on the
subscriber side) and easy to reason about. Whenever bulk inserts
happen on the publisher with COPY FROM, the apply side on the
subscriber also uses multi-inserts, reducing the amount of WAL
(keeping the same amount of WAL as that of the publisher for the same
bulk insert operation), helping not only write less WAL and improve
recovery times but also improve catch-up times on standbys connected
to the subscriber.
I did some thinking around this idea but before proceeding, I will
wait for others to provide some thoughts.
[1] Test setup: 3 Amazon EC2 r7i.8xlarge instances (32 vCPU, 256 GB
RAM) as publisher, subscriber, and physical standby of the subscriber.
gp3 data volume (16000 IOPS, 1000 MB/s), separate gp3 WAL volume.
wal_level=logical, shared_buffers=16GB, max_wal_size=80GB,
checkpoint_timeout=24h, autovacuum off, streaming=parallel. 100M rows
in one transaction (50B = 3 bigints, 128B = 13 bigints). Subscriber
crash-restarted with immediate stop. The logs in [2] and [3] are from
run 1 of the 2 runs.
[2] 50 byte rows
Crash recovery
HEAD
2026-08-16 17:15:20.444 UTC LOG: redo starts at 23/8B01E5A8
2026-08-16 17:15:47.673 UTC LOG: redo done at 25/9B2856C8 system
usage: CPU: user: 24.45 s, system: 2.76 s, elapsed: 27.22 s
replayed 8.86 GB in 27.22 s = 326 MB/s
PATCHED
2026-08-16 17:48:36.426 UTC LOG: redo starts at 11/3201C4C0
2026-08-16 17:48:40.786 UTC LOG: redo done at 11/F73C1228 system
usage: CPU: user: 2.33 s, system: 2.02 s, elapsed: 4.36 s
replayed 3.31 GB in 4.36 s = 759 MB/s
[3] 128 byte rows
Crash recovery
HEAD
2026-08-16 17:31:32.176 UTC LOG: redo starts at 27/AB479110
2026-08-16 17:32:05.475 UTC LOG: redo done at 2B/9E13AD08 system
usage: CPU: user: 26.75 s, system: 6.54 s, elapsed: 33.29 s
replayed 16.96 GB in 33.29 s = 509 MB/s
PATCHED
2026-08-16 18:02:28.497 UTC LOG: redo starts at 12/BC77EFC0
2026-08-16 18:02:38.740 UTC LOG: redo done at 15/6712D9A8 system
usage: CPU: user: 4.77 s, system: 5.46 s, elapsed: 10.24 s
replayed 11.45 GB in 10.24 s = 1118 MB/s
--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com
