# DuckDB

DuckDB is an in-process analytical database that runs fast SQL on local files, Parquet and data frames without a server. Learn why analysts and engineers love it.

## Overview

DuckDB is an open-source analytical database that runs inside your own program. There is no server to install, configure or connect to. You import a library in Python, R, Java, Node.js, Rust or another language, or start a small command-line tool, and you can immediately run SQL over CSV, Parquet and JSON files, data frames and its own database files.

DuckDB is built for analytics, often called OLAP: scanning many rows, grouping, joining and aggregating. Its engine stores and processes data by column and works on batches of values at a time, using all CPU cores. It can also spill to disk when a query needs more memory than the machine has. Together these make a laptop or a single server surprisingly capable for work that once needed a cluster or a cloud warehouse.

People often call DuckDB "the SQLite of analytics". SQLite is the tiny database that lives inside phones and apps for everyday record-keeping. DuckDB plays the same embedded role for number crunching. An everyday analogy: a cloud warehouse is a large commercial kitchen you book by the hour. DuckDB is a very good set of knives in your own kitchen. For most meals you never need to book the big kitchen at all.

## What it is

| Feature | What it means |
|---|---|
| In-process | Runs inside the host application; no separate server process |
| Columnar storage | Database files store data by column with compression |
| Vectorised execution | Operators process batches of values, using CPU caches and SIMD well |
| Parallelism | Queries use multiple threads across cores |
| Out-of-core processing | Large sorts, joins and aggregates can spill to disk |
| Direct file queries | `SELECT` from Parquet, CSV and JSON files, local or remote |
| Data frame integration | Query pandas, Polars and Arrow data without copying it in first |
| ACID transactions | Transactions with multi-version concurrency control |
| Extensions | Loadable modules for remote storage, spatial data, lake formats, other databases and more |
| Friendly SQL | PostgreSQL-like dialect with conveniences such as `GROUP BY ALL` and `SELECT * EXCLUDE` |

A DuckDB database can be in memory only, or a single file on disk that you can copy like any other file.

## Where it came from

DuckDB was created by Mark Raasveldt and Hannes Mühleisen at CWI, the Dutch national research institute for mathematics and computer science in Amsterdam. Their research showed that data scientists struggled to use client-server databases for analytics and that moving data out of databases into scripts was slow. They designed an analytical engine that could be embedded the way SQLite is. The first public releases came in 2019, under the MIT licence.

The intellectual property is held by the non-profit DuckDB Foundation, and a company founded by the creators provides support and development. Version 1.0.0, released in June 2024, signalled a stable storage format and API. Version 1.4, released in September 2025, was the first long-term support release. DuckDB 2.0 followed in October 2026.

## How it works

### Running a query

1. The parser reads SQL and the binder resolves table and column names, including file paths and data frames.
2. The optimiser rewrites the logical plan. It pushes filters and column selections down to the scans, reorders joins and removes unneeded work.
3. The physical plan is broken into pipelines. Each pipeline streams data chunks, typically 2,048 values per column at a time, through operators such as filter, projection, hash join and aggregate.
4. Pipelines run in parallel on several threads. Each thread works on a different part of the input.
5. If an operator's state outgrows the memory limit, it writes partitions to temporary files and continues.

### Reading files

When you query a Parquet file, DuckDB reads the footer, keeps only the columns referenced in the query and skips row groups whose statistics cannot match the filter. With the `httpfs` extension it can do the same over HTTP and S3-compatible storage, fetching only the byte ranges it needs. CSV reading includes automatic detection of delimiters, headers and types.

### Working with data frames

In Python, DuckDB can see pandas and Polars data frames and Arrow tables in local variables and query them by name. Results can come back as data frames or Arrow tables. Where layouts allow, this avoids copying.

```python
import duckdb
duckdb.sql("""
  SELECT region, sum(amount) AS total
  FROM 'sales/*.parquet' WHERE year = 2026
  GROUP BY region ORDER BY total DESC
""").show()
```

### Storage and concurrency

A database file holds tables in compressed columnar row groups with a write-ahead log for durability. Within one process, many threads can read and write concurrently. Across processes, one process can open a file for writing, or several can open it read-only. DuckDB is not designed as a shared server that many separate clients write to at once.

## Key concepts and terms

| Term | Meaning |
|---|---|
| OLAP | Online analytical processing: large scans and aggregations |
| In-process database | A database engine that runs as a library inside an application |
| Vector | A batch of values for one column processed together |
| Pipeline | A chain of operators that stream data without stopping |
| Morsel-driven parallelism | Splitting input into chunks that threads pick up |
| Out-of-core | Processing data larger than memory using disk |
| Replacement scan | Treating a file path or data frame name as a table |
| Extension | Loadable module that adds functions, file systems or formats |
| MVCC | Multi-version concurrency control for transactions |
| Write-ahead log | Journal of changes written before they reach the main file |

## A worked example

This scenario is illustrative. An analyst has 40 GB of Parquet files: three years of web orders, 900 million rows, stored in object storage. She has a laptop with 16 GB of memory and a pandas data frame of 5,000 marketing campaigns.

1. She installs DuckDB with `pip install duckdb` and loads the `httpfs` extension with her storage credentials.
2. She runs a query that joins orders to the campaign data frame and sums revenue by campaign and month for 2026 only.
3. DuckDB reads only the five columns used and, because files are partitioned by year, skips the earlier two years entirely. It streams about 4 GB over the network rather than 40 GB.
4. The hash join and aggregate fit in memory. A second query that sorts all order lines does not, so DuckDB spills to a temporary folder and still completes.
5. She saves the summary to a local `.duckdb` file and as a Parquet file for a colleague, then hands the same SQL to a data engineer, who uses it as a test fixture for the production pipeline.

## Strengths and limitations

**Strengths:**

- No server: install a library and start querying.
- Fast columnar, vectorised and parallel execution.
- Excellent support for Parquet, CSV, JSON and Arrow.
- Works well with pandas, Polars and R data frames.
- Handles data larger than memory on one machine.
- Permissive MIT licence and a portable single-file database.

**Limitations:**

- Runs on a single machine; it does not distribute queries across a cluster.
- Not built for many separate processes writing to the same database at once.
- Not an operational database for high volumes of small transactions.
- Performance over remote storage depends on network bandwidth and file layout.
- Some extensions and features are newer and evolve quickly between releases.

## How it compares with alternatives

| Tool | Model | Best for |
|---|---|---|
| DuckDB | Embedded analytical SQL | Local and single-node analytics, pipelines and testing |
| SQLite | Embedded row-store SQL | Application state and small transactional workloads |
| [Polars](/tools/polars) | Embedded DataFrame library | Python or Rust data wrangling with expressions |
| [ClickHouse](/tools/clickhouse) | Analytical database server | Shared, real-time analytics for many users |
| [Apache Spark](/tools/apache-spark) | Distributed engine | Data too large or too heavy for one machine |
| [BigQuery](/tools/bigquery) | Serverless cloud warehouse | Shared, governed analytics at petabyte scale |

## Practical guidance and best practices

1. Query Parquet directly instead of importing CSVs each time; convert CSVs to Parquet once.
2. Set a memory limit and a temporary directory on shared machines so large queries spill predictably.
3. Partition and sort large file collections so DuckDB can skip files and row groups.
4. Use DuckDB to develop and unit-test SQL transformations locally before running them on larger engines.
5. Keep one writer process per database file; use read-only connections for others.
6. Return results as Arrow or Polars when the next step is another data tool, to avoid conversions.
7. Pin the DuckDB version in projects and prefer a supported long-term release for production.
8. Move to a distributed engine only when data or concurrency genuinely outgrows one machine.

## Common mistakes

- Treating DuckDB like a database server and pointing many writers at one file.
- Reading thousands of tiny remote files, where request overhead dominates.
- Loading data into pandas first and then querying it, instead of querying files directly.
- Leaving the default memory and thread settings on a shared server that runs other jobs.
- Assuming SQL dialect details match another database exactly without testing.
- Using it as the transactional back end of a busy web application.

## Frequently asked questions

### Does DuckDB need a server?

No. It runs inside your program as a library or through its command-line tool. There is nothing to install or run separately, although some services build hosted offerings on top of it.

### Can DuckDB handle data larger than memory?

Yes, for many operations. It streams data from files and can spill large sorts, joins and aggregations to disk. Very heavy queries will slow down when they spill, but they usually complete.

### How is DuckDB different from SQLite?

Both are embedded, but they target different work. SQLite stores rows and excels at small reads and writes for applications. DuckDB stores columns and excels at large scans, joins and aggregations for analysis.

### Can DuckDB read from S3 or other object storage?

Yes. Extensions such as `httpfs` let it read Parquet, CSV and JSON over HTTP and S3-compatible APIs, and other extensions add support for lake table formats. It reads only the parts of files it needs.

### Is DuckDB good for production pipelines?

It is widely used for batch transformations, data quality checks and embedded analytics in applications. It fits best where one process does the work. For many concurrent users or very large data, a server or distributed engine is a better fit.

### Can I use DuckDB with dbt?

Yes. A community-maintained adapter lets [dbt](/tools/dbt) run models on DuckDB, which is popular for local development and small to medium data platforms.

## Glossary

- Embedded database: a database that runs inside the application that uses it.
- Columnar compression: compressing each column separately using its patterns.
- Hash join: a join that builds a hash table on one input and probes it with the other.
- Projection pushdown: reading only the columns a query needs.
- Filter pushdown: applying filters while scanning, to skip data early.
- Spilling: writing intermediate data to disk when memory runs short.
- LTS: long-term support, a release maintained for an extended period.
- Arrow: the in-memory columnar format DuckDB can exchange data in.

## Summary

DuckDB puts a fast analytical SQL engine inside any program, with no server to run. Its columnar, vectorised and parallel design, direct reading of Parquet, CSV and JSON, and close integration with data frames and [Apache Arrow](/tools/apache-arrow) make a single machine handle tens or hundreds of gigabytes comfortably. It is not a shared server or a distributed engine, so heavy multi-user or cluster-scale workloads still belong elsewhere. For local analysis, pipeline development and embedded analytics, it is often the simplest and fastest choice, and it works well alongside [Apache Parquet](/tools/apache-parquet), [Polars](/tools/polars) and [dbt](/tools/dbt).