The DuckDB backend: the SQLite backend’s tables and views on DuckDB’s columnar engine. Source:
duckdb_store.rs.Sources: DuckDB (duckdb.org),
duckdbcrate 1.10506 withbundled; Database Comparison; S-ADR-0010 Primary backend and an analytics replica. code:duckdb_store.rs
Shape
The file was derived from sqlite and changed where DuckDB differs:
$nparameters;- no
WITHOUT ROWID; starts_with;excluded.in upserts;octet_length.
The schema and the views are otherwise the same, so the same sql queries run on both.
DuckDb::open_read_only (2026-11-18, B-017) opens an existing file in DuckDB’s read-only access mode, on one thread, without touching the schema. DuckDB locks the file, so it fails while another process holds the store for writing. The query worker of bounded uses it; one thread because each thread’s stack counts against the worker’s memory bound.
The query guard
DuckDB has no authorizer. So:
- the text must be one statement;
- it runs as a subquery (
SELECT * FROM (…)), which admits only queries; - it runs inside a transaction that is always rolled back;
- external access (files, extensions) is switched off for the connection at open;
- a timer interrupts it after
max_steps_kmilliseconds; - it may name only the documented views and its own CTEs, checked on the parse tree (since 2026-11-08, see below).
Scalar functions are not checked by name, as SQLite’s authorizer does for readfile; with external access off they cannot reach files. The test tries DELETE, INSERT, two statements, ATTACH, read_csv and a runaway query.
Findings (2026-10-20)
- Reserved words:
byandatare column names in the SQLite schema, and keywords in DuckDB.byis quoted, and the log’satis stored aslogged_at(thelogview still calls itat). LIMIT -1is an error in DuckDB, while SQLite reads it as “no limit”. “No limit” was passed asusize::MAX as i64, which is −1. It is now clamped in both backends.- About 100× slower than SQLite on Sophia’s workload of many small transactions (about 100 definitions/s ingest, about 200 µs lookups), and 3–20× faster on analytical joins and scans. So DuckDB belongs as an analytics replica loaded in bulk (the Appender API), not as the primary store.
- The bundled build compiles DuckDB’s C++ engine (minutes on first build). The backend is behind the
duckdbfeature and is not in Moonkale’s default build.
Access paths (2026-10-20): DuckDB overrides kind_of, refs_of, all_kinds and all_refs with SQL. Without the scan overrides, Sophia’s “count by kind” query made 10⁴ point queries and took 1.9 s; with them it takes 7.6 ms.
Fixes from Gemini’s review, 2026-11-07 (Review 2026-11-07 Gemini (external)); fixed 2026-11-08:
annotation(id)reads by primary key, as in sqlite (S-BUG-02).query_readonlyreads only the views (§5.3). DuckDB has no authorizer, soonly_viewsserialises the query withjson_serialize_sqland walks the tree. The rules:- every table it names must be a documented view or one of its own CTEs, with no schema or catalog;
- a CTE may not shadow a stored table;
- table functions other than
range,generate_seriesandunnestare refused (query_tableandquerywould reach the tables). - This needs the
jsonfeature of theduckdbcrate, added to the workspace dependency: about 3½ minutes more on a fresh build.serde_jsonreads the tree. - Test:
duckdb_queries_are_read_only_capped_and_timedchecks 11 refused and 4 allowed queries, and fails without the check.
- The time budget waits on a
Condvar(S-PERF-01). It used to poll every 2 ms, and a query waited for the poll to see it was done. Measured with 500 queries ofSELECT path FROM nameson an empty store, release build, three runs each (a temporary test, deleted): 2.10–2.12 ms per query before, 0.67–0.70 ms after, including the two extra queries of the view check.
Fixes from Mistral’s review of v0.1.9 (2026-11-08, Review 2026-11-08 Mistral v0.1.9 (external)):
- The timer is a drop guard (
Timer). It used to be stopped only at the end ofquery_readonly. Whenconn.transaction()failed, or the code panicked, the thread lived for the whole budget and then interrupted whatever query ran next on the connection. Now dropping the guard wakes and joins the thread on every return path. No test makestransaction()fail, so this path is untested. - The row cap is clamped to
i64::MAX.max_rows + 1overflowed forusize::MAX, and DuckDB refuses aLIMITabovei64::MAX. Test: the same test queries withmax_rows: usize::MAX. Without the clamp, a debug build panics on the overflow.
Memory (Fable’s review, 2026-11-10, Review 2026-11-10 Fable (external), S-3):
query_readonlyruns underSET memory_limit = '512MiB'and resets it afterwards. DuckDB’s limit belongs to the database, so bulk loads are not bounded by it.- A value over
max_value_bytesis refused. - The result stops at
max_bytes.
Test: duckdb_queries_are_read_only_capped_and_timed, which aggregates 2 GB of strings. It fails without the memory limit, and it fails without the byte budget.