Skip to content
Tutorials Center
My learning

Topics

Lesson 30 of 38 ยท Extensions and Performance

Performance: The Vectorized Engine

Why DuckDB is fast: columnar storage, vectorized execution, parallelism and compression, plus practical tuning of threads, memory limits, types and file formats.

DuckDB regularly aggregates hundreds of millions of rows on a laptop in seconds. That is not magic; it comes from a handful of design choices that suit analytics, where queries read a few columns from many rows. Understanding them tells you which habits make queries faster and which waste the engine's strengths.

The real DuckDB cannot run on this site, so DuckDB-specific settings are read-only. The runnable examples use portable SQL to demonstrate principles such as indexing, column selection and pre-aggregation.

Columnar storage

A row store keeps each row's fields together. A column store keeps each column together. For SELECT avg(fare) FROM trips, a column store reads only the fare column and skips the others.

flowchart TB
    subgraph Row store
      R1["id, city, fare, note"]
      R2["id, city, fare, note"]
    end
    subgraph Column store
      C1["id id id ..."]
      C2["city city city ..."]
      C3["fare fare fare ..."]
    end

An aggregate over fare touches only the third column block.

Same-typed values sitting side by side also compress very well. DuckDB picks encodings per column segment automatically: constant, run-length, dictionary, bit-packing, FSST for strings, and Chimp/ALP for floating-point numbers. A table in a .duckdb file is often several times smaller than the equivalent CSV.

Vectorized execution

Classic databases process one row at a time through a tree of operators (the Volcano model), paying function-call overhead per row. DuckDB processes vectors of about 2,048 values at once. Each operator runs a tight loop over a vector, which fits in the CPU cache and lets the compiler use SIMD instructions. The result is high speed without compiling each query to machine code.

Parallelism

DuckDB splits data into row groups of about 122,880 rows and gives them to worker threads (one per CPU core by default). Scans, filters, hash aggregation, sorts and joins all run in parallel using morsel-driven scheduling: a thread grabs the next chunk when it finishes one, so cores stay busy.

SQL
SET threads = 4;                 -- cap CPU use
SELECT current_setting('threads');
SET memory_limit = '4GB';        -- cap RAM, spill to disk beyond it
SET temp_directory = '/fast-ssd/duck_tmp';
SET preserve_insertion_order = false;   -- lets large exports use less memory

When a join or sort exceeds memory_limit, DuckDB spills intermediate data to temp_directory instead of failing, so out-of-memory queries still finish, only slower. Default memory is about 80% of the machine's RAM, which is too much on a shared server: set it explicitly.

What to do in practice

Select only what you need

Because it is columnar, SELECT * over wide tables wastes I/O. Name columns. Use SELECT * EXCLUDE (note) or COLUMNS('fare|tip') in DuckDB when a list is long. The same habit helps in any engine:

SQL Narrow selects and early filters

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

Use the right types

Smaller, exact types help: INTEGER instead of VARCHAR for numbers, DATE or TIMESTAMP instead of text dates, DECIMAL(18,2) for money, ENUM or low-cardinality strings for repeated labels (dictionary encoding makes them cheap). Avoid casting columns inside a WHERE that could use statistics: WHERE CAST(ts AS DATE) = ... may block min/max pruning, while ts >= DATE '2026-03-01' AND ts < DATE '2026-03-02' keeps it.

Zone maps and ordering

Every row group stores min/max per column (zone maps). If you insert data sorted by the column you filter on, row groups cover narrow ranges and many are skipped:

SQL
CREATE TABLE events_sorted AS SELECT * FROM events ORDER BY event_date;

The same applies to Parquet files you write: sort by your most common filter column before COPY.

Indexes: usually not needed

DuckDB has an ART index for PRIMARY KEY, UNIQUE and explicit CREATE INDEX. It helps highly selective point lookups (WHERE id = 42) but slows inserts and uses memory, and it does not speed up scans and aggregations. The rule: do not add indexes for analytics; rely on columnar scans and zone maps. Keep primary keys only where you need the constraint.

In row stores, an index changes plan costs dramatically. You can see the effect even in SQLite, here comparing a search with and without an index (the planner choice shows in the plan text):

SQL Plan with and without an index (SQLite)

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

The plan says SCAN t, a full scan. In DuckDB an analytic scan of a column is cheap enough that this is usually fine; for point lookups you would add an index. Try adding CREATE INDEX t_id ON t(id); before the EXPLAIN to see SEARCH t USING INDEX.

Pre-aggregate and materialize

If a dashboard runs the same heavy aggregation constantly, store the result:

SQL
CREATE TABLE daily_sales AS
SELECT date_trunc('day', ts) AS day, region, sum(amount) AS revenue, count(*) AS orders
FROM orders GROUP BY ALL;

Querying 365 rows per year beats re-scanning 100 million.

SQL Rollup table for repeated questions

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

File formats

  • Parquet is the best default for data you read repeatedly: compressed, typed, with statistics. Use zstd compression for a better ratio.
  • CSV is slow to parse and untyped; convert once to Parquet or a DuckDB table.
  • The native .duckdb format is fastest for repeated local queries and supports updates and transactions.

Benchmark honestly

Run each query a few times: the first run warms caches. Measure with .timer on in the CLI or EXPLAIN ANALYZE (see profiling). Compare on realistic data sizes, since small samples hide the algorithmic differences.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why does a column store speed up SELECT avg(fare) on a wide table?

    Columns are stored separately, so unneeded columns are never read.
  2. 2. What does vectorized execution mean?

    Batches of about 2,048 values keep data in cache and allow tight, SIMD-friendly loops.
  3. 3. What happens when a sort exceeds memory_limit?

    Out-of-core operators spill to disk, trading speed for completion.
  4. 4. When are indexes usually worth it in DuckDB?

    Zone maps and fast scans cover analytics; ART indexes mainly help point lookups and cost memory and insert time.

Summary

  • DuckDB is fast because of columnar storage, compression, vectorized operators and parallel scans.
  • Set threads and memory_limit on shared machines; spilling prevents out-of-memory failures.
  • Select needed columns, use precise types, sort data by filter columns, and avoid casts in WHERE.
  • Skip indexes for scans; pre-aggregate repeated questions; store hot data as Parquet or native tables.

Next: Reading Plans: EXPLAIN and Profiling