3 ms·

> 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. Does that mean that the app

by frafra 5y ago

> 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. Does that mean that the app server would just fail, and then your network replays the same HTTP request (made by the client to the edge), toward an instance running the app server inside the region where there the Postgres primary instance is running? If so, you should then be able to figure out which client request provoked the SQL error and the app server would report some errors, right? Otherwise, the app server should be able to tell you if it failed because the Postgres server does not support write statements, which would require some minor adjustment in the app server code, isn't it? I am sorry if I missed something from your description, but I am trying to figure out what the flow would look like. Thank you!

viraptor 5y ago
> which would require some minor adjustment in the app server code, isn't it? Yes, that's what the code fragment does: > if e.cause.is_a?(PG::ReadOnlySqlTransaction)
- frafra 5y ago
  Ok, so that's an example of how the app server code should look like, no middleware involved. Thank you!
  - mrkurt 5y ago
    We should have definitely linked to these docs, I appreciate you asking and it sounds like you all managed to figure everything out though: https://fly.io/docs/getting-started/multi-region-databases/ https://fly.io/docs/getting-started/multi-region-databases/
VWWHFSfQ 5y ago
> It is much, much faster to ship the whole HTTP request where it needs to be If you're already routing at the HTTP level then why not just route the request based on the HTTP method? (assuming the handlers for those are properly implemented.) Why not just route a POST request to a backend connected to a writable database? And GET to a read-only database? I don't understand how it's any kind of an optimization to be continually bouncing queries out of the read-only replicas because Postgres threw an error.
- frafra 5y ago
  I agree with you, but I just was trying to understand the fly.io post. They also wrote: "Most GETs are reads, but not all of them. The platform has to work for all the requests, not just the orthodox ones." So I think it all depends on which level of the application you take the decision to gracefully fail and replay the request elsewhere. In some case, it is trivial: as soon as you see a POST/UPDATE/DELETE, because you trust your app; sometimes you may need to take the decision later, or at a lower level. In the simplest scenario, fly.io could just forward the request to the right region, without even bothering the app server to reply with an error, but that would work only if GET requires no writes.
  - mrkurt 5y ago
    You got it. We can give people a library that catches Postgres readonly errors and make it a reasonably standard experience. We can't ensure that peoples' apps have good write hygiene. We _can_, though, educate people and tell them what to look for when they're trying to optimize performance. There's also the graphql problem (and really any kind of non-rest RPC). It's somewhat rare that applications use HTTP verbs appropriately, APIs tend to bypass HTTP methods.
- tptacek 5y ago
  You obviously can't just do that, because ordinary applications are full of GET requests that cause database updates.
  - rattray 5y ago
    You can't _only_ do that, but it seems like a reasonable place to start for REST API's. I don't think there's anything to change in your product, just that the docs should recommend ways to reroute as early as possible in a request lifespan (eg, at the routing layer or before). I'd worry about a POST route that does some expensive/slow reads/computations in the first half of the request, and then only writes at the end – lots of lost time! Would have been much better to say, hey, for this route (or for any POST route in _my_ application), please bounce to the region with the primary db instance. Actually, while that could be done at the application level with reasonable latency, it'd probably still be better to allow the user to write some rules at the proxy layer for the "first guess".

by frafra 5y ago

viraptor 5y ago

> which would require some minor adjustment in the app server code, isn't it? Yes, that's what the code fragment does: > if e.cause.is_a?(PG::ReadOnlySqlTransaction)

frafra 5y ago
Ok, so that's an example of how the app server code should look like, no middleware involved. Thank you!
- mrkurt 5y ago
  We should have definitely linked to these docs, I appreciate you asking and it sounds like you all managed to figure everything out though: https://fly.io/docs/getting-started/multi-region-databases/ https://fly.io/docs/getting-started/multi-region-databases/

VWWHFSfQ 5y ago

> It is much, much faster to ship the whole HTTP request where it needs to be If you're already routing at the HTTP level then why not just route the request based on the HTTP method? (assuming the handlers for those are properly implemented.) Why not just route a POST request to a backend connected to a writable database? And GET to a read-only database? I don't understand how it's any kind of an optimization to be continually bouncing queries out of the read-only replicas because Postgres threw an error.

frafra 5y ago
I agree with you, but I just was trying to understand the fly.io post. They also wrote: "Most GETs are reads, but not all of them. The platform has to work for all the requests, not just the orthodox ones." So I think it all depends on which level of the application you take the decision to gracefully fail and replay the request elsewhere. In some case, it is trivial: as soon as you see a POST/UPDATE/DELETE, because you trust your app; sometimes you may need to take the decision later, or at a lower level. In the simplest scenario, fly.io could just forward the request to the right region, without even bothering the app server to reply with an error, but that would work only if GET requires no writes.
- mrkurt 5y ago
  You got it. We can give people a library that catches Postgres readonly errors and make it a reasonably standard experience. We can't ensure that peoples' apps have good write hygiene. We _can_, though, educate people and tell them what to look for when they're trying to optimize performance. There's also the graphql problem (and really any kind of non-rest RPC). It's somewhat rare that applications use HTTP verbs appropriately, APIs tend to bypass HTTP methods.
tptacek 5y ago
You obviously can't just do that, because ordinary applications are full of GET requests that cause database updates.
- rattray 5y ago
  You can't _only_ do that, but it seems like a reasonable place to start for REST API's. I don't think there's anything to change in your product, just that the docs should recommend ways to reroute as early as possible in a request lifespan (eg, at the routing layer or before). I'd worry about a POST route that does some expensive/slow reads/computations in the first half of the request, and then only writes at the end – lots of lost time! Would have been much better to say, hey, for this route (or for any POST route in _my_ application), please bounce to the region with the primary db instance. Actually, while that could be done at the application level with reasonable latency, it'd probably still be better to allow the user to write some rules at the proxy layer for the "first guess".