DDIA Chapter 3: Storage & Retrieval — Why We Replaced SQLite With DuckDB

Chapter 3 of Designing Data-Intensive Applications by Martin Kleppmann is titled "Storage and Retrieval." It sounds like a dry chapter on first pass — hash indexes, B-trees, SSTables. But when I reread it recently, I kept thinking about a decision we made in ClawMetry: replacing SQLite with DuckDB as our primary data layer.

That decision looks obvious in hindsight. Before we made it, though, we kept reaching for SQLite by default — everyone does. DDIA Chapter 3 is the best explanation I've found for why that instinct is wrong for analytics workloads. Here's how the chapter maps to our actual architecture.

The two questions DDIA Chapter 3 asks

Kleppmann opens Chapter 3 with a deceptively simple question: what happens inside a storage engine when you write and then read data? He works through two families of answers:

• B-tree indexes (what SQLite, PostgreSQL, and most OLTP databases use): great for random point lookups ("give me the row with id=123"), terrible for scanning large datasets while reading only a few columns out of many.
• Column-oriented storage (what DuckDB, Redshift, and Parquet use): poor for random point lookups, exceptional for analytical queries that scan millions of rows but care about only 2–3 columns.

The insight he hammers home: your storage engine's design should match your query shape. Optimizing for random access (B-trees) and optimizing for sequential column scans (column stores) involve fundamentally different tradeoffs in disk layout, compression, and execution. Pick wrong and you pay for it on every single query.

What agent analytics actually look like

When we first built ClawMetry, we stored everything in SQLite. Session transcripts, token counts, tool calls, cron job runs, subagent outcomes. It worked fine under a few hundred sessions.

Then users started running 24/7 agents. One power user reported their ClawMetry dashboard took 18 seconds to load the "Token Usage by Model" chart for the last 30 days. I pulled up the query running against SQLite:

SELECT model, SUM(input_tokens), SUM(output_tokens), COUNT(*)
FROM session_events
WHERE timestamp > datetime('now', '-30 days')
GROUP BY model;

SQLite was reading full rows to answer this query — including the complete session transcript JSON blob, sometimes 50KB per row — just to aggregate three integer columns. On a table with a few million rows, that's an enormous amount of wasted I/O.

This is exactly the OLAP vs. OLTP split DDIA describes. Our common queries looked like:

• "Show me total tokens by model for the last 7 days" — full scan, 2 columns, group by model
• "What's my 95th-percentile session cost this month?" — full scan, 1 column, percentile aggregate
• "Which tool calls failed most often?" — full scan, 2 columns, group by tool name
• "How many tokens did this subagent chain burn?" — filtered scan, 3 columns

Every one of these is an analytical query. None of them are point lookups. We had built our data layer around a B-tree-optimized engine for a workload that needed column scans. DDIA explains exactly why that hurts, and why the hurt gets worse as data grows.

Why DuckDB fit the shape

DuckDB is a column-oriented analytical database. Instead of storing data row by row, it stores data column by column: all model values together, all timestamp values together, all input_tokens values together. For our token aggregation query, DuckDB reads three columns off disk and skips everything else.

The performance difference was immediate. That same 30-day aggregation dropped to well under a second on the same dataset — a workload that had been timing out for users on slower hardware.

DuckDB also handles JSONL ingestion natively, which matters for our ingest path. Agent sessions are append-only JSONL files on disk. DuckDB reads them without an intermediate parsing loop:

conn.execute("""
    INSERT INTO session_events
    SELECT * FROM read_json_auto('/path/to/sessions/*.jsonl')
    WHERE timestamp > ?
""", [last_sync_ts])

This maps to what DDIA calls "log-structured storage": our data is naturally append-only. Sessions accumulate events and never update them in place. Column stores are designed for exactly this pattern — they compress and scan appended column data efficiently, and DuckDB's vectorized execution engine processes batches of values rather than row-by-row iteration.

The single-writer lock — DDIA's concurrency lesson applied

Here's the part DDIA Chapter 3 helps clarify that isn't obvious from the DuckDB marketing page: DuckDB doesn't support concurrent writers. One process holds the write lock at a time.

This isn't a bug. DDIA explains why it's fundamental. Column-oriented stores achieve their performance partly through aggressive compression: run-length encoding, delta encoding, dictionary compression across column values in sequence. Concurrent writes from multiple processes would break this layout — you'd need row-level locks that fight the very data organization that makes column stores fast. The column-store design and the single-writer constraint are two sides of the same architectural coin.

So we made it explicit: the ClawMetry sync daemon owns the DuckDB writer lock. It's the only process that writes. The Flask dashboard and all API handlers go through a lightweight localhost proxy server (clawmetry/local_server.py) that opens read-only connections against the same file.

# sync daemon (only writer)
# clawmetry/sync.py owns the DuckDB write connection
# all ingest — JSONL, gateway WebSocket, OTLP — flows through it

# dashboard + route handlers (read-only)
# route reads through local_server.py at /__local_query__/<method>
# no write lock acquired; returns JSON over localhost HTTP

DDIA calls this the single-leader pattern for writes: you get consistency (no write conflicts) at the cost of write throughput (one writer at a time). For ClawMetry's workload — a background daemon syncing in batches every few seconds — this is the right tradeoff. We're not doing sub-second transactional writes. We're ingesting append-only agent events and serving analytic reads continuously.

What we kept SQLite for

We didn't throw SQLite away. The optional history.py module — which polls the gateway every 60 seconds and stores aggregate time-series summaries — still uses SQLite.

Why? Because that workload is different. History stores one summary row per minute per metric. The queries are narrow range lookups: "give me the last 24 hours of token-per-minute for this model." That's a B-tree-friendly access pattern, not a wide column scan. SQLite handles it perfectly. We kept the right tool for the right job.

DDIA's chapter actually makes this explicit: different storage engines exist because different query patterns have genuinely different optimal designs. The mistake is assuming one engine covers everything. It doesn't, and the two workloads we had — analytic aggregation (DuckDB) and time-series range lookup (SQLite) — sit at opposite ends of that spectrum.

What we gave up

Honest accounting:

• Ecosystem maturity. SQLite has a 20-year track record. DB Browser for SQLite is an excellent debugging tool. DuckDB's tooling ecosystem is newer, though it has grown quickly.
• Per-row ACID granularity. SQLite gives you fine-grained row-level transactions via WAL mode. DuckDB gives you table-level serializability. For append-only ingest this doesn't matter in practice, but it's a real architectural difference worth understanding.
• Dependency weight. SQLite ships with Python's standard library — zero extra install. DuckDB is an additional pip install with a larger binary footprint. We added a dependency.
• Concurrent reader-writer model. SQLite supports concurrent reads alongside a single writer using WAL mode. DuckDB's concurrent read access works, but requires the proxy architecture described above. There's more machinery to operate.

We made these tradeoffs deliberately. For a tool that exists to give visibility into AI agent costs over time, analytical query performance isn't a nice-to-have — it is the product. A dashboard that takes 18 seconds to load is a dashboard nobody opens.

Token usage by model over the last 30 days — a typical analytical query that DuckDB handles in under a second on months of agent history

The meta-lesson from DDIA

Kleppmann's Chapter 3 is really about one thing: the mismatch between your query shape and your storage engine's design is the root of most performance problems at scale.

When you're building an observability system, ask this early: are my reads analytical scans or point lookups? If 80% of your queries are "aggregate this column over this time window, grouped by that dimension," a row-store is fighting you the whole way. The B-tree is optimizing for access patterns you don't have.

For agent observability specifically, the answer is almost always "analytical." Sessions accumulate as append-only events. Queries aggregate over time ranges. Models, costs, and tool calls are dimensions to group by, not rows to fetch by ID. Pick the storage engine that matches that shape from day one — not after you hit 18-second dashboard loads and start debugging queries that were always going to be slow.

We picked DuckDB. DDIA Chapter 3 explains why that was the right call.