Because BDR is asynchronous multi-master _replication_ though, clients
are expected to be aware of some of the anomalies that can occur. A
naïve client that just picked a random BDR server and did the next
transaction on it would be very likely to cause unwanted replication
anomalies, apply conflicts, etc.
For example, if the client inserted a row on one server then tried to
immediately update it on another, the update would likely fail because
the row probably hasn't replicated yet.
Any ideas giving BDR an option to be synchronous. I mean in ”close quarters” with low latency it should work ok.
Just to clarify, I don't mean a 48 node synchronous group, but perhaps allow one synchronous ”neighbor” node.
That way we could get two synch writeable nodes in one datacenter(or 2 datacenter really close to each other. common in Europe), and can be combined with asynch nodes in more distant datacenters. That would enable load balancing between 2 nodes for all type of queries.
Synchrounous Streaming Replication is supported with BDR, but that only gives support for a read-only replica. It does not give a fully writeable node.
Figuring out what queries are readonly to direct to a readonly replica, is not the easiest thing, if I’ve understood documentation for pgpool etc correct.
It is generally a good idea to make clients "sticky" to a given server,
but clients also need to be aware of replication anomalies unless each
server's data is a self-contained shard that doesn't interact with the
others. In which case you probably wouldn't be using BDR
