# DuckDB over Quack: three-way comparison

Is it worth running mentat inside a long-lived DuckDB Quack server
(`crates/duckdb/server/serve.sh`, commit `1f3e2d3f`), compared with calling
the extension in-process?

- **(a) per-call open, 1.9.0**: every call opened the store, and `Store::open`
  scanned the whole log. This is `ext-cache-2026-09-27T214617Z/baseline/`.
- **(a') per-call open, O(1) open**: the (b) build with `MENTAT_STORE_CACHE=0`,
  four scenarios only. This is `ext-cache-2026-09-27T214617Z/nocache-timings.csv`.
- **(b) in-process + cache**: `d34e0177`, the store cached per thread. This is
  `ext-cache-2026-09-27T214617Z/`.
- **(c) Quack + cache**: the same extension build, loaded into one
  `duckdb -unsigned` server. Each client process sends the (b) SQL through
  `quack_query` (backend `duckdb-quack`, `91ef563d`; same `d34e0177` extension
  build as (b)). This is this directory.

Box: c7i.8xlarge, 32 vCPU, 61.8 GiB, AL2023 6.12 (`env.txt`). DuckDB 1.5.5 CLI
server, Python duckdb 1.5.5 clients, `REPS=1`, clients and server on the same
host over loopback. The `m` store is a copy of the embedded-built 9.85M-datom
store, as in (b). Checks: 52 PASS, 0 FAIL (`checks.txt`), including post-write.
`threeway.md` is generated by `scripts/threeway.py` (fixed so it reads the
committed dirs and the `m` (a) rows). Every rerun below is under `rerun/`, with
its script in `scripts/`.

## Summary

- **Over per-call open (a)**, going through the server turns a 465-570 ms call
  at `s` (5-6 s at `m`) into 2.7-3.5 ms. In-process with the cache (b) gets
  the same speed-up and more. Two things account for it, and neither is
  Quack: the O(1) `Store::open` (a → a') and the store cache (a' → b).
- **Over in-process with cache (b)**, the server gains no speed at all. Every
  call over Quack costs a fixed ~2.2 ms extra: three new TCP connections per
  call, with no keep-alive. It is +2.1 to +2.5 ms on small results (≤100
  rows), 0-11% on queries of ~40 ms or more, and never faster. Concurrent
  throughput with the stock server is 79-84% of (b) at `s` and 95-100% at
  `m`, where the query dominates. With the listen-backlog fix below it is
  88-92% at `s`.
- **What the server does buy:**
  - One process holds the warm stores and page cache for every client.
  - Clients need only the core `quack` extension, not mentat (no `-unsigned`
    on the client).
  - The result is an ordinary DuckDB table, so a client can join it with its
    own local tables (`gate/quack-verify.txt`).
  - A cold one-shot client is **not** faster. A fresh `duckdb` process doing
    one point lookup takes 17 ms in-process vs 40 ms over Quack, at both `s`
    and `m` (`rerun/fresh-process.md`). A bare CLI start is 15 ms. `LOAD
    mentat` + open + query add ~2 ms. `LOAD quack` adds ~11 ms, and the
    process's first `quack_query` another ~14 ms. The open that (a) paid is
    gone in (a'), so there is nothing left for the server to amortise.
- **The stock server has two operational problems**, both below:
  1. A listen backlog of 5 causes 1-3 s tail stalls.
  2. An unbounded WAL under sustained writes, which is not Quack-specific.

## Single client, p50 ms

Back-to-back reruns on the same checkpointed store copies (`rerun/ab-*.csv`,
the server with the backlog shim). This isolates the protocol from the
original run's cache state. Rows and connects are from `scripts/qconns.py`.

| scale | scenario | result rows | TCP connects / call | (b) in-process | (c) Quack | Quack − in-process ms | % |
|---|---|---:|---:|---:|---:|---:|---:|
| s | point_lookup | 1 | 3 | 0.630 | 2.743 | +2.11 | +335% |
| s | pull | 7 | 3 | 0.541 | 2.766 | +2.23 | +411% |
| s | input_bindings | 95 | 3 | 1.057 | 3.525 | +2.47 | +233% |
| s | since | 4,742 | 3 | 6.499 | 9.448 | +2.95 | +45% |
| s | ref_traversal | 86 | 3 | 38.678 | 42.157 | +3.48 | +9% |
| s | predicate_scan | 10,459 | 3 | 28.174 | 35.096 | +6.92 | +25% |
| s | aggregate | 5 | 3 | 90.465 | 96.672 | +6.21 | +7% |
| s | as_of | 86 | 3 | 85.192 | 89.330 | +4.14 | +5% |
| m | point_lookup | 1 | 3 | 1.131 | 3.372 | +2.24 | +198% |
| m | pull | 8 | 3 | 0.566 | 2.770 | +2.20 | +389% |
| m | input_bindings | 100 | 3 | 2.677 | 5.217 | +2.54 | +95% |
| m | since | 8,921 | 3 | 47.487 | 51.943 | +4.46 | +9% |
| m | ref_traversal | 90 | 3 | 491.376 | 500.679 | +9.30 | +2% |
| m | predicate_scan | 103,711 | 34 | 335.880 | 374.035 | +38.16 | +11% |
| m | aggregate | 5 | 3 | 1,063.818 | 1,097.171 | +33.35 | +3% |
| m | as_of | 90 | 3 | 1,032.819 | 1,034.474 | +1.65 | +0% |

The same table from the original run, with (a) and (a'), is in `threeway.md`.
Its `(c) − (b)` column agrees within ~1 ms at `s` and within 1% at `m`,
except for `predicate_scan` at `m`, which is +47.5 ms there vs +38.2 ms here. The original's p95 was
1,837 ms: see the listen-backlog section.

### Protocol and serialization overhead

With mentat out of the picture, the same `SELECT i, 'User ' || i FROM
range(n)` was run locally and through `quack_query` (`rerun/protocol-overhead.md`):

| rows | local ms | Quack ms, backlog 4096 | Quack − local | Quack ms, stock backlog 5 | TCP connects / call |
|---:|---:|---:|---:|---:|---:|
| 1 | 0.135 | 2.509 | +2.37 | 2.511 | 3 |
| 100 | 0.158 | 2.454 | +2.30 | 2.620 | 3 |
| 1,000 | 0.311 | 2.855 | +2.54 | 2.849 | 3 |
| 10,000 | 2.241 | 5.705 | +3.46 | 5.750 | 3 |
| 100,000 | 23.946 | 32.986 | +9.04 | 34.817 | 34 |
| 1,000,000 | 264.413 | 301.544 | +37.13 | **1,278.331** | 44 |

- **Fixed cost ≈ 2.3 ms per call**: three new TCP connections, since Quack
  v1.5.5 does not keep connections alive. 50 `quack_query` calls on one client connection leave 150
  TIME_WAIT sockets on the client side, and 50 `SELECT`s through `ATTACH`
  leave 50.
- **Serialization ≈ 35-110 ns per row** for (BIGINT, short VARCHAR): +1.1 ms
  per 10k rows, +35 ms per 1M, above the fixed cost.
- **Large results fetch in parallel.** Once a result is bigger than one FETCH
  batch (`quack_fetch_batch_chunks` = 12 chunks × 2048 rows ≈ 24.6k rows),
  the client opens 34 connections for 100k rows and 44 for 1M. That is
  roughly one per client thread (32 here), not one per batch, so it looks
  like a parallel fetch. The connections arrive together, which is how a
  single client overflows a backlog of 5.
- Mentat's `edn_q` results are VARCHAR cells, so the per-row cost is the same
  as above. Only `predicate_scan` at `m` (104k rows) goes past the 3-request
  floor.

## Concurrency: 1 / 8 / 32 / 64 / 128 client processes

The mix is point_lookup, ref_traversal and pull, with one host connection per
client process. Throughput is ops/s; latencies are ms.

| scale | clients | (a) ops/s | (b) ops/s | (c) ops/s stock | (c') ops/s backlog 4096 | (b) p50 / p99 | (c) p50 / p99 / max | (c') p50 / p99 / max |
|---|---:|---:|---:|---:|---:|---|---|---|
| s | 1 | 2.06 | 74.8 | 62.4 | | 0.69 / 39.4 | 3.84 / 42.4 / 43.7 | |
| s | 8 | 15.2 | 578.8 | 458.8 | | 0.72 / 40.7 | 3.12 / 64.6 / 66.5 | |
| s | 32 | 24.9 | 1,282 | 1,030 | 1,135 | 1.31 / 79.3 | 3.68 / 82.2 / 1,147 | 5.33 / 90.2 / 146 |
| s | 64 | 26.7 | 1,261 | 1,010 | 1,121 | 2.91 / 198 | 6.41 / 1,108 / 2,537 | 20.2 / 217 / 270 |
| s | 128 | 27.5 | 1,206 | 1,014 | 1,106 | 10.0 / 386 | 9.44 / 1,547 / 5,647 | 36.0 / 381 / 470 |
| m | 1 | | 5.98 | 5.83 | | 1.22 / 497 | 4.29 / 507 / 507 | |
| m | 8 | | 46.5 | 44.8 | | 1.59 / 510 | 4.73 / 548 / 563 | |
| m | 32 | | 109.9 | 104.8 | 107.0 | 2.86 / 847 | 9.36 / 903 / 1,070 | 7.76 / 936 / 938 |
| m | 64 | | 104.0 | 104.8 | 104.4 | 9.84 / 2,501 | 26.5 / 2,084 / 2,945 | 25.7 / 2,063 / 2,143 |
| m | 128 | | 101.6 | 97.2 | 96.4 | 33.7 / 4,157 | 49.1 / 3,684 / 4,849 | 44.8 / 3,680 / 3,816 |

- (a), (b) and (c) are from the original runs. (c') is `rerun/B-*.csv`, the
  same server with only the listen backlog raised. `(a)` at `m` never ran
  (per-call open > 20 s). (a) ran pinned to 24 CPUs; (b) and (c) ran on 32.
- All three plateau from 32 clients (32 vCPUs). The server and the client
  processes share those CPUs. The server averaged ~31 cores over its
  lifetime in the 32-client sustained run (`logs/quack-sampler.log`, `ps`
  %cpu ≈ 3,140).
- The throughput ceiling is ref_traversal's cost. At `s`, the ~2.3 ms per call
  of connection setup plus client-side request work costs 16-20% of the
  plateau with the stock server, and 8-12% with the backlog fixed. At `m`
  (500 ms per ref_traversal) it disappears.
- The p50 gap narrows with load and inverts at c=128, s. Under the stock
  backlog, stalled clients are parked in SYN retransmit, and they are not
  competing for CPU.

## The listen-queue overflows

`TcpExtListenOverflows` rose from 4 to 2,122 during the original run
(`logs/quack-sampler.log`). The increments fall in exactly three places: the
`s` concurrency sweep (4 → 1,790, starting when estab first passed ~18
clients), the `m` check (1,790 → 2,002, which is `predicate_scan`'s
34-connection fetch), and the `m` sweep (2,002 → 2,122).

The counter is cumulative since boot. It reached 2,122 in the `m` sweep and
stayed flat through the whole Quack sustained run (whose `phase=` column
shows the serve.sh line) and through the in-process `duckdb m sustained` run
that produced the file's last line. **The sustained run had no overflows.**
The server logs hold only the `quack_serve` banner. Quack v1.5.5 logs nothing
per request.

**Cause: Quack's HTTP server calls `listen(fd, 5)`.** `ss -ltn` shows
`Send-Q 5` on the listening socket. The kernel limits are not the cap:
`net.core.somaxconn` = 4096 and `tcp_max_syn_backlog` = 4096 on this AMI, and
`somaxconn` only lowers a backlog, never raises it. So `sysctl -w
net.core.somaxconn=…` cannot help. There is no DuckDB or Quack setting for the
backlog. Every call opens 3 fresh TCP connections, so c ≥ 32 clients easily
put more than 5 completed handshakes in the accept queue.

With the queue full, the kernel drops handshakes
(`tcp_abort_on_overflow=0`), and the client retransmits its SYN after 1 s,
then 2 s, then 4 s. `TcpExtTCPSynRetrans` rose by 1,283 in config A at `s`
and by 0 in B. Those retransmits are the 1.1 s / 2.5 s / 5.6 s max
latencies and the ~1 s p99s in the stock rows. It also hits a single
client: `predicate_scan` at `m` has 34 fetch connections in flight, and the
original run's p95 of 1,837 ms is that.

**The thread pool is not the cap.** The server has 161 threads: DuckDB's 32
(`threads` = vCPU count; the plain CLI has 32 in total) plus 129 that
`quack_serve` starts, most likely a 128-worker HTTP pool plus the acceptor.
`SET threads=4` gives 133 and `threads=64` gives 193, so the 129 is fixed.
With the backlog raised, 128 concurrent clients see no overflows and a
p99 equal to in-process (381 vs 386 ms). So up to 128 clients the pool does
not limit concurrency. Beyond that, requests would queue for a worker.
Raising `threads` to 64 changes nothing (configuration D below).

**Measured** (`rerun/`, `scripts/rr.sh`): concurrency_sweep c = 32/64/128 on
checkpointed copies of both stores, a fresh server per row. B and C raise the
backlog to 4096 with a 10-line `LD_PRELOAD` shim around `listen()`
(`scripts/backlog.c`), since Quack has no knob for it.

| config | backlog | threads | s: overflows | s c=32 / 64 / 128 ops/s | s p99 / max ms at c=64 | m: overflows | m c=32 ops/s |
|---|---:|---:|---:|---|---|---:|---:|
| A stock | 5 | 32 | 1,761 | 931 / 1,037 / 1,008 | 1,097 / 3,109 | 136 | 99.5 |
| B backlog | 4096 | 32 | **0** | 1,135 / 1,121 / 1,106 | 217 / 270 | **0** | 107.0 |
| C backlog + threads | 4096 | 64 | 0 | 1,126 / 1,099 / 1,079 | 220 / 287 | 0 | 106.3 |
| D threads only | 5 | 64 | 1,769 | 944 / 983 / 1,024 | 1,109 / 3,116 | 138 | 103.2 |

For a single client (`rerun/single-*`, predicate_scan at `m`, 104k rows), the
stock server has 210 overflows and a p50/p95 of 1,360 / 2,418 ms. The 4096
backlog brings that to 0 overflows and 377 / 386 ms.

Raising the backlog removes every overflow. The worst-case tail falls
8-12x (s max 3.1 s → 0.27 s at c=64, 4.6 s → 0.47 s at c=128), and the
32-client throughput at `s` rises 22%. The p50 rises (6.7 → 20 ms at c=64)
because clients that used to wait out a SYN timeout now compete for CPU. The
p99 falls 4-5x at c=64/128. Doubling the threads does nothing, with or without the backlog
change.

**What to do:** the backlog is compiled into Quack (duckdb_httplib), so the
real fix is upstream: a larger `listen()` backlog, or HTTP keep-alive, which
would also remove most of the 2.3 ms. Until then, `serve.sh` could run the
server under the `LD_PRELOAD` shim. That shim is not committed as part of
`crates/duckdb/server/`. The README's "Listen backlog is 5 … may be refused
and retried" line is accurate but understates the cost: 1-3 s stalls from 32
concurrent clients, or from a single query returning 100k+ rows.

## Sustained: m, 32 readers + 1 writer, 300 s

| | read ops/s | read p50 / p95 / p99 / max ms | write ops/s | write p50 / p99 / max ms | errors |
|---|---:|---|---:|---|---:|
| (b) in-process | 37.0 | 997 / 2,944 / 4,264 / 7,481 | 133.8 | 4.8 / 29.5 / 272 | 0 |
| (c) Quack | 53.7 | 921 / 1,407 / 1,694 / 3,891 | 53.3 | 15.9 / 57.0 / 459 | 0 |

**Stable** means no errors, no overflows (the sampler's counter was flat at
2,122 for all 300 s), 31-34 established connections throughout, and server
CPU steady at ≈3,140% (`logs/quack-sampler.log`). The `(b)` row ran after the
Quack run, in the same results dir.

This comparison is **not like-for-like**, and it should not be read as
"Quack reads faster":

1. **Write rate.** Over Quack the writer does 53 tx/s (15.9 ms each: 3
   connections plus contention with 32 readers on the same server CPUs). In
   process it does 134 tx/s. Every write invalidates every reader's cached
   store, so (b)'s readers reopen 2.5x as often.
2. **WAL growth, a finding of its own.** With 32 readers always holding a
   snapshot, SQLite's passive `wal_autocheckpoint=32` never gets to restart
   the log (`crates/sqlite/db/src/db.rs`). So the WAL grows for the whole run,
   by 3.7 MB/s at 50 tx/s (`rerun/wal-growth.txt`: 0 → 442 MB in 120 s).
   `journal_size_limit` only applies after a checkpoint resets the WAL. The
   300 s runs left a 1.37 GB WAL (Quack) and a 2.2 GB WAL (in-process, with
   2.5x the writes). Readers read through that WAL. On the Quack run's final
   store, single-client ref_traversal takes **11.4 s**. After `PRAGMA
   wal_checkpoint(TRUNCATE)` the same store takes **0.50 s**
   (`rerun/wal-probe.txt`). So (b)'s reads carried a WAL about 1.6x as
   large, and the store's read path degrades for as long as readers never
   pause. The WAL also survives the server: it was still 442 MB after
   `stop.sh`.
   - This is a store property. It affects the in-process extensions equally
     and probably the embedded backend under the same load. It is also why
     every rerun here (`rr-*` copies) started from a checkpointed store.
   - Fix direction, **not done here**: after N commits or M bytes of WAL,
     the writer runs `wal_checkpoint(RESTART)` or `TRUNCATE` with a busy
     timeout, or a reader pause is forced.

## Server holds one store connection per HTTP worker thread

The cache is thread_local. The server's HTTP pool hands successive requests
to different threads, so one sequential client leads the server to open the
store once per thread it lands on: 1, 10, 49, 125 and 128 open handles on the
store file after 1, 10, 50, 200 and 500 calls
(`rerun/server-store-handles.txt`). That is bounded by the pool size (~128)
and is harmless for reads. It does mean 128 SQLite connections and page
caches per store per server, and on every write all 128 go stale and reopen
lazily. A process-wide cache (per path, shared across threads) would fit the
server better. Not done here.

## Gate (floki tree `43d0d8fd` + uncommitted CHANGELOG.md, rsynced to the box)

| step | result |
|---|---|
| `cargo build --release -p mentat_sqlite_ext` | ok |
| `cd crates/duckdb && make release` | ok |
| `crates/sqlite/ext/test/smoke.sh` (`EXT=target/release/libmentat_sqlite`) | PASS |
| `crates/duckdb/test/smoke.sh` (`DUCKDB=~/duckdb EXT=build/release/…`) | PASS |
| `cargo clippy -p mentat_sqlite_ext -p mentat_duckdb -- -D warnings` | clean |

Logs are in `gate/`. Both smoke scripts default to the **debug** build
(`target/debug`, `build/debug`), so after the release builds the gate points
`EXT` at the release artefacts. The first duckdb smoke attempt without `EXT`
failed with "build/debug/mentat.duckdb_extension not found": harness only, no
code fix needed. `mentat::options_from_json`, schema v3 and the store cache
build, lint and smoke clean together.

**Quack verification** (`gate/quack-verify.txt`, `scripts/qverify.sh`, the
gate build under `serve.sh` on port 9700):

- A remote `edn_t` (schema, then two entities) and a remote `edn_q` return
  `Alice`, `Bob`. The same `edn_q` joined against a client-local table gives
  `Alice|30`, `Bob|41`, from a client that never loaded mentat.
- A wrong token fails with `Invalid Input Error: Authentication failed`
  (exit 1). No token fails with `Invalid Input Error: Could not find a Quack
  authentication token` (exit 1).
- `ATTACH` with a wrong token fails with `Invalid Input Error: Authentication
  failed`. With the right token it exposes the server's tables (`SELECT *
  FROM r.t` → 42) but not its functions: `r.edn_q(…)` → `Catalog Error:
  Table Function with name edn_q does not exist!`. This matches the README.
- `stop.sh` leaves no listener behind.

## Files

- `timings.csv`, `raw.csv`, `checks.txt`, `env.txt`, `loads.csv`,
  `sizes.txt`, `run.log`: the original E4 run (`scripts/e4.sh`). Its
  `report.py` output is `report.md`.
- `threeway.md` comes from `python3 scripts/threeway.py
  ../ext-cache-2026-09-27T214617Z/baseline ../ext-cache-2026-09-27T214617Z .
  ../ext-cache-2026-09-27T214617Z/nocache-timings.csv`.
- `logs/`: the sampler (5 s: threads, CPU, established connections,
  overflow counters) and the three server logs.
- `rerun/`: the backlog/thread A-D matrix, the single-client backlog test,
  the protocol probe, the same-store A/B, fresh-process timings, the
  120 s WAL-growth run, the WAL probe, and server store handles.
- `gate/`: the gate logs and the Quack verification.