A Tour of DuckDB's Internals: The Forty-Three Tricks

TL;DR: Forty-three deep dives into DuckDB’s source, in the order they appeared. Each one is a specific, self-contained trick: how a validity mask disappears when a column has no NULLs, how a 16-bit salt lets a hash probe skip the tuple entirely, how Node48 buys O(1) child lookup with a byte-indexed side table, how a DELETE of consecutive rows stores zero row IDs. Every entry links to the full write-up.

Start anywhere. There is no dependency between them.

Why DuckDB’s source is worth reading

Most database engines are two systems wearing a trenchcoat: a storage engine that predates the query engine that sits on top of it, and a query engine that inherits the storage engine’s compromises. Read enough of DuckDB and you notice something different. It is an analytical database — columnar, vectorised, single-node, designed for scans and joins over data that does not fit in memory — and every single design decision in it is a bet on the shape of the workload. The bets are not written down in a design document. They are in the code, in the constants, in the if statements that nobody wrote because the alternative never occurs in practice.

That is what makes the code so instructive. There is no legacy to apologise for, so every strange-looking constant is load-bearing. STANDARD_VECTOR_SIZE is 2048 because 2048 rows of an 8-byte column is exactly 16 KiB, which is exactly one page, which is exactly what you want when a vector’s worth of data has to survive a cache line boundary. string_t is 16 bytes with 12 bytes of inline storage because uint32_t + char[12] lands on a 16-byte slot that fits an AVX register. The hash table entry packs a 48-bit pointer and a 16-bit salt into one 64-bit word because the salt lets the probe reject a non-match without touching the tuple, and one cache line is the unit of pain.

The other thing you notice is how much of the work is avoidance rather than acceleration. DuckDB is fast in large part because it declines to do things: it does not decompress a string column to measure it (SELECT strlen(col) never reads the string), it does not build a hash table for a perfectly dense integer range, it does not store keys in an aggregate table when the range is dense enough to be an array, it does not copy strings into a heap when they are 12 bytes or shorter. Each of those is a bet too, and each of those bets is visible in a specific, readable piece of code.

That is what this series is about: forty-three bets, one per article, each taken apart.

Table of contents

The vectorised engine

DuckDB does not process one row at a time. It processes 2048 of them at a time, in a Vector, and every trick in this section is about what that decision buys and what it costs.

Orrify: six vector types into three words. DuckDB has six physical vector types — flat, constant, dictionary, sequence, list and struct — and every operator that consumes a vector has to handle all of them. UnifiedVectorFormat collapses them into a single flat layout of three machine words per column (a data pointer, a validity pointer and a selection-vector pointer), so an operator writes its logic once against the unified form instead of six times against the concrete forms. The trick is that the unified format is a view: it does not copy or flatten anything, it just points at whichever representation the vector happens to be in. Read the deep dive — src/include/duckdb/common/vector/unified_vector_format.hpp.

The auxiliary buffer. Slicing a vector is a pointer bump, not a copy — but a slice points into its parent’s bytes, so the parent must outlive it. VectorBuffer solves this with an AuxiliaryDataHolder: a type-erased bag of extra allocations that a buffer can carry without owning them in the ordinary sense. The slice holds a shared_ptr to the parent, and the lifetime problem becomes a type-system problem instead of a programmer discipline problem. Read the deep dive — src/common/vector/flat_vector.cpp.

The validity mask is optional. A column with no NULLs should not pay for a NULL bitmap. DuckDB’s ValidityMask represents “no NULLs” as a null pointer, and every read of the mask checks for that first. The mask is allocated lazily, on the first NULL, and the whole engine benefits: a scan of a NOT NULL column touches no mask bytes at all. Read the deep dive — src/include/duckdb/common/types/validity_mask.hpp.

The 64-bit constant mask. Scalar expressions are not compiled 2048 separate times. At runtime DuckDB classifies the inputs of a vectorised operation — all constant, all flat, or mixed — and packs that classification into a 64-bit mask that is then used to select among specialisations. The specialisation happens once per vector, and the per-row loop becomes a switch on a bit instead of a nest of ifs. Read the deep dive — src/include/duckdb/common/vector_operations/scalar_executor.hpp.

The vector cache. A DataChunk’s buffers are not freed when the chunk is reset; they are cached and reused. The cache is keyed by type and size, so the same allocation is handed out again and again. The result is that a steady-state query performs essentially zero allocations in the executor. Read the deep dive — src/common/types/vector_cache.cpp.

The storage format

DuckDB’s on-disk format is columnar, compressed, and — this is the interesting part — self-describing enough to be re-read without a schema. The tricks here are about how a column gets encoded, and how a scan manages to skip the parts of it that cannot match.

The compression auction. When DuckDB checkpoints a column it does not pick a codec from a lookup table. Every codec that supports the column’s type is asked to compress a sample of the data, and each one reports how well it did. The winner is chosen by a scoring function that weighs compression ratio against decompression speed. The “auction” is the metaphor, but the mechanism is a real benchmark on real data. Read the deep dive — src/storage/table/column_data_checkpointer.cpp.

FSST learns its symbol table from a sample. Fast Static Symbol Table compression works by replacing common byte sequences with short symbol codes. DuckDB builds the symbol table from a random 25% sample of the column, then measures how well the resulting table compresses the whole column — and if the sample was unrepresentative, it goes back and tries again. The metadata it writes is a backwards metadata table, so a scan can skip an entire 1024-value vector without touching a byte of payload. Read the deep dive — src/storage/compression/fsst.cpp.

ALP replaces floating-point numbers with integers. Adaptive Lossless floating-Point compression works by observing that most float columns are really integers with an exponent and a factor attached. ALP searches for that (exponent, factor) pair by sampling, converts the values to integers, and stores those with ordinary integer compression — losslessly, because the original floats can be reconstructed exactly. Read the deep dive — src/include/duckdb/storage/compression/alp/alp_compress.hpp.

Zone maps. Every column segment and every row group carries min/max statistics. Before a segment is read, the scan asks whether the predicate’s range can possibly intersect the segment’s range; if not, the segment is skipped without a byte being read. The same zone-map logic decides how many rows to prefetch, so DuckDB does not even fetch the blocks it knows it does not need. Read the deep dive — src/storage/table/row_group.cpp.

Joins and aggregates

This is where an analytical database spends most of its time. The tricks here are about making the hash table smaller, the probe cheaper, and the group-by state smaller.

The 16-bit salt. A hash table entry is a single 64-bit word holding a 48-bit pointer to the tuple and 16 bits taken from the tuple’s hash. The probe compares the whole word: if the salts differ, the keys are definitely different, and the engine never dereferences the tuple to find out. Only a salt match triggers the expensive comparison. The same 16 bits are reused to derive the linear-probing increment, so the probe step is hash-dependent without a second hash. Read the deep dive — src/include/duckdb/execution/ht_entry.hpp.

The perfect hash join. When the join key is a dense integer range, a hash table is the wrong data structure: the “hash table” can be a flat array indexed by the key itself, with a bitmap for presence. DuckDB detects this case and switches to a completely different executor. Read the deep dive — src/execution/operator/join/perfect_hash_join_executor.cpp.

chains_longer_than_one. The join probe has two completely different algorithms: one for a build side that fits in cache, one for a build side that does not. Which one to use is decided by a single boolean flag on the hash table — a flag that is itself cheap to maintain, because it only flips when a chain actually gets long. Read the deep dive — src/execution/join_hashtable.cpp.

The found flag lives inside the tuple. A FULL or RIGHT OUTER join has to mark the build rows that found a match. DuckDB does not keep a side bitmap; it writes a boolean into the tuple itself, next to the payload. The “found” state and the data travel together, in the same cache line, and the flag costs nothing because the row layout already had padding. Read the deep dive — src/execution/join_hashtable.cpp.

Spilling the aggregate state. When a GROUP BY outgrows memory, DuckDB does not fail and does not spill the input. It spills the aggregate state: the partially-aggregated groups are partitioned by hash and written to disk, and the final aggregation runs over the spilled partitions. The state is usually much smaller than the input, which is the whole point. Read the deep dive — src/execution/aggregate_state_spilling.cpp.

The perfect aggregate hash table stores no keys. For a GROUP BY over a dense integer range, DuckDB uses a flat array of aggregate states indexed by the group value and stores no keys at all. There is nothing to hash, nothing to compare, and nothing to spill — the array is the table. Read the deep dive — src/execution/perfect_aggregate_hashtable.cpp.

Clustered aggregation. If the input arrives sorted by the grouping column, consecutive rows belong to the same group. DuckDB detects runs of equal group values and updates the aggregate state once per run instead of once per row, using the run length as a multiplier. Read the deep dive — src/common/clustered_aggregate.cpp.

count(DISTINCT a) and sum(DISTINCT a) share one table. Two DISTINCT aggregates over the same column are not two dedup hash sets. They are one: the deduplication happens once, the per-distinct-value aggregation is deferred to the finalize phase, and each aggregate keeps its own state vector indexed by the dedup table’s position. Read the deep dive — src/execution/operator/aggregate/distinct_aggregate_data.cpp.

Sorting

Sorting is where an analytical database is most likely to spill to disk, and where the “avoid work” strategy shows up most clearly.

One byte-comparable blob. DuckDB serialises a whole sort key — several columns, with NULL ordering and collations — into a single byte string whose memcmp order is the desired sort order. The sort then operates on one opaque byte array instead of a tuple of mixed-typed columns, which means one comparison function, one radix pass, and one serialization format for every query. Read the deep dive — src/function/scalar/create_sort_key.cpp.

Nine sort key layouts. There are nine fixed-size sort-key layouts, chosen at bind time from the number of columns, their types and their sizes. Alongside them DuckDB computes which byte positions of the key are provably constant (because the column’s statistics say so), and the radix sort skips those byte positions entirely — one fewer pass over the data. Read the deep dive — src/include/duckdb/common/sorting/sort_key.hpp.

TopN. A LIMIT 10 over a million rows does not sort a million rows. DuckDB keeps a bounded heap, and — this is the good part — pushes the current boundary value down as a dynamic filter, so the scan below can discard rows that could never make the cut. Read the deep dive — src/execution/operator/order/physical_top_n.cpp.

The merge that never allocates. Merging sorted runs does not need a new buffer for the output. DuckDB merges in place inside the buffer manager’s existing blocks, and it degrades gracefully — falling back to a slower path — when the buffer manager refuses to cooperate. Read the deep dive — src/include/duckdb/common/sorting/buffered_merge.hpp.

The optimizer

DuckDB’s optimizer is a pipeline of fairly ordinary rewrite rules. What makes it interesting is that the rules are proving things about the data, not just rewriting syntax.

The FilterCombiner. A pile of predicates on the same column — a > 3 AND a > 5 AND a < 9 AND a <> 7 — is folded into one tight range, and the combiner infers new predicates that were not written: a > 5 implies a > 3. The result is one range that the storage layer can act on. Read the deep dive — src/optimizer/filter_combiner.cpp.

The statistics propagator. If a column’s statistics say max(a) = 100, then a > 200 is not a filter — it is the constant false, and the optimizer can replace the expression with it. The propagator walks expressions bottom-up and turns comparisons that statistics have settled into constants. Read the deep dive — src/optimizer/statistics/expression/propagate_comparison.cpp.

The join order optimizer. DPhyp-style dynamic programming over plan subgraphs, with a cost model and a cardinality estimator — and a greedy escape hatch for the cases where the full enumeration would be too expensive. Read the deep dive — src/optimizer/join_order/plan_enumerator.cpp.

The Deliminator. A correlated subquery becomes a join with a delim join on top. The Deliminator recognises that the join exists only to carry a column up from the correlated side, and deletes it — along with the associated duplication machinery. Read the deep dive — src/optimizer/deliminator.cpp.

Distributivity. a = 1 OR b = 2 becomes a = 1 OR (a = 1 AND b = 2) — and then the common factor a = 1 can be hoisted out, giving the optimizer something it can push down into the scan. Read the deep dive — src/optimizer/rule/distributivity.cpp.

Memory management

DuckDB is a database that is expected to run on a laptop with 16 GB of RAM while scanning a 100 GB file. The memory manager is the machinery that makes that survivable.

Pinning is ownership. The buffer handle’s pin is a shared_ptr; the pin count is only a hint to the eviction policy. A buffer is evicted when nothing holds a shared_ptr to it, which makes “the data must stay alive” a property of the type system rather than of a counter discipline. Read the deep dive — src/include/duckdb/storage/buffer/buffer_handle.hpp.

The lock-free eviction queue. Buffers are evicted through a lock-free multi-producer multi-consumer ring of weak pointers with sequence-number versioning — the classic Vyukov-style queue. Because the nodes are weak, a buffer that was pinned again while sitting in the eviction queue is simply skipped rather than resurrected. Read the deep dive — src/storage/buffer/buffer_pool.cpp.

The temporary memory manager solves a convex optimization. When several operators want temporary memory at once — two hash aggregates, a sort, a join — the manager does not split it evenly. It solves a small convex optimization that weights each request by how much it will actually use, and hands out the split. Read the deep dive — src/storage/temporary_memory_manager.cpp.

The block allocator. Underneath everything is one big mmap arena with per-thread free lists, so the allocator itself never takes a lock on the hot path. It also keeps a decaying estimate of how much memory it should retain, so a burst of allocations does not permanently inflate the resident set. Read the deep dive — src/storage/block_allocator.cpp.

Transactions

DuckDB’s concurrency control is optimistic: readers never block, and conflicts are detected at commit time. The tricks here are all about making the bookkeeping cheap, because the bookkeeping is what every transaction pays for.

The undo log is a self-describing byte stream. The undo log is not a list of allocated objects; it is a contiguous byte stream where each entry describes its own type and length, so the log can be walked backwards and forwards without a side table of offsets. And it lives inside the buffer manager, which means it is pinned, accounted and spillable for free. Read the deep dive — src/transaction/undo_buffer_allocator.cpp.

An UPDATE never touches the row layout. An update does not rewrite the row. It appends the previous values to a version chain stored in the row group’s own memory, and readers who need the old value walk the chain backwards. The row layout never changes, which is what makes updates cheap. Read the deep dive — src/storage/table/update_segment.cpp.

The consecutive-rows fast path. A DELETE of 1000 consecutive rows does not store 1000 row IDs. There is a fast path that stores a range instead, and the row-ID array is often not stored at all. Read the deep dive — src/include/duckdb/transaction/delete_info.hpp.

VisibilityBound. Off-by-one errors in timestamp comparisons are the classic source of subtle MVCC bugs. In DuckDB the “visible up to” bound is a distinct type, so an exclusive timestamp passed where an inclusive one is required is a compile error rather than a wrong answer. Read the deep dive — src/include/duckdb/common/constants.hpp.

Parallelism

DuckDB is single-node but fully parallel. The parallelism is not thread-per-row and not thread-per-query; it is thread-per-pipeline with fine-grained work handoff.

All-or-nothing. A pipeline runs in parallel only if every stage in it agrees to be parallel. If any stage says no, the whole pipeline silently degrades to a single task — no partial parallelism, no correctness bugs from half-parallel operators. Read the deep dive — src/parallel/pipeline.cpp.

Morsels. Scan work is handed out as morsels — row groups — by a cursor that hands each one to whichever thread asks next. The scan itself is never locked; only the cursor is. Read the deep dive — src/storage/table/row_group_collection.cpp.

The query plan is an event DAG. Dependencies between operators are expressed as counters that the producer decrements, not as condition variables that the consumer waits on. The whole plan becomes a DAG of events, and the executor never blocks a thread waiting for data — it parks the task instead. Read the deep dive — src/parallel/event.cpp.

Reading files

Reading Parquet and CSV is a whole separate set of tricks, because the format itself gives you opportunities to avoid work that the query engine would otherwise have to do.

Never touch the bytes. Parquet’s footer carries per-column min/max statistics per row group. DuckDB parses them once at open time and asks, before a single byte of data is read, whether a row group can possibly contain a match. If not, the group is skipped wholesale — no page header parsed, no dictionary built, no column decompressed. The subtle part is the float handling: because NaN is not described by min/max, a float column is pruned twice, and skipped only if both prunes agree. Read the deep dive — extension/parquet/parquet_reader.cpp.

The dictionary is the filter. For a dictionary-encoded Parquet column, DuckDB applies the pushed-down filter to the dictionary values, producing a boolean per dictionary entry, and then turns per-row filtering into a table lookup. The row-level selection is written branchlessly: the code always writes the row index and only advances the counter on a match, so there is no branch misprediction on the hot path. Read the deep dive — extension/parquet/decoder/dictionary_decoder.cpp.

SWAR. The CSV parser is a precomputed 256-bytes-by-19-states transition table, so the hot loop is one array lookup per byte. On top of that, runs of uninteresting bytes are skipped 64 at a time using SIMD-within-a-register: eight uint64_t loads, one MaybeZeroBytes comparison per stop pattern, the per-byte flags packed into a 64-bit mask, and the first candidate found with a single count-trailing-zeros. Read the deep dive — src/include/duckdb/common/swar.hpp.

Three bonus deep dives

These three did not fit the once-a-day schedule, but they are worth the detour.

SELECT strlen(col) never reads the string. A Parquet string is a 4-byte length followed by a payload. DuckDB pushes strlen into the scan, which swaps in a reader that reads the length prefix and skips the payload with a pointer bump. The string data is never copied, never validated as UTF-8, never materialised. Read the deep dive — extension/parquet/read_single_parquet_file.cpp.

The 16-byte string_t and its 4-byte shadow prefix. string_t is a tagged union: 12 bytes of inline storage for short strings, a pointer for long ones — and for long strings, a copy of the first four bytes of the payload sitting next to the pointer. That shadow prefix makes equality a pair of 16-byte loads and makes hashing of a short string a dereference-free affair. Read the deep dive — src/include/duckdb/common/types/string_type.hpp.

The ART’s adaptive fanout. DuckDB’s index is an adaptive radix tree: shared key prefixes collapse into dedicated prefix nodes, and each node grows through Node4, Node16, Node48 and Node256 so that its memory cost tracks its actual branching. Node48 is the interesting one — a 256-byte byte-indexed side table buys O(1) child lookup — and every child reference is a single 64-bit word packing buffer id | offset | node type. Read the deep dive — src/include/duckdb/execution/index/art/node48.hpp.

Where to start

If you are new to DuckDB’s internals, this is the order I would read them in.

1. Orrify first, then the auxiliary buffer and the validity mask. These three establish the vocabulary — what a vector is, how it owns memory, how NULLs are represented. Everything else in the engine is written in those terms.
2. The 16-bit salt next. It is the shortest article in the series and it teaches you how DuckDB thinks about hash tables, which then makes the join and aggregate articles much easier.
3. The vector cache, because it explains why the executor feels allocation-free and why the memory articles matter.
4. The compression auction and then FSST and ALP. These are self-contained and they change how you think about storage.
5. Zone maps, then the Parquet row-group pruning article. Together they are the “skip work you do not need” theme, and they are the two that will change how you write queries.
6. Never touch the bytes and the dictionary is the filter if you mostly care about file formats; the statistics propagator and the filter combiner if you mostly care about the optimizer.
7. The undo log, then VisibilityBound, if you want to understand DuckDB’s concurrency model. These two are the ones that surprise people coming from a row-store engine.

Everything else is reference material. Come back to it when you need it.