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: Masahiko Sawada <sawada.mshk@gmail.com>, Amit Kapila <amit.kapila16@gmail.com>
Cc: shveta malik <shveta.malik@gmail.com>, pgsql-hackers <pgsql-hackers@postgresql.org>, "Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>
Date: 2024-09-25T05:44:00Z
Lists: pgsql-hackers

Attachments

On Friday, September 20, 2024 11:59 AM Hou, Zhijie/侯 志杰 wrote:
> 
> On Friday, September 20, 2024 10:55 AM Zhijie Hou (Fujitsu)
> <houzj.fnst@fujitsu.com> wrote:
> > On Friday, September 20, 2024 2:49 AM Masahiko Sawada
> <sawada.mshk@gmail.com> wrote:
> > >
> > >
> > > I think that such a time-based configuration parameter would be a
> > > reasonable solution. The current concerns are that it might affect
> > > vacuum performance and lead to a similar bug we had with
> > vacuum_defer_cleanup_age.
> >
> > Thanks for the feedback!
> >
> > I am working on the POC patch and doing some initial performance tests
> > on this idea.
> > I will share the results after finishing.

Here is a POC patch for vacuum_committs_age idea. The patch adds a GUC
vacuum_committs_age to prevent dead rows from being removed if the age of the
delete transaction (xmax) has not exceeded the vacuum_committs_age threshold.
E.g. , it ensures the row is retained if now() - commit_timestamp_of_xmax <
vacuum_committs_age.

However, please note that the patch is still unfinished due to a few
issues that need to be addressed. For instance: We need to prevent
relfrozenxid/datfrozenxid from being advanced in both aggressive and
non-aggressive vacuum modes. Otherwise, the commit timestamp data is cleaned
up after advancing frozenxid, and we won’t be able to compute the age of a tuple.

Additionally, the patch has a noticeable performance impact on vacuum
operations when rows in a table are deleted by multiple transactions. Here are
the results of VACUUMing a table after deleting each row in a separate
transaction (total of 10000000 dead rows) and the xmax ages of all the dead
tuples have exceeded the vacuum_committs_age in patched tests (see attachment
for the basic configuration of the tests):

    HEAD:			Time: 848.637 ms
    patched, SLRU 8MB:	Time: 1423.915 ms
    patched, SLRU 1G:		Time: 1310.869 ms

Since we have discussed about an alternative approach that can reliably retain
dead tuples without modifying vacuum process. We plan to shift our focus to
this new approach [1]. I am currently working on another POC patch based on this
new approach and will share it later.

[1] https://www.postgresql.org/message-id/CAD21AoD%3Dm-YHceYMpsdu0HnGCaezeyVhaCPFxDLHU7aN0wgzqg%40mail.gmail.com

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.