Summary

Problem: before DuckDB, users often had to choose between two database styles.

• SQLite-style embedded databases: They were portable, small, and easy to add into applications

Problem: This database type is only intended for transactional tasks such as inserting, updating, and looking up.

• Analytical databases: This database style was extremely useful for performing large scans, joins, aggregations, and summaries

Problem: They typically operate on a separate server process and require more resources and set-up.

• This differences raised the problem when working with R, Python, notebooks, and local files for data scientists as they needed analytical speed and query optimization without the complexity that came with managing a full database server.

DuckDB has been created to address the limitations of SQLite and will not require additional database features to be added to SQLite, nor will it reduce the size/scale of on-site traditional databases. The design of DuckDB is directly tied to solving this problem:

• No server process -> runs anywhere, inside any application.
• Vectorized execution -> fast analytical queries on local data.
• Native optimizations -> works directly within R and Python workflows.

OLTP vs. OLAP

The missing category exists because embedded databases and analytical databases were usually designed for 2 different workloads:

OLTP means Online Transaction Processing. Many small and frequent operations like insert, update, or lookup records are what make up these workloads. SQLite does well with these workloads because it is lightweight, portable, and can be easily embedded in applications.

OLAP means Online Analytical Processing.
These workloads involve analyzing larger amounts of data through scans, joins, grouping, aggregations, and summaries. Traditional analytical databases are strong here, but they usually require a separate server process.

-> DuckDB brings OLAP-style analytical performance into an embedded database. => DuckDB fills the gap by being embedded like SQLite, but analytical like an OLAP database.

What Is DuckDB?

DuckDB is a free, open-source, embedded analytical database that runs as a library inside your application with no server required:

• Embedded like SQLite, but fast for analytics.
• Full SQL support, MIT license.
• Compatible with existing SQLite applications.
• Designed for R and Python workflows.

Importance of DuckDB

DuckDB affects how people actually work with data.

• Interactive analysis: In R and Python, users often work with local files, data frames, and notebooks. Libraries such as dplyr and pandas already implement SQL-like operations (filtering, joining, grouping, and summarizing), but do not provide all the features of databases, including full query optimization.

• Edge computing: Data is often collected locally by devices such as sensors and/or power meters. The raw data produced by these devices is sent to a centralized server, which often leads to issues with bandwidth, privacy, or resource consumption (e.g., CPU, memory, storage). Embedded analytical databases can be used to process this raw data closer to where it was generated.

These two use cases both need a portable, stable, efficient, and lightweight system.

DuckDB addresses this directly: it is embedded, server-free, analytically optimized, and works inside existing R and Python tools without replacing them.

How DuckDB works

Vectorized execution processes 1,024 rows simultaneously instead of one at a time — far faster for analytical queries.

Columnar storage keeps each column’s data together, so a query about salaries reads only the salary column, skipping everything else.

MVCC lets multiple threads read and write simultaneously without conflicts -> essential for dashboards updating in real time.

Demonstration Setup

The paper does not provide a full benchmark result table. Instead, it presents a live demo designed to compare how four systems respond under the same analytical workload.

In the demo, SQLite, MonetDBLite, HyPer, and DuckDB run on identical benchmark computers. Each system uses the same TPC-H data and the same analytical query. The audience can increase the dataset size and observe live metrics such as queries/sec and memory pressure.

Expected behavior as the workload grows:

• SQLite: easy to embed, but slows on OLAP queries because it processes data in a row-based model.
• MonetDBLite: analytical, but can create large intermediate results that increase memory pressure.
• HyPer:very fast, but client-server result transfer can become a bottleneck.
• DuckDB: built for this use case, embedded, vectorized, and optimized for analytical SQL inside the same process.

Benchmark Results

Tested live at SIGMOD 2019 against three systems on identical hardware using the TPC-H dataset. A dial controlled dataset size.

DuckDB was the only system that was embedded, analytically fast, and transferred results quickly, because everything runs in the same process with no network overhead.

Trade-offs & Limitations

• Demonstration-focused evidence: The DuckDB paper presents DuckDB as an alternative to traditional benchmarks by demonstrating its use in a live comparison with other database systems. Therefore, the paper has an extensive discussion of how DuckDB was designed and what to expect from it, but no detailed performance statistics.

• Portability over JIT: DuckDB decides to use vectorized interpreted execution rather than JIT compilation for several reasons. The most prominent reason is that JIT can produce very fast execution code, but JIT relies on a large number of dependencies. This causes DuckDB to accept some potential performance loss in exchange for the benefits of being lightweight, portable, and easier to integrate with other systems.

• Stability pressure: Since DuckDB operates as a database within the host application, it can cause the host application’s process to crash if DuckDB crashes. Therefore, considerations for controlling memory during a crash and for allowing applications to exit gracefully in case of such crashes is a very important part of programming DuckDB.

• Still developing in 2019: There are features that DuckDB lacked at the time this paper was written, such as a buffer manager, intra-query parallelism, full TPC-DS compliance, and R/Python APIs.

Main critique: The missing features of DuckDB simply indicate its design priorities. The developers of DuckDB do not want the database system to be a large, stand-alone database system. DuckDB is designed for use in local, portable, embedded analytical workflows.

Impact on Data Science

DuckDB’s most direct impact is on how data scientists interact with data in everyday workflows.

Before DuckDB, running analytical SQL on local data in R or Python required either accepting the slowness of SQLite, spinning up a full database server, or relying on libraries like dplyr and pandas that lack full query optimization.

After DuckDB, those workflows run faster with full SQL, inside the same R or Python session, on the same local files, with no server and no setup.

• R integration: DuckDB connects directly to R data frames and local .csv or .parquet files. Queries run in-process, meaning results come back as R objects instantly with no data transfer overhead.
• Python integration: DuckDB works natively with pandas DataFrames and numpy arrays, meaning you can query a DataFrame with SQL directly, which is something neither SQLite nor a remote database handles well.
• Relevance to 405: The tradeoffs DuckDB makes, namely vectorized vs. row-based execution, columnar vs. row storage, embedded vs. server, are exactly the kinds of decisions that define how data systems perform at scale.

References

Raasveldt, M. & Mühleisen, H. (2019). DuckDB: An Embeddable Analytical Database. SIGMOD ’19. ACM. https://doi.org/10.1145/3299869.3320212