DuckDB extension: datoms in DuckDB (1.11) vs a SQLite file (1.10.3)
Same dataset (benchmarks/scale gen.py), same extension calls, one client,
release builds, DuckDB v1.5.6 Python client, EC2 c7i.8xlarge (32 vCPU, Xeon
8488C, 61 GiB). 1.10.3’s extension keeps its datoms in a SQLite file (run
with an in-memory DuckDB); 1.11’s keeps them in tables of a persistent DuckDB
database. Driver: ab.sh (SC=xs|s); tables: abtab.py. Correctness is
checked separately (tests-harness differential test vs SQLite: 0
disagreements), so these are timings only.
xs (~0.1M datoms)
| scenario (p50 ms, 1 client) |
1.10.3: datoms in a SQLite file |
1.11: datoms in DuckDB |
new / old |
|---|
| point_lookup |
0.58 |
2.79 |
4.78 |
| ref_traversal |
2.62 |
6.80 |
2.60 |
| aggregate |
3.02 |
3.68 |
1.22 |
| predicate_scan |
3.47 |
9.97 |
2.87 |
| pull |
0.55 |
2.98 |
5.43 |
| as_of |
8.77 |
19.98 |
2.28 |
| since |
1.26 |
2.34 |
1.85 |
| input_bindings |
0.92 |
4.90 |
5.33 |
| bulk load (s) |
2.3 |
5.7 |
2.51 |
| single-datom transact (tx/s) |
186 |
66 |
0.36 |
| store size (MB) |
21 |
16 |
0.74 |
s (~1M datoms)
| scenario (p50 ms, 1 client) |
1.10.3: datoms in a SQLite file |
1.11: datoms in DuckDB |
new / old |
|---|
| point_lookup |
0.60 |
3.23 |
5.40 |
| ref_traversal |
22.39 |
10.37 |
0.46 |
| aggregate |
30.15 |
5.53 |
0.18 |
| predicate_scan |
28.89 |
40.43 |
1.40 |
| pull |
0.56 |
3.83 |
6.85 |
| as_of |
88.57 |
25.46 |
0.29 |
| since |
5.25 |
2.73 |
0.52 |
| input_bindings |
1.12 |
8.78 |
7.82 |
| bulk load (s) |
177.4 |
62.7 |
0.35 |
| single-datom transact (tx/s) |
182 |
54 |
0.29 |
| store size (MB) |
213 |
157 |
0.74 |
Reading it
- Faster on DuckDB at 1M datoms: queries that touch many datoms.
Aggregates 5.5x, as-of 3.5x, ref traversal 2.2x, since 1.9x, and bulk load
2.8x. The store is a quarter smaller. These get better with scale, because
DuckDB scans columns and the SQLite store probes its indexes row by row.
- Slower on DuckDB: point lookups, pull and
:in bindings, by about
3-8 ms each, and single-datom transactions (~55 tx/s vs ~180). These are
per-statement costs. Each call runs several statements (a schema-generation
check, the query, and for pull the attribute fetch), and DuckDB plans and
runs each in ~0.2-1 ms, where SQLite answers an indexed lookup in
microseconds. DuckDB can’t index the value column (a UNION) and doesn’t use
its ART indexes for these joins, so a value lookup is a scan of the
attribute’s rows.
- Two optimizations got it here, both measured at xs:
- Caching each store’s schema per thread (per-call overhead 4.4 -> 2.1 ms).
- Plain typed copies of the value (
v_i/v_d/v_s) for filters, joins
and comparisons. Ref traversal went 12.1 -> 6.8 ms; on the UNION alone,
DuckDB took 16 ms for a join that takes 3.3 ms on plain columns.
- Not done (possible next steps): batch a call’s statements; a connection
pool so calls run concurrently (they are serialized on one connection
today); clustering
datoms by (a, e) for zone-map pruning (2.6 vs
3.3 ms on the ref traversal probe).
