Sharing a DBConnection pool with multiple Ecto Repo's

I attempted to create a naive implementation that would just delegate everything to DBConnection.Poolboy just to see if I could get something working. I assumed Ecto.Repo started the pool using DBConnection.Poolboy.start_link/2 but that doesn’t seem to be the case. It looks like DBConnection.Connection does a lot of heavy lifting but it’s not clear from the docs how it works.

Super basic, don’t laugh:

defmodule Data.Pool do
  @moduledoc false

  @behaviour DBConnection.Pool

  @registry Data.Pool.Registry

  defdelegate checkin(pool_ref, state, opts), to: DBConnection.Poolboy

  defdelegate checkout(pool, opts), to: DBConnection.Poolboy

  defdelegate child_spec(mod, opts, child_opts), to: DBConnection.Poolboy

  defdelegate disconnect(pool_ref, err, state, opts), to: DBConnection.Poolboy

  defdelegate ensure_all_started(opts, type), to: DBConnection.Poolboy

  defdelegate stop(pool_ref, err, state, opts), to: DBConnection.Poolboy

  def start_link(mod, opts) do
    case @registry.get() do
      [] ->
        @registry.put(DBConnection.Poolboy.start_link(mod, opts))
      [ pid ] ->
        { :ok, pid }
    end
  end
end

This is the reason I just share a repo between all umbrella apps, and declare the schemas separately in each app. Otherwise you can easily max out the 200 connection limit on an AWS db.t2.small instance

This is typically what I do and I believe most other people using umbrella apps do as well. For me, the problem arises when using tools like Postgres schemas. You can have multiple logical datastores within the same physical database which share a connection limit. In that scenario each application should have it’s own Repo with a shared connection pool.

Pus, you can’t generate your migrations within the contextually relevant applications. That’s the real problem! Makes my eye twitch.

@anthonator Just dropping by to say that your approach inspired me to figure out something similar.

I did just slightly differently without a new connection pool module (and thus no delegations).

defmodule DB.SharedConnectionPool do
  alias DBConnection.ConnectionPool

  def child_spec({mod, opts}) do
    opts = Keyword.put_new(opts, :name, pool_name(opts))
    Supervisor.Spec.worker(__MODULE__, [{mod, opts}])
  end

  def start_link({mod, opts}) do
    case GenServer.start_link(ConnectionPool, {mod, opts}, start_opts(opts)) do
      {:ok, pid} -> {:ok, pid}
      {:error, {:already_started, pid}} -> {:ok, pid}
      error -> error
    end
  end

  defp pool_name(opts) do
    case Keyword.fetch(opts, :shared_pool_id) do
      {:ok, pool} -> String.to_atom("#{__MODULE__}-#{pool}")
      :error -> __MODULE__
    end
  end

  # Exact same as `DBConnection.ConnectionPool`
  defp start_opts(opts) do
    Keyword.take(opts, [:name, :spawn_opt])
  end
end

And I have the repos configured like this:

config :my_app, MyApp.Repo,
  pool: DB.SharedConnectionPool,
  shared_pool_id: :my_shared_pool_id

config :my_second_app, MySecondApp.Repo,
  pool: DB.SharedConnectionPool,
  shared_pool_id: :my_shared_pool_id

This way makes it also possible to specifically configure what repos should share a pool, and multiple shared pools can be created.

The downside is that it still relies on the connection info from the configs, which means if two repos with different configs share the same pool, there’s a race condition and one repo config will be taken to start the pool. But otherwise this approach has the least impact on how Ecto works.

Full code here: Allow DB connection pool sharing between repos by unnawut · Pull Request #853 · omgnetwork/ewallet · GitHub (disclaimer: I’m employed by this project)

With db_connection 2.4.0, i get this error

** (UndefinedFunctionError) function Solve.Utils.DB.SharedConnectionPool.checkout/3 is undefined or private
    (utils 0.1.0) Solve.Utils.DB.SharedConnectionPool.checkout(#PID<0.13239.0>, [#PID<0.13592.0>, #PID<0.13591.0>], [log: #Function<14.6198013/1 in Ecto.Adapters.SQL.with_log/3>, source: "query_history_v1", timeout: 15000, pool_size: 10, pool: Solve.Utils.DB.SharedConnectionPool])
    (db_connection 2.4.0) lib/db_connection.ex:1082: DBConnection.checkout/3
    (db_connection 2.4.0) lib/db_connection.ex:1407: DBConnection.run/6
    (db_connection 2.4.0) lib/db_connection.ex:574: DBConnection.parsed_prepare_execute/5
    (db_connection 2.4.0) lib/db_connection.ex:566: DBConnection.prepare_execute/4
    (ecto_sql 3.5.4) lib/ecto/adapters/sql.ex:690: Ecto.Adapters.SQL.execute!/4
    (ecto_sql 3.5.4) lib/ecto/adapters/sql.ex:682: Ecto.Adapters.SQL.execute/5
    (ecto 3.5.6) lib/ecto/repo/queryable.ex:224: Ecto.Repo.Queryable.execute/4
    (ecto 3.5.6) lib/ecto/repo/queryable.ex:19: Ecto.Repo.Queryable.all/3
    (saved_query_service 0.1.0) lib/query_service.ex:55: Solve.SavedQueryService.fetch_history/2
    (query_runner 0.1.0) lib/query_runner/tidy_history_task.ex:7: Solve.QueryRunner.TidyHistory.run/0
    (elixir 1.11.3) lib/task/supervised.ex:90: Task.Supervised.invoke_mfa/2
    (stdlib 3.14) proc_lib.erl:226: :proc_lib.init_p_do_apply/3

Adding:

  defdelegate checkout(pool, callers, opts), to: ConnectionPool

to the connection pool here Sharing a DBConnection pool with multiple Ecto Repo's - #5 by unnawut seems to fix the issue