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