comparison

Daniel asked to try several databases and compare them (SR-64). This page states the candidates, what is measured, and the results as they come in. The comparison runs the same conformance tests and the same benchmarks (Benchmark Plan B-03 to B-06, B-11) on each backend behind sophia_store::Backend. Until the numbers say otherwise, SQLite is the default (S-ADR-0005 SQLite first behind a store trait).

Sources: SQLite (sqlite.org; rusqlite with bundled); redb (github.com/cberner/redb); DuckDB (duckdb.org; the duckdb crate with bundled); LMDB (Chu, MDB: A Memory-Mapped Database and Backend for OpenLDAP, 2011; the heed crate); LadybugDB (Moonkale research/Database Backends.md); State of the Art - Graph Databases for Code. code: crates/sophia-store-*.

Candidates

BackendModelEmbedded?PlatformsQuery languageExpected strengthExpected weakness
SQLiterelational, B-tree, WALyesall, including Android and iOSSQL, WITH RECURSIVEtransactional small writes; ubiquity; views for queriesrecursive graph queries; a single writer
redbtyped key-value, copy-on-write B-tree, pure Rustyesall (no C toolchain)none (Rust API)hash lookups; no FFI; simple buildno query language: SQL views must be emulated
DuckDBcolumnar, vectorisedyesLinux, macOS, Windows (Android unclear)SQLanalytics and scans (SR-66 precompilation statistics)many small transactional writes
LMDB (heed)memory-mapped B+treeyesLinux, macOS, Windows, Androidnone (key-value)very fast reads, zero-copyone writer; fixed map size
LadybugDBproperty graphyesLinux only for now (Moonkale P-144)Cyphermulti-hop graph queriesplatform coverage
HelixDB, FalkorDB, TypeDB, TursoDBgraph and vector; graph (Redis); typed; SQLite-compatiblemixedmixedvarious—server-shaped or young; considered after the first four

Results

Each run adds a dated table per benchmark and a one-paragraph conclusion. A backend that fails the conformance suite is recorded here with the failing test.

2026-10-20: SQLite and redb (M1)

Both pass the conformance suite (14 tests each). The full tables are in Benchmark Results.

SQLiteredb
ingest at 10⁵ (defs/s)9 20012 400
record by id at 10⁵4.5 µs1.4 µs
closure of 5 713 definitions22.8 ms7.5 ms
bytes on disk per definition6 80010 800 (no compaction)
query languageSQL views, enforced read-onlynone
buildC (bundled), every platformpure Rust, every platform

Interim conclusion: redb is faster at everything the editor does per keystroke. SQLite is smaller, queryable, and already in Moonkale. SQLite stays the default for now, because the sql dialect and Moonkale’s familiarity matter more than 3 µs per lookup. That is only a defensible default until DuckDB and LMDB are measured (M2), and until real code (not synthetic) is measured.

2026-10-20 (evening): under Sophia’s own query model

The forwarded review asked for a comparison of SQLite, LMDB and redb under the proposed query model: Sophia’s own algebra on top, backends as access paths (SPEC-007 Sophia Query Language, S-ADR-0011 Sophia has its own query algebra). DuckDB is included for completeness. Numbers are B-17 at 10⁴ definitions (Benchmark Results).

SQLiteredbLMDBDuckDB
Sophia’s query languageyes, native executoryes, nativeyes, nativeyes, native
the same query as SQLyes (WITH RECURSIVE over base tables; mutual recursion refused)——not yet (§11 of SPEC-007)
access paths for the 8 base relationsall by index (t_ref both ways, kind, name target)all; refs_of decodes the record (no forward-reference table)as redball by index, but each point query costs ~0.2 ms
set-at-a-time scans (all_kinds, all_refs)SQLindex-table scansindex-table scansSQL
transitive dependants, magic sets (per probe)0.171 ms native, 0.167 ms SQL0.180 ms0.132 ms18.8 ms
transitive dependencies (per probe)0.97 ms native, 0.84 ms SQL1.22 ms1.00 ms146 ms
count by kind10.1 ms native, 4.6 ms SQL5.4 ms4.8 ms7.6 ms
unreferenced definitions (negation)19.9 ms native, 2.7 ms SQL13.6 ms8.3 ms2.0 s
names by prefix3.8 ms native, 0.44 ms SQL3.5 ms3.0 ms6.4 ms
zero-copy readsno (copies out of its pager)possible (AccessGuard)yes: 0.21 µs borrowed against 0.87 µs copied and decoded (Zero-Copy Storage of Canonical Graphs)no
writersone (WAL; readers concurrent)oneone (lock-free readers)one

At 10⁵ (SQLite, redb, LMDB):

  • transitive dependants per probe: LMDB 0.37 ms; redb 0.49 ms; SQLite 0.64 ms native, 0.61 ms SQL;
  • count by kind: LMDB 51 ms, redb 65 ms; SQLite 106 ms native, 57 ms SQL;
  • unreferenced definitions: LMDB 99 ms, redb 168 ms; SQLite 310 ms native, 32 ms SQL.

What this says for the backend choice:

  • The query language no longer decides it. LMDB, with no query language of its own, runs Sophia’s queries fastest of the native backends.
  • SQLite has one advantage left: a second, independent executor. Its SQL engine wins set-at-a-time workloads by 2–9× today, and it cross-checks the native executor in the tests. Most of that gap is in Sophia’s executor (tuple-at-a-time, no prefix access path), not in the storage. LMDB’s native count by kind is already about equal to SQLite’s SQL.
  • Goal-directed evaluation matters more than storage: 4.5 orders of magnitude, against 1.3× between the best and worst primary candidates.
  • Still open for the decision (S-ADR-0010 Primary backend and an analytics replica):
    • B-15, edit latency through Moonkale;
    • B-16, real code;
    • LMDB’s single writer under concurrent agents (SR-104).

2026-10-20 (later): all four candidates (M2)

All four pass the conformance suite and the interface acceptance tests. Full tables are in Benchmark Results.

SQLiteredbLMDBDuckDB
ingest at 10⁵ (defs/s)7 70010 30017 600~100 (10⁴)
record by id at 10⁵4.9 µs1.3 µs0.9 µs~200 µs (10⁴)
closure of 5 713 definitions26.9 ms9.2 ms8.0 ms—
bytes per definition at 10⁵6 80010 8008 60013 800 (10⁴)
analytics (join, scan)12–90 ms——4 ms
query languageSQL, view-restricted guardnonenoneSQL, structural guard
buildC, bundledpure RustC (small), bundledC++, large bundled build
limits found—file grows without compaction511-byte keys; fixed map size; one writer; no empty keysreserved words (by, at); no negative LIMIT; slow small transactions

Recommendation (S-ADR-0010 Primary backend and an analytics replica, since accepted in an amended form):

  • SQLite stays the default for Moonkale now. It is queryable, the smallest, and already shipped.
  • LMDB is the performance candidate, to be re-measured on real code (B-16) and on edit latency (B-15) before any switch.
  • DuckDB becomes a bulk-loaded analytics replica, never the primary store.

Bugs the second pair of backends exposed:

  • LMDB rejects a range scan from the empty key.
  • DuckDB rejects LIMIT -1, which SQLite silently accepted as “no limit”.

Both are fixed in the backends, and the conformance suite now lists without a limit from the empty prefix on every backend, so the same mistake cannot recur.

Lesson recorded: the first SQLite layout (WITHOUT ROWID for multi-KB rows) was 4× slower than the corrected one. A backend comparison is only as good as each backend’s layout, so each layout choice is written down in its file note (sqlite, redb_store).