Horton

RE: Conflict detection for update_deleted in logical replication

Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.com>

View thread View on postgr.es
From: "Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>
To: 'Amit Kapila' <amit.kapila16@gmail.com>, Masahiko Sawada <sawada.mshk@gmail.com>
Cc: "Zhijie Hou (Fujitsu)" <houzj.fnst@fujitsu.com>, pgsql-hackers <pgsql-hackers@postgresql.org>, shveta malik <shveta.malik@gmail.com>, vignesh C <vignesh21@gmail.com>, Nisha Moond <nisha.moond412@gmail.com>, Dilip Kumar <dilipbalaut@gmail.com>
Date: 2025-07-10T12:37:53Z
Lists: pgsql-hackers 
Dear hackers,

> As per the above observations, it is less of a regression of this
> feature but more of a lack of parallel apply or some kind of pre-fetch
> for apply, as is recently proposed [1]. I feel there are use cases, as
> explained above, for which this feature would work without any
> downside, but due to a lack of some sort of parallel apply, we may not
> be able to use it without any downside for cases where the contention
> is only on a smaller set of tables. We have not tried, but may in
> cases where contention is on a smaller set of tables, if users
> distribute workload among different pub-sub pairs by using row
> filters, there also, we may also see less regression. We can try that
> as well.

I verified row filter idea by benchmark and this was the valid approach.
Please see the below report.

Highlights
=======
- No regression was observed when publisher changes were processed by multiple
  apply workers on the subscriber.

Used source
=========
pgHead commit 62a17a92833 + v47 patch set

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

Setup
====
 - Publisher and Subscriber nodes are created with configurations:
    autovacuum = false
    shared_buffers = '30GB'
    -- Also, worker and logical replication related parameters were increased
       as needed (see attached scripts for details).

Workload
======
 - The publisher has 4 pgbench tables: pgbench_pub_accounts, pgbench_pub_tellers,
   pgbench_pub_branches, and pgbench_pub_history
 - Also the publisher has 15 publications, say pub_[0..14]. Each publications could
   publish tuples which PK % 15 was same as their suffic
 - Ran pgbench with 15 clients for the *both side*.
 - On subscriber, there were 15 subscribers which subscribed one of the publication
 - On subscriber, the default pgbench workload is also run.
 - The duration was 5 minutes, and the measurement was repeated 3 times.

Test Scenarios & Results:
Publisher:
 - pgHead : Median TPS = 12201.92205
 - pgHead + patch : Median TPS = 12368.58001
(TPS reduced ~1.5%)

Subscriber:
 - pgHead : Median TPS = 11264.78483
 - pgHead + patch : Median TPS = 11471.8107
(TPS reduced ~1.8%)

Observation:
 - No performance regression was observed on either the publisher or subscriber
   with the patch applied.

Detailed Results Table
======================
Publisher:
#run	head		patched
1	12201.92205	12368.58001
2	12263.03531	12410.21465
3	12171.24214	12330.47522
median	12201.92205	12368.58001

Subscriber:
#run	head		patched
1	11383.51717	11471.8107
2	11264.78483	11422.47011
3	11146.6676	11518.8403
median	11264.78483	11471.8107

Best regards,
Hayato Kuroda
FUJITSU LIMITED

Commits

  1. Fix intermittent BF failures in 035_conflicts.

  2. Resume conflict-relevant data retention automatically.

  3. Fix intermittent test failure introduced in 6456c6e2c4.

  4. Fix Coverity issue reported in commit a850be2fe.

  5. Add test to prevent premature removal of conflict-relevant data.

  6. Post-commit review fixes for 228c370868.

  7. Add max_retention_duration option to subscriptions.

  8. Detect and report update_deleted conflicts.

  9. Preserve conflict-relevant data during logical replication.

  10. Integrate FullTransactionIds deeper into two-phase code

  11. Improve checks for GUC recovery_target_timeline

  12. Prevent excessive delays before launching new logrep workers.

  13. Keep WAL segments by slot's last saved restart LSN

  14. Rework some code handling pg_subscription data in psql and pg_dump

  15. Use generateClonedIndexStmt to propagate CREATE INDEX to partitions.