DuckDB: an Embeddable Analytical Database

In one breath — the whole paper, compressed

SQLite proved that developers want a database that runs inside their own process, but it targets OLTP with a row-major execution engine over B-Tree storage, so its analytical performance is very poor. DuckDB fills that empty quadrant: a purpose-built embeddable relational DBMS with no server process and no client protocol, reached through a C/C++ API plus a SQLite compatibility layer and R and Python bindings. Its architecture is deliberately unoriginal, assembling a stripped-down Postgres parser, dynamic-programming join ordering, a vectorized interpreted execution engine, HyPer's serializable MVCC and the DataBlocks storage layout, because each of those pieces happens to fit the embedded analytics use case. Being a demonstration paper, the contribution is a live head-to-head rather than a benchmark study: four identical machines run SQLite, MonetDBLite, HyPer and DuckDB over TPC-H while the audience turns a physical dial to grow the data and watches queries per second and memory pressure diverge. The paper also states the requirement list that defines the class: OLAP speed without abandoning OLTP, cheap in-process table transfer, never crashing the host, and no awkward dependencies or process-level side effects.

Before this paper — the world it landed in

By 2019 data management had consolidated into large monolithic servers running as stand-alone processes, reached over client protocols that the same authors had earlier shown to be a serious bottleneck for moving result sets. The one mass-deployed exception was the embedded case, dominated by SQLite, the most widely deployed SQL engine with more than a trillion databases in active use, but built around transactional workloads with a row-major engine on B-Tree storage. Analysts doing interactive work in R and Python therefore used dplyr and Pandas instead, whose operators closely resemble stacked relational algebra but come without full-query optimization or transactional storage. The authors' own MonetDBLite, derived from MonetDB, had already proved the demand was real, with thousands of downloads per month and users ranging from the Dutch central bank to the New Zealand police. It also exposed problems that were very complex to address in a system not purpose-built for embedding, which is the direct motivation for starting over with DuckDB.

The problem — what was actually breaking

SQLite focuses on transactional workloads with a row-major execution engine over B-Tree storage, and as a consequence its performance on analytical workloads is very poor, leaving the embedded OLAP quadrant of the systems landscape empty.
Stand-alone database servers require considerable effort to set up properly and their client protocols constrict data access, while an embedded system shares an address space with its host and must exploit that unique opportunity for efficient transfer of tables in and out.
The data manipulation packages analysts actually use, dplyr in R and Pandas in Python, closely resemble stacked relational operators but lack full-query optimization and transactional storage.
Edge computing scenarios such as connected power meters currently forward their data to a central location for analysis, which is problematic given bandwidth limits on radio interfaces and also raises privacy concerns.
An embedded analytical engine must be highly efficient for OLAP without completely sacrificing OLTP, because dashboard scenarios have some threads updating data while other threads run the analytical queries that drive the visualizations.
If an embedded database crashes, for example on an out-of-memory situation, it takes the host process down with it, so queries must abort cleanly under resource pressure and the library must avoid external dependencies, signal handling, calls to exit and modification of singular process state such as locale or working directory.

Core ideas — the contributions, and why they work

The missing quadrant of embedded OLAP

The paper frames the systems landscape as a two-by-two of OLTP versus OLAP and embedded versus stand-alone, and observes that three cells are well populated while the embedded analytical cell holds a question mark. The demand for that cell comes from two sources that look unrelated: interactive data analysis in R and Python, and edge computing where data should be analysed on the node rather than shipped to a central site. The authors argue these two use cases surprisingly yield similar requirements, because both make portability and modest resource consumption critical. That convergence is what justifies building one system rather than two.

Purpose-built rather than derived

MonetDBLite showed that an existing analytical engine can be shrunk into a library and that people will use it, but it also uncovered issues that proved very complex to address in a system that was not designed for embedding. From that experience the paper distils an explicit requirement list: OLAP efficiency without abandoning OLTP, efficient in-process table transfer, a high degree of stability including clean query abort under resource exhaustion, and practical embeddability with no problematic compile-time or runtime dependencies. DuckDB is written against that list from scratch. The requirements list, more than any single algorithm, is the paper's lasting contribution.

State-of-the-art components, no new algorithms

The paper states plainly that while DuckDB is first in a new class of systems, none of its components is revolutionary in its own regard; instead the authors combined methods and algorithms from the state of the art that were best suited for their use case. Table 1 is the whole design in one page: a C/C++ and SQLite-compatible API, the libpg_query Postgres parser, a cost-based optimizer following Moerkotte and Neumann, a vectorized engine following MonetDB/X100, serializable MVCC following HyPer, and DataBlocks storage. The value lies in the selection and integration, since each component is chosen for its fit to the embedded constraint rather than for peak benchmark numbers. The paper also stresses that DuckDB is no research prototype, with millions of test queries run on each commit.

Vectorized interpretation instead of JIT

DuckDB executes queries with a vectorized interpreted engine rather than just-in-time compiling SQL into machine code, and the stated reason is portability, not performance. JIT engines depend on massive compiler libraries such as LLVM with additional transitive dependencies, which directly violates the embeddability requirement that the database must run wherever the host runs. Vectorization recovers most of the per-tuple interpretation overhead by amortising the interpreter dispatch across a whole vector of values, so the engine stays competitive without the toolchain. This is the clearest example in the paper of an embedding constraint overriding a raw-speed argument.

Serializable MVCC for a hybrid workload

DuckDB provides ACID compliance through Multi-Version Concurrency Control, specifically HyPer's serializable variant that is tailored to hybrid OLTP and OLAP systems. That variant updates data in place immediately and keeps the previous states in a separate undo buffer, which readers of concurrent transactions and aborting writers consult. The authors explicitly rejected simpler schemes such as Optimistic Concurrency Control because, even though analytics is DuckDB's main use case, modifying tables in parallel was an often-requested feature in the past. The design choice follows directly from the dashboard requirement stated in the introduction.

Read-optimized DataBlocks storage

For persistent storage DuckDB adopts the read-optimized DataBlocks layout, in which logical tables are horizontally partitioned into chunks of columns that are compressed into physical blocks using light-weight compression methods. Every block carries min and max indexes for each of its columns, so the scan can decide quickly whether a block is relevant to a query at all and skip it otherwise. Blocks additionally carry a lightweight index per column that restricts the amount of values scanned even further within a block that was not skipped. This gives coarse-grained and fine-grained pruning on compressed data, which is what makes scanning large tables on constrained hardware feasible.

A library, not a server

Being embedded, DuckDB has no client protocol interface and no server process; it is accessed through a C/C++ API from inside the host. To capture existing users it ships a SQLite compatibility layer so that applications previously using SQLite can switch by re-linking or library overloading, without changing application code. As with MonetDBLite, the standard database APIs for R, namely DBI, and for Python, namely PEP 249, are implemented so the engine appears as an ordinary database driver in data-science tooling. Removing the protocol boundary is what turns result transfer from a serialization problem into a memory-sharing problem.

How it works — the mechanism, concretely

Parsing with a stripped-down Postgres parser

The SQL parser is derived from the Postgres parser, stripped down as far as possible and packaged as the libpg_query library. This gives DuckDB a full-featured and stable front end for the most volatile form of its input, arbitrary SQL query strings, without writing a grammar from scratch. The parser takes a query string and returns a parse tree of C structures, which is then immediately transformed into DuckDB's own parse tree of C++ classes so that the reach of the Postgres data structures is limited to that one boundary. The resulting tree consists of statements such as SELECT and INSERT and of expressions such as SUM(a)+1.

Binding and logical plan generation

The logical planner has two parts: a binder and a plan generator. The binder resolves every expression that refers to a schema object such as a table or view, attaching column names and types. The plan generator then rewrites the parse tree into a tree of basic logical operators such as scan, filter and project, producing a fully type-resolved logical plan. DuckDB keeps statistics on the stored data and propagates them through the expression trees during planning, where they serve the optimizer and additionally prevent integer overflow by upgrading types when required.

Optimization and physical planning

The optimizer performs join order optimization by dynamic programming, with a greedy fallback for join graphs too complex for the dynamic program to finish. It flattens arbitrary subqueries using the unnesting technique of Neumann and Kemper, and applies a set of rewrite rules on the expression tree such as common subexpression elimination and constant folding. The physical planner then maps the optimized logical plan to physical operators, choosing implementations where a choice exists: a scan may use an existing index instead of reading the base table based on selectivity estimates, and a join may be executed as a hash join or a merge join depending on the join predicates.

Vector layout and the vector operation library

DuckDB operates on vectors holding a fixed maximum number of values, 1024 by default. Fixed-length types such as integers live in native arrays, while variable-length values such as strings are a native array of pointers into a separate string heap. NULL values are tracked in a separate bit vector that exists only if NULL values actually appear in the vector, which allows binary vector operations to intersect the NULL vectors quickly and avoids redundant computation on the common all-non-NULL case. To avoid excessive shifting of data inside a vector after operations such as filtering, a vector may carry a selection vector: a list of offsets stating which indices of the vector are still relevant. An extensive library of vector operations backs the relational operators, expanded across all supported data types using C++ templates.

Vector Volcano execution

Execution follows what the paper calls a Vector Volcano model, a pull-based iterator pipeline in which the unit exchanged between operators is a chunk rather than a single tuple. A chunk is a horizontal subset of a result set, a query intermediate or a base table. Query execution begins by pulling the first chunk from the root node of the physical plan; that node recursively pulls chunks from its children until the recursion reaches a scan operator, which produces chunks by reading from the persistent tables. The query is complete when the chunk arriving at the root is empty, so end-of-stream needs no separate signalling.

MVCC with in-place update and undo buffers

Transactions run under HyPer's serializable MVCC variant, designed for hybrid OLAP and OLTP systems. A writer updates the data in place immediately, so scans of the current version stay dense and cache-friendly, which matters because analytical scans dominate. The previous states are not discarded but written to a separate undo buffer, where concurrent transactions read them to reconstruct their own snapshot and aborting transactions read them to roll back. The scheme was chosen over Optimistic Concurrency Control specifically because parallel table modification had been a frequently requested feature.

DataBlocks persistence and block skipping

Persistent tables are stored in the read-optimized DataBlocks layout. A logical table is horizontally partitioned into chunks; within a chunk the columns are compressed with light-weight compression methods into physical blocks. Each block carries min and max indexes for every column, so a scan can quickly determine whether the block can contain any qualifying row and skip it entirely if not. Each block also carries a lightweight per-column index that restricts the number of values that must be scanned inside blocks that were not skipped, so pruning happens at two granularities without decompressing everything.

What the paper showed — measurements and proofs

As of writing, DuckDB runs all TPC-H queries and all but two TPC-DS queries, and the authors expect complete TPC-DS coverage by the time the demonstration is presented.
DuckDB already completes most of SQLite's SQL logic test suite, which contains millions of queries, and millions of test queries are run on each commit to ensure correct operation and completeness of the SQL interface.
The execution engine uses vectors of a fixed maximum size of 1024 values by default, with fixed-length types in native arrays, strings as pointers into a separate string heap, and a NULL bit vector materialized only when NULL values are present.
The demonstration setup is four identical benchmark computers running SQLite, MonetDBLite, HyPer and DuckDB, each preloaded with the TPC-H tables, wired over Ethernet to a fifth management computer, with a screen showing at least query completion rate in queries per second and memory pressure, and a physical dial controlling how much input data the configured query reads.
The paper reports no measured benchmark numbers; it predicts qualitatively that all systems behave comparably on very small data, that SQLite will suffer from its row-based execution model and MonetDBLite from excessive intermediate result materialization due to its bulk processing model, and that HyPer, while extremely fast at processing queries, will not transfer result sets as quickly as DuckDB because it uses a socket client protocol.
The demand argument rests on reported deployment figures rather than experiments: SQLite is cited as the most widely deployed SQL database engine with more than a trillion databases in active use, and MonetDBLite is reported to enjoy thousands of downloads per month with users from the Dutch central bank to the New Zealand police.

Limits and trade-offs — conceded and discovered

Conceded by the paper: the DataBlocks storage scheme and cardinality estimation are not finished, and a buffer manager is not yet implemented although it is planned, so at the time of writing the storage and memory management story is incomplete.
Conceded by the paper: DuckDB supports only inter-query parallelism, with intra-query parallelism still to be added, along with a work-stealing scheduler to balance resources between short and long running queries and to balance resource usage against the host application.
Conceded by the paper: two TPC-DS queries do not yet run, and the promised complete coverage is stated as an expectation for the demonstration date rather than an achieved result.
Not conceded, but visible in the text: the paper contains no quantitative evaluation at all, and the head-to-head against SQLite, MonetDBLite and HyPer is a predicted outcome of a live demonstration rather than a controlled experiment; the choice of interpretation over JIT is defended on portability grounds while the paper itself calls the JIT-based HyPer extremely fast at processing queries.
Exposed by later work: nearly every roadmap item here became a real redesign, as DuckDB went on to replace the DataBlocks-derived scheme with its own custom columnar storage format, add a buffer manager, and adopt morsel-driven intra-query parallelism, so the storage and parallelism described in this paper should be read as an early snapshot rather than the architecture that made DuckDB successful. The speculative self-checking direction, keeping checksums on persistent and intermediate data and piggy-backing verification on scan operators, remained a proposal in this paper.

What it became — the systems that inherited it

DuckDB became what this four-page demo predicted it would be: the default analytical engine for local, in-process data work, and the OLAP counterpart to SQLite that the paper's Figure 1 left as a question mark. Its Python and R bindings, together with zero-copy interchange with Arrow and Pandas data frames, made SQL a first-class option inside notebooks and dataframe pipelines, and dbt, Jupyter workflows and desktop BI tools adopted it as a local engine. The requirement list in Section 1, no external dependencies, no process-level side effects, never take the host down, became the de facto specification for the embedded analytics category, later joined by ClickHouse's clickhouse-local and chDB. DuckDB-WASM carried the same engine into browsers, MotherDuck built a hybrid local and cloud service on it, and extensions such as httpfs and the Iceberg and Delta readers turned it into a query engine over object storage rather than only over local files. Architecturally it helped normalise vectorized interpretation over JIT compilation for portable engines, an argument echoed by Apache DataFusion and Polars in the same single-node analytics wave. It also revived the case that a single well-engineered node handles most real analytical workloads, pushing back on the assumption that analytics implies a distributed cluster.

In the paper’s words — verbatim

“The immense popularity of SQLite shows that there is a need for unobtrusive in-process data management solutions. However, there is no such system yet geared towards analytical workloads.”

Abstract

“While DuckDB is first in a new class of data management systems, none of DuckDB's components is revolutionary in its own regard. Instead, we combined methods and algorithms from the state of the art that were best suited for our use cases.”

§2 Design and Implementation

“High degree of stability, if the embedded database crashes, for example due to an out-of-memory situation, it takes the host down with it. This can never happen.”

§1 Introduction

Vocabulary — as this paper uses it

Embedded database: A database system that is a linked library running completely inside a host process, with no server process and no client protocol. SQLite is the canonical example, and DuckDB is positioned as its analytical counterpart.
Vectorized interpreted execution: An execution style, taken from MonetDB/X100, in which operators are interpreted but process a whole vector of values per call instead of one tuple. It was chosen over just-in-time compilation because JIT requires massive compiler libraries such as LLVM that break embeddability.
Vector Volcano model: DuckDB's execution model: a classical pull-based Volcano iterator pipeline in which each next call moves a chunk of vectors rather than a single tuple. Execution starts at the plan root and ends when the chunk arriving at the root is empty.
Chunk: The unit of data passed between operators, defined in the paper as a horizontal subset of a result set, a query intermediate or a base table. Chunks are produced by scan operators reading persistent tables and flow upward through the plan.
Selection vector: A list of offsets into a vector stating which indices of that vector are currently relevant. It lets operators such as filters mark surviving rows without physically shifting data inside the vector.
String heap: The separate memory region holding variable-length values, which a vector references through a native array of pointers. It keeps the vector itself fixed-width so vectorized operations stay uniform across types.
Serializable MVCC (HyPer variant): The concurrency control scheme DuckDB implements, tailored for hybrid OLAP and OLTP systems: data is updated in place immediately and the previous states are kept in a separate undo buffer for concurrent transactions and for aborts. It was preferred over Optimistic Concurrency Control because parallel table modification was an often-requested feature.
DataBlocks: The read-optimized persistent storage layout DuckDB adopts, in which tables are horizontally partitioned into chunks of columns compressed into physical blocks by light-weight compression. Each block carries per-column min and max indexes for skipping plus a lightweight per-column index to restrict how many values are scanned.
libpg_query: The Postgres SQL parser extracted as a standalone C library and stripped down, used as DuckDB's front end. Its output parse tree of C structures is immediately converted into DuckDB's own C++ parse tree so the Postgres data structures do not leak into the rest of the system.

On the timeline — where this sits in the story

Year2019–2020s

MilestoneDuckDB

Problem solvedDevelopers had SQLite for embedded OLTP-style use, but no comparably simple in-process database optimized for analytics.

How it solved itDuckDB offered embedded, vectorized analytical SQL over local data, files, and data frames.

Why it matteredMade local analytical processing and notebook/dataframe SQL much easier.

DDIACh. 4: Storage & Retrieval Ch. 11: Batch Processing

View on the timeline