Re: Conflict detection for update_deleted in logical replication

Here is a summary of tests targeted to the Publisher node in a
Publisher-Subscriber setup.
(All tests done with v14 patch-set)

----------------------------
Performance Tests:
----------------------------
Test machine details:
Intel(R) Xeon(R) CPU E7-4890 v2 @ 2.80GHz CPU(s) :120 - 800GB RAM

Setup:
- Created two nodes ( 'Pub' and 'Sub'), with logical replication.
- Configurations for Both Nodes:

shared_buffers = 40GB
max_worker_processes = 32
max_parallel_maintenance_workers = 24
max_parallel_workers = 32
checkpoint_timeout = 1d
max_wal_size = 24GB
min_wal_size = 15GB
autovacuum = off

- Additional setting on Sub: 'track_commit_timestamp = on' (required
for the feature).
- Initial data insertion via 'pgbench' with scale factor 100 on both nodes.

Workload:
- Ran pgbench with 60 clients for the publisher.
- The duration was 120s, and the measurement was repeated 10 times.

(Attached the scripts - "measure_pub.sh" and "setup_pub.sh" to
automate the above steps.)

Test Scenarios & Results:
- TPS (Transactions Per Second) were measured for the following scenarios:
- pgHead (with default wal_receiver_status_interval): Median TPS =
57708.24016
- pgHead (with wal_receiver_status_interval = 1s): Median TPS = 57467.58763
- pgHead + patch (detect_update_deleted=ON and default
wal_receiver_status_interval): Median TPS = 58598.98634
- pgHead + patch (detect_update_deleted=ON and
wal_receiver_status_interval = 1s): Median TPS = 58549.6975

Observation:
- No performance regression observed with the patch applied.
- 1-2% TPS improvement observed with the patch, likely due to some
compiler-level optimizations with the patch.
- The results were consistent across runs.

Detailed Results Table:
-- each cell shows the TPS in each case.
-- patch(ON) means patched and detect_update_deleted=ON is set.
-- interval=1s means wal_receiver_status_interval = 1s

Run# | pgHEAD | pgHead(interval=1s) | pgHead+patch(ON) |
pgHead+patch(ON)(interval=1s)
1 | 57737.49728 | 57547.05839 | 58656.83886 | 58570.13829
2 | 57650.25327 | 57795.05524 | 58397.82185 | 58624.47823
3 | 57708.24016 | 57467.58763 | 58618.85799 | 58381.69373
4 | 57677.51559 | 57438.90709 | 58640.20102 | 58529.25671
5 | 57845.84897 | 57390.75618 | 58616.45811 | 58122.4126
6 | 57667.91187 | 57598.73271 | 58567.73797 | 58576.74363
7 | 57821.66669 | 57642.50589 | 58573.27994 | 58507.67747
8 | 57649.50195 | 57792.58659 | 58581.51457 | 58605.13375
9 | 57672.72002 | 57656.59962 | 58677.14569 | 58512.77625
10 | 57606.99544 | 57660.21762 | 58502.80998 | 58590.33837
Median | 57708.24016 | 57467.58763 | 58598.98634 | 58549.6975
std. dev. | 76.9972099 | 138.9779921 | 82.35632411 | 150.2302062
~~~~

----------------------------
Walsender Profiling:
----------------------------
- Profiling was done for the 'walsender' process with the patch
enabled (both default and 'wal_receiver_status_interval=1s' cases).
- Functions GetOldestTransactionIdInCommit() and
ReadNextFullTransactionId() do not appear in the 'walsender' profile
in both cases.
- The walsender profile reports are attached.
~~~~

Note: Some performance regression was observed on the Subscriber side
with the patch and detect_update_deleted=ON. To detect update_deleted
conflicts, dead tuples are retained for a bit longer period, which is
likely the cause of the observed TPS regression.
A detailed analysis of the Subscriber-side cases will be shared in a
separate email.

--
Thanks,
Nisha