Skip to content
Tutorials Center
My learning

Topics

Lesson 6 of 38 ยท Concepts and CLI

Columnar Storage and Vectorized Execution

Understand why DuckDB is fast: column-oriented storage, compression, vectorized execution, row groups, zone maps and parallel query processing, with runnable comparisons.

DuckDB is quick for reasons you can explain in five ideas: it stores data in columns, compresses it, processes it in batches, skips what it does not need, and uses all your cores. Understanding them helps you write queries that play to the engine's strengths, and explains behavior that might otherwise seem odd.

Rows versus columns

A traditional database stores a table row by row. All values of the first row sit together on disk, then the second row, and so on. That is ideal for "give me everything about order 1234". An analytical question looks different:

What is the average price of all products?

This needs one column out of maybe forty. A row store must read every row in full and throw away 39/40 of what it read. A column store keeps all price values together, so the engine reads only that column.

flowchart LR
  subgraph Row store
    R1["row 1: id, name, price, stock"]
    R2["row 2: id, name, price, stock"]
  end
  subgraph Column store
    C1["id: 1, 2, 3 ..."]
    C2["name: ..."]
    C3["price: 9.9, 18.5 ..."]
  end

Row store vs column store

Row store Whole rows together
Column store Whole columns together

Analytics touches few columns and many rows.

1 / 2

You can feel the principle even in a stand-in: a query that needs only one column is cheap, and a query that selects every column of a wide table returns far more data.

SQL Stand-in: one column versus all columns ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

In DuckDB, writing SELECT * on a Parquet file with 200 columns reads all 200, while naming the three you need reads three. Name your columns on big data.

Compression

Because a column holds values of one type that are often similar, it compresses well. DuckDB picks a scheme per segment of data automatically:

  • Constant: all values the same, store one.
  • RLE (run-length encoding): A A A A B B becomes "A x4, B x2".
  • Dictionary: repeated strings are stored once and the column keeps small numbers.
  • Bit-packing: small integers use only as many bits as they need.
  • FSST for strings and Chimp/ALP for floating-point numbers.

A country column with 13 distinct values across millions of rows shrinks to almost nothing with a dictionary. This is why a DuckDB file is often several times smaller than the CSV it came from.

You can see the idea by building the dictionary yourself:

SQL Stand-in: dictionary encoding by hand ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Vectorized execution

Most older databases process one row at a time through a tree of operators: scan a row, filter it, project it, aggregate it. That costs a function call per row per operator. DuckDB passes vectors between operators instead, small batches of up to 2,048 values from one column. An operator like price * quantity then runs one tight loop over 2,048 numbers. The loop is easy for the CPU to predict, fits in the fast L1 cache and can use SIMD instructions that multiply several numbers at once.

The batch size is a compromise: big enough to amortize overhead, small enough to stay in cache. Execution is also push-based: each operator pushes its output vectors to the next one in a pipeline, so intermediate results rarely touch memory.

Row groups, zone maps and skipping

Inside a persistent file, a table is divided into row groups of 122,880 rows. For each column in each row group, DuckDB stores the minimum and maximum value, called a zone map (or min-max index). Take the filter WHERE order_date >= '2025-06-01'. If a row group's maximum date is April, DuckDB never reads it. If your data is roughly sorted by the filter column, whole stretches of the file are skipped.

This makes insertion order matter. Data loaded in date order gives tight zone maps for date filters. Data in random order gives wide, useless ranges. If you filter mostly on one column, load or sort the data by it:

SQL
-- DuckDB (read-only example)
CREATE TABLE events_sorted AS SELECT * FROM events ORDER BY event_time;

Parquet files carry the same kind of statistics per row group, so the same skipping works when you query them, a topic that continues in Parquet files.

Parallelism

DuckDB splits the work into chunks called morsels (roughly one row group each) and hands them to worker threads, one per core by default. Every thread scans, filters and pre-aggregates its own chunk, and then the partial results are combined. You usually do nothing to get this. You can inspect or limit it:

SQL
-- DuckDB (read-only example)
SELECT current_setting('threads');
SET threads = 2;

Operators that must see everything, such as a global sort, still work in parallel by sorting parts then merging. The result: a query on a laptop with 8 cores often finishes several times faster than a single-threaded engine would manage.

Memory, spilling and large data

If a hash join or a sort does not fit in the memory limit, DuckDB spills to a temp directory (by default <database>.tmp). That is why it can handle data much bigger than your RAM. Set SET temp_directory = '/fast/disk'; to choose where spills go, and SET memory_limit = '8GB'; to cap RAM use.

What this means for your queries

  • Select only the columns you need.
  • Filter early on columns that match the file's order.
  • Prefer set-based SQL over row-at-a-time loops, in SQL and in Python.
  • Insert large batches, not row by row. A single INSERT ... SELECT is far faster than thousands of single-row INSERT statements.
  • Use Parquet or a DuckDB table for repeated analysis rather than re-parsing CSV.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why does a column store help an analytic query that averages one column of a wide table?

    Values of one column are stored together, so the engine skips every other column.
  2. 2. What is a "vector" in DuckDB execution?

    Vectorized execution processes batches of values in tight loops instead of single rows.
  3. 3. How can zone maps speed up WHERE price > 500?

    Each row group stores min and max values, so groups that cannot contain a match are never read.
  4. 4. What does DuckDB do when a sort needs more memory than memory_limit?

    Out-of-core processing lets DuckDB finish queries larger than RAM, at a lower speed.

Summary

  • Column storage reads only the columns a query names and compresses similar values well.
  • Vectorized, push-based execution processes batches of about 2,048 values at a time.
  • Row groups of about 122,880 rows carry min-max zone maps so filters can skip data; insertion order matters.
  • Morsel-driven parallelism uses all cores; large operators can spill to disk.
  • Name columns, filter early, load in bulk and keep data sorted by your main filter.

Next: Transactions, files and attached databases