Fly.io multi-region Postgres read replicas

For improved latency, you might want to deploy your app to one or more of Fly.io's 20+ regions on their own Points of Presence.

You can also pair your regional servers with Fly's multi-region Postgres read replicas. The caveat is Fly Postgres is not managed.

Develop

Copy this as main.rb and run the commands in the comments:

#!/usr/bin/env ruby

# createdb db
# chmod +x main.rb
# DATABASE_URL=postgres:///db ./main.rb

require "bundler/inline"

gemfile do
  source "https://rubygems.org"

  gem "connection_pool"
  gem "pg"
  gem "puma"
  gem "sinatra"

  group :development do
    gem "standard"
  end
end

class DB
  def initialize
    connect
  end

  def exec(sql)
    do_exec(sql)
  rescue PG::ConnectionBad
    connect
    do_exec(sql)
  end

  private

  def connect
    url = ENV.fetch("DATABASE_URL")
    primary = ENV["PRIMARY_REGION"].to_s
    current = ENV["FLY_REGION"].to_s

    if primary != "" && current != "" && primary != current
      u = URI.parse(url)
      u.port = 5433
      url = u.to_s
    end

    @pool = ConnectionPool.new(size: 5, timeout: 5) {
      PG.connect(url)
    }
  end

  def do_exec(sql)
    @pool.with do |conn|
      conn.exec(sql)
    end
  end
end

db = DB.new

configure do
  set :protection, except: [:json_csrf]
end

get "/" do
  db.exec "SELECT 1"
  content_type :json
  {status: "ok"}.to_json
end

Sinatra::Application.run!

Fly

Install the flyctl CLI, which is symlinked as fly:

brew install flyctl

Connect to your account:

fly auth login

Create a Fly app:

fly launch --remote-only

Tell the app which region will be primary, such as Sunnyvale, California:

fly secrets set PRIMARY_REGION=sjc

Create Fly Postgres database in that region:

fly pg create --name db-replicas --region sjc

Set the DATABASE_URL environment variable by attaching the database cluster to the app:

fly pg attach db-replicas

For improved latency to the app servers, add regions such as Frankfurt, Germany and Sydney, Australia:

fly regions add fra syd

Create Postgres read replicas in those regions:

fly volumes create pg_data -a example-read-replicas-db --size 1 --region fra
fly volumes create pg_data -a example-read-replicas-db --size 1 --region syd

Scale 1 instance per region:

fly autoscale standard min=3 max=3

Done!