3 ms·
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
by simonw 3y ago
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?
- deleted 3y ago[deleted]
- crest 3y agoMaybe a column with a server id could work? Can you have a schema differing only in the default value for a server id column or would it be replicated as "insert default value"?
- gregw2 3y agoNo need... here's a different twist on that .. make the integer primary key generation setup such that one server has odd IDs and the other even and they each increment by two. I did this for many years on Sybase and on SQL Server for active-active failover.
- derefr 3y agoI would assume the intended use-case is a "pseudo-sharded" cluster. In an actually-sharded cluster, some scheme (e.g. a hash ring) directs writes to particular shards, and so any given row only lives on one shard. In a pseudo-sharded cluster, you still direct writes to different "shards" — but all the pseudo-shards also mirror each-other's data, so every pseudo-shard actually has a complete dataset. But each row is still only owned by one particular shard; when updating that row, you must update it through that shard. All the data not owned by the shard, should be treated as a read-only/immutable cache on that shard of the other shards' data. Personally, though, if I were setting something like this up, rather than bi-di logical replication on a single table, I'd just partition the table with one partition per shard, and then have the "owning" shard be the publisher for that partition and the rest of the shards be subscribers for that partition. Same effect, much less implementation complexity and much less risk.
- simonw 3y agoThat sounds like a smart model for thinking about this, thanks.