DuckDB
An in-process analytical SQL database: columnar, vectorized, one file, zero servers.
DuckDB is an embedded OLAP database — what SQLite is to OLTP. It runs in-process, stores everything in a single columnar file, executes queries over fixed-size vector batches, and speaks plain SQL with Postgres-style semantics. No server, no daemon, no setup: the library IS the database.
SQLite ergonomics, OLAP engine
No server, no config: import the library, open a file, run analytical SQL. ACID transactions with MVCC snapshot isolation.
Vectorized execution
Operators process fixed 2048-value vectors, keeping data in CPU caches and letting the compiler vectorize inner loops — the engine-level reason for its throughput.
Direct file queries
Reads CSV, Parquet, and JSON files directly as tables — SELECT * FROM "data.parquet" — and exchanges data zero-copy with pandas, Arrow, and R data frames.
Single-file storage
One .duckdb file holds the whole database with ACID semantics. Checkpointing folds the write-ahead log into the file.
Embedded analytics: the SQLite lesson, applied to OLAP
DuckDB proves a server is not required for analytical SQL. Running in-process removes the network hop, serialization layer, and daemon management — queries read the file at memory speed with no round trips.
The engine is columnar and vectorized: columns compress and scan sequentially, and operators consume 2048-value batches. That combination is why aggregates over millions of rows run in milliseconds on a laptop.
SELECT region, sum(bytes) AS total
FROM read_parquet('events/*.parquet')
WHERE day >= DATE '2026-10-01'
GROUP BY region
ORDER BY total DESC;
The execution model is the product
Unlike ClickHouse, there is no cluster story — DuckDB is deliberately single-node, competing on developer experience rather than scale-out. Data skipping comes from zone maps (min/max per block) much like granule pruning in bigger columnar engines.
The walkthrough below runs the columnar execution loop — column pruning, block skipping, batch predicates, mergeable aggregation — the exact mechanics DuckDB implements with its own vectorized operator set.
Diagrams
static storyboard — WebGPU animates when available
A live WebGPU particle field tinted to this exhibit. Plays only with WebGPU and no reduced-motion preference; a static storyboard renders for everyone else.
[caption] Generic WAL/checkpoint timeline — DuckDB applies it to the single-file database image.
Writes append to the WAL; checkpointing folds committed transactions into the .duckdb file. Crash recovery replays the log into the last checkpoint.
[caption] In-process: no wire protocol, no daemon — the library is the server.
SQL binds against the catalog, the optimizer builds a pipeline of vectorized operators, and blocks stream from the single-file columnar storage.
| table | 32 rows × 4 columns |
| granules skipped | 5/8 (primary key range) |
| cells read | 36 of 128 (user_id never read) |
| rows examined | 12/32 |
| rows after predicate | 6 |
| output rows | 3 |
All steps
- Parse the query — SELECT region, sum(bytes) … WHERE day ≥ 21 AND bytes > 1000 GROUP BY region — only day, region, and bytes are referenced. user_id is never read: a row store would carry every column through the operator; here it costs zero bytes.
- Skip granules by primary key range — The sparse primary index stores min/max of day per granule. Granules covering days 1-20 cannot contain day ≥ 21: 5 of 8 granules are skipped without reading a single row — data skipping.
- Read 3 granules × 3 columns — Only referenced columns of surviving granules are read: 12 rows × day/region/bytes = 36 cells (of 128 in the table). Columnar layout makes each read a contiguous scan of one column’s block.
- Evaluate the predicate per block — bytes > 1000 is evaluated block-by-block (vectorized, not row-at-a-time): 6 of the 12 loaded rows pass into the aggregate. Note this filter is independent of the key — key ranges cannot prune it, only the block loop can.
- Aggregate by region — Each block produces partial sums per region; partial states merge up the pipeline. sum() is a mergeable aggregate, so blocks combine without re-touching rows.
- Output — One row per region with its byte total. Rows examined 12/32, granules skipped 5/8, cells read 36, rows out: 3.
Columnar execution: column pruning, key-range granule skipping, block predicates, and mergeable aggregation — ClickHouse-flavored. Teaching model: 8 granules × 4 rows, one part; real engines run many parts with merges in the background and much larger granules.
Generic columnar mechanics on a toy granule layout; DuckDB specifics (2048-vectors, ART indexes, zone maps) live in its docs.
[caption] Generic columnar mechanics on a toy granule layout; DuckDB specifics (2048-vectors, ART indexes, zone maps) live in its docs.
One analytical query traced step by step: column pruning, key-range block skipping, vectorized predicate evaluation, and mergeable aggregation with honest row counters.