> route everything to replicas, catch exceptions on writes, and replay to the writer),
I am curious about the specifics here. Do you do it at the application level or proxy level?
Does doing it at the app level require that all the requests can be routed to any DB instance regardless of whether they are master or slave. A write to a mster is passed through. A read to a master is routed to slaves LB. A read on slave is passed through. A write on replica is routed to the master.
There's a bunch of different ways to do this, and upthread I just gave a sketch. I can tell you what we do at Fly.io --- or rather, what we suggest our users do:
* We have an HTTP header you can set in a reply that says "replay this request elsewhere".
* We boot up clusters of Postgres servers in read-replica configurations --- a single writer, lots of readers.
* If you try to write to a read replica, you get an error from Postgres, and your framework passes that error up to you.
* We suggest our users catch the exception and set the "replay elsewhere" header to redirect the request to the write master.
And that's pretty much it. Most apps are read-heavy. Reads get serviced from replicas (and, usually, close to their users --- that's the point of the service we built) and writes go to the single write master. You don't write any serious code to make that work.
But if apps could reliably say "a POST isn't just a complicated GET, but is almost certainly changing mutable serverside state", this would be an even easier problem; you'd just have the CDN route POSTs to the write master region, and GETs to the nearest region, and you'd be done.
You can architect your apps like this today, I guess, using QUERY or abusing PUT or something. My point is just that there's significant value in being able to shift all the read-only operations out of POST.
GETs are idempotent and pure, at least in theory. Any replica can serve them, give an identical result, and change no data.
POST, on the other hand, is expected to mutate data. It needs to go to the master node, unless you have bidirectional replication, which is way more complex.
This is why a load balancer / proxy can spread GET requests and cache their results, but can't do so with POSTs.
QUERY is like POST in that it has a body, but is like GET in that it's idempotent, and should return the same result given the same URL and body. Load-balancing and caching work again.
> GETs are idempotent and pure, at least in theory.
No, gets are safe (do not induce any client-responsibility state changes) and idempotent (do not induce any additional client-responsibility change of state for a second identical request with no intervening action after the first), not pure (do not depend on any state outside of the request).
I am curious about the specifics here. Do you do it at the application level or proxy level?
Does doing it at the app level require that all the requests can be routed to any DB instance regardless of whether they are master or slave. A write to a mster is passed through. A read to a master is routed to slaves LB. A read on slave is passed through. A write on replica is routed to the master.
Does this look like a sound design?