DuckDB

DuckDB is an in-process analytical database — the OLAP counterpart to SQLite. No server, no cluster, no setup: pip install duckdb (or one binary) gives you a columnar, vectorized SQL engine that queries Parquet and CSV files in place, joins them against Pandas DataFrames, and processes datasets larger than RAM on a laptop.

Its rise reframed a whole tier of data engineering: an enormous share of "big data" workloads are under 100 GB, and for those, a single machine running DuckDB routinely beats a Spark cluster on speed, cost, and simplicity combined. It has become the default engine for local analytics, pipeline prototyping, and the small-to-medium end of the warehouse spectrum.

TL;DR

Quick Example

The core party trick — SQL over raw files, no loading step:

And the same engine from the shell:

Core Concepts

In-Process, Like SQLite — but Columnar

The architecture comparison that locates DuckDB:

In-process means no network hop, no serialization, no credentials — a query over an Arrow table is close to a function call. The .duckdb database file is optional: many workflows never persist anything, treating Parquet on disk (or S3) as the storage layer and DuckDB as pure compute.

Files Are Tables

read_parquet, read_csv, read_json (usually implicit — just quote the path) handle globs, compression, schema inference, and messy CSVs unusually well. With the httpfs extension, the same syntax reaches s3:// and https:// — and Parquet's metadata lets DuckDB push filters and column selection into the scan, reading only the bytes the query needs. Extensions add Iceberg and Delta Lake table support and ATTACH for live Postgres/MySQL/SQLite databases — DuckDB as a federation point over wherever your data already lives.

An Analyst-Friendly SQL Dialect

DuckDB's SQL is PostgreSQL-flavored with ergonomics the big engines are now copying:

Plus first-class nested types (STRUCT/LIST/MAP), lambdas over lists, and full window-function support — see SQL for the foundations.

Bigger Than RAM, Smaller Than a Cluster

The engine is vectorized (processes data in CPU-cache-sized chunks), parallel across cores, and spills to disk when aggregations or joins exceed memory — so 200 GB of Parquet on a 16 GB laptop is a normal Tuesday, just a slower one. The practical envelope: single-digit GBs feel instant; tens-to-hundreds of GBs are comfortable on one beefy machine; beyond that — or when many users need concurrent access — you're back in warehouse/Spark territory. MotherDuck (the commercial cloud built on DuckDB) extends the same engine into a serverless shared service for exactly that transition.

Where DuckDB Fits

The role it does not fill: application OLTP backend (that's Postgres/SQLite — DuckDB allows one writer process, and per-row updates aren't its shape) and org-wide concurrent BI (that's the warehouse tier).

Common Mistakes

Using It as an Application Database

Tempting — it's a fast database in a file — but the single-writer model and columnar storage make it wrong for transactional workloads. Rule of thumb: many small reads/writes of individual rows → SQLite/Postgres; scans and aggregations → DuckDB.

Loading Data You Could Query in Place

CREATE TABLE AS SELECT * FROM 'big.parquet' followed by one query doubles your I/O and disk for nothing. Query files directly; persist into the DuckDB file only for repeated access where its own format's stats help.

CSV When Parquet Is Available

CSV parses slowly and carries no types or statistics. Convert once (COPY ... TO ... (FORMAT parquet)) and every subsequent query gets column pruning and filter pushdown — the same advice as everywhere in analytics, but DuckDB makes the conversion a one-liner.

Pandas Round-Trips for Things SQL Does

Pulling 50M rows into a DataFrame to group and join it burns memory DuckDB would never allocate. Do the heavy relational work in SQL, hand Pandas the result — the integration makes the handoff free.

Assuming Concurrency It Doesn't Have

One process writes at a time (many threads within it, fine; concurrent read-only processes, fine). A web app with multiple writer workers pointed at one .duckdb file will fight over the lock — that architecture wants Postgres or MotherDuck.

FAQ

DuckDB or Pandas/Polars?

Complements more than competitors — and DuckDB interoperates with both zero-copy. Reach for DuckDB when the work is relational (joins, group-bys, window functions over big files) or exceeds RAM; reach for DataFrames for programmatic manipulation, ML handoff, and plotting. Many workflows are DuckDB for the heavy lifting → Pandas/Polars for the last mile.

DuckDB or Spark?

Honest sizing: under ~100 GB per job, DuckDB on one machine is usually faster end-to-end, radically simpler, and free — much of the industry's Spark usage is at sizes DuckDB now handles. Spark keeps genuinely distributed scale, streaming, and established lakehouse platform tooling.

DuckDB or a cloud warehouse?

Different concurrency stories: warehouses serve whole organizations concurrently with governance; DuckDB serves a process. The boundary blurs at the edges — dbt+DuckDB pipelines feeding Parquet/Iceberg that both tiers read, and MotherDuck bridging into shared serverless — but org-wide BI still lands on the warehouse tier.

Is it production-ready?

Yes — 1.0 shipped in 2024, the format is stable, and it runs inside production pipelines, CLIs, and products (its embeddability means it ships inside other software the way SQLite does). The maturity questions are ecosystem ones (extension coverage, ops tooling), not engine correctness.

What's MotherDuck?

The commercial cloud service from DuckDB's creators: serverless DuckDB with shared storage, hybrid execution (queries split between your laptop and the cloud), and multi-user access — the upgrade path for teams whose DuckDB usage outgrows single-machine, without changing dialects.

Related Topics

References