RE: Conflict detection for update_deleted in logical replication

On Friday, September 20, 2024 11:59 AM Hou, Zhijie/侯 志杰 wrote:
>
> On Friday, September 20, 2024 10:55 AM Zhijie Hou (Fujitsu)
> <houzj(dot)fnst(at)fujitsu(dot)com> wrote:
> > On Friday, September 20, 2024 2:49 AM Masahiko Sawada
> <sawada(dot)mshk(at)gmail(dot)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