DuckDB extension performance: connection pool and per-call overhead

Branch duckdb-perf on top of master a377c4d7 (stores without secondary indexes). Same host, datasets and drivers as baseline.md: a 32-vCPU Xeon 8488C, Debian 12, release extension, DuckDB v1.5.6 Python client, gen.py xs (~0.1M datoms) and s (~1M). perf.sh measures single-client p50s (bench.py run, median of 3 reps), single-datom transacts and conc.py throughput (Python threads, each with its own duckdb cursor on ONE database). writers.py measures concurrent writes and ab.sh + loop.py interleave two builds call by call.

Columns:

  • before: a377c4d7, the index-free layout (before-noidx/). The baseline.md numbers were taken on the indexed 1.14.0 layout. The two differ by less than 0.2 ms per read and ~5 tx/s, so the before/after below uses the index-free run.
  • stmt: 9e43b7b7 + 63c86ce0, statement-overhead changes (m2-fixed/).
  • pool: 42094d96, connection pool + per-store write lock (m3/).

Concurrency (ops/s at 1 / 8 / 32 client threads)

workload scale before stmt pool
point_lookup xs 280 / 321 / 315 280 / 322 / 317 285 / 1510 / 1574
mixed reads (point, ref, pull) xs 154 / 167 / 166 154 / 171 / 171 160 / 980 / 1039
point_lookup s 254 / 294 / 295 259 / 299 / 293 258 / 1310 / 1262
mixed reads s 125 / 132 / 128 121 / 132 / 130 120 / 598 / 606

Point-lookup p50 at 32 threads: xs 101 -> 20 ms, s 108 -> 25 ms. Before the pool, throughput was flat from 1 to 32 threads (one connection, one global lock). With the default pool of 8 connections it scales about 5x and then flattens. Past 8 concurrent calls, the extra calls wait for a connection. MENTAT_DUCKDB_POOL raises the limit.

Writes (m3/writers.txt, single-datom edn_t, tx/s):

layout threads stmt (serialized) pool
one store 1 71 72
one store 8 69 73
a store per thread 8 65 291

Writes to one store take turns by design (see “Writes” below); writes to different stores now run in parallel.

Single client (p50 ms)

scenario xs before xs pool s before s pool
point_lookup 3.47 3.43 3.80 3.79
pull 3.52 3.46 3.85 3.80
input_bindings 5.64 5.58 6.37 6.18
ref_traversal 11.04 10.91 15.54 14.77
aggregate 4.99 4.93 6.40 6.10
as_of 24.16 24.12 28.21 28.60
since 2.40 2.40 2.82 2.80
single-datom transact (tx/s) 55 58 50 54

Interleaved per-call A/B on xs (m2-fixed/ab-xs.log, 3 rounds): point lookup 3.50 -> 3.47 ms, pull 3.54 -> 3.54, transact 18.50 -> 17.22 ms.

Single-client latency barely moved, and that is what the profile predicts. Of a 2.8 ms in-store point lookup, 2.2 ms is DuckDB executing the query’s SQL: two filtered scans of datoms and a hash join. Only ~0.13 ms of that is preparing it, and mentat’s Rust is ~0.2 ms. In perf record of a loop of extension point lookups, mentat’s own code is 1.4% of the samples.

What changed

  1. Connection pool (crates/duckdb/src/lib.rs). The entrypoint opens MENTAT_DUCKDB_POOL connections (default one per core, at most 8; the database handle is only valid during the entrypoint, so they can’t be opened later). try_clone() repeated N times inside the entrypoint works. Each call leases one and returns it on drop; with all leased, a call waits on a condvar.
  2. Writes: a per-store lock (DuckStore::transact, store creation, the legacy-index drop). Without it, the harness’s 8 concurrent writers fail on their first transaction with “TransactionContext Error: Conflict on update!”: every transaction updates known_parts, and DuckDB’s optimistic concurrency lets one of two concurrent updaters win. Retrying would also work. The lock is simpler, and a single store’s writes can’t overlap anyway. It is per process; another process can’t open the database for writing.
  3. Schema cache is per thread, revalidated against meta.generation on every call. It doesn’t depend on which connection a call gets, so it is unchanged.
  4. One generation read fewer per transact (open returns the generation it validated; transact compares against it inside the transaction), and known_parts is updated only for partitions whose index moved (1 UPDATE instead of 3 for an ordinary transaction).
  5. Prepared-statement cache, opt-in. SqlConn::query_cached / execute_cached keep a statement prepared. The store uses them only for single-table statements on its own bookkeeping tables, with every varying value bound: the generation read, the known_parts read and the known_parts UPDATE. Engine queries, joins and the transact DML are prepared fresh.

The prepared-statement bug (and why engine queries aren’t cached)

9e43b7b7 cached every statement. That broke queries: with two stores on one connection, a count on the second store kept returning 0 after it had datoms. On DuckDB 1.5.x a reused prepared plan keeps the column statistics it was planned with. A filter with a constant outside them (the engine inlines entids: datoms.a = 65536 before that attribute has datoms) keeps matching nothing after rows arrive. repro-stale-prepared.sql shows it in plain SQL. A bound parameter (a = $1) is not affected there; the join-statistics variant is. Upstream: duckdb/duckdb#23704 / #21207, fixed by PR #24696 (merged to main 2026-08-14, in no 1.5.x release); joins: #24182. 63c86ce0 restricts caching as above. Two harness tests fail on 9e43b7b7 and pass since. Caching engine queries can be reconsidered once the pinned DuckDB includes #24696 and #24182; it would save ~0.1-0.2 ms per call.

Not done

  • Faster single-client reads. The cost is DuckDB scanning datoms. Things tried and measured:
    • A sorted copy of datoms cut the join from 2.4 to 1.8 ms (probes/layout.py).
    • Pre-filtering the transact search join by entity made it slower (2.4 -> 3.4 ms, probes/search.py).
    • Disabling statistics propagation changed nothing (probes/statcost.py).

    Clustering datoms by (a, e) on checkpoint is the most promising lead.

  • Transact cost (17 ms) is mostly DuckDB too: COMMIT 3.5 ms, the search INSERT ... SELECT 3.4 ms, and recreating three temp tables per transaction (0.9 ms). Reusing the temp tables with DELETE measured the same (probes/cine.py).
  • The WAL replay index bug (duckdb/duckdb#26828) was found here and is handled on master (a377c4d7); repro5.sh and repro-mentat.sh reproduce it.

Gate (on 42094d96, the perf box)

check result
cargo fmt --all --check (+ tests-harness) pass
cargo clippy -p mentat_duckdb -p mentat_duckdb_store -- -D warnings pass, 0 warnings
cargo test --manifest-path crates/duckdb/store/tests-harness/Cargo.toml pass: differential (0 disagreements vs SQLite), store 10, script_model 15, concurrency 1
make debug && DUCKDB=~/duckdb bash test/smoke.sh PASS
python test/concurrency.py build/debug/mentat.duckdb_extension OK
SQLLogicTest (duckdb==1.5.6, duckdb-sqllogictest-python@2ac8dbc) SUCCESS, 1/1
workspace cargo test (default members) pass: 1769 passed, 0 failed, 30 ignored