[HN Gopher] PostgreSQL 16 Bi-Directional Logical Replication
       ___________________________________________________________________
        
       PostgreSQL 16 Bi-Directional Logical Replication
        
       Author : alexzeitler
       Score  : 33 points
       Date   : 2023-12-18 19:58 UTC (3 hours ago)
        
 (HTM) web link (www.highgo.ca)
 (TXT) w3m dump (www.highgo.ca)
        
       | perlgeek wrote:
       | The description of preconditions seems a bit incomplete.
       | 
       | To me it seems you need to avoid certain constructs, like UNIQUE
       | constraints. Otherwise you might have a local insert plus a
       | replicated one, both the same value in the unique column, and
       | different nodes reject different inserts.
        
         | smilliken wrote:
         | Agreed. Also exclusion constraints (a generalization of unique
         | constraints). Also foreign key constraints, because one
         | database might delete a record simultaneously with another
         | database adding a reference to it.
        
           | hinkley wrote:
           | Deleting from a database is a sucker's bet. If you add to a
           | record and mark it dead there are fewer semantic problems to
           | deal with.
           | 
           | Partial indexes give you approximately the same speedup as
           | deletes.
        
       | egamirorrim wrote:
       | I don't know that this article tackled the real elephant in the
       | room for bi-di replication. What happens when the same insert
       | happens on both primaries at once, who wins?
        
         | singron wrote:
         | They will each apply the insert locally. Then they will attempt
         | to replicate that insert to each other. Each will attempt to
         | apply the replicated conflicting insert, which will cause an
         | error and halt replication for both nodes.
         | 
         | If you update the same data on both nodes, this is a recipe for
         | almost certain disaster. Postgres is not a distributed database
         | and this doesn't make it one.
        
           | cogman10 wrote:
           | Yeah, read the docs here [1] and really does not seem like
           | this would be a good system to work with. To make something
           | fault tolerant you have to have a side subscription running
           | watching for server errors so you can resolve these conflicts
           | when they arise.
           | 
           | pglogical gave you the option to determine the winner of a
           | conflicting write (which I think is preferable). The inbuilt
           | postgres stuff seems like it'd be rife with potential errors.
           | 
           | [1] https://www.postgresql.org/docs/16/logical-replication-
           | confl...
        
         | simonw wrote:
         | Right: Is the idea here that you use UUIDs for inserted rows,
         | hence avoiding duplicate inserts?
         | 
         | But what happens if multiple updates or deletes target the same
         | existing row?
        
         | grogers wrote:
         | In the MySQL world, bidirectional replication is common, but
         | with at-most-one of the two being writable (the replication
         | user can still write even if the secondary is read-only). Maybe
         | they meant it to be similar and not truly multi-master? It does
         | seem like a glaring omission given they talk about having two
         | primaries.
        
       ___________________________________________________________________
       (page generated 2023-12-18 23:00 UTC)