12 ms·

Globally Distributed Postgres

329 points by woodrow 5y ago

picardo 5y ago
I tried this a few months ago. Many features were missing then. I couldn't set up backups, and connect directly to `psql`. It was such a pain. I don't know if they've addressed those issues yet. It'd be a hard sell for me to store a database system for a production app in a Docker image.
- mrkurt 5y ago
  It's better, but still beta. We handle backups and you can connect directly to psql. We also added migration support as part of app releases. The global distribution feature, though, works with any database, not just our postgres. Between you and me, we tried really hard not to build a Postgres service. We're committed now, it's gonna be good. For future DBs we plan to get big enough that people like Timescale to ship their products on Fly infrastructure.
  - jfyne 5y ago
    This might be in the docs somewhere, but can you bring your own postgres image to your managed service?
    - deleted 5y ago
      [deleted]
    - mrkurt 5y ago
      Our postgres clusters are just a Fly app: https://github.com/fly-apps/postgres-ha https://github.com/fly-apps/postgres-ha You could run your own PG by modifying that app. Right now we're calling it "automated" and not managed, though. All alerts about health and other issues go straight to customers, we don't have DBAs that will touch these things yet.
      - jfyne 5y ago
        Ah ok, interesting. Thanks
- tptacek 5y ago
  Just a note about directly managing Postgres here: we launched Postgres like, a minute ago (a couple months in normal people time). For a pretty long time, you couldn't do any real persistent storage on a Fly instance; we were still mostly targeting edge applications, where people slice components of their apps off to run closer to the edge. I think it's safe to say that while we still love edge apps and are committed to them long term, we've switched gears towards full-stack applications (that is, we've gotten a lot more ambitious about what we want to be a good platform for). So this stuff is shaping up pretty quickly. Postgres (and volume support) happened at about the same time as private networking did (Postgres was our first internal application for private networks). In January, we started doing user-mode WireGuard, so that `flyctl` runs its own TCP/IP stack and speaks WireGuard directly, without involving the OS (so you don't need root to use it). User-mode WireGuard gave us `flyctl ssh console`, and is also the way we do direct `psql`. There's more stuff in that vein coming; hopefully, it's gotten a lot easier to manage Postgres as `flyctl` has gotten more capable. Where we want to be is for it to be trivial to run something like Postico.app to talk to a Fly Postgres database. You can do that now, but it's not yet _totally_ trivial. Hopefully you can sort of look at `flyctl` (it's open source) and see how that'll work.
simonw 5y ago
The way this is implemented, with the ability for an application server attached to a replica to say "error: this needs to perform a write - hey CDN, replay this request against the region with the database leader in it" is SO clever.
- zozbot234 5y ago
  There's probably a way to do this in native postgres by abusing the FDW facility. No real need to involve the CDN or replay the request.
  - mrkurt 5y ago
    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-load-balancing.html 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.
    - namibj 5y ago
      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.
- rad_gruchalski 5y ago
  And probably adds quite a lot of latency under load.
monocasa 5y ago
I've been kicking around an idea like this for a while. The train of thought that brought me there was the recognition of a distinct memory hierarchy in today's common distributed applications that parallels the one in your computer. So your computer has memory banks that (as a rough first approximation) each get ~10x bigger, but with ~10x greater latency. The neat part though is that global consistency for writes happens with L1 and L2 working together, so pretty damn high up in the hierarchy. In contrast distributed applications typically at best will have a write through cache where global consistency happens all the day down at the actual data store. Exploring the idea of distributed MOESI where one client, because they had the permissions to write something in the first place, can then be the owner for that database row as it's still being flushed out seems like a great basis for a distributed system that might not even need the dedicated datastore at all anymore, but a sea of clients participating in coherency and replication. Albeit this has oodles of consistency and availability problems that very well might kill the whole concept, like how MOESI would absolutely fall over when trying to hotplug CPUs at arbitrary times.
- valzam 5y ago
  Maybe not directly related but this reminds of a paper[1] by Microsoft Research where they replicate state with DMA (Direct Memory Access) across the network and battery backups that have just enough power to flush memory to disk if the power goes out. you might find it interesting [1] https://pdos.csail.mit.edu/6.824/papers/farm-2015.pdf https://pdos.csail.mit.edu/6.824/papers/farm-2015.pdf
  - monocasa 5y ago
    That's a fantastic paper, thanks for sharing.
- natmaka 5y ago
  Some HSM ( https://en.wikipedia.org/wiki/Hierarchical_storage_management https://en.wikipedia.org/wiki/Hierarchical_storage_managemen... ) tools and know-how may be pertinent.
  - 5y ago
mkl95 5y ago
It reminds me of Yugabyte, which is Postgres compatible > YugabyteDB is the open source, distributed SQL database for global, internet- scale applications with low query latency, extreme resilience against failures. * However Fly is nicer from a developer's point of view, because it doesn't require you to learn a new query language, you can write good old Postgres. * https://www.yugabyte.com/ https://www.yugabyte.com/
- qaq 5y ago
  What new query language do you need to learn for yugabyte ?
  - mkl95 5y ago
    YSQL. From their docs: > Successive YugabyteDB releases honor PostgreSQL syntax and semantics, although some features (for example those that are specific to the PostgreSQL monolithic SQL database architecture) might not be supported for distributed SQL. The YSQL documentation specifies the supported syntax and extensions. It is psql compatible, but you may run into missing features, so you will have to find a way around that. The documentation says that their latest release is based on psql 11.2, and psql 14 will be released soon.
- rad_gruchalski 5y ago
  Yugabyte with replica placement and tablespaces does this for free without having to do any try / catch.
  - tptacek 5y ago
    All the next-gen distributed SQL databases are neat. We are definitely not trying to compete with them; that's not at all where we're heading (though we'd be happy to work with them to bring their databases to our platform). This is just a neat hack you can do with totally standard Postgres. It's a thing other people do outside of Fly; we just like how easy you can make it if you can program both the CDN and the applications.
  - 5y ago
Thaxll 5y ago
Running a master in us-west and having read replica in europe, what could go wrong ... My advice is don't do those things in the blog and keep the DB and the app in the same region it will save you so many problems. If you can't just use a DB that was designed for that like Spanner or Cockroach.
- tptacek 5y ago
  All the things that can go wrong with globally distributed databases. You can just run an HA cluster in a single region if you're looking to keep it simple, just the same way you can run a single-region app on Fly if you like. We try to go out of our way to say that this stuff isn't a perfect fit for every application or a "best" way to do things. I think if a deployment like this is attractive for your application, you probably know it. I'm certainly not going to evangelize against people scaling up single-region databases!
- simonw 5y ago
  Genuine question: what could go wrong? I'm a big fan of "pinning" mechanisms where any time a user performs a write to your database you set a cookie that lasts for a few seconds and causes any subsequent requests from that user to go to the lesser, not a replica. This solves the stale-read-after-write problem and ensures users will see any changes that they have personally made. This does assume a mostly-reads, occasional-write application (which defines most of the applications I've worked on in my career). So given that kind of application, what are the problems I'm not considering that I should look out for with a read-replica on a different continent?
  - dkhenry 5y ago
    Of the many things that can go wrong, the one I would be most worried about would be replication lag causing the disk on the primary to fill with WAL logs bringing down the primary, or if you have limited the WAL logs, the replica not being able to keep up and detaching from the primary causing stale reads. Also long distances will make replication latency increase so unless _all_ requests are sent to the primary region you will likely have a read after write state data situation unless you go out of your way to ensure consistency
chatmasta 5y ago
Love this. We’re building a similar architecture at Splitgraph [0] which we refer to as a “Data Delivery Network.” The basic idea is we implement a proxy that forwards queries to backend data (either live data sources via an FDW, or versioned snapshots via our Docker-inspired “layered querying”). Soon anyone will be able to connect data sources by adding their read only credentials in the web (already possible via CLI but undocumented). The idea is to make exposing a database to the DDN as simple as exposing a website to a CDN. We’ve designed all this to be multi-tenant and horizontally scalable, but we’re not actually running it on a distributed network yet. Personally, I’ve followed Fly for a long time and always loved the angle you’re taking. If any of you at Fly read this and want to potentially collaborate on a solution in this space, my email is in my profile. [0] https://www.splitgraph.com https://www.splitgraph.com
- rozap 5y ago
  I work at Socrata, and I've skimmed splitgraph and it looks pretty neat. How do you all deal with pushdowns or query rewriting in general when the target datasource doesn't implement the functionality your user writing real sql is trying to use? Does it explode or take forever, because it doesn't look like you're storing copies of everything in Real Postgres? We expose a pretty limited subset of sql through our APIs, and an even more limited form to public APIs, so I'm interested in how you're doing things providing joins to your users. "It exists but is super slow" is a reasonable answer as well :)
  - chatmasta 5y ago
    Hey! Socrata is awesome :) The basic idea here is that you can write a Splitgraph plugin for any type of data source [0], so we like to use Socrata as a convenient example. Each plugin includes a Postgres Foreign Data Wrapper (FDW) to translate from SQL to whatever upstream query language (e.g. Socrata FDW [1]). By default we don't copy any data into Splitgraph (although we do cache query results keyed by AST). When a query arrives, we parse it for any table identifiers, and "mount" a temporary foreign table for each, using the appropriate FDW. Foreign tables are mostly transparent to the Postgres query planner, so a JOIN will work (example [2]), and an FDW can even optionally implement aggregate pushdown. But yes, especially in the case of cross-dataset JOINS, there are some pathological cases. When your use case outgrows live querying (e.g. due to pathological JOIN queries), you can ingest any data source into a Splitgraph image, a versioned snapshot of data similar to a Docker image. [3] Our focus right now is on the internal enterprise use case. We use the Socrata plugin as a demo, but our customers are more interested in plugins for data sources they use internally, e.g. Postgres, Snowflake, Google Search Console, CSV in S3, etc. We support all those too; you can mount them all locally, and you can also mount them on Splitgraph.com (or an internal deployment), but still thin docs / no web UI yet. [0] https://www.splitgraph.com/blog/foreign-data-wrappers https://www.splitgraph.com/blog/foreign-data-wrappers [1] https://github.com/splitgraph/splitgraph/blob/master/splitgraph/ingestion/socrata/fdw.py https://github.com/splitgraph/splitgraph/blob/master/splitgr... [2] https://www.splitgraph.com/workspace/ddn?layout=hsplit&query=SELECT%0A++++cambridge_cases.date+AS+date%2C%0A++++chicago_cases.cases_total+AS+chicago_daily_cases%2C%0A++++cambridge_cases.new_positive_cases+AS+cambridge_daily_cases%0AFROM%0A++++%22cityofchicago%2Fcovid19-daily-cases-deaths-and-hospitalizations-naz8-j4nc%22.covid19_daily_cases_deaths_and_hospitalizations+chicago_cases%0AFULL+OUTER+JOIN%0A++++%22cambridgema-gov%2Fcovid19-case-count-by-date-axxk-jvk8%22.covid19_case_count_by_date+cambridge_cases%0AON%0A++++date_trunc%28%27day%27%2C+chicago_cases.lab_report_date%3A%3Atimestamp%29+%3D+cambridge_cases.date%3A%3Atimestamp%0AWHERE+date+IS+NOT+NULL%0AORDER+BY+date+DESC%3B%0A%0A https://www.splitgraph.com/workspace/ddn?layout=hsplit&query... [3] https://www.splitgraph.com/docs/concepts/images https://www.splitgraph.com/docs/concepts/images
zomglings 5y ago
This is a nice solution, although I prefer for my application to be aware of read vs. read-write connections. Question about fly.io - how strict is the docker image requirement? How easy would it be to deploy a go application + systemd service?
- tptacek 5y ago
  We'll accept any container that a Docker registry will; you don't have to use Docker to build your container instance (`flyctl launch` will build an application from a buildpack). You can run systemd in a container. We don't use Docker to run containers; in fact, we don't run "containers" at all. Instead, we transmogrify OCI containers into root filesystems for Firecracker micro-vms. Your "container" runs as a virtual machine with its own Linux kernel. You can do whatever you'd like with it. https://fly.io/blog/docker-without-docker/ https://fly.io/blog/docker-without-docker/
- CameronNemo 5y ago
  Is there a reason you need systemd? You can build a pretty minimal image with just the go binary in it (assuming you have statically linked it using musl).
  - zomglings 5y ago
    At Bugout, we don't use docker. We deploy our code directly to machines over SSH and use systemd to coordinate deployed services, run timers, etc. Building the go binary is not a problem, but determining how the go binary runs, what it needs in order to be functional, etc. is where systemd comes into our setup.
holistio 5y ago
I first read the title as "Globally Distributed Progress" and I was like "if an article with this title made it to the front page of HN it must sure be some interesting read. I am now slightly disappointed.
- mrkurt 5y ago
  It is pretty boring. But "the most boring possible way to build globally distributed postgres" is interesting, I think.
- unnouinceput 5y ago
  Me too, having worked with PGSQL in past 15 years, I was expecting...something else. But what bothers me the most is their 2 times quote: "HTTP is cheap". No, it's not. Try having local local servers in remote locations, like ski resorts or country side fair, where internet connectivity is sketchy at best, and then you'll realize how cheap a request is.
  - mrkurt 5y ago
    HTTP is cheap! Latency is expensive, but all our HTTP shenanigans happen between our proxy and user VMs. We're not inflicting multiple requests on slow clients at a ski resort.
mixmastamyk 5y ago
I know this is hacker news and all and you’re trying to be fun, but as I’m looking at platforms like fly for a future saas, the first sentence (cool hack) rubbed me the wrong way. Would rather hear “we found an elegant design” and are working hard to make it bulletproof… etc. Databases are like that, the place to get serious. Sure I won’t let it deter me from a proper eval, but I could definitely see it scaring away suits.
- deleted 5y ago
  [deleted]
- mrkurt 5y ago
  We are at the stage where attracting developers and scaring suits is probably good. I'd have a hard time calling something I was partially responsible for elegant, though, so this might just be our self deprecating nature rearing its head.
  - treis 5y ago
    >We are at the stage where attracting developers and scaring suits is probably good. As a developer the way it was written scared me off. It reads like one of those recipe sites that game Google by adding a nonsensical story to the recipe. It seems like it took half the article to get to your "one trick". And ultimately you didn't deliver what the title teased.
    - mrkurt 5y ago
      I definitely like writing the story parts, and the actual replay header is about halfway down. So you're right about that. But it really does result in globally distributed postgres.
    - mixmastamyk 5y ago
      Yes, as a dev with important data is was kinda "nails on the chalkboard." That said I'm all up for more fun. Just not with the biz critical data.
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
Also relevant: https://fly.io/docs/getting-started/multi-region-databases/ https://fly.io/docs/getting-started/multi-region-databases/
itsjloh 5y ago
How are POST requests replayed into different regions? Does your edge proxy hold the whole POST requests (body and all?) just in case it needs to be replayed?
- mrkurt 5y ago
  It does, and we need to update our docs with this. We buffer up to 1mb of request data to be possibly replayed. We can set that arbitrarily high, it just seemed like a reasonable starting point.
  - malloryerik 5y ago
    Fly.io and the Postgres service look great. Do you have a roadmap for future regions? (I'd personally love to see Korea.) If I may ask, do you use major cloud services or run your own machines, and how easy or hard is it for Fly.io to add new regions?
    - mrkurt 5y ago
      We don't have a firm roadmap for future regions, but Seoul is high up on my wish list. Our infrastructure is all dedicated hosts, mostly running in Equinix facilities (we buy a lot of servers from what used to be packet.com). New regions are hard in one particular way. Since we accept anycast traffic, ever new region has to be anycast-able for us. We outsource anycast management so we're kind of stuck waiting on other providers to opt in to a region. When we get to a scale where we can manage our own anycast and decide we want to, we will be able to ship new regions relatively quickly. Some places are harder to get servers to than others, but we don't actually need a very large footprint to get started.
  - itsjloh 5y ago
    Cool, makes sense. Thanks for taking the time to reply! Really big fan of fly, run a few apps on it now and the experience has been fantastic.
AtlasBarfed 5y ago
Oh look a distributed database with nary a mention of CAP. And they are allowing multi-master writes? What is the update collision resolution? cell timestamps? vector clocks?

329 points by woodrow 5y ago

I tried this a few months ago. Many features were missing then. I couldn't set up backups, and connect directly to `psql`. It was such a pain. I don't know if they've addressed those issues yet. It'd be a hard sell for me to store a database system for a production app in a Docker image.

mrkurt 5y ago
It's better, but still beta. We handle backups and you can connect directly to psql. We also added migration support as part of app releases. The global distribution feature, though, works with any database, not just our postgres. Between you and me, we tried really hard not to build a Postgres service. We're committed now, it's gonna be good. For future DBs we plan to get big enough that people like Timescale to ship their products on Fly infrastructure.
- jfyne 5y ago
  This might be in the docs somewhere, but can you bring your own postgres image to your managed service?
  - deleted 5y ago
    [deleted]
  - mrkurt 5y ago
    Our postgres clusters are just a Fly app: https://github.com/fly-apps/postgres-ha https://github.com/fly-apps/postgres-ha You could run your own PG by modifying that app. Right now we're calling it "automated" and not managed, though. All alerts about health and other issues go straight to customers, we don't have DBAs that will touch these things yet.
    - jfyne 5y ago
      Ah ok, interesting. Thanks
tptacek 5y ago
Just a note about directly managing Postgres here: we launched Postgres like, a minute ago (a couple months in normal people time). For a pretty long time, you couldn't do any real persistent storage on a Fly instance; we were still mostly targeting edge applications, where people slice components of their apps off to run closer to the edge. I think it's safe to say that while we still love edge apps and are committed to them long term, we've switched gears towards full-stack applications (that is, we've gotten a lot more ambitious about what we want to be a good platform for). So this stuff is shaping up pretty quickly. Postgres (and volume support) happened at about the same time as private networking did (Postgres was our first internal application for private networks). In January, we started doing user-mode WireGuard, so that `flyctl` runs its own TCP/IP stack and speaks WireGuard directly, without involving the OS (so you don't need root to use it). User-mode WireGuard gave us `flyctl ssh console`, and is also the way we do direct `psql`. There's more stuff in that vein coming; hopefully, it's gotten a lot easier to manage Postgres as `flyctl` has gotten more capable. Where we want to be is for it to be trivial to run something like Postico.app to talk to a Fly Postgres database. You can do that now, but it's not yet _totally_ trivial. Hopefully you can sort of look at `flyctl` (it's open source) and see how that'll work.

simonw 5y ago

The way this is implemented, with the ability for an application server attached to a replica to say "error: this needs to perform a write - hey CDN, replay this request against the region with the database leader in it" is SO clever.

zozbot234 5y ago
There's probably a way to do this in native postgres by abusing the FDW facility. No real need to involve the CDN or replay the request.
- mrkurt 5y ago
  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-load-balancing.html 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.
  - namibj 5y ago
    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.
rad_gruchalski 5y ago
And probably adds quite a lot of latency under load.

monocasa 5y ago

I've been kicking around an idea like this for a while. The train of thought that brought me there was the recognition of a distinct memory hierarchy in today's common distributed applications that parallels the one in your computer. So your computer has memory banks that (as a rough first approximation) each get ~10x bigger, but with ~10x greater latency. The neat part though is that global consistency for writes happens with L1 and L2 working together, so pretty damn high up in the hierarchy. In contrast distributed applications typically at best will have a write through cache where global consistency happens all the day down at the actual data store. Exploring the idea of distributed MOESI where one client, because they had the permissions to write something in the first place, can then be the owner for that database row as it's still being flushed out seems like a great basis for a distributed system that might not even need the dedicated datastore at all anymore, but a sea of clients participating in coherency and replication. Albeit this has oodles of consistency and availability problems that very well might kill the whole concept, like how MOESI would absolutely fall over when trying to hotplug CPUs at arbitrary times.

valzam 5y ago
Maybe not directly related but this reminds of a paper[1] by Microsoft Research where they replicate state with DMA (Direct Memory Access) across the network and battery backups that have just enough power to flush memory to disk if the power goes out. you might find it interesting [1] https://pdos.csail.mit.edu/6.824/papers/farm-2015.pdf https://pdos.csail.mit.edu/6.824/papers/farm-2015.pdf
- monocasa 5y ago
  That's a fantastic paper, thanks for sharing.
natmaka 5y ago
Some HSM ( https://en.wikipedia.org/wiki/Hierarchical_storage_management https://en.wikipedia.org/wiki/Hierarchical_storage_managemen... ) tools and know-how may be pertinent.
- 5y ago

mkl95 5y ago

It reminds me of Yugabyte, which is Postgres compatible > YugabyteDB is the open source, distributed SQL database for global, internet- scale applications with low query latency, extreme resilience against failures. * However Fly is nicer from a developer's point of view, because it doesn't require you to learn a new query language, you can write good old Postgres. * https://www.yugabyte.com/ https://www.yugabyte.com/

qaq 5y ago
What new query language do you need to learn for yugabyte ?
- mkl95 5y ago
  YSQL. From their docs: > Successive YugabyteDB releases honor PostgreSQL syntax and semantics, although some features (for example those that are specific to the PostgreSQL monolithic SQL database architecture) might not be supported for distributed SQL. The YSQL documentation specifies the supported syntax and extensions. It is psql compatible, but you may run into missing features, so you will have to find a way around that. The documentation says that their latest release is based on psql 11.2, and psql 14 will be released soon.
rad_gruchalski 5y ago
Yugabyte with replica placement and tablespaces does this for free without having to do any try / catch.
- tptacek 5y ago
  All the next-gen distributed SQL databases are neat. We are definitely not trying to compete with them; that's not at all where we're heading (though we'd be happy to work with them to bring their databases to our platform). This is just a neat hack you can do with totally standard Postgres. It's a thing other people do outside of Fly; we just like how easy you can make it if you can program both the CDN and the applications.
- 5y ago

Thaxll 5y ago

Running a master in us-west and having read replica in europe, what could go wrong ... My advice is don't do those things in the blog and keep the DB and the app in the same region it will save you so many problems. If you can't just use a DB that was designed for that like Spanner or Cockroach.

tptacek 5y ago
All the things that can go wrong with globally distributed databases. You can just run an HA cluster in a single region if you're looking to keep it simple, just the same way you can run a single-region app on Fly if you like. We try to go out of our way to say that this stuff isn't a perfect fit for every application or a "best" way to do things. I think if a deployment like this is attractive for your application, you probably know it. I'm certainly not going to evangelize against people scaling up single-region databases!
simonw 5y ago
Genuine question: what could go wrong? I'm a big fan of "pinning" mechanisms where any time a user performs a write to your database you set a cookie that lasts for a few seconds and causes any subsequent requests from that user to go to the lesser, not a replica. This solves the stale-read-after-write problem and ensures users will see any changes that they have personally made. This does assume a mostly-reads, occasional-write application (which defines most of the applications I've worked on in my career). So given that kind of application, what are the problems I'm not considering that I should look out for with a read-replica on a different continent?
- dkhenry 5y ago
  Of the many things that can go wrong, the one I would be most worried about would be replication lag causing the disk on the primary to fill with WAL logs bringing down the primary, or if you have limited the WAL logs, the replica not being able to keep up and detaching from the primary causing stale reads. Also long distances will make replication latency increase so unless _all_ requests are sent to the primary region you will likely have a read after write state data situation unless you go out of your way to ensure consistency

chatmasta 5y ago

Love this. We’re building a similar architecture at Splitgraph [0] which we refer to as a “Data Delivery Network.” The basic idea is we implement a proxy that forwards queries to backend data (either live data sources via an FDW, or versioned snapshots via our Docker-inspired “layered querying”). Soon anyone will be able to connect data sources by adding their read only credentials in the web (already possible via CLI but undocumented). The idea is to make exposing a database to the DDN as simple as exposing a website to a CDN. We’ve designed all this to be multi-tenant and horizontally scalable, but we’re not actually running it on a distributed network yet. Personally, I’ve followed Fly for a long time and always loved the angle you’re taking. If any of you at Fly read this and want to potentially collaborate on a solution in this space, my email is in my profile. [0] https://www.splitgraph.com https://www.splitgraph.com

rozap 5y ago
I work at Socrata, and I've skimmed splitgraph and it looks pretty neat. How do you all deal with pushdowns or query rewriting in general when the target datasource doesn't implement the functionality your user writing real sql is trying to use? Does it explode or take forever, because it doesn't look like you're storing copies of everything in Real Postgres? We expose a pretty limited subset of sql through our APIs, and an even more limited form to public APIs, so I'm interested in how you're doing things providing joins to your users. "It exists but is super slow" is a reasonable answer as well :)
- chatmasta 5y ago
  Hey! Socrata is awesome :) The basic idea here is that you can write a Splitgraph plugin for any type of data source [0], so we like to use Socrata as a convenient example. Each plugin includes a Postgres Foreign Data Wrapper (FDW) to translate from SQL to whatever upstream query language (e.g. Socrata FDW [1]). By default we don't copy any data into Splitgraph (although we do cache query results keyed by AST). When a query arrives, we parse it for any table identifiers, and "mount" a temporary foreign table for each, using the appropriate FDW. Foreign tables are mostly transparent to the Postgres query planner, so a JOIN will work (example [2]), and an FDW can even optionally implement aggregate pushdown. But yes, especially in the case of cross-dataset JOINS, there are some pathological cases. When your use case outgrows live querying (e.g. due to pathological JOIN queries), you can ingest any data source into a Splitgraph image, a versioned snapshot of data similar to a Docker image. [3] Our focus right now is on the internal enterprise use case. We use the Socrata plugin as a demo, but our customers are more interested in plugins for data sources they use internally, e.g. Postgres, Snowflake, Google Search Console, CSV in S3, etc. We support all those too; you can mount them all locally, and you can also mount them on Splitgraph.com (or an internal deployment), but still thin docs / no web UI yet. [0] https://www.splitgraph.com/blog/foreign-data-wrappers https://www.splitgraph.com/blog/foreign-data-wrappers [1] https://github.com/splitgraph/splitgraph/blob/master/splitgraph/ingestion/socrata/fdw.py https://github.com/splitgraph/splitgraph/blob/master/splitgr... [2] https://www.splitgraph.com/workspace/ddn?layout=hsplit&query=SELECT%0A++++cambridge_cases.date+AS+date%2C%0A++++chicago_cases.cases_total+AS+chicago_daily_cases%2C%0A++++cambridge_cases.new_positive_cases+AS+cambridge_daily_cases%0AFROM%0A++++%22cityofchicago%2Fcovid19-daily-cases-deaths-and-hospitalizations-naz8-j4nc%22.covid19_daily_cases_deaths_and_hospitalizations+chicago_cases%0AFULL+OUTER+JOIN%0A++++%22cambridgema-gov%2Fcovid19-case-count-by-date-axxk-jvk8%22.covid19_case_count_by_date+cambridge_cases%0AON%0A++++date_trunc%28%27day%27%2C+chicago_cases.lab_report_date%3A%3Atimestamp%29+%3D+cambridge_cases.date%3A%3Atimestamp%0AWHERE+date+IS+NOT+NULL%0AORDER+BY+date+DESC%3B%0A%0A https://www.splitgraph.com/workspace/ddn?layout=hsplit&query... [3] https://www.splitgraph.com/docs/concepts/images https://www.splitgraph.com/docs/concepts/images

zomglings 5y ago

This is a nice solution, although I prefer for my application to be aware of read vs. read-write connections. Question about fly.io - how strict is the docker image requirement? How easy would it be to deploy a go application + systemd service?

tptacek 5y ago
We'll accept any container that a Docker registry will; you don't have to use Docker to build your container instance (`flyctl launch` will build an application from a buildpack). You can run systemd in a container. We don't use Docker to run containers; in fact, we don't run "containers" at all. Instead, we transmogrify OCI containers into root filesystems for Firecracker micro-vms. Your "container" runs as a virtual machine with its own Linux kernel. You can do whatever you'd like with it. https://fly.io/blog/docker-without-docker/ https://fly.io/blog/docker-without-docker/
CameronNemo 5y ago
Is there a reason you need systemd? You can build a pretty minimal image with just the go binary in it (assuming you have statically linked it using musl).
- zomglings 5y ago
  At Bugout, we don't use docker. We deploy our code directly to machines over SSH and use systemd to coordinate deployed services, run timers, etc. Building the go binary is not a problem, but determining how the go binary runs, what it needs in order to be functional, etc. is where systemd comes into our setup.

holistio 5y ago

I first read the title as "Globally Distributed Progress" and I was like "if an article with this title made it to the front page of HN it must sure be some interesting read. I am now slightly disappointed.

mrkurt 5y ago
It is pretty boring. But "the most boring possible way to build globally distributed postgres" is interesting, I think.
unnouinceput 5y ago
Me too, having worked with PGSQL in past 15 years, I was expecting...something else. But what bothers me the most is their 2 times quote: "HTTP is cheap". No, it's not. Try having local local servers in remote locations, like ski resorts or country side fair, where internet connectivity is sketchy at best, and then you'll realize how cheap a request is.
- mrkurt 5y ago
  HTTP is cheap! Latency is expensive, but all our HTTP shenanigans happen between our proxy and user VMs. We're not inflicting multiple requests on slow clients at a ski resort.

mixmastamyk 5y ago

I know this is hacker news and all and you’re trying to be fun, but as I’m looking at platforms like fly for a future saas, the first sentence (cool hack) rubbed me the wrong way. Would rather hear “we found an elegant design” and are working hard to make it bulletproof… etc. Databases are like that, the place to get serious. Sure I won’t let it deter me from a proper eval, but I could definitely see it scaring away suits.

deleted 5y ago
[deleted]
mrkurt 5y ago
We are at the stage where attracting developers and scaring suits is probably good. I'd have a hard time calling something I was partially responsible for elegant, though, so this might just be our self deprecating nature rearing its head.
- treis 5y ago
  >We are at the stage where attracting developers and scaring suits is probably good. As a developer the way it was written scared me off. It reads like one of those recipe sites that game Google by adding a nonsensical story to the recipe. It seems like it took half the article to get to your "one trick". And ultimately you didn't deliver what the title teased.
  - mrkurt 5y ago
    I definitely like writing the story parts, and the actual replay header is about halfway down. So you're right about that. But it really does result in globally distributed postgres.
  - mixmastamyk 5y ago
    Yes, as a dev with important data is was kinda "nails on the chalkboard." That said I'm all up for more fun. Just not with the biz critical data.

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

Also relevant: https://fly.io/docs/getting-started/multi-region-databases/ https://fly.io/docs/getting-started/multi-region-databases/

itsjloh 5y ago

How are POST requests replayed into different regions? Does your edge proxy hold the whole POST requests (body and all?) just in case it needs to be replayed?

mrkurt 5y ago
It does, and we need to update our docs with this. We buffer up to 1mb of request data to be possibly replayed. We can set that arbitrarily high, it just seemed like a reasonable starting point.
- malloryerik 5y ago
  Fly.io and the Postgres service look great. Do you have a roadmap for future regions? (I'd personally love to see Korea.) If I may ask, do you use major cloud services or run your own machines, and how easy or hard is it for Fly.io to add new regions?
  - mrkurt 5y ago
    We don't have a firm roadmap for future regions, but Seoul is high up on my wish list. Our infrastructure is all dedicated hosts, mostly running in Equinix facilities (we buy a lot of servers from what used to be packet.com). New regions are hard in one particular way. Since we accept anycast traffic, ever new region has to be anycast-able for us. We outsource anycast management so we're kind of stuck waiting on other providers to opt in to a region. When we get to a scale where we can manage our own anycast and decide we want to, we will be able to ship new regions relatively quickly. Some places are harder to get servers to than others, but we don't actually need a very large footprint to get started.
- itsjloh 5y ago
  Cool, makes sense. Thanks for taking the time to reply! Really big fan of fly, run a few apps on it now and the experience has been fantastic.

AtlasBarfed 5y ago

Oh look a distributed database with nary a mention of CAP. And they are allowing multi-master writes? What is the update collision resolution? cell timestamps? vector clocks?