Analytics (DuckDB / HTAP)

The idea: two engines, one floor

The deciding rule (spec 12) is that you add analytics by composing a second engine, not by bloating the OLTP engine with a columnar path. Twill DB keeps its row engine focused on transactions. DuckDB — an off-the-shelf analytical engine — reads an open columnar snapshot of the same data. The only code you own is the materialization job: the glue that publishes the snapshot.

  writes ──▶  Twill row engine (OLTP)  ──┐
                                          │ materialization job (thin glue we own)
                                          ▼
                         Parquet snapshot on shared store  ◀── DuckDB (OLAP) ──▶ aggregations

Materialize a snapshot

The @twilldb/bun/olap helper snapshots a table to an open columnar file, publishing atomically (write to a temp file, then rename) so a reader only ever sees a whole snapshot.

import { open } from "@twilldb/bun";
import { materialize } from "@twilldb/bun/olap";

using db = open("file://./app.db"); // or s3://bucket/app — snapshot lands on the same store

db.exec("CREATE TABLE events (id INTEGER PRIMARY KEY, kind TEXT, amount REAL)");
db.exec("INSERT INTO events VALUES (1,'click',1.5),(2,'view',0.0),(3,'click',2.5)");

const snap = materialize(db, { table: "events", dir: "./olap", format: "parquet" });
// → { path: "./olap/events.parquet", rows: 3, format: "parquet" }

Parquet is the production format. DuckDB is also the off-the-shelf writer the job shells out to (COPY … TO … (FORMAT PARQUET)); when DuckDB is not installed the job falls back to CSV, which DuckDB still reads via read_csv_auto — so a snapshot is always publishable, only the encoding changes.

Query it from DuckDB

DuckDB reads the snapshot unmodified — no import step, no second copy of the schema:

duckdb -c "SELECT kind, count(*) AS n, sum(amount) AS total
           FROM './olap/events.parquet' GROUP BY kind ORDER BY kind"

The aggregation runs entirely in DuckDB over the columnar snapshot; the row engine is never on this path. A runnable end-to-end version is clients/bun/examples/duckdb-olap.ts (bun run examples/duckdb-olap.ts).

Configurable cadence

Snapshots refresh on a cadence you choose. Materializer wraps materialize with a configurable interval; the row engine keeps serving OLTP while snapshots refresh in the background, and DuckDB always reads the last whole snapshot.

import { Materializer } from "@twilldb/bun/olap";

const m = new Materializer(db, { table: "events", dir: "./olap", cadenceMs: 60_000 });
m.start();   // publish now, then every 60s
// … later …
m.stop();

Pick the cadence by how fresh the analytics need to be versus how often you want to pay the snapshot cost — seconds for near-real-time dashboards, minutes or hours for periodic reporting.

Scope

Related