If the two libraries are being exercised in different ways and then the numbers compared, it isn’t a meaningful benchmark. Pears to pears.

In particular, having Ecto do string interpolation and SQL do none in the cursor benchmark means they are measuring different things that aren’t really related to the performance of these libraries. In fact, SQL is being asked to do less work. How much less? Can’t tell from the benchmarks.

It’s also doesn’t help that the benchmarks are triivial, e.g. “fetch the literal value 1”. It’s similar to the http benchmarks that measure how long it takes to fetch a static file whose contents fit in a single network packet.

All I can muster from the benchmarks is that SQL is a lot better at queries that my applications will never produce. It might be faster/lighter at all queries, but these benchmarks don’t help one gauge that.

big of a limitation Ecto’s pool and driver design is

I still don’t understand how much of a limitation either of these two parts are. Is one more of a culprit than the other? If so, by how much? Is Ecto’s front-end query building implicated in this?

In a more perfect world, Ecto’s pooling and/or use of drivers would improve and separately from that sql (hopefully with a better name) could provide a different access point via it’s “literal sql” text→query approach.

And if that isn’t a real possibility, if it really is Ecto’s front-end, it would be great to understand why. I keep looking at these benchmarks and your posts about sql looking for this information so I can make an informed decision. I can’t be the only one.

So we now have a SOTA pool not only on the BEAM but across all languages (AFAIK), some of the things I listed didn’t make it, mostly becuase they ended up being completely unnecessary when you lean into the BEAM.

import Ecto.Query
defmodule SQL.Repo do
  use Ecto.Repo, otp_app: :sql, adapter: Ecto.Adapters.Postgres
  use SQL, adapter: SQL.Adapters.Postgres
  import Ecto.Query
  def sql(type \\ :transaction)
  def sql(:statement) do
    Enum.to_list(~SQL"SELECT 1")
  end
  def sql(:empty) do
    SQL.transaction do
      :ok
    end
  end
  def sql(:transaction) do
    SQL.transaction do
      Enum.to_list(~SQL"SELECT 1")
    end
  end
  def sql(:savepoint) do
    SQL.transaction do
      SQL.transaction do
        Enum.to_list(~SQL"SELECT 1")
      end
    end
  end
  def sql(:cursor) do
    SQL.transaction do
      Stream.run(~SQL"SELECT g, repeat(md5(g::text), 4) FROM generate_series(1, 5000000) AS g")
    end
  end

  def ecto(type \\ :transaction)
  def ecto(:statement) do
    SQL.Repo.all(select(from("users"), [1]))
  end
  def ecto(:empty) do
    SQL.Repo.transaction(fn ->
      :ok
    end)
  end
  def ecto(:transaction) do
    SQL.Repo.transaction(fn ->
      SQL.Repo.all(select(from("users"), [1]))
    end)
  end
  def ecto(:savepoint) do
    SQL.Repo.transaction(fn ->
      SQL.Repo.transaction(fn ->
        SQL.Repo.all(select(from("users"), [1]))
      end)
    end)
  end
  def ecto(:cursor) do
    SQL.Repo.transaction(fn ->
      from(row in fragment("SELECT g, repeat(md5(g::text), 4) FROM generate_series(1, ?) AS g", 5000000), select: {fragment("?::int", row.g), fragment("?::text", row.repeat)})
      |> SQL.Repo.stream()
      |> Stream.run()
    end)
  end
end
Application.put_env(:sql, :ecto_repos, [SQL.Repo])
Application.put_env(:sql, SQL.Repo, log: false, username: "postgres", password: "postgres", hostname: "localhost", database: "sql_test#{System.get_env("MIX_TEST_PARTITION")}", pool_size: :erlang.system_info(:schedulers_online), ssl: false)
SQL.Repo.__adapter__().storage_up(SQL.Repo.config())
SQL.Repo.start_link()

Benchee.run(
  %{
  "sql" => fn -> SQL.Repo.sql(:transaction) end,
  "ecto" => fn -> SQL.Repo.ecto(:transaction) end,
  },
  parallel: 500,
  warmup: 10,
  memory_time: 2,
  reduction_time: 2,
  unit_scaling: :smallest,
  measure_function_call_overhead: true
)

➜ sql git:(main) ✗ mix sql.bench
Operating System: macOS
CPU Information: Apple M1 Max
Number of Available Cores: 10
Available memory: 64 GB
Elixir 1.20.0-dev
Erlang 28.1
JIT enabled: true

Benchmark suite executing with the following configuration:
warmup: 10 s
time: 5 s
memory time: 2 s
reduction time: 2 s
parallel: 500
inputs: none specified
Estimated total run time: 38 s

Measured function call overhead as: 0 ns
Benchmarking ecto …
Benchmarking sql …
Calculating statistics…
Formatting results…

Name ips average deviation median 99th %
sql 5507.15 181.58 μs ±1576.97% 177.13 μs 324.92 μs
ecto 56.22 17788.30 μs ±8.30% 17331.33 μs 22480.54 μs

Comparison:
sql 5507.15
ecto 56.22 - 97.96x slower +17606.72 μs

Memory usage statistics:

Name average deviation median 99th %
sql 984.00 B ±0.02% 984 B 984 B
ecto 17952.31 B ±0.03% 17952 B 17952 B

Comparison:
sql 984 B
ecto 17952.31 B - 18.24x memory usage +16968.31 B

Reduction count statistics:

Name average deviation median 99th %
sql 0.0920 K ±0.03% 0.0920 K 0.0920 K
ecto 1.56 K ±0.30% 1.56 K 1.56 K

Comparison:
sql 0.0920 K
ecto 1.56 K - 16.91x reduction count +1.46 K
➜ sql git:(main) ✗

Yes, we’re building a state of the art database connection pool, an initial implementation in under 100 lines has been done and is already out performing db_connection in every metric. Ecto uses db_connection in a way that made it impossible to introduce features like: 100% cache hit on prepared queries.

It would actually be underselling the library to only call the pool SOTA, as we’re closing in to be SOTA SQL library across all languages as I’ve never seen anything like this before.