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
