3 ms·

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 in

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 3y ago
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-conflicts.html https://www.postgresql.org/docs/16/logical-replication-confl...
- brianwawok 3y ago
  I assume you just go very basic. All updates to table X go to database Y. Don’t have all the clients slamming different databases with updates to the same stuff. Not ideal but would still help with some scale stuff.
  - philjohn 3y ago
    Or an analytics system - spray events at whatever server, because each row is a unique record separate from the others, you get no conflicts. Benefit - if your current system with a single primary for writes is suffering and your data and use-case fits this pattern, you can increase your write throughput without scaling up.
gregw2 3y ago
This. But if you use integer surrogate primary keys and seed one server with even ones and one server with odd ones, both incrementing by two, and put some uniqueness constraint on your natural key, you won't have this issue of insert collisions and halted replication, just normal SQL errors. IIRC (non postgres-based advice from an earlier life but should work.)

cogman10 3y ago

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-conflicts.html https://www.postgresql.org/docs/16/logical-replication-confl...

brianwawok 3y ago
I assume you just go very basic. All updates to table X go to database Y. Don’t have all the clients slamming different databases with updates to the same stuff. Not ideal but would still help with some scale stuff.
- philjohn 3y ago
  Or an analytics system - spray events at whatever server, because each row is a unique record separate from the others, you get no conflicts. Benefit - if your current system with a single primary for writes is suffering and your data and use-case fits this pattern, you can increase your write throughput without scaling up.

gregw2 3y ago

This. But if you use integer surrogate primary keys and seed one server with even ones and one server with odd ones, both incrementing by two, and put some uniqueness constraint on your natural key, you won't have this issue of insert collisions and halted replication, just normal SQL errors. IIRC (non postgres-based advice from an earlier life but should work.)