It didn't work cross region because no one builds an app expecting latency between their app server code and database. What we'd see on write requests was something like:
1. Query for data from read replica, perhaps for validation (0ms)
2. Do a write to the primary in a different region (20-400ms)
3. Query primary for consistency (20-400ms)
4. More queries against primary for consistency (20-400ms)
5. Maybe another write (20-400ms)
6. repeat
You can actually use our postgres this way if you want! But it breaks for most apps. It is much, much faster to ship the whole HTTP request where it needs to be than the move the database away from an app instance.
The problem is that most postgres clients can't handle multiple parametric queries in a single connection (necessary for them to be in a single transaction).
This came to light for me as I was trying to build an app on CockroachDB, and the cluster nature of that (at least for geo-redundant setups) implies a non-negligible latency in the low single digits.
Comments
We tried running something like pgpool's split write setup, where the app or a proxy in front of the DB knows where to send transactions: https://www.pgpool.net/docs/latest/en/html/runtime-config-lo...
It didn't work cross region because no one builds an app expecting latency between their app server code and database. What we'd see on write requests was something like:
1. Query for data from read replica, perhaps for validation (0ms)
2. Do a write to the primary in a different region (20-400ms)
3. Query primary for consistency (20-400ms)
4. More queries against primary for consistency (20-400ms)
5. Maybe another write (20-400ms)
6. repeat
You can actually use our postgres this way if you want! But it breaks for most apps. It is much, much faster to ship the whole HTTP request where it needs to be than the move the database away from an app instance.
The problem is that most postgres clients can't handle multiple parametric queries in a single connection (necessary for them to be in a single transaction).
This came to light for me as I was trying to build an app on CockroachDB, and the cluster nature of that (at least for geo-redundant setups) implies a non-negligible latency in the low single digits.