DuckDB: What Actually Happens Under the Hood — Vectorized Execution, Push-Based Pipelines, and Morsel-Driven Parallelism

You know DuckDB as "SQLite for analytics." You've probably run SELECT * FROM 'data.parquet' and watched it scan millions of rows in milliseconds. But do you know why it's that fast?

Most developers treat DuckDB as a black box: embed it, query it, get results. But the speed comes from three deliberate architectural choices that fundamentally differ from how PostgreSQL, SQLite, or even Spark execute queries.

Let's open the box.

The Three Pillars

These aren't optimizations. They're a different execution model.

Vectorized Execution: Why 2,048 Rows?

DuckDB doesn't process rows. It processes vectors — contiguous arrays of 2,048 values per column.

// Traditional row-at-a-time (PostgreSQL, SQLite)
for (row in table) {
    value = row.column;
    result = fn(value);
    output.append(result);
}
// 1 function call per row, terrible cache usage, no SIMD

// DuckDB vectorized
for (vector in table) {
    values = vector.column;        // 2048 values in contiguous memory
    results = fn_simd(values);     // 1 function call, SIMD-friendly
    output.append(results);
}

Why 2,048?

• Fits in L1/L2 cache (~16 KB for int64)
• Power of 2 -> efficient bit operations
• Large enough to amortize function call overhead
• Small enough to avoid memory pressure

Each vector lives inside a DataChunk — the fundamental unit of data flow:

DataChunk:
  Column 0: [int1, int2, ..., int2048]   // 2048 integers
  Column 1: [str1, str2, ..., str2048]   // 2048 strings
  Column 2: [ts1,  ts2,  ..., ts2048]    // 2048 timestamps
  Count: 2048  (valid rows <= capacity)

The count can be less than 2048 — that's how filters work. A WHERE clause doesn't remove rows; it just reduces the count field. The underlying memory stays allocated.

Push-Based Execution: Data Flows Downhill

Traditional databases use the Volcano iterator model (pull-based):

// Parent pulls from child
while (chunk = child.GetNext()) {
    process(chunk);
    parent_push(chunk);  // if parent exists
}

Problem: the parent controls the pace. The child can't push multiple chunks ahead. You get pipeline bubbles — the parent waits for the child, the child waits for I/O, nobody overlaps work.

DuckDB flips it. Children push to parents:

// Child pushes to parent
void Child::Execute(DataChunk &input) {
    process_data();
    parent.Execute(output_chunk);  // push, don't wait
}

What this enables

1. True pipelining — A chunk flows through Filter -> Project -> Aggregate in one pass. No materialization between operators.

2. Cache locality — Data stays hot in L1/L2 as it moves through the pipeline. The same 2048-value array gets transformed in place.

3. Natural parallelism — Multiple pipelines run independently on different cores. No central scheduler bottleneck.

4. Backpressure for free — If a parent's buffer fills, the child's Execute blocks. I/O pauses propagate naturally.

Morsel-Driven Parallelism: No Central Scheduler

Most parallel databases have a central executor that hands out work. DuckDB doesn't.

Instead, each operator decides its own parallelism based on the data it sees.

idx_t PhysicalOperator::EstimatedThreadCount() const {
    if (children.empty()) {
        // Table scan: parallelism = row groups / 2
        return max(cardinality / (ROW_GROUP_SIZE * 2), 1);
    }
    if (type == UNION) {
        // Sum children's threads
        return sum(child.EstimatedThreadCount());
    }
    // Other operators: max of children
    return max(child.EstimatedThreadCount());
}

A table with 1M rows (8 row groups of ~122K each) on 4 cores:

Thread 1: row groups 0, 1
Thread 2: row groups 2, 3
Thread 3: row groups 4, 5
Thread 4: row groups 6, 7
-> All partial results combined

No synchronization during processing. Threads only sync at pipeline boundaries (HashJoin build, HashAggregate, Order).

Pipeline breakers

Some operators must materialize and break the pipeline:

DuckDB organizes operators into pipelines separated by these breakers.

Query: SELECT country, COUNT(*) FROM users WHERE age > 18 GROUP BY country ORDER BY count DESC LIMIT 10

Pipeline 1: TableScan(users) -> Filter(age>18) -> HashAggregate(GROUP BY country)
  -> Parallel: 4 threads scan different row groups, each builds partial hash table

Pipeline 2: HashAggregate (combine) -> Order(count DESC)
  -> Single-threaded: merge partial aggregates, sort

Pipeline 3: Order -> Limit(10)
  -> Early termination after 10 rows

The Optimizer: Stratified and Subquery-Aware

Before execution, DuckDB runs a stratified optimizer — multiple passes, each with a specific goal:

1. Logical rewrites — predicate pushdown, projection pushdown, join reordering, subquery unnesting
2. Physical planning — choose operators, partition joins, decide parallelism
3. Adaptive execution — runtime statistics feed back into filter ordering, join strategy

Predicate pushdown (logical)

-- Original
SELECT name FROM (SELECT * FROM users) WHERE age > 30;

-- Optimized: filter pushed into table scan
TableScan(users, FILTER age > 30) -> Project(name)

DuckDB reads only the age and name columns. The other 48 columns never leave disk.

Projection pushdown

-- Only 'name' and 'age' columns read from storage
SELECT name FROM users WHERE age > 30;

Columnar storage + projection pushdown = massive I/O reduction.

Join ordering

-- Small table (countries) as build side, large table (users) as probe side
SELECT * FROM users
JOIN countries ON users.country_id = countries.id;

DuckDB estimates cardinalities and puts the smaller table on the build side automatically.

Adaptive Execution: Runtime Feedback

DuckDB doesn't just trust the optimizer. It adapts during execution.

Adaptive filtering

WHERE expensive_function(x) > 10 AND cheap_column = 'value'

The executor tracks selectivity. If cheap_column = 'value' eliminates 99% of rows, it evaluates that first next time — even if the optimizer ordered it differently.

Adaptive hash joins

The same operator switches strategies mid-query based on actual row counts.

What This Means in Practice

Query Parquet files directly — no ETL

import duckdb

# Zero-copy: DuckDB reads Parquet column chunks directly
# No import, no copy, no schema inference step
result = duckdb.query("""
    SELECT station_id, AVG(temperature)
    FROM 's3://weather-data/2024/*.parquet'
    WHERE year = 2024
    GROUP BY station_id
""").df()

The httpfs extension streams row groups. Predicate pushdown skips entire row groups via Parquet statistics.

Larger-than-memory analytics

DuckDB 0.9+ (Sep 2023) added out-of-core support for all operators:

• Hash aggregate: spills to disk when groups exceed memory
• Hash join: partitions build/probe sides to disk
• Sort: external merge sort
• Window functions: segment tree with disk spill

-- 50 GB data, 16 GB RAM — works
SELECT customer_id, COUNT(*)
FROM 's3://huge-dataset/*.parquet'
GROUP BY customer_id;

When NOT to Use DuckDB

DuckDB is single-writer, multiple-reader. It's not a general-purpose RDBMS replacement.

Mental Model Summary

Peek Under the Hood Yourself

-- See the physical plan with parallelism
EXPLAIN ANALYZE
SELECT country, COUNT(*)
FROM users
WHERE age > 18
GROUP BY country;

-- Enable detailed profiling
PRAGMA enable_profiling;
PRAGMA profiling_output = 'profile.json';
SELECT ...;
-- Open profile.json: operator timings, memory, thread counts, actual vs estimated rows

The profile shows exactly which pipelines ran in parallel, where data spilled, and whether the optimizer's estimates matched reality.

The Takeaway

DuckDB isn't "SQLite for analytics" because of its API. It's "SQLite for analytics" because it threw away the Volcano iterator model and built an execution engine from first principles for analytical workloads:

• Columnar storage -> read only what you need
• Vectorized (2048) -> SIMD, cache, amortized overhead
• Push-based -> fused pipelines, no materialization
• Morsel-driven -> self-parallelizing operators, no central bottleneck
• Adaptive -> runtime reality beats static estimates

Next time you run a query and get results in milliseconds on a 10 GB Parquet file, you'll know exactly which thread processed which morsel, which pipeline it flowed through, and why it didn't need a server.

That's not magic. That's mechanical sympathy.