Horton

Re: Conflict detection for update_deleted in logical replication

Amit Kapila <amit.kapila16@gmail.com>

View thread View on postgr.es
From: Amit Kapila <amit.kapila16@gmail.com>
To: Masahiko Sawada <sawada.mshk@gmail.com>
Cc: "Zhijie Hou (Fujitsu)" <houzj.fnst@fujitsu.com>, Nisha Moond <nisha.moond412@gmail.com>, "Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>, shveta malik <shveta.malik@gmail.com>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2025-02-07T05:47:21Z
Lists: pgsql-hackers 
On Fri, Feb 7, 2025 at 2:18 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
> I'd like to confirm what users would expect of this
> max_conflict_retention_duration option and it works as expected. IIUC
> users would want to use this option when they want to balance between
> the reliable update_deleted conflict detection and the performance. I
> think they want to detect updated_deleted reliably as much as possible
> but, at the same time, would like to avoid a huge performance dip
> caused by it. IOW, once the apply lag becomes larger than the limit,
> they would expect to prioritize the performance (recovery) over the
> reliable update_deleted conflict detection.
>

Yes, this understanding is correct.

> With the subscription-level max_conflict_retention_duration, users can
> set it to '5min' to a subscription, SUB1, while not setting it to
> another subscription, SUB2, (assuming here that both subscriptions set
> retain_conflict_info = true). This setting works fine if SUB2 could
> easily catch up while SUB1 is delaying, because in this case, SUB1
> would stop updating its xmin when delaying for 5 min or longer so the
> slot's xmin can advance based only on SUB2's xmin. Which is good
> because it ultimately allow vacuum to remove dead tuples and
> contributes to better performance. On the other hand, in cases where
> SUB2 is as delayed as or more than SUB1, even if SUB1 stopped updating
> its xmin, the slot's xmin would not be able to advance. IIUC
> pg_conflict_detection slot won't be invalidated as long as there is at
> least one subscription that sets retain_conflict_info = true and
> doesn't set max_conflict_retention_duration, even if other
> subscriptions set max_conflict_retention_duration.
>

Right.

> I'm not really sure that these behaviors are the expected behavior of
> users who set max_conflict_retention_duration to some subscriptions.
> Or I might have set the wrong expectation or assumption on this
> parameter. I'm fine with a subscription-level
> max_conflict_retention_duration if it's clear this option works as
> expected by users who want to use it.
>

It seems you are not convinced to provide this parameter at the
subscription level and anyway providing it as GUC will simplify the
implementation and it would probably be easier for users to configure
rather than giving it at the subscription level for all subscriptions
that have set retain_conflict_info set to true. I guess in the future
we can provide it at the subscription level as well if there is a
clear use case for it. Does that make sense to you?

-- 
With Regards,
Amit Kapila.

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.