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.