Re: Conflict detection for update_deleted in logical replication

On Fri, Jan 10, 2025 at 6:13 AM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>
> 3. If the apply worker cannot catch up, it could enter to a bad loop;
> the publisher sends huge amount of data -> the apply worker cannot
> catch up -> it needs to wait for a longer time to advance its
> oldest_nonremovable_xid -> more garbage are accumulated and then have
> the apply more slow -> (looping). I'm not sure how to deal with this
> point TBH. We might be able to avoid entering this bad loop once we
> resolve the other two points.
>

I don't think we can avoid accumulating garbage especially when the
workload on the publisher is more. Consider the current case being
discussed, on the publisher, we have 30 clients performing read-write
operations and there is only one pair of reader (walsender) and writer
(apply_worker) to perform all those write operations on the
subscriber. It can never match the speed and the subscriber side is
bound to have less performance (or accumulate more bloat) irrespective
of its workload. If there is one client on the publisher performing
operation, we won't see much degradation but as the number of clients
increases, the performance degradation (and bloat) will keep on
increasing.

There are other scenarios that can lead to the same situation, such as
a large table sync, the subscriber node being down for sometime, etc.
Basically, any case where apply_side lags by a large amount from the
remote node.

One idea to prevent the performance degradation or bloat increase is
to invalidate the slot, once we notice that subscriber lags (in terms
of WAL apply) behind the publisher by a certain threshold. Say we have
max_lag (or max_lag_behind_remote) (defined in terms of seconds)
subscription option which allows us to stop calculating
oldest_nonremovable_xid for that subscription. We can indicate that
via some worker_level parameter. Once all the subscriptions on a node
that has enabled retain_conflict_info have stopped calculating
oldest_nonremovable_xid, we can invalidate the slot. Now, users can
check this and need to disable/enable retain_conflict_info to again
start retaining the required information. The other way could be that
instead of invalidating the slot, we directly drop/re-create the slot
or increase its xmin. If we choose to advance the slot automatically
without user intervention, we need to let users know via LOG and or
via information in the view.

I think such a mechanism via the new option max_lag will address your
concern: "It's reasonable behavior for this approach but it might not
be a reasonable outcome for users if they could be affected by such a
performance dip without no way to avoid it." as it will provide a way
to avoid performance dip only when there is a possibility of such a
dip.

I mentioned max_lag as a subscription option instead of a GUC because
it applies only to subscriptions that have enabled
retain_conflict_info but we can consider it to be a GUC if you and
others think so provided the above proposal sounds reasonable. Also,
max_lag could be defined in terms of LSN as well but I think time
would be easy to configure.

Thoughts?

--
With Regards,
Amit Kapila.