Horton

RE: Conflict detection for update_deleted in logical replication

Zhijie Hou (Fujitsu) <houzj.fnst@fujitsu.com>

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

Attachments

On Wed, Jul 2, 2025 at 2:03 PM Zhijie Hou (Fujitsu) wrote:
> 
> On Tue, Jul 1, 2025 at 6:10 PM Zhijie Hou (Fujitsu) wrote:
> > Here is V45 patch set.
> 
> With the main patch set now stable, I am summarizing the performance tests
> conducted before for reference.
> 
> In earlier tests [1], we confirmed that in a pub-sub cluster with high workload
> on the publisher (via pgbench), the patch had no impact on TPS (Transactions
> Per Second) on the publisher. This indicates that the modifications to the
> walsender responsible for replying to publisher status do not introduce
> noticeable overhead.
> 
> Additionally, we confirmed that the patch, with its latest mechanism for
> dynamically tuning the frequency of advancing slot.xmin, does not affect TPS
> on the subscriber when minimal changes occur on the publisher. This test[2]
> involved creating a pub-sub cluster and running pgbench on the subscriber to
> monitor TPS. It further suggests that the logic for maintaining non-removable
> xid in the apply worker does not introduce noticeable overhead for concurrent
> user DMLs.
> 
> Furthermore, we tested running pgbench on both publisher and subscriber[3].
> Some regression was observed in TPS on the subscriber, because workload on
> the publisher is pretty high and the apply workers must wait for the amount of
> transactions with earlier timestamps to be applied and flushed before
> advancing the non-removable XID to remove dead tuples. This is the expected
> behavior of this approach since the patch's main goal is to retain dead tuples
> for reliable conflict detection.
> 
> When discussing the regression, we considered providing a workaround for
> users to recover from the regression (the 0002 of the latest patch set). We
> introduces a GUC option max_conflict_retention_duration, designed to prevent
> excessive accumulation of dead tuples when subscription with
> retain_conflict_info enabled is present and the apply worker cannot catch up
> with the publisher's workload. In short, the conflict detection replication slot
> will be invalidated if lag time exceeds the specified GUC value.
> 
> In performance tests[4], we confirmed that the slot would be invalidated as
> expected when the workload on the publisher was high, and it would not get
> invalidated anymore after reducing the workload. This shows even if the slot
> has been invalidated once, users can continue to detect the update_deleted
> conflict by reduce the workload on the publisher.
> 
> The design of the patch set was not changed since the last performance test;
> only some code enhancements have been made. Therefore, I think the results
> and findings from the previous performance tests are still valid. However, if
> necessary, we can rerun all the tests on the latest patch set to verify the same.

During local testing, I discovered a bug caused by my oversight in assigning
the new xmin to slot.effective, which resulted in dead tuples remaining
non-removable until restart. I apologize for the error and have provided
corrected patches. Kindly use the latest patch set for performance testing.

Best Regards,
Hou zj

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.