Thread
-
Re: Conflict detection for update_deleted in logical replication
Nisha Moond <nisha.moond412@gmail.com> — 2025-08-28T12:30:08Z
Hi, As per the test results in [1], the TPS drop observed on the subscriber with update_deleted enabled was mainly because only a single apply worker was handling the replication workload from multiple concurrent publisher clients. The following performance benchmarks were conducted to evaluate the improvements using parallel apply when update_deleted (retain_dead_tuples) is enabled, under heavy workloads, without leveraging row filters or multiple subscriptions to distribute the load. Note: The earlier tests from[1] are repeated with few workload modifications to see the improvements using parallel-apply. Highlights =============== - No regression was observed when running pgbench individually on either Pub or Sub nodes. - When pgbench was run on both Pub and Sub, performance improved significantly with the parallel apply patch. With just 4 workers, Sub was able to catch up with Pub without regression. - With max_conflict_retention_duration=60s, retention on Sub was not stopped when using 4 or more parallel workers. Machine details =============== Intel(R) Xeon(R) CPU E7-4890 v2 @ 2.80GHz CPU(s) :88 cores, - 503 GiB RAM Source code: =============== - pgHead(e9a31c0cc60) and parallel-apply v1 patch[2] - additionally used v64-0001 of update_deleted for max_conflict_retention_duration related tests. Test-01: pgbench on publisher ============================ Setup: --------- Pub --> Sub - Two nodes created in pub-sub logical replication setup. - Both nodes have the same set of pgbench tables created with scale=60. - The Sub node is subscribed to all the changes from the Pub's pgbench tables and the subscription has retain_dead_tuples = on Workload: -------------- - Run default pgbench(read-write) only on Pub with #clients=40 and run duration=10 minutes Results: ------------ #run pgHead_TPS pgHead+v1_patch_TPS 1 41135.71241 39922.7163 2 40466.23865 39980.29034 3 40578.16374 39867.44929 median 40578.16374 39922.7163 - No regression. ~~~ Test-02: pgbench on subscriber ======================== Setup: same as Test-01 Workload: -------------- - Run default pgbench(read-write) only on Sub node with #clients=40 and run duration=10 minutes Results: ----------- #run pgHead_TPS pgHead+v1_patch_TPS 1 42173.90504 42071.18058 2 41836.10027 41839.84837 3 41921.81233 41494.9918 median 41921.81233 41839.84837 - No regression. ~~~ Test-03: pgbench on both sides ======================== Setup: ------ Pub --> Sub - Two nodes created in a pub-sub logical replication setup. - Both nodes have different sets of pgbench tables created with scale=60. - The sub node also has Pub's pgbench tables and is subscribed to all the changes. Workload: -------------- - Run default pgbench(read-write) on both Pub and Sub nodes for their respective pgbench tables - Both pgbench runs are with #clients=15 and duration=10 minutes Observations: -------------- - On pgHead when retain_dead_tuples=ON, the Sub's TPS reduced by ~76% - With the parallel apply patch, performance improves significantly as parallel workers increase, since conflict_slot.xmin advances more quickly. - With just 4 parallel workers, subscription TPS matches the baseline (no regression). - Performance remains consistent at 8 and 16 workers. Detailed results: ------------------ case-1: - The base case pgHead(e9a31c0cc60) and retain_dead_tuples=OFF #run Pub_tps Sub_tps 1 17140.08647 16994.63269 2 17421.28513 17445.62828 3 17167.57801 17070.86979 median 17167.57801 17070.86979 case-2: - pgHead(e9a31c0cc60) and retain_dead_tuples=ON #run Pub_tps Sub_tps 1 18667.29343 4135.884924 2 18200.90297 4178.713784 3 18309.87093 4227.330234 median 18309.87093 4178.713784 - The Sub sees a ~76% of TPS reduction by default on head. case-3: - pgHead(e9a31c0cc60)+ v1_parallel_apply_patch and retain_dead_tuples=ON - number of parallel apply workers varied as 2,4,8,16 3a) #workers=2 #run Pub_tps Sub_tps 1 18336.98565 4244.072357 2 18629.96658 4231.887288 3 18152.92036 4253.293648 median 18336.98565 4244.072357 - There is no significant TPS improvement with 2 parallel workers, ~76% TPS reduction 3b) #workers=4 #run Pub_tps Sub_tps 1 16796.49468 16850.05362 2 16834.06057 16757.73115 3 16647.78486 16762.9107 median 16796.49468 16762.9107 - No regression 3c) #workers=8 #run Pub_tps Sub_tps 1 17105.38794 16778.38209 2 16783.5085 16780.20492 3 16806.97569 16642.87521 median 16806.97569 16778.38209 - No regression 3d) #workers=16 #run Pub_tps Sub_tps 1 16827.20615 16770.92293 2 16860.10188 16745.2968 3 16808.2148 16668.47974 median 16827.20615 16745.2968 - No regression. ~~~ Test-04. pgbench on both side, and max_conflict_retention_duration was tuned ======================================================================== Setup: ------- Pub --> Sub - setup is same as Test-03(above) - Additionally, subscription option max_conflict_retention_duration=60s Workload: ------------- - Run default pgbench(read-write) on both Pub and Sub nodes for their respective pgbench tables - Started with 15 clients on both sides. - When conflict_slot.xmin becomes NULL on Sub, pgbench was paused to let the subscription catch up. Then reduced publisher clients by half and resumed pgbench. Here, slot.xmin becomes NULL to indicate conflict retention is stopped under high publisher load but stays non-NULL when Sub is able to catchup with Pub's load. - Total duration of pgbench run is 10 minutes (600s). Observations: ------------------ - Without the parallel apply patch, publisher clients reduced from 15->7->3,and finally the retention was not stopped at 3 clients and slot.xmin remained non-NULL. - With the parallel apply patch, using 2 workers the subscription handled up to 7 publisher clients without stopping the conflict retention. - With 4+ workers, retention continued for the full 10 minutes and Sub TPS showed no regression. Detailed results: ----------------- case-1: - pgHead(e9a31c0cc60) + v64-001 and retain_dead_tuples=ON On publisher: #cleints durations[s] TPS 15 73 17953.52 7 100 9141.9 3 426 4286.381132 On Subscriber: #cleints durations[s] TPS 15 73 10626.67 15 99 10271.35 15 431 19467.07612 ~~~ case-2: - pgHead(e9a31c0cc60) + v64-001 + v1_parallel-apply patch[2] and retain_dead_tuples=ON - number of parallel apply workers varied as 2,4,8 2a) #workers=2 On publisher: #cleints durations[s] TPS 15 87 17318.3 7 512 9063.506025 On Subscriber: #cleints durations[s] TPS 15 87 10299.66 15 512 18426.44818 2b) #workers=4 On publisher: #cleints durations[s] TPS 15 600 16953.40302 On Subscriber: #cleints durations[s] TPS 15 600 16812.15289 2c) #workers=8 On publisher: #cleints durations[s] TPS 15 600 16946.91636 On Subscriber: #cleints durations[s] TPS 15 600 16708.12774 ~~~~ The scripts used for all the tests are attached. [1] https://www.postgresql.org/message-id/OSCPR01MB1496663AED8EEC566074DFBC9F54CA%40OSCPR01MB14966.jpnprd01.prod.outlook.com [2] https://www.postgresql.org/message-id/OS0PR01MB5716D43CB68DB8FFE73BF65D942AA%40OS0PR01MB5716.jpnprd01.prod.outlook.com -- Thanks, Nisha