We have a multi-tenanted application which has a similar problem, but even a little worse (we have like 800 Postgres schemas across 5 servers).
What we do is have 5 normal Ecto.Repo modules, one for each server. Then we wrote our own MultitenantRepo that (nearly) implements the Ecto.Repo behavior. Then we send our queries to that.
Ecto.Query.from(...)
|> MultitenantRepo.all()
Each function in MultitenantRepo figures out which normal repo to delegate to and how to set the proper :prefix if applicable.
It’s kind of a PITA because there are a lot of functions in the Ecto.Repo behaviour to implement, but with a little bit of clever metaprogramming, it’s not too bad.
I tried the custom pool route to do this, and this way is much simpler IMO.
