[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)