Re: Parallel Apply

Hi Amit,

Following up on my earlier mail about the uninitialized oldtup, I measured
v27 throughput to see which workloads non-streamed parallel apply speeds up.
The case I started from is the small-transaction / hot-counter pattern: many
tiny transactions, each updating one hot row or inserting a single row.
Setup: master 8e82d6610e72 + v27 0001-0012, -O2, publisher and subscriber on
one machine.

Method: backlog drain. With the subscription disabled, 8 pgbench clients
commit 30,000 transactions; then ENABLE and time until a marker row committed
last becomes visible on the subscriber. Median of 3 rounds (5 where noted),
alternating order, VACUUM + CHECKPOINT before each drain. Ratios are against
max_parallel_apply_workers_per_subscription = 0.

1. Where it helps: transaction size. Each transaction updates K rows that no
other transaction touches (nothing shared at all). Speedup with 2 / 4 workers:

K=1: 0.014x / 0.0099x (serial ~142k txn/s, 4 workers ~1.4k)
K=10: 0.21x / 0.15x
K=100: 0.79x / 0.50x (5 rounds)
K=300: 1.23x / 1.22x
K=1000: 1.47x / 1.70x (5 rounds)

So parallel apply pays off from a few hundred rows per transaction here, and
small transactions are 70-100x slower than serial even with zero dependencies.

2. For single-row transactions the dependency density does not matter. With 4
workers, 50% UPDATE of one of H hot rows plus 50% INSERT, throughput is
1,506 / 1,432 / 1,408 / 1,405 / 1,396 txn/s for H = 1 / 10 / 100 / 1,000 /
10,000, and 1,392 txn/s for a fully independent mix. Serial is ~190k-280k.

3. Where the time goes (H=10, 4 workers). Stack samples put the leader 95% of
the time in pa_wait_for_depended_transaction(), called from
apply_handle_commit() for the ~20% of transactions it applies itself
(TRANS_LEADER_APPLY, maintain_commit_order_dependency(NULL)); the workers sit
idle in LogicalParallelApplyLoop most of the time. Each call that finds the
entry does lock + unlock and then pg_usleep(1000L) before re-checking
(applyparallelworker.c, the loop already marked "XXX: might be a performance
bottleneck"), so every real wait costs at least 1 ms after the predecessor
commits. With one LOG line per call, a 23.3 s drain had 22,405 such sleeps
(6,739 leader + 15,666 workers), i.e. 22.4 s, 96% of the wall time.

To attribute it, I changed only the sleep length (an experiment, not a
proposal; 0 means busy re-check). 4 workers, H=10, txn/s:

1000 us: 1,387
100 us: 9,179
10 us: 21,076
0 us: 132,616
serial: 231,461

So about 96x of the ~167x comes from that sleep, and ~1.7x remains with no
sleep at all; there the leader still spends 0.10 s of a 0.23 s drain waiting
in the lock at apply_handle_commit().

On correctness, a serial-vs-parallel oracle (same WAL into a 0-worker and a
4-worker subscriber, plus live invariants) found no divergence across FK,
two_phase, a subscriber-only unique index, and 25 immediate-stop crashes of
the subscriber, and commit order was preserved; the only problem was the
oldtup read reported earlier.

Caveats: single laptop-class machine, both nodes local, fsync=off; serial
drains take 0.1-0.25 s, so serial txn/s are approximate, but the ratios are
far outside that error. I can share the scripts, the instrumentation diff
and the raw results.

Regards,
Manu