Subject: Logical Replication Conflict Resolution

From: Stavros Koureas <[email-masked]>
Date: Thu, 25 May 2023 12:10:06 +0300

Working with PostgreSQL Logical Replication is just great! It helps a lot
doing real time replication for analytical purposes without using any other
3d party service. Although all these years working as a product architect
of reporting I have noted a few requirements which are always a challenge
and may help enhance logical replication even better.

To the point:
PostgreSQL15 Logical Replication is going to stop while there is a conflict
on the destination database. On the other hand, PGLogical can handle the
conflicts with more options like error, apply_remote,
keep_local, last_update_wins, first_update_wins while streaming.

I was thinking that it would be great to add those capabilities into
Logical Replication during streaming and even better on snapshot if it is
possible. This enhancement method is going to solve a lot of issues while
there are hybrid solutions which are updating databases with SQL Scripts
and Logical Replication. At the same time will make Logical Replication
"more reliable" as it will not stop replicating lines in live
environments when the decision of conflict is already decided and
configured.

In my case I am consolidating data from multiple erp system databases to a
destination database for reporting purposes. All erps, have the same table
schema as the destination database, source database has the tenant_id
identifier in non primary keys but has replica identity index. Now there
are scenarios where we may need to update manually the destination database
using scripts which are having the ONCONFLICT statement, but during that
time if a new record is inserted into the database and the batch statement
finishes earlier than replication, the replication will find a conflict.