4. The vectorised execution engine
DuckDB · 4. The vectorised execution engineThis is the chapter that transfers. Every analytical engine on this site — ClickHouse, Spark, Trino, every warehouse — implements the ideas in this chapter. DuckDB implements them in one process where you can see all of them at once.
Three ways to run a query
Section titled “Three ways to run a query” 1. ROW AT A TIME (the "Volcano" model)
every operator has next(). filter.next() calls scan.next() which returns ONE ROW.
→ 1 billion rows = 1 billion virtual function calls per operator. The interpretation overhead dwarfs the actual work. → SQLite, Postgres (mostly), MySQL
2. WHOLE COLUMN AT A TIME
each operator processes an ENTIRE column, materialises the result, passes it on.
→ no per-row overhead at all → but a 10 GB column produces a 10 GB intermediate that does not fit in cache, or in RAM → MonetDB, early column stores
3. VECTOR AT A TIME ← DuckDB
each operator processes a CHUNK of about 2048 values.
→ per-row overhead amortised over 2048 → the chunk FITS IN L1/L2 CACHE → the sweet spot, and it is not closeThe 2048 is the whole idea. It is large enough that the cost of one virtual call, one branch, one loop setup is divided by 2048 and disappears. It is small enough that the working set of a whole operator pipeline stays in CPU cache, so the data is not making round trips to RAM between operators.
COST PER ROW OF PROCESSING
1 row/call ████████████████████ interpretation dominates
2048/call █ ~free
1M/call ███ cache misses dominateThis is the finding of the “MonetDB/X100: Hyper-Pipelining Query Execution” paper from CWI in 2005 — the same research group DuckDB came out of. DuckDB is, in a real sense, that paper packaged as a library.
What a vector actually is
Section titled “What a vector actually is” A DataChunk — what flows between operators
┌─────────────────────────────────────┐ │ count: 2048 │ │ │ │ vector[0] "country" │ │ type: VARCHAR │ │ validity mask: 2048 bits │ │ data: ──────────────────────── │ │ │ │ vector[1] "amount" │ │ type: DOUBLE │ │ validity mask: 2048 bits │ │ data: [12.5][99.0][3.2]... │ └─────────────────────────────────────┘The validity mask is how NULL works — one bit per value, separate from the data. This means a NULL costs one bit rather than a sentinel value or a nullable wrapper, and a whole vector with no NULLs can be processed by a loop that never checks for them at all. That check-once-per-vector rather than once-per-row pattern is everywhere in the engine.
Vectors come in several physical layouts, and knowing them explains several performance surprises:
FLAT 2048 values, laid out contiguously. The default.
CONSTANT ONE value, meaning "all 2048 rows have this". A literal, or a column the optimiser proved constant. Operations on it do 1 unit of work, not 2048.
DICTIONARY an index vector plus a shared child vector. A join or a filter can produce this without copying any strings.
SEQUENCE a start and an increment. `range(0, 2048)` stored in sixteen bytes.Operations stay in the compact form as long as they can. amount * 1.2 where amount is dictionary-encoded multiplies the dictionary, not the 2048 rows. This is the same “compute on the compressed representation” idea as FSST in chapter 3, and it recurs constantly.
Selection vectors — filters that do not copy
Section titled “Selection vectors — filters that do not copy” WHERE amount > 100
NAIVE: allocate a new vector, copy the matching values into it.
DUCKDB: produce a SELECTION VECTOR — a list of the positions that matched.
data: [12][150][3][900][45] selection: [1, 3] count: 2
The data vector is UNCHANGED. Downstream operators read through the selection.No copying at all. A chain of five filters produces one selection vector, narrowed five times, over the original data. This is why filters in DuckDB are close to free and why pushing them down (chapter 9) is so effective.
Push, not pull
Section titled “Push, not pull”Older engines are pull-based: the top of the plan calls next() and the call cascades down. DuckDB moved to a push-based model, and the difference matters for two specific things.
PULL PUSH
result.next() scan reads a chunk → aggregate.next() → hands it to filter → filter.next() → hands it to project → scan.next() → hands it to reads one chunk aggregate
control flows DOWN, control flows UP, data flows UP data flows UP
✗ hard to parallelise ✓ many threads can — the top has to push independent coordinate chunks ✗ awkward for operators ✓ an operator that with multiple inputs needs both sides (joins, unions) just receives from bothThe practical payoff is parallelism. In a push model, the scan is the driver — and if the scan can hand different chunks to different threads, the whole pipeline above it parallelises with no coordination.
Pipelines and pipeline breakers
Section titled “Pipelines and pipeline breakers”A query plan is cut into pipelines at every operator that cannot pass data straight through.
SELECT country, sum(amount) FROM orders JOIN customers USING (cust_id) WHERE ts >= '2026-01-01' GROUP BY country;
PIPELINE 1 (must finish first) scan customers → BUILD the hash table ▲ pipeline breaker: nothing comes out until everything has gone in
PIPELINE 2 (runs after 1) scan orders → filter ts → PROBE the hash table → feed the aggregate hash table ▲ another breaker
PIPELINE 3 (runs after 2) read the aggregate hash table → outputA pipeline breaker is any operator that must see all its input before producing output:
─ the BUILD side of a hash join ─ a hash aggregate (GROUP BY) ─ ORDER BY ─ a window function's sort ─ DISTINCTStreaming operators — filter, projection, the probe side of a join — are not breakers, and a run of them fuses into a single pass with no intermediate materialisation.
This is the vocabulary for reading a profile. When chapter 7 traces a query and chapter 8 reads an EXPLAIN ANALYZE, “which pipeline is slow” is the first question, and “is this operator a breaker” is what tells you whether it is holding memory.
Morsel-driven parallelism
Section titled “Morsel-driven parallelism”Here is where DuckDB uses all your cores, and the scheme is elegant enough to be worth the detail.
The naive approach is to split the input into N pieces for N threads. It fails for an obvious reason: pieces are not equally expensive, so seven threads finish and wait for the eighth.
Morsel-driven parallelism — from a 2014 paper by Leis and others on the HyPer system — inverts it.
The table is divided into MORSELS. In DuckDB, a morsel is a row group — 122,880 rows.
┌──┬──┬──┬──┬──┬──┬──┬──┬──┬──┬──┬──┐ │M0│M1│M2│M3│M4│M5│M6│M7│M8│M9│..│..│ └──┴──┴──┴──┴──┴──┴──┴──┴──┴──┴──┴──┘ a shared, atomic work queue
thread 1: take M0. done. take M4. done. take M7... thread 2: take M1. (slow one) ......... take M8... thread 3: take M2. done. take M5. done. take M9... thread 4: take M3. done. take M6. done. take M10..
→ NO thread waits for another. → A slow morsel delays ONE thread, not the query. → Threads that finish early simply take more work.It is work-stealing without the stealing — a single atomic counter over a shared queue. Each thread runs the entire pipeline for its morsel: scan, filter, project, probe, and push into a thread-local hash table for the aggregate. Only at the end are the thread-local tables combined.
thread 1 → local hash table 1 ─┐ thread 2 → local hash table 2 ─┼→ COMBINE → thread 3 → local hash table 3 ─┤ final thread 4 → local hash table 4 ─┘ resultThread-local state and one merge at the end is why DuckDB scales nearly linearly with cores on aggregations. There is no lock on the shared hash table, because until the combine step there is no shared hash table.
The consequence for you: parallelism needs enough row groups. A table of 100,000 rows is one row group, which is one morsel, which is one thread — no matter how many cores you have. This is why DuckDB’s advantage over pandas can look modest on small data and enormous on large.
Where the parallelism comes from for files
Section titled “Where the parallelism comes from for files”For a Parquet scan, the morsel is a row group in the Parquet file. For a CSV, DuckDB parallelises by splitting the file into byte ranges and having each thread find the next record boundary — which works because CSV is (usually) line-oriented.
A gzipped CSV cannot be split, because you cannot start decompressing in the middle. That is a common and invisible reason a query is single-threaded. Chapter 5 says more; .csv.gz is one of the quiet performance traps.
Reading it in the profile
Section titled “Reading it in the profile”PRAGMA enable_profiling;PRAGMA profiling_mode = 'detailed';SELECT country, sum(amount) FROM orders GROUP BY 1;What you get back, per operator: timing, rows in, rows out, and the cardinality the optimiser estimated versus what actually happened.
┌───────────────────────────┐ │ HASH_GROUP_BY │ │ country │ │ sum(amount) │ │ ───────────────────── │ │ 194 rows │ │ (0.31s) │ ← 82% of the query └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ FILTER │ │ (ts >= ...) │ │ ───────────────────── │ │ 18,204,882 rows │ │ (0.04s) │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ TABLE_SCAN │ │ orders │ │ ts, country, amount │ ← only 3 columns │ ───────────────────── │ │ 41,000,000 rows │ │ (0.02s) │ └───────────────────────────┘Three things to read every time:
- Which operator holds the time. Almost always a join, an aggregate or a sort — the breakers.
- Where the row count collapses. If the filter cuts 41 million to 18 thousand, you want that filter as low as possible — ideally pushed into the scan (chapter 9).
- Estimated versus actual rows. A big divergence means the optimiser chose a plan for a query it misunderstood, and that is the usual cause of a mysteriously slow join (chapter 8).
What to take from this chapter
Section titled “What to take from this chapter”- Three execution models: row-at-a-time (interpretation dominates), column-at-a-time (intermediates blow the cache), and vector-at-a-time, which is the sweet spot. 2048 amortises the overhead and still fits in CPU cache.
- It comes straight from the MonetDB/X100 paper (CWI, 2005) — DuckDB is that research packaged as a library.
- A vector carries a validity mask (one bit per NULL) and may be flat, constant, dictionary or sequence — and operations stay in the compact form, computing on the dictionary rather than the rows.
- Filters produce selection vectors, not copies. Five chained filters narrow one list of positions over untouched data.
- Push-based execution makes the scan the driver, which is what makes the whole pipeline parallelise without coordination.
- Pipeline breakers — hash-join build, hash aggregate, ORDER BY, DISTINCT, window sorts — cut the plan into pipelines. Everything else fuses into one pass.
- Morsel-driven parallelism: a shared atomic queue of row groups, threads taking the next one when free, thread-local hash tables combined at the end. No thread waits for another, and there is no shared lock.
- Parallelism needs enough row groups. 100,000 rows is one morsel is one thread, however many cores you own.
- A gzipped CSV cannot be split and silently runs single-threaded.
- In a profile, read which operator holds the time, where the row count collapses, and estimated versus actual rows.
Next: reading files in place — Parquet, CSV, JSON, globs and object storage, with no import step.