RE: Conflict detection for update_deleted in logical replication

From: "Hayato Kuroda (Fujitsu)" <kuroda(dot)hayato(at)fujitsu(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Cc: shveta malik <shveta(dot)malik(at)gmail(dot)com>, vignesh C <vignesh21(at)gmail(dot)com>, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>, Nisha Moond <nisha(dot)moond412(at)gmail(dot)com>, Dilip Kumar <dilipbalaut(at)gmail(dot)com>, "Zhijie Hou (Fujitsu)" <houzj(dot)fnst(at)fujitsu(dot)com>, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
Subject: RE: Conflict detection for update_deleted in logical replication
Date: 2025-07-09 12:39:33
Message-ID: OSCPR01MB1496601BDEC7FCF7A822D270DF549A@OSCPR01MB14966.jpnprd01.prod.outlook.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Dear hackers,

All the tests in [1] are done with autovacuum=off, so I checked how would be in
autovacuum=on case.

Highlights
==========
* Regression on subscriber-side became bit larger than autovacuum=on case
when pgbench was run on both side
* Other than that, they were mostly same.

Used source
===========
pgHead commit fd7d7b7191 + v46 patchset

Machine details
===============
Intel(R) Xeon(R) CPU E7-4890 v2 @ 2.80GHz CPU(s) :88 cores, - 503 GiB RAM

01. pgbench on publisher
========================
The workload is mostly same as [1], but autovacuum was enabled.

Workload:
- Ran pgbench with 40 clients for the publisher.
- The duration was 300s, and the measurement was repeated 3 times.

Test Scenarios & Results:
- pgHead : Median TPS = 39757.24404
- pgHead + patch : Median TPS = 40871.37782

Observation:
- same trend as autovacuum=off
- No performance regression observed with the patch applied.
- The results were consistent across runs.

Detailed Results Table:
- each cell shows the TPS in each case.
- patch(ON) means patched and retain_conflict_info=ON is set.

run# pgHEAD pgHead+patch(ON)
1 40020.19703 40871.37782
2 39650.27381 40299.31806
3 39757.24404 41017.21089
median 39757.24404 40871.37782

02. pgbench on subscriber
========================
The workload is mostly same as [1].

Workload:
- Ran pgbench with 40 clients for the *subscriber*.
- The duration was 300s, and the measurement was repeated 3 times.

Test Scenarios & Results:
- pgHead : Median TPS = 41552.27857
- pgHead + patch : Median TPS = 41677.02942

Observation:
- same trend as autovacuum=off
- No performance regression observed with the patch applied.
- The results were consistent across runs.

Detailed Results Table:

run# pgHEAD pgHead+patch(ON)
1 41656.71589 41673.42577
2 41552.27857 41677.02942
3 41504.98347 42114.66964
median 41552.27857 41677.02942

03. pgbench on both sides
========================
The workload is mostly same as [1].

Workload:
- Ran pgbench with 15 clients for the *both side*.
- The duration was 300s, and the measurement was repeated 3 times.

Test Scenarios & Results:
Publisher:
- pgHead : Median TPS = 17355.08998
- pgHead + patch : Median TPS = 18382.41213

Subscriber:
- pgHead : Median TPS = 16998.14496
- pgHead + patch : Median TPS = 5804.129821

Observation:
- Regression became larger than autovacuum = off (-50%->-60%)
- No performance regression observed on the publisher with the patch applied.
- The performance is reduced on the subscriber side (TPS reduction (~60%)) due
to dead tuple retention for the conflict detection

Detailed Results Table:

On publisher:
run# pgHEAD pgHead+patch(ON)
1 17537.52375 18382.41213
2 17355.08998 18408.0712
3 17286.78467 18119.77276
median 17355.08998 18382.41213

On subscriber:
run# pgHEAD pgHead+patch(ON)
1 17130.63876 5886.375748
2 16998.14496 5737.799408
3 16891.2713 5804.129821
median 16998.14496 5804.129821

[1] https://www.postgresql.org/message-id/OSCPR01MB1496663AED8EEC566074DFBC9F54CA%40OSCPR01MB14966.jpnprd01.prod.outlook.com

Best regards,
Hayato Kuroda
FUJITSU LIMITED

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Lev Nikolaev 2025-07-09 12:40:23 stylesheet-html-common: only apply Bootstrap container classes in website build
Previous Message Álvaro Herrera 2025-07-09 12:27:35 Re: ABI Compliance Checker GSoC Project