3. The storage layer

You can put a whole database in one file, hand it to somebody, and they can query it. That property is worth understanding rather than assuming.

The shape of the file

   analytics.duckdb

   ┌──────────────────────────────────────┐
   │ HEADER 1   (block 0)                 │
   │ HEADER 2   (block 1)                 │  two headers,
   │ HEADER 3   (block 2)                 │  written
   ├──────────────────────────────────────┤  alternately
   │                                      │
   │  BLOCK  BLOCK  BLOCK  BLOCK  ...     │  256 KB each
   │                                      │
   │  ─ metadata blocks: the catalog,     │
   │    the schema, block pointers        │
   │  ─ data blocks: column segments      │
   │  ─ free blocks: reusable             │
   │                                      │
   └──────────────────────────────────────┘

Everything is a 256 KB block. Tables, indexes, the catalog, the free list — all of it lives in fixed-size blocks, and the file is a sequence of them plus three headers at the front.

The three headers are the durability mechanism, and they are the same trick SQLite and many other single-file formats use.

   MAIN HEADER   version, block size, the
                 magic bytes. Written once.

   HEADER 1  ──┐  each holds a checkpoint:
   HEADER 2  ──┘  an iteration number and a
                  pointer to the metadata
                  block tree for that version.

   A CHECKPOINT:
     1. write all new data blocks
     2. fsync
     3. write the header with the HIGHER
        iteration number, alternating
        between slot 1 and slot 2
     4. fsync

   → if the crash happens anywhere before
     step 3, the OTHER header is still
     valid and still points at a complete,
     consistent database.

The commit is one 4 KB header write. Either it landed or it did not; there is no half-committed state. This is why a .duckdb file survives a power cut, and why copying one while nothing is writing to it is a valid backup.

Row groups — the unit of everything

Inside a table, rows are grouped into row groups of 122,880 rows.

That number looks arbitrary and is not: it is 2048 × 60. 2048 is the vector size (chapter 4), so a row group is exactly sixty vectors’ worth of rows. Everything in DuckDB is sized in multiples of the vector.

   TABLE events (id, country, amount, ts)

   ROW GROUP 0        rows 0 – 122,879
   ┌─────────┬─────────┬─────────┬─────────┐
   │ id      │ country │ amount  │ ts      │  each column
   │ segment │ segment │ segment │ segment │  stored
   │         │         │         │         │  separately
   │ min: 0  │ min: AR │ min:0.01│ min: ...│  ← zone map
   │ max:122k│ max: ZW │ max:9999│ max: ...│    per segment
   └─────────┴─────────┴─────────┴─────────┘

   ROW GROUP 1        rows 122,880 – 245,759
   ┌─────────┬─────────┬─────────┬─────────┐
   │   ...   │   ...   │   ...   │   ...   │
   └─────────┴─────────┴─────────┴─────────┘

A row group is the unit of:

   ─ parallelism.   One thread takes one row
                    group. Chapter 4.
   ─ pruning.       Zone maps let a scan skip
                    a whole row group.
   ─ compression.   Chosen per column segment.
   ─ checkpointing. Written out as a unit.

This is the same design ClickHouse uses with granules and Parquet uses with row groups — and it is the same design for the same reason. Column-at-a-time storage needs a horizontal chunking so that scans can be parallelised and skipped; the row group is that chunk.

Zone maps do the skipping

Every column segment carries its min and max. A filter compares against those before reading anything:

   SELECT * FROM events WHERE amount > 5000

   row group 0   amount: min 0.01  max 4200
                 → 4200 < 5000. SKIP ENTIRELY.
                   Zero bytes read.

   row group 1   amount: min 12    max 9800
                 → might match. read it.

   row group 2   amount: min 3     max 900
                 → SKIP.

This is free and it is enormous, and it explains a property people find surprising: a query filtering on a column that correlates with insertion order is often ten or a hundred times faster than one filtering on a randomly distributed column. If your rows arrived in timestamp order, a filter on the timestamp skips nearly everything. A filter on user_id skips nothing, because every row group contains the whole range of user IDs.

The practical consequence: insert in the order you filter in. It costs nothing and it is the single cheapest performance decision available in DuckDB. It is exactly the same advice ClickHouse’s ORDER BY key gives, arrived at from the same mechanics.

Compression, chosen per segment

DuckDB does not pick one compression algorithm. It analyses each column segment and picks the best scheme for that segment’s actual data.

   SCHEME            GOOD FOR

   Constant          every value identical
   RLE               long runs of the same value
   Bitpacking        small integers — store 9-bit
                     values in 9 bits, not 32
   Frame of Ref      values in a narrow band —
                     store the delta from a base
   Delta / Chimp /
     Patas / ALP     floating point and
                     timestamps
   Dictionary        repeated strings
   FSST              strings with shared
                     substrings — compresses
                     the strings themselves,
                     and stays SEARCHABLE
   Zstd              a general fallback

The analysis pass is real work at write time and it is why DuckDB files are small. A table that takes 4 GB as CSV routinely lands at 200–400 MB in DuckDB’s format.

FSST is worth singling out. Most string compression means “decompress before you can look at it.” FSST — Fast Static Symbol Table — compresses strings with a symbol table small enough to stay in cache, and DuckDB can evaluate some predicates against the compressed form. Compressed data that you do not have to decompress to filter is the whole game in a column store, and it is why the file format frequently beats Parquet on scan speed for the same data.

Which is why `.duckdb` and Parquet are different tools

                     DUCKDB FILE      PARQUET

   read by            DuckDB only     everything
   compression        tuned per       good, general
                      segment
   scan speed         fastest         very fast
   updates/deletes    yes, MVCC       no — rewrite
   indexes            yes (ART)       no
   transactions       yes             no
   splittable         no              yes, per file

Chapter 15 turns this into a recommendation. The short version: DuckDB’s format for anything only DuckDB reads; Parquet for anything shared.

The ART index

DuckDB has exactly one index type: an Adaptive Radix Tree.

   CREATE INDEX idx ON events(user_id);

   ─ used for: point lookups, and enforcing
     PRIMARY KEY and UNIQUE constraints
   ─ NOT used for: range scans that touch a
     lot of rows, and not for the analytical
     queries DuckDB exists for
   ─ lives entirely in memory when the
     database is open
   ─ is rebuilt on load for persistent
     databases

The important thing about indexes in DuckDB is how rarely you want one. In a row store, an index is how you avoid reading the table. In a column store with zone maps and 10× compression, a full scan of one column is already fast, and an index costs memory, slows every insert, and helps only for selective point lookups.

A PRIMARY KEY in DuckDB is not free. It builds an ART over the column, which must be maintained on every insert and held in RAM. On a table of 500 million rows that is a substantial amount of memory being spent to enforce a constraint you may not need. Declare keys when you actually need uniqueness enforced; do not declare them by habit.

MVCC, and how updates work

DuckDB is transactional and uses multi-version concurrency control, in the style of the HyPer research system.

   UPDATE events SET amount = 99 WHERE id = 7;

   1. the row's OLD values are copied into an
      UNDO BUFFER, in memory, belonging to
      this transaction
   2. the new value is written IN PLACE into
      the column segment
   3. other transactions that started earlier
      read through the undo buffer and see
      the old value
   4. on COMMIT, the undo buffer is discarded
      on ROLLBACK, it is replayed backwards

Readers never block writers and writers never block readers, within the process. Every transaction sees a consistent snapshot from the moment it started. This is the same guarantee PostgreSQL gives, implemented differently — Postgres writes new row versions into the heap and needs VACUUM to clean up; DuckDB keeps the old versions in memory and drops them at commit, so there is no vacuum and no bloat.

The trade is that a very large transaction holds its undo information in RAM. Updating a hundred million rows in one statement is a memory cost, not just a time cost. Chapter 14 has this as a failure mode.

Deletes are marked, not applied. A deleted row gets a bit set in the row group’s deletion vector; the space is reclaimed at checkpoint. This is the same approach Iceberg’s position deletes take.

The WAL

Between checkpoints, changes go to a write-ahead log — analytics.duckdb.wal.

   COMMIT
     ↓
   append to the .wal, fsync
     ↓
   transaction is durable
     ↓
   ...later, when the WAL exceeds a threshold
      (default 16 MB) or on a clean shutdown:
     ↓
   CHECKPOINT: write the changed row groups
   into the main file, write the header,
   truncate the WAL

If you see a .wal file sitting next to your database, the last process did not shut down cleanly. It is not a problem — the next open replays it — but it is a signal. And it means a backup must copy both files, or none: copying only the .duckdb while a .wal exists gives you a database missing its most recent commits.

You can force one with CHECKPOINT;, and FORCE CHECKPOINT; will do it even while other queries are running in the same process.

Larger than memory, on the storage side

Two separate things that both get called “out of core”, and confusing them causes real mistakes.

   1. THE DATABASE FILE can be far larger
      than RAM. Blocks are read as needed
      and cached in the buffer manager.
      A 500 GB .duckdb on a 16 GB machine
      is fine.

   2. A QUERY that needs more working memory
      than it has — a big hash join, a big
      sort — spills to the temp directory.
      That is chapter 10, and it is a
      different mechanism entirely.

The buffer manager holds blocks up to memory_limit and evicts on a least-recently-used basis. In an in-memory database there is nowhere to evict to, which is why an in-memory DuckDB can run out of memory on data a persistent one handles comfortably — unless you have set temp_directory, which chapter 13 says you always should.

What to take from this chapter

A .duckdb file is 256 KB blocks plus three headers. A commit is one header write with a higher iteration number, alternating between two slots — so a crash always leaves a complete previous version.
Row groups are 122,880 rows — exactly 2048 × 60, sixty vectors. They are the unit of parallelism, pruning, compression and checkpointing.
Zone maps (min/max per segment) skip whole row groups for free. So insert in the order you filter in — the cheapest performance decision available.
Compression is chosen per segment from a dozen schemes. FSST keeps strings searchable while compressed, which is why the format often out-scans Parquet.
Use the DuckDB format for what only DuckDB reads; Parquet for anything shared.
One index type, an ART — for point lookups and constraints, held in RAM, rebuilt on load. A PRIMARY KEY is not free; declare it only when you need it enforced.
MVCC with in-memory undo buffers — readers and writers never block each other, and there is no vacuum and no bloat. The cost is that a huge transaction holds its undo data in RAM.
A .wal next to the file means an unclean shutdown. Back up both files or neither.
“Larger than memory” is two different things: the file exceeding RAM (buffer manager, fine) and a query exceeding RAM (spilling, chapter 10).

Next: the execution engine — vectors of 2048 rows, morsel-driven parallelism, and why the middle size wins.

Written by Sepuri Sai Krishna