Thanks for the answer!
The strategy we used was to create 2 ecto repo, one for the leader (read / write) and one for the replicas (read only). If the node is in the same region as the leader, both repositories will have the same database url.
The configuration looks:
fly ssh console -s -a shy-rain-1653
? Select instance: gru.shy-rain-1653.internal
Connecting to [fdaa:0:332b:a7b:1f60:5836:bb9a:2]... complete
/ # /app/bin/brandkit remote
Erlang/OTP 24 [erts-12.0.2] [source] [64-bit] [smp:1:1] [ds:1:1:10] [async-threads:1] [jit:no-native-stack]
Interactive Elixir (1.12.1) - press Ctrl+C to exit (type h() ENTER for help)
iex(shy-rain-1653@fdaa:0:332b:a7b:1f60:5836:bb9a:2)1> Application.get_env(:brandkit, Brandkit.Repo)
[
url: "postgres://xxx:xxx@brandkit-pg-st.internal:5432/shy_rain_1653?sslmode=disable",
socket_options: [:inet6],
pool_size: 10
]
iex(shy-rain-1653@fdaa:0:332b:a7b:1f60:5836:bb9a:2)2> Application.get_env(:brandkit, Brandkit.RepoReadOnly)
[
url: "postgres://xxx:xxx@gru.brandkit-pg-st.internal:5432/shy_rain_1653?sslmode=disable",
socket_options: [:inet6],
pool_size: 10
]
iex(shy-rain-1653@fdaa:0:332b:a7b:1f60:5836:bb9a:2)3>
We explicitly use this RepoReadOnly in some queries. We do not all migrate for now.
I followed that post High Availability & Global Replication · Fly Docs and took some ideas from this Multi region database guide discussion.
The difference in the time of the requests is big.
GRU (using replica and leader)
app[33dcfef1] gru [info] 19:37:29.017 request_id=FqWzLO6dfiNa7ZoAAAXh [info] Sent 200 in 3462ms
IAD (using leader)
app[2e575cad] iad [info] 19:51:03.302 request_id=FqWz60zLOpGGZIIAAAbh [info] Sent 200 in 122ms
These requests only have read operations, but they use both repo.
We’re sure that our actual configuration is not the best, so it’s going to be awesome!
We will continue testing, we just want to share our experience and try to better understand how is the correct way to use our PG cluster.
Thanks!