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