DuckDB is an open-source, in-process analytical database that executes SQL queries directly inside the host application, without a separate server process. Among database systems it occupies the niche of embedded OLAP: it plays the role for analytical workloads that SQLite plays for transactional ones, running as a library in Python, R, Java, Node.js, C and C++ or as a standalone command-line tool.
DuckDB was created at the CWI research institute in Amsterdam and is released under the MIT license. Its engine combines columnar storage, vectorized execution and a PostgreSQL-like SQL dialect, which lets a laptop query files of tens or hundreds of gigabytes in seconds.
How DuckDB executes analytical queries
DuckDB processes data in vectors of about 2,048 values per column rather than one row at a time. Each operator, such as a filter, hash join or aggregation, works on a whole vector in a tight loop, which keeps data in CPU caches and lets the compiler use SIMD instructions. This vectorized model delivers much of the speed of compiled query engines with simpler code.
Queries run in parallel through morsel-driven parallelism: the input is cut into small chunks that worker threads pick up dynamically, so all cores stay busy even when data is skewed. Large joins, sorts and aggregations can spill to disk when they exceed the configured memory limit, which allows workloads larger than RAM.
The main architectural components and their effect are shown below.
| Component | What it does | Practical effect |
|---|---|---|
| Columnar storage | Stores each column in compressed row groups | Reads only the columns a query needs |
| Vectorized executor | Processes batches of values per operator call | High throughput on aggregations and scans |
| Cost-based optimizer | Reorders joins and pushes down filters | Efficient plans for complex SQL |
| MVCC transactions | Gives ACID guarantees with snapshot isolation | Safe concurrent reads during writes |
| Out-of-core operators | Spill intermediate data to temporary files | Queries larger than available memory |
Storage, file formats and extensions
DuckDB can persist data in a single database file or work purely in memory. Equally important, it queries external files in place: SELECT * FROM 'events/*.parquet' scans a folder of Parquet files directly, using their column statistics to skip irrelevant row groups.
Functionality beyond the core is delivered through loadable extensions. The ones below are among the most widely used.
| Source or extension | Capability | Typical scenario |
|---|---|---|
| Parquet | Reads and writes columnar files with predicate pushdown | Data lake analytics |
| CSV and JSON | Auto-detects delimiters, types and nested fields | Ad hoc exploration of exports |
| httpfs | Reads files over HTTP and S3-compatible storage | Querying cloud object stores |
| postgres and mysql scanners | Attaches live operational databases | Joining production data with files |
| Iceberg and Delta | Reads open table formats | Lakehouse queries without a cluster |
| spatial | Adds geometry types and GIS functions | Geospatial analysis |
In Python, DuckDB can query pandas and Polars data frames and Apache Arrow tables by name, often with zero-copy access to Arrow memory. This integration makes it a common replacement for slow data frame operations in notebooks.
DuckDB compared with other database systems
DuckDB is best understood by comparing it with neighbors that share either its embedded deployment or its analytical focus. The table contrasts the main design choices.
| System | Deployment | Storage layout | Optimized for |
|---|---|---|---|
| DuckDB | In-process library | Columnar | Analytical queries on one machine |
| SQLite | In-process library | Row-based B-trees | Transactional point reads and writes |
| PostgreSQL | Client-server | Row-based heap | General-purpose OLTP with many users |
| ClickHouse | Client-server cluster | Columnar | High-volume, multi-user analytics |
The decisive distinction is concurrency model. DuckDB supports many readers but only one process writing to a database file at a time, so it is not a backend for a multi-user web application. ClickHouse or a cloud warehouse fits shared, always-on analytics, whereas DuckDB excels when one analyst, job or service needs fast answers locally.
When DuckDB is the right choice
DuckDB fits workloads where data is read far more than it is changed and where the volume is large for a spreadsheet but small for a cluster, typically from megabytes to a few terabytes. The decision table summarizes common situations.
| Situation | Recommendation | Reason |
|---|---|---|
| Local analysis of Parquet or CSV | Use DuckDB | No loading step and fast scans |
| ETL step inside a pipeline | Use DuckDB | Single binary, no infrastructure to run |
| Web app with many concurrent writers | Use PostgreSQL or MySQL | Single-writer limit |
| Petabyte-scale shared warehouse | Use a distributed system | Single-node compute ceiling |
| Analytics inside a browser | Use DuckDB-Wasm | Runs client-side via WebAssembly |
A popular pattern pairs DuckDB with dbt for local transformation development, then runs the same models against a cloud warehouse in production. Another pattern uses DuckDB as a feature engineering engine that feeds machine learning training directly from Parquet.
Limitations and common misconceptions
DuckDB is fast, but its design trade-offs are deliberate and should be understood before adoption.
- It is not a replacement for an OLTP database: frequent single-row updates and many concurrent writers perform poorly.
- Performance depends on local memory and disk; querying remote S3 files is bounded by network throughput.
- The storage format became stable with version 1.0 in 2024; files written by much older releases may need export and reimport.
- Being embedded, it has no built-in user management or network access control; security comes from the host application.
- Very wide row-by-row INSERT loops are slow; bulk loading with COPY or from data frames is the efficient path.
A frequent misconception is that an in-process engine must be a toy. In practice DuckDB implements full ACID transactions, window functions, CTEs and complex joins, and it scores competitively on standard TPC-H style benchmarks for single-node analytics.
Explained without jargon
DuckDB is a tool for asking questions about large amounts of data on your own computer. Most powerful databases run as separate servers that someone must install and maintain. DuckDB instead lives inside the program you already use, such as a Python notebook, so there is nothing extra to set up.
It is built to summarize data quickly, for example to total sales by month across millions of rows. It reads only the columns it needs and works on many values at once, a bit like a cashier scanning a whole basket instead of typing each price. The result is warehouse-style analysis on a laptop.
A typical use case
A data analyst at a logistics company receives 18 months of delivery events exported as about 600 Parquet files, roughly 90 GB in total. Loading them into the company warehouse would take a ticket and several days. Instead, the analyst opens a Python notebook, imports DuckDB and runs a SQL query that joins the event files with a small CSV of depot locations and computes late-delivery rates per depot and week.
With a memory limit of 12 GB on a 16-core workstation, the first full scan finishes in under a minute, and follow-up queries on filtered columns return in a few seconds thanks to row-group skipping. The analyst then writes the aggregated result, a few thousand rows, back to Parquet for a dashboard. The analysis is delivered the same afternoon without provisioning any server.