Malhar Ujawane

Blog

Explainer·(updated October 31, 2026)

What is DuckDB? The in-process analytical database

DuckDB is an in-process SQL database built for analytics: no server, a columnar engine, and direct queries over Parquet and CSV. How it works, when to use it.

What is DuckDB? The in-process analytical database

DuckDB is an in-process SQL database built for analytical queries: it runs as a library inside your program (Python, R, Node.js, Go, Rust, Java, a CLI or a browser) instead of as a separate server, and it uses a columnar, vectorized engine so aggregations and joins over millions of rows are fast on a laptop. It can query Parquet, CSV and JSON files directly by path, with no load step. The short version I use: SQLite for analytics.

What is DuckDB, exactly?

The project’s own Why DuckDB page describes it as “a relational (table-oriented) DBMS that supports the Structured Query Language (SQL)” that “is designed to support analytical query workloads.” Two design choices define it.

In-process. “By default, DuckDB does not run as a separate process but is completely embedded within a host process.” There is no daemon to install, no port, no connection pool. “DuckDB has no external dependencies, neither for compilation nor during run-time.”

Columnar and vectorized. It uses a “columnar-vectorized query execution engine,” processing batches of values per operation rather than one row at a time.

It is released under the “very permissive MIT License,” with the intellectual property held by the DuckDB Foundation. DuckDB 1.0.0 shipped on June 3, 2024 with the theme “stability” and a promise that files it wrote stay readable by later releases. DuckDB 2.0.0, released October 21, 2026, is a long-term support release “with support until at least October 2027.”

How does DuckDB work?

Two ideas carry most of the speed, and they are easier to see than to describe.

An analytical query like “total sales per store” touches a few columns of every row. A row store (Postgres, SQLite, MySQL) keeps each row’s fields together on disk, so it reads every column to get one. A column store keeps each column together, so it reads only what the query names. It also compresses better, because a column holds values of one type that often repeat.

Vectorized execution means each operator (filter, sum, hash join) works on a batch of values from one column at a time in a tight loop, instead of interpreting the plan once per row. That keeps CPU caches warm and lets the compiler use SIMD.

Here is the shape of both ideas with Python’s standard library. sqlite3 stands in for an embedded engine to show what “in-process” means: a database that is a function call away. Then a byte count shows why a column layout reads less for SUM(amount). SQLite is a row store; DuckDB is not; the point is the architecture, not a benchmark.

# embedded_olap.py: what "in-process" and "columnar" mean, with the stdlib only.
# sqlite3 stands in for an embedded engine (no server, a library call away);
# the byte count shows why a column store like DuckDB reads less for analytics.
import array, random, sqlite3, struct, time

random.seed(7)
N = 1_000_000
rows = [(i, random.choice(["SUR", "BOM", "DEL", "BLR"]), random.randint(1, 500),
         random.random() * 100, "note " * 8) for i in range(N)]

# 1. In-process: the database is a library inside this Python process. No port, no daemon.
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE sales(id INT, store TEXT, qty INT, amount REAL, note TEXT)")
db.executemany("INSERT INTO sales VALUES (?,?,?,?,?)", rows)
t = time.perf_counter()
result = db.execute("SELECT store, round(sum(amount), 2) FROM sales GROUP BY store ORDER BY store").fetchall()
print(f"GROUP BY over {N:,} rows in-process: {time.perf_counter() - t:.2f}s", result)

# 2. Row layout vs column layout for SUM(amount): how many bytes must be read?
fmt = "<q3sqd40s"                                   # id, store, qty, amount, note: one row
row_bytes = struct.calcsize(fmt) * N                # a row store reads whole rows
amount_col = array.array("d", (r[3] for r in rows)) # a column store reads one column
col_bytes = amount_col.itemsize * len(amount_col)
print(f"row layout scans {row_bytes / 1e6:.0f} MB, column layout scans {col_bytes / 1e6:.0f} MB"
      f" ({row_bytes / col_bytes:.1f}x less)")
print("sum check:", round(sum(amount_col), 2) == round(db.execute("SELECT sum(amount) FROM sales").fetchone()[0], 2))

Output (Python 3.13.11, Apple M4):

GROUP BY over 1,000,000 rows in-process: 0.19s [('BLR', 12511179.12), ('BOM', 12515565.06), ('DEL', 12463803.9), ('SUR', 12516460.72)]
row layout scans 67 MB, column layout scans 8 MB (8.4x less)
sum check: True

With five columns, summing one of them in a row layout means reading 67 bytes per row to use 8. Real analytical tables often have dozens or hundreds of columns, so the gap grows. Real column stores add compression on top, since a column of one type compresses well.

What does DuckDB look like in practice?

In Python, after pip install duckdb, a query is one call. The Python client docs note that duckdb.sql() “runs on an in-memory database that is stored globally inside the Python module,” and you can query a Pandas DataFrame by its variable name:

import duckdb

duckdb.sql("SELECT 42").show()
duckdb.sql("SELECT store, sum(amount) FROM pandas_df GROUP BY store").df()  # back to Pandas

The feature I reach for most is querying files where they sit. From the Parquet docs:

SELECT * FROM 'test.parquet';
SELECT *, filename FROM read_parquet('test/*.parquet');
COPY (SELECT * FROM tbl) TO 'result-snappy.parquet' (FORMAT parquet);

DuckDB “supports projection pushdown into the Parquet file itself” and “filter pushdown into the Parquet reader,” so it reads only the columns and row groups the query needs. Point it at a folder of Parquet on S3 (through the httpfs extension) and it behaves like a small warehouse with no warehouse.

DuckDB vs SQLite vs Postgres: which should you use?

SQLite is in-process too, but it is a row store built for transactional work: many small reads and writes of whole rows. It is the right embedded database for app state.

Postgres is a client-server row store for transactional systems with many concurrent writers, constraints and long-lived data. It is the right system of record.

DuckDB is in-process and columnar, built for scans, aggregations and joins over large tables, usually with one writer. It is the right tool for analysis, data pipelines, notebooks, local BI and the “small warehouse” tier.

These are complements. A common pattern is Postgres or SQLite for the app, with DuckDB reading exported Parquet (or the database directly through an extension) for analytics.

Why does DuckDB matter to a builder?

From a data-platform seat, my view is that many analytical questions are not big data. They are a few gigabytes of Parquet, and they end up queued behind a shared cluster only because that is where the SQL engine lives. DuckDB moves those queries to the laptop or to a single container, where they finish in seconds and cost nothing. The cluster stays for the work that needs it.

It also travels. The Why DuckDB page notes that DuckDB-Wasm runs in browsers, which is the use case I explain in what WebAssembly is: a full analytical engine shipped to the client.

For AI work, DuckDB is a good backend for text-to-SQL prototypes, because a model can generate SQL against local files without credentials to a production warehouse.

Where does DuckDB break?

Embedded means one owner. The default model is a database embedded in one host process. Several services writing to the same database at once is a client-server job, which is what Postgres is for.

One machine. However efficient the engine, a single machine has limits. When a query truly needs more than one machine’s memory and disk, you need a distributed engine.

Version upgrades of the file format. The 2.0 post is explicit: “DuckDB versions before 2.0 cannot read database files created by DuckDB 2.0.” If several tools share a .duckdb file, upgrade them together. For sharing across tools, Parquet or an open table format is safer than the native file.

Server mode is new. DuckDB 2.0 “ships a polished version of the Quack protocol” for client-server use, with a new CONNECT statement. Embedded is still the mature path; I would treat server mode as new until it has more production miles.

What I would do

For notebooks, data exploration, CI data tests and small-to-mid pipelines, I would use DuckDB by default and keep data in Parquet so other tools can read it too. For a team lakehouse, I would put tables in Apache Iceberg and let DuckDB be one of several engines reading them; its iceberg extension reads tables directly and writes through a REST catalog.

I would not use it as an app’s transactional database, and I would not share one .duckdb file across services with different DuckDB versions. If the job is embeddings and similarity search rather than aggregation, look at a vector database first.

FAQ

Is DuckDB free?

Yes. It is open source under the MIT License, and the DuckDB Foundation holds the intellectual property.

Is DuckDB faster than Pandas?

For SQL-shaped work (group-bys, joins, filters on large data) a columnar, vectorized engine usually beats row-by-row or single-threaded dataframe code, and it can query a DataFrame in place. For small data, either is instant; pick the one your team reads more easily.

Can DuckDB read Parquet and CSV files directly?

Yes. SELECT * FROM 'file.parquet' works, as do globs with read_parquet, and DuckDB pushes column selection and filters down into the Parquet reader.

Does DuckDB need a server?

No. It runs inside your process by default. DuckDB 2.0 added an optional client-server mode through the Quack protocol and a CONNECT statement.

What is the difference between DuckDB and SQLite?

Both are embedded, single-file databases. SQLite stores rows and is built for transactional app data. DuckDB stores columns and is built for analytical queries over many rows.

Sources

Get new posts in your inbox

© 2026 Malhar Ujawane. Built with Astro.