Re: Conflict detection for update_deleted in logical replication

On Thu, Jul 10, 2025 at 6:46 PM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>
> On Wed, Jul 9, 2025 at 9:09 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> >
> > > 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.
> > >
> >
> > 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.
>
> While I understand that there are some possible solutions we have
> today to reduce the contention, I'm not really sure these are really
> practical solutions as it increases the operational costs instead.
>

I assume by operational costs you mean defining the replication
definitions such that workload is distributed among multiple apply
workers via subscriptions either by row_filters, or by defining
separate pub-sub pairs of a set of tables, right? If so, I agree with
you but I can't think of a better alternative. Even without this
feature as well, we know in such cases the replication lag could be
large as is evident in recent thread [1] and some offlist feedback by
people using native logical replication. As per a POC in the
thread[1], parallelizing apply or by using some prefetch, we could
reduce the lag but we need to wait for that work to mature to see the
actual effect of it.

The path I see with this work is to clearly document the cases
(configuration) where this feature could be used without much downside
and keep the default value of subscription option to enable this as
false (which is already the case with the patch). Do you see any
better alternative for moving forward?

[1]: https://www.postgresql.org/message-id/7b60e4e1-de40-4956-8135-cb1dc2be62e9%40garret.ru

--
With Regards,
Amit Kapila.