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?
* 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.
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.
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).
If these are POST, you find yourself trying to conditionally cache POST requests, which opens up a whole new class of caching bugs.
This is a lot of application logic to add just to avoid adding a QUERY method. It's just too much.
1. pre-existing behavior. maybe queries have only started getting big enough to where this mattered, but all of our existing clients communicate the original way and won't migrate
2. more complex behavior on clients and services as you've now introduced a new stateful resource between client and service. should clients store query ids, or does the service handle idempotency? what db does the service use to store queries? how do we limit stored queries? do clients have to manage number of queries stored? does the service?
pretty much the same pro's and con's too
safer, more controlled, better optimized but it's more limiting, another dev dependency and a responsibility bottleneck
So an HTTP framework would not retry, moving retry logic into the application.
If you can only use POST, then you also can't cache the result because POST is not safe: you've no idea if the same POST is intended to produce the same response.
Can cache in your JS code. But for browsers:
Do browsers respect cache headers for GET requests to say an API endpoint? maybe with an etag?
I did write some tracking code that ended up having really long strings and a ton of parameters and wrote it to break into multiple requests at like 1800.