Disable query cache?

I feel like the normal Ecto approach here is to have one Repo module per database. What about that approach won’t work here?

500+ databases and they are created dynamically. Give them each a pool of 20 connections and then combine that with we have over 100 web/worker servers, that’s too many connections for pgBouncer.

So given that, I want to try the idea of an LRU connection cache (that implements the DBConnection.Pool behavior so it can be used with Ecto).

And it generally works, except for the aforementioned issue. Fishcakez offered a workaround, which works (but needs a code review) and I’ll post about in a little bit.

Interesting, would love to see what the solution turns out to be.

Here’s the solution (and a description of the problem)…

So the first time a query is run Ecto prepares the query which stores type information in the query struct, then subsequently when that query is executed, it uses that cached type info and can execute directly instead of preparing first.

Here’s the relevant code in Ecto.Adapters.Postgres.Connection:

def prepare_execute(conn, name, sql, params, opts) do
  query = %Postgrex.Query{name: name, statement: sql}
  DBConnection.prepare_execute(conn, query, params, opts)
end

def execute(conn, sql, params, opts) when is_binary(sql) do
  query = %Postgrex.Query{name: "", statement: sql}
  case DBConnection.prepare_execute(conn, query, params, opts) do
    {:ok, _, query} -> {:ok, query}
    {:error, _} = err -> err
  end
end

def execute(conn, %{} = query, params, opts) do
  DBConnection.execute(conn, query, params, opts)
end

So the solution is to change those functions to simply discard the cached query info in the call to prepare_execute, then modify the other execute functions to always call prepare_execute:

def prepare_execute(conn, name, sql, params, opts) do
  query = %Postgrex.Query{name: "", statement: sql}
  { :ok, result } = execute(conn, query, params, opts)
  { :ok, query, result }
end

def execute(conn, sql, params, opts) when is_binary(sql) do
  query = %Postgrex.Query{name: "", statement: sql}
  execute(conn, query, params, opts)
end

def execute(conn, %{} = query, params, opts) do
  { :ok, _query, result } = DBConnection.prepare_execute(conn, query, params, opts)
  { :ok, result }
end

Notice that prepare_execute returns the initial, almost bare %Postgrex.Query{} struct without any cached query info.

Obviously the downside to this is that every query now has to hit the db twice since we’re no longer saving any of the info returned by prepare_execute.

Huge thanks to @fishcakez to helping me with this; saving me many hours of trying to figure it out on my own.

There is still one last problem. Those modified functions live in Ecto.Adapters.Postgres.Connection, so it’s not just a matter of making a new Ecto.Adapters.PostgresWithoutCache adapter; I have to make both a new adapter module and connection module for that adapter. And I’m not sure how to do that cleanly in a functional language. In an OOP language, I could simply inherit new classes and overwrite those specific methods… but how do something similar in Elixir?

I want to make a new Adapter module and new Connection module that are exactly the same except for those three methods.

Thanks for the help.

Is there no way to cache based on the database?