How DuckDB Works Internally: Vectorized Execution, Columnar Storage, and the In-Process Analytics Engine That Replaced Your Python Scripts

Most analytical workloads that teams run in Python boil down to: load a CSV into a pandas DataFrame, filter it, group it, join it against something else, and produce numbers. Pandas does this well enough until the file is larger than a few hundred megabytes, at which point memory pressure and the GIL turn a ten-second script into a forty-minute ordeal. The instinct is to stand up a database server, but that introduces client-server round trips, connection pooling, network serialization, and operational overhead for a job that runs once a day.

DuckDB solves this by being an analytical database that runs inside your process. No server, no port, no connection string, no separate daemon to babysit. You embed it as a library and it gives you full SQL semantics over local files, remote object storage, Parquet, Arrow, JSON, and PostgreSQL tables without moving the data anywhere.

Understanding why DuckDB is fast for analytics (and where it is not) requires understanding three things: the execution model, the storage format, and the parallelism strategy.

The In-Process Architecture

SQLite is also in-process, but SQLite is optimized for OLTP: small reads and writes on individual rows, many concurrent writers, a row-oriented storage engine. DuckDB deliberately inverts those priorities. It targets OLAP: sequential full-table scans, aggregations over millions of rows, joins between wide tables, and read-heavy workloads where writes are either bulk loads or rare updates.

Running in-process means the query engine and your application share the same address space. When DuckDB reads a Parquet file and materializes results, those results are memory buffers that your application can consume through zero-copy Arrow record batches. There is no serialization cost, no socket overhead, and no data transfer across a process boundary. For analytics scripts, ETL transforms, and embedded reporting, this matters.

The database engine itself is a shared library (libduckdb.so on Linux, duckdb.dll on Windows). Language bindings for Python, TypeScript/Node, R, Java, Go, and Rust all wrap this same library. The Node.js binding, for example, calls into the native addon which calls the C++ DuckDB API directly.

import Database from "duckdb";

// Database opens in-process, no server required
const db = new Database(":memory:");
const conn = db.connect();

// Query a remote Parquet file directly via httpfs extension
conn.all(
  `
  SELECT
    date_trunc('month', order_date) AS month,
    SUM(amount) AS total_revenue,
    COUNT(DISTINCT customer_id) AS unique_customers
  FROM read_parquet('s3://my-bucket/orders/*.parquet')
  WHERE order_date >= '2025-01-01'
  GROUP BY 1
  ORDER BY 1
`,
  (err, rows) => {
    if (err) throw err;
    console.log(rows);
  }
);

Vectorized Execution Engine

The classical database execution model is the Volcano (or iterator) model: each operator implements a next() method that returns one row at a time. The plan is a tree of operators. The root operator calls next() on its child, which calls next() on its child, recursively, until a base table scan produces a row that bubbles up through filters, projections, and aggregations. This is cache-unfriendly for analytical queries because each row crosses many operator boundaries, and the function call overhead per row accumulates badly over millions of rows.

DuckDB uses a vectorized execution model. Instead of processing one row at a time, each operator processes a batch (called a vector) of values. The default vector size is 2048 values. A filter operator receives 2048 rows, applies the predicate to all 2048 using tight loops, and passes the surviving rows to the next operator as a selection vector (a list of indices into the original buffer, not a copy of the data).

This matters for CPU efficiency in three ways. First, tight loops over arrays of the same type are SIMD-friendly: the compiler can autovectorize or you can use explicit SIMD intrinsics to process 4, 8, or 16 values per CPU cycle. Second, a vector of 2048 int64 values fits in L1/L2 cache (16 KB), so repeated operator passes touch hot data. Third, the number of virtual function dispatches drops from one per row to one per vector, which reduces branch mispredictions and instruction cache pressure.

DuckDB further fuses operators into pipelines. The pipeline concept is similar to Apache Flink’s chaining: adjacent operators that do not require materialization are executed in a single pass over the data. A scan followed by a filter followed by a projection is one pipeline. Only operators that block (hash joins building their hash table, sorts, global aggregations) break pipelines. This reduces intermediate materialization and keeps data in registers and cache as long as possible.

-- DuckDB executes this without materializing intermediate results:
-- scan -> filter -> project -> aggregate is one pipeline
EXPLAIN SELECT
  region,
  AVG(sale_amount) AS avg_sale
FROM sales
WHERE sale_date BETWEEN '2025-01-01' AND '2025-12-31'
  AND status = 'completed'
GROUP BY region;

The output of EXPLAIN shows a physical plan with HASH_GROUP_BY, FILTER, and SEQ_SCAN operators. The pipeline boundary is at HASH_GROUP_BY because aggregation requires seeing all input before producing output. Everything below it runs in a single streaming pass.

Columnar Storage Format

DuckDB’s native storage format (.duckdb files) stores data in row groups of 122,880 rows by default. Within each row group, data is stored column-by-column. Each column segment carries zone map metadata: the minimum and maximum value in that segment. Before reading a segment, the query engine checks whether the predicate can eliminate the entire segment. If a filter says WHERE sale_amount > 1000 and a column segment’s max is 800, the segment is skipped entirely without touching disk.

This is segment elimination, also called late materialization or zone map pruning. For range predicates on sorted or clustered columns it is extremely effective. A table with 100 row groups might satisfy a date range filter by reading only 5 of them.

Compression in DuckDB is lightweight and type-aware. The goal is fast decompression during scans, not maximum compression ratio. The techniques applied per column type include:

• Dictionary encoding: for low-cardinality columns (status codes, categories, enums), store a dictionary of unique values and replace column values with integer codes. Dictionary-encoded columns also accelerate GROUP BY because grouping on the code avoids string comparisons.
• Bit packing: integers are stored using only as many bits as required by the actual range. A column with values 0-255 uses 8-bit storage even if the declared type is int64.
• Run-length encoding (RLE): repeated consecutive values (sorted columns, sparse flags) are stored as (value, count) pairs.
• FSST (Fast Static Symbol Table): a compression scheme designed specifically for strings with shared substrings. FSST builds a symbol table of up to 255 byte sequences that appear frequently in the column and replaces occurrences with one-byte codes. It achieves meaningful compression ratios on string columns while being decompressible at multiple GB/s.

The buffer manager sits between the storage layer and the execution engine. It manages a pool of memory-mapped pages. When a query needs data from disk, the buffer manager loads pages into the pool, evicting LRU pages when memory pressure rises. This is the mechanism that enables out-of-core processing: datasets larger than RAM can still be queried, with performance degrading gracefully as pages spill to disk rather than crashing with an OOM error.

Query Optimizer

DuckDB’s optimizer follows a classical architecture but includes several analytical-specific techniques.

Join order enumeration uses dynamic programming over subsets of relations. For queries joining up to a threshold number of tables (typically around 10), the optimizer exhausts the search space to find the optimal join order based on estimated cardinalities. Beyond that threshold it falls back to a greedy heuristic. Getting join order right is the single biggest optimizer lever for analytical queries because a bad join order can increase intermediate result sizes by orders of magnitude.

Filter pushdown moves predicates as close to the base scan as possible. If a filter on sale_date appears after a join, and the predicate only references columns from one side of the join, the optimizer pushes it below the join into the scan of that table. This reduces the number of rows the join operator ever sees.

Common subexpression elimination (CSE) deduplicates repeated expressions within a query. If the same window function or subquery appears multiple times in a SELECT list, the optimizer computes it once and references the result.

DuckDB also does adaptive query processing at runtime. If a hash join builds its build-side hash table and the table turns out to be large, the engine can switch to a partitioned hash join that spills partitions to disk. This prevents out-of-memory failures on joins between large tables.

Morsel-Driven Parallelism

DuckDB’s parallel execution model is morsel-driven, following the paper “Morsel-Driven Parallelism” by Leis et al. (HyPer, 2014). The key insight is that work is divided into small chunks called morsels (think: a slice of a table scan or a partition of a hash table). Worker threads pull morsels from a task queue. No operator is assigned statically to a thread. Instead, the scheduler continuously dispatches morsels to whichever thread is free.

This approach has two advantages. First, it achieves natural load balancing: threads that finish their current morsel immediately pull the next one, so no thread sits idle while another is overloaded. Second, it enables NUMA-aware scheduling: morsels can be assigned to threads that share a NUMA node with the memory containing the data.

The number of threads defaults to the number of physical cores on the machine. You can override it:

-- Limit DuckDB to 4 threads
SET threads = 4;

-- Check current thread count
SELECT current_setting('threads');

Parallel aggregation uses thread-local hash tables. Each thread builds its own partial aggregate hash table over the morsels it processes. At the end of the scan phase, the partial tables are merged. This eliminates contention on a shared aggregation structure and scales linearly with core count on most GROUP BY workloads.

MVCC Concurrency Control

DuckDB implements multi-version concurrency control (MVCC) with optimistic transactions. Reads never block writes and writes never block reads. Each transaction works against a consistent snapshot of the database taken at transaction start.

DuckDB’s MVCC is designed around the assumption that concurrent write transactions are rare. For analytical workloads, this is almost always true: you have one writer bulk-loading data and many readers running queries against it. The implementation uses an undo buffer to track modifications. When a transaction modifies a row, the original version is saved in the undo buffer. Other transactions that started before the modification see the original version. Once the modifying transaction commits and all overlapping read transactions finish, the undo buffer entry is garbage-collected.

For write-heavy workloads with many concurrent transactions, DuckDB is not the right tool. A single in-process writer means no horizontal scaling for writes, and the MVCC overhead for high-contention OLTP patterns would be wasted on a system not designed for it.

Extension System

One of DuckDB’s most useful properties is its extension system, which turns it into a universal analytics frontend. Extensions are shared libraries that DuckDB loads at runtime. They register new scan functions, file format readers, and SQL functions.

The extensions that matter most in production:

httpfs: enables reading files directly from HTTP/HTTPS URLs and S3-compatible object storage. DuckDB will issue range requests (HTTP Range headers) to read only the row groups and column chunks needed by the query, minimizing data transfer. This makes it practical to query multi-gigabyte Parquet files on S3 without downloading them.

parquet: Parquet support is built in, but the extension handles nested types, dictionary encodings, and predicate pushdown into Parquet metadata. DuckDB can use Parquet row group statistics (min/max per column chunk) to skip row groups before transferring them, the same way its own zone maps work.

iceberg and delta: these extensions allow querying Apache Iceberg and Delta Lake tables directly. DuckDB reads the table metadata format to resolve which data files belong to the current snapshot, then scans only those files. This enables DuckDB as a query engine over data lakehouse tables without Spark.

postgres_scanner: attaches a live PostgreSQL database as a read-only data source. DuckDB pushes predicates and projections down into PostgreSQL via its wire protocol and streams the results back. You can join a PostgreSQL table against a local Parquet file in one SQL query.

spatial: adds geometry types and spatial functions (PostGIS-compatible). Backed by GEOS for geometry operations.

-- Install and load extensions
INSTALL httpfs;
LOAD httpfs;

SET s3_region = 'us-east-1';

-- Join a live Postgres table against a Parquet file on S3
ATTACH 'dbname=production host=db.internal' AS pg (TYPE POSTGRES, READ_ONLY);

SELECT
  pg.customers.name,
  SUM(orders.amount) AS total_spend
FROM pg.customers
JOIN read_parquet('s3://data-lake/orders/year=2025/**/*.parquet') AS orders
  ON pg.customers.id = orders.customer_id
GROUP BY pg.customers.name
ORDER BY total_spend DESC
LIMIT 100;

Production Considerations

Memory limits: by default DuckDB will use as much memory as the OS allows. For embedded use cases where DuckDB shares a process with application code, set an explicit limit to prevent it from starving other allocations.

SET memory_limit = '4GB';

Out-of-core processing: when DuckDB exceeds the memory limit, it spills intermediate data (hash tables, sort buffers) to a temporary directory. Performance degrades but the query completes. Set the temp directory explicitly if the default location has limited space.

SET temp_directory = '/mnt/fast-nvme/duckdb-tmp';

Persistence modes: DuckDB operates in either in-memory mode (:memory:) where data is lost on process exit, or file-backed mode where the database is persisted to a .duckdb file. The file format is transactional: a crash during a write leaves the file in the last committed state, not a corrupt state. WAL-style write-ahead logging handles durability.

Concurrent writers: DuckDB supports only one writer at a time per database file. Multiple readers can share a file-backed database in read-only mode. If you need concurrent writes from multiple processes, DuckDB is not the right choice. ClickHouse, PostgreSQL, or a purpose-built lakehouse format with a catalog service handles that better.

When DuckDB is NOT the right tool: DuckDB is the wrong choice for high-throughput transactional workloads (use PostgreSQL or a distributed OLTP database), for serving thousands of concurrent short queries (DuckDB is not a server and does not multiplex connections), for datasets that require distributed processing across many nodes (use Spark, Trino, or ClickHouse), and for use cases requiring strict multi-tenant query isolation (DuckDB’s single-process model means one tenant’s runaway query affects all others).

Tradeoffs Comparison

Dimension	DuckDB	SQLite	ClickHouse	PostgreSQL	Polars
Processing model	Vectorized, columnar	Row-at-a-time, Volcano	Vectorized, columnar	Row-at-a-time (columnar indexes optional)	Vectorized, DataFrame API
Storage format	Columnar row groups, zone maps	Row-oriented B-tree pages	MergeTree columnar with primary index	Row-oriented heap with optional columnar extensions	In-memory Arrow buffers (no native persistence)
Concurrency	One writer, many readers; MVCC	One writer at a time, readers block	Many concurrent writers, distributed	Full MVCC, many concurrent writers	Single-threaded mutations; thread-safe reads
Query language	Full SQL (ANSI + extensions)	Full SQL (some limitations)	SQL dialect (ClickHouse-specific)	Full SQL + PL/pgSQL	Python/Rust API, limited SQL via sql()
Deployment model	In-process library	In-process library	Server process (cluster or single node)	Server process	In-process library
Data movement	Zero-copy Arrow, remote Parquet, S3	Local files only (without extensions)	Local disk, S3, HDFS	Local disk, foreign data wrappers	In-memory; must load data explicitly
Sweet spot	Analytical queries over local files, Parquet, S3, and lakehouse tables from a single process	Embedded OLTP, simple read/write for apps, SQLite’s strength is simplicity	High-throughput analytical ingestion and queries at petabyte scale with a server fleet	General-purpose OLTP with ad-hoc analytics; mature tooling and ecosystem	In-memory DataFrame transformations in Python/Rust without SQL

Closing Insight

The reason DuckDB is displacing ad-hoc Python scripts is not that it is a new technology. Columnar storage, vectorized execution, and zone map pruning are well-understood techniques, present in Vertica, MonetDB (which DuckDB’s authors worked on), and Dremel since the late 2000s. What changed is the packaging: those techniques are now available as a zero-dependency library with a permissive license, full SQL support, and bindings for every major language.

The mental model shift is worth internalizing: DuckDB is not a database you deploy, it is a query engine you link. For analytical tasks that used to require either a Python ecosystem dependency chain or a full database server, that distinction matters considerably.