Re: Conflict detection for update_deleted in logical replication

On Mon, Jul 7, 2025 at 12:03 PM Zhijie Hou (Fujitsu)
<houzj(dot)fnst(at)fujitsu(dot)com> wrote:
>
> On Sun, Jul 6, 2025 at 10:51 PM Masahiko Sawada wrote:
>
> >
> > On Sun, Jul 6, 2025 at 8:03 PM Hayato Kuroda (Fujitsu)
> > <kuroda(dot)hayato(at)fujitsu(dot)com> wrote:
> > >
> > > Dear hackers,
> > >
> > > As a confirmation purpose, I did performance testing with four
> > > workloads we did before.
> >
> > Thank you for doing the performance tests!
> >
> > >
> > > 03. pgbench on both sides
> > > ========================
> > > The workload is mostly same as [3].
> > >
> > > Workload:
> > > - Ran pgbench with 40 clients for the *both side*.
> > > - The duration was 120s, and the measurement was repeated 10 times.
> > >
> > > (bothtest.tar.gz can run the same workload)
> > >
> > > Test Scenarios & Results:
> > > Publisher:
> > > - pgHead : Median TPS = 16799.67659
> > > - pgHead + patch : Median TPS = 17338.38423
> > > Subscriber:
> > > - pgHead : Median TPS = 16552.60515
> > > - pgHead + patch : Median TPS = 8367.133693
> >
> > My first impression is that 40 clients is a small number at which a 50%
> > performance degradation occurs in 120s. Did you test how many clients are
> > required to trigger the same level performance regression with
> > retain_conflict_info = off?
>
> Could you please elaborate further on the intention behind the suggested tests
> and what outcomes are expected? I ask because we anticipate that disabling
> retain_conflict_info should not cause regression, given that dead tuples will
> not be retained.

I think these performance regressions occur because at some point the
subscriber can no longer keep up with the changes occurring on the
publisher. This is because the publisher runs multiple transactions
simultaneously, while the Subscriber applies them with one apply
worker. When retain_conflict_info = on, the performance of the apply
worker deteriorates because it retains dead tuples, and as a result it
gradually cannot keep up with the publisher, the table bloats, and the
TPS of pgbench executed on the subscriber is also affected. This
happened when only 40 clients (or 15 clients according to the results
of test 4?) were running simultaneously.

I think that even with retain_conflict_info = off, there is probably a
point at which the subscriber can no longer keep up with the
publisher. For example, if with retain_conflict_info = off we can
withstand 100 clients running at the same time, then the fact that
this performance degradation occurred with 15 clients explains that
performance degradation is much more likely to occur because of
retain_conflict_info = on.

Test cases 3 and 4 are typical cases where this feature is used since
the conflicts actually happen on the subscriber, so I think it's
important to look at the performance in these cases. The worst case
scenario for this feature is that when this feature is turned on, the
subscriber cannot keep up even with a small load, and with
max_conflict_retetion_duration we enter a loop of slot invalidation
and re-creating, which means that conflict cannot be detected
reliably.

> >
> > >
> > > 04. pgbench on both side, and max_conflict_retention_duration was
> > > tuned
> > >
> > ================================================
> > ======================
> > > ==
> > > The workload is mostly same as [4].
> > >
> > > Workload:
> > > - Initially ran pgbench with 40 clients for the *both side*.
> > > - Set max_conflict_retention_duration = {60, 120}
> > > - When the slot is invalidated on the subscriber side, stop the benchmark
> > and
> > > wait until the subscriber would be caught up. Then the number of clients
> > on
> > > the publisher would be half.
> > > In this test the conflict slot could 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.
> > > - Total period of the test was 900s for each cases.
> > >
> > > (max_conflixt.tar.gz can run the same workload)
> > >
> > > Observation:
> > > -
> > > - Parallelism of the publisher side is reduced till 15->7->3 and finally the
> > > conflict slot is not invalidated.
> > > - TPS on the subscriber side is improved when the concurrency was
> > reduced.
> > > This is because the dead tuple accumulation is reduced on subscriber
> > due to
> > > the reduced workload on the publisher.
> > > - when publisher has Nclients=3, no regression in subscriber's TPS
> >
> > I think that users typically cannot control the amount of workloads in
> > production, meaning that once the performance regression starts to happen
> > the subscriber could enter the loop where invalidating the slot, recovreing the
> > performance, creating the slot, and having the performance problem.
>
> Yes, you are right. The test is designed to demonstrate that the slot can
> be invalidated under high workload conditions as expected, while
> remaining valid if the workload is reduced. In production systems where
> workload reduction may not be possible, it’s recommended to disable
> `retain_conflict_info` to enhance performance. This decision involves balancing
> the need for reliable conflict detection with optimal system performance.

Agreed. I'm a bit concerned that the range in which users can achieve
this balance is small.

> I think the hot standby feedback also has a similar impact on the performance
> of the primary, which is done to prevent the early removal of data necessary
> for the standby, ensuring that it remains accessible when needed.

Right. I think it's likely to happen if there is a long running
read-only query on the standby. But does it happen also when there are
only short read-only transactions on the standbys?

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com