What Is DuckDB? In-Process Analytics SQL

Photo: cottonbro studio / Pexels
DuckDB is an embedded SQL database built specifically for analytics: aggregations, joins across large tables, and scans over millions of rows. Instead of running as a separate server you connect to, it loads as a library inside your Python process, your CLI session, or even a browser tab, and lets you run full SQL directly against Parquet, CSV, JSON, or Arrow files without staging them anywhere first. It was created by Mark Raasveldt and Hannes Mühleisen at Centrum Wiskunde & Informatica (CWI) in Amsterdam and first described publicly at the SIGMOD 2019 conference, where the pair pitched it as a system that could match the performance of production analytical databases while running embedded inside a single Python or R process, with no server and no data-transfer overhead (SIGMOD 2019 demo paper).
The name is a deliberate echo of SQLite. SQLite proved that a database does not need a server process to be useful — it just needs to be a library. DuckDB applies the same idea to the opposite workload: SQLite is optimized for many small transactional reads and writes (an app storing a user's settings), while DuckDB is optimized for a few large analytical scans (summing revenue across ten million rows). Same embedding philosophy, opposite storage engine underneath.
Why an embedded analytical database matters
For most of database history, "analytics" meant "spin up a server." You installed PostgreSQL, or you provisioned a Snowflake warehouse, or you stood up a Spark cluster, and only then could you run a GROUP BY over a few million rows. That server had to be started, secured, connected to, and eventually torn down. For a data scientist who just wants to know the average order value by region for a CSV sitting on their laptop, that is an enormous amount of infrastructure for a five-second question.
DuckDB removes the server from the equation entirely. pip install duckdb, import it, and you have a columnar SQL engine running inside the same process as your Python interpreter. There is no connection string, no authentication handshake, no network round-trip. The query planner, the execution engine, and the storage layer are all just function calls inside your program.
The practical effect shows up in three places. First, iteration speed: exploratory data analysis in a notebook goes from "wait for the cluster" to "run the cell." Second, deployment simplicity: a script that embeds DuckDB is a single file with a single pip dependency, not a script plus a database server plus a network policy. Third, and least obvious, cost: because there is no persistent server, there is no idle compute bill. You pay for CPU only while the query actually runs.
How DuckDB actually executes a query
Two architectural decisions do almost all the work of making DuckDB fast, and both were borrowed from decades of academic database research that, until DuckDB, mostly stayed inside expensive commercial warehouses like Vertica and Teradata.
The first is columnar storage. A row-oriented database like PostgreSQL or SQLite stores each record as a contiguous block: all of a customer's fields sit next to each other on disk. That is efficient when you need the whole record, which is the common case for a web application looking up one user. But an analytical query rarely wants a whole record — it wants one or two columns (say, order_total and region) across every row. A columnar engine stores each column contiguously instead, so a query touching two columns out of forty only has to read those two columns off disk or out of memory. It is the difference between fetching one shelf from a warehouse versus walking the entire floor to grab one item from every aisle.
The second is vectorized execution. Rather than processing one row at a time (the "tuple-at-a-time" model most traditional databases use, which incurs function-call overhead on every single row), DuckDB processes data in batches of roughly 2,048 values at once. Each batch, called a vector, fits comfortably in the CPU's L1 or L2 cache, and operating on a whole batch lets the CPU use SIMD instructions (single instruction, multiple data) to apply the same operation to many values simultaneously. Think of the difference between paying a cashier for one item at a time versus scanning a whole cart's worth of barcodes in one pass. Both approaches get every item scanned eventually, but batching cuts the fixed overhead per item to nearly zero.
Layered on top of columnar storage and vectorized execution, DuckDB runs a cost-based query optimizer that reorders joins, pushes filters down to the storage layer so irrelevant rows are never even read, and automatically parallelizes execution across every CPU core available on the machine. None of these techniques is new to database research. What is new is bundling all of them into a library small enough to embed, with a build that compiles down to roughly the same footprint as SQLite.
How is DuckDB different from a traditional client-server database?
A traditional database like PostgreSQL runs as a standalone server process that clients connect to over a network socket, even if that "network" is localhost. DuckDB has no server process at all — it is a library linked directly into your application, so a query is a function call, not a network request, and there is nothing to start, stop, or authenticate against.
Why is DuckDB faster than pandas for large data?
Pandas loads an entire DataFrame into memory as Python/NumPy objects and largely processes it single-threaded, row by row, for many operations. DuckDB uses columnar storage, vectorized batch execution, and automatic multi-core parallelism, so operations like GROUP BY and joins on multi-gigabyte datasets typically run several times faster and with a smaller peak memory footprint.
Where DuckDB runs, and what it can read natively
DuckDB ships official client bindings for Python, R, Java, Node.js, Go, Rust, C, C++, and .NET, plus a standalone command-line shell that behaves like sqlite3 or psql. It also compiles to WebAssembly as DuckDB-Wasm, which means it can run entirely inside a browser tab — the project's own interactive shell at shell.duckdb.org lets you query a Parquet file with zero server-side infrastructure at all, executing the whole engine on the visitor's device.
| Environment | What it enables | Typical use |
|---|---|---|
Python (pip install duckdb) |
Zero-copy queries against pandas and Polars DataFrames | Notebooks, ML feature pipelines |
| CLI shell | duckdb interactive prompt, like sqlite3 |
Ad-hoc file exploration |
| R, Java, Go, Rust, Node.js, C++, .NET | Native bindings for each ecosystem | Embedding in production services |
| WebAssembly (DuckDB-Wasm) | Runs the full engine inside a browser tab | Client-side dashboards, no backend |
| MotherDuck (managed cloud) | Hybrid local/cloud execution, sharing, persistent storage | Teams that outgrow a single laptop |
Table: DuckDB deployment surfaces and where each one is used in practice.
On the file-format side, DuckDB reads Parquet, CSV, JSON, and Apache Arrow natively, including files hosted remotely on S3, Google Cloud Storage, Azure Blob Storage, or any plain HTTPS URL. You do not declare a schema first. You point a SELECT statement at the file path or URL, and DuckDB infers types, applies predicate pushdown (skipping row groups in a Parquet file that the WHERE clause has already ruled out), and returns results.
import duckdb
df = duckdb.sql("""
SELECT user_id, SUM(amount) AS total_spent
FROM 's3://my-bucket/transactions/*.parquet'
WHERE date >= '2026-01-01'
GROUP BY 1
ORDER BY total_spent DESC
""").df()
That query streams the relevant Parquet row groups from S3, filters and aggregates in parallel across every available core, and hands back a pandas DataFrame, typically in seconds even against files that would take much longer to fully download and load.
Where teams are actually putting DuckDB to work
The clearest signal that DuckDB moved from research curiosity to production tool is where it shows up inside other companies' data stacks, not just inside notebooks. A few concrete patterns:
- Local-first BI tools. Products like Rill Data and Evidence embed DuckDB directly as their query engine, so a dashboard can run entirely against local files with sub-second latency instead of round-tripping to a cloud warehouse for every filter change.
- dbt with DuckDB as a target. dbt Labs ships an official DuckDB adapter, which lets analytics engineers develop and test full SQL transformation pipelines on a laptop before ever pointing the same dbt project at Snowflake or BigQuery in production.
- ML feature engineering. Because DuckDB can join and aggregate faster than pandas at scale, teams building tabular features for gradient-boosted models increasingly do the heavy joins in a DuckDB query rather than in pandas, then hand the result to scikit-learn or XGBoost.
- In-database extensions. DuckDB supports a growing extension ecosystem, including a
httpfsextension for remote file access, aspatialextension for geographic data types, and a full-text search extension, all loaded on demand rather than bundled by default, keeping the core binary small. - Postgres integration. The
pg_duckdbextension, developed with backing from Hydra and MotherDuck, embeds DuckDB's analytical engine directly inside a running PostgreSQL instance, letting a single Postgres database run OLTP queries on its native engine and OLAP queries through DuckDB side by side (pg_duckdb 1.0 announcement, MotherDuck).
The 1.0 milestone and what changed
DuckDB reached its 1.0.0 release on June 3, 2024. The headline feature of 1.0 was not a burst of new capability — the release notes explicitly describe "very limited new features" — but a stability guarantee: database files written by DuckDB 1.0.0 are guaranteed to remain readable by all future DuckDB versions (DuckDB Labs, "Announcing DuckDB 1.0.0"). Before 1.0, upgrading DuckDB could mean a stored file needed a manual migration step, which is a real obstacle for anyone treating a .duckdb file as a durable artifact rather than disposable scratch space. The 1.0 release also formalized the project's governance: DuckDB Labs, the commercial company co-founded by Raasveldt and Mühleisen, continues development, while the nonprofit DuckDB Foundation holds the intellectual property and licenses it under the permissive MIT license, which is meant to keep the core project independent of any single company's fortunes.
Development did not slow down after 1.0. Point releases through 1.5 and beyond have continued shipping performance work; independent benchmarking by Oxbow Research found meaningful speedups across the 1.0-to-1.5 series on standard analytical workloads, and MotherDuck's own tracking put the cumulative improvement at roughly 2x since 1.0.0 on the benchmarks it monitors (Oxbow Research, "How Much Faster Is DuckDB 1.5 vs 1.0?"). In December 2024, the DuckDB team published a widely shared benchmark showing the engine completing a 100 GB-scale TPC-H run on a mobile phone, a demonstration aimed squarely at showing how far single-node, cache-conscious columnar execution has come since the era when that scale of query needed a server room (DuckDB, "Running TPC-H SF100 on Mobile Phones").
MotherDuck and the cloud layer
DuckDB itself has no built-in cloud service, by design — it is a library, not a platform. MotherDuck fills that gap. Founded by Jordan Tigani, a former Google engineer and early BigQuery team member, and backed by Hannes Mühleisen, MotherDuck is a managed, serverless analytics platform built on top of DuckDB that adds persistent cloud storage, sharing between team members, and what it calls "hybrid execution" — the ability to run part of a query locally and part in the cloud depending on where the data lives.
MotherDuck's funding history tracks how quickly the DuckDB ecosystem attracted venture capital. The company raised a $12.5 million seed round led by Redpoint, followed by a $35 million Series A led by Andreessen Horowitz that valued the company at $175 million as of November 2022 (PR Newswire, "MotherDuck Raises $47.5 Million at $175M Valuation"). Roughly a year later, MotherDuck closed a $52.5 million Series B led by Felicis, putting its post-money valuation at $400 million and bringing total funding to $100 million (GeekWire, "Seattle startup MotherDuck raises $52.5M at a $400M valuation"). That level of investor interest in what is, at its core, a hosted wrapper around an open-source embedded database is itself a signal of how much demand exists for "warehouse power without warehouse operational overhead."
Comparing DuckDB to the databases it gets confused with
DuckDB sits in a crowded field of analytical tools, and the comparisons people search for most are against SQLite, Spark, Postgres, and Polars.
| Feature | DuckDB | SQLite | PostgreSQL | Apache Spark | ClickHouse |
|---|---|---|---|---|---|
| Workload | Analytics (OLAP) | Transactional (OLTP) | OLTP + OLAP | Analytics (OLAP) | Analytics (OLAP) |
| Storage layout | Columnar | Row-oriented | Row-oriented | Columnar (in-memory) | Columnar |
| Deployment | Embedded library | Embedded library | Client/server | Distributed cluster | Client/server |
| Setup | pip install |
pip install |
Server + config | Cluster orchestration | Server install |
| Concurrent writers | Limited | Limited | Excellent | N/A (batch) | Good |
| Reads Parquet/CSV natively | Yes, direct | Via extension | Via foreign data wrapper | Yes | Yes |
| Practical scale ceiling | Single node, up to ~TB | Single node, GB scale | Single server | Distributed, PB scale | Distributed, PB scale |
| License | MIT | Public domain | PostgreSQL License | Apache 2.0 | Apache 2.0 |
Table: DuckDB positioned against the databases most commonly compared to it.
DuckDB vs. SQLite
This is the cleanest of the comparisons because both tools share an architecture, not a workload. SQLite is row-oriented and built for transactional correctness at small scale — think of a mobile app storing a user's saved preferences, where you read and write one small record at a time. DuckDB is columnar and built for scanning large tables to compute aggregates. Both are embedded, single-file, zero-configuration databases. The difference is entirely in what kind of query each one was optimized to make fast.
DuckDB vs. Apache Spark
Apache Spark is a distributed engine that scales out across a cluster of machines, which means it can process datasets measured in petabytes that would never fit on one machine at all. DuckDB is single-node by design. But single-node hardware has grown enormous — cloud instances with hundreds of gigabytes of RAM and dozens of cores are routine now — and at the terabyte scale, DuckDB running on one large machine frequently outperforms a Spark cluster on the same data, because Spark's network shuffle and JVM overhead start to dominate its runtime once the cluster coordination cost outweighs the benefit of parallel machines. A reasonable rule of thumb: reach for DuckDB first under roughly 10 TB of data; above that, distributed compute usually starts paying for itself.
DuckDB vs. PostgreSQL
Postgres remains the better choice whenever the workload includes many concurrent writers or requires strict transactional guarantees under high concurrency, such as a production application backend handling thousands of simultaneous small writes. DuckDB is not trying to replace that. For read-heavy analytical queries — the kind of GROUP BY and multi-table join a BI dashboard or an analyst runs — DuckDB is typically dramatically faster on identical hardware, because Postgres's row-oriented storage was never designed to scan wide swaths of a handful of columns efficiently. Some teams now run both side by side, using pg_duckdb to get DuckDB's analytical engine without leaving their Postgres instance.
DuckDB vs. pandas and Polars
DuckDB and Polars are closer to siblings than competitors: both are columnar, multi-threaded, Arrow-compatible engines built in the last several years specifically to outperform pandas at scale. The practical split is about interface, not raw capability. Reach for DuckDB when your team thinks in SQL, when a query needs to span multiple Parquet files at once, or when you want one tool that can also persist and share results as a database file. Reach for Polars when you want Python-native method chaining, tight integration with feature-engineering code, and a DataFrame API rather than a query string. In practice you rarely have to choose: duckdb.sql("SELECT * FROM polars_df") runs directly against a Polars DataFrame with zero copying, and the same works in reverse.
For readers coming from a pandas background specifically, the honest framing is that DuckDB is not a pandas replacement for every task — pandas still wins for small datasets and rich ecosystem integration with plotting and statistics libraries — but for anything that starts to feel slow or memory-hungry in pandas, routing the aggregation step through a DuckDB query first is usually the fastest fix available without touching infrastructure at all.
DuckDB vs. real-time analytics engines
It is worth being explicit about what DuckDB is not built for. Systems purpose-built for real-time, sub-second dashboards on continuously streaming data, such as ClickHouse, Apache Druid, or Apache Pinot, are optimized for ingest-and-query latency at high concurrency, serving many simultaneous dashboard users hitting fresh data. DuckDB is not a streaming ingestion system and has no built-in concept of continuously arriving data; it is built to answer ad-hoc or scheduled analytical queries against files or a local database, typically for one user or process at a time. Teams building customer-facing real-time analytics products still reach for the streaming-native engines; teams doing exploratory or batch analysis reach for DuckDB.
What people get wrong about DuckDB
The most common misconception is treating DuckDB as a drop-in data warehouse replacement for any team, at any scale. It is not that ambitious, and the project has never claimed to be. DuckDB is a single-node engine. If your organization has hundreds of concurrent analysts running queries against a shared, constantly updated multi-terabyte dataset, you still want a managed warehouse like Snowflake, BigQuery, or Databricks, or MotherDuck's hosted layer on top of DuckDB itself. DuckDB's sweet spot is a single analyst, a single pipeline, or a single application process doing analytical work against data that fits comfortably on one machine, which in 2026 means datasets well into the hundreds of gigabytes or low terabytes.
A second misconception is that DuckDB is just "fast pandas." It shares some use cases with pandas but is a genuinely different tool: a real SQL engine with ACID transaction support on its native storage format, a persistent on-disk database file if you want one, and a query optimizer, none of which pandas has. The reason people reach for DuckDB from a pandas workflow is usually not that pandas is "wrong," but that a specific operation, most often a large GROUP BY or a multi-table join, has become the bottleneck, and DuckDB's columnar, vectorized, parallel execution handles exactly that kind of operation far better than pandas's largely single-threaded row processing does.
A third misconception worth correcting: DuckDB does support concurrent access and does have ACID transactions, but it is not designed for the high-concurrency, many-small-writes pattern of an OLTP system like Postgres. Multiple readers can query a DuckDB file simultaneously, but write concurrency is intentionally limited because the engine is optimized for the opposite access pattern: infrequent large writes, frequent large reads.
FAQ
Q: Is DuckDB free to use? A: Yes. DuckDB is released under the MIT license, which permits free use, modification, and commercial deployment with no licensing fee. MotherDuck, the managed cloud layer built on top of it, is a separate paid product, but the core engine itself is fully open source.
Q: Can DuckDB replace my company's data warehouse? A: For a small-to-medium team or a single analyst working with datasets that fit on one machine, often yes. For an organization with hundreds of concurrent analysts querying a constantly updating multi-terabyte dataset, a managed platform like Snowflake, BigQuery, Databricks, or MotherDuck's hosted layer is still the better fit, since DuckDB is single-node by design.
Q: Does DuckDB support ACID transactions? A: Yes, DuckDB provides full ACID transactions on its native on-disk storage format. What it does not offer is high write-concurrency; it is optimized for infrequent large writes and frequent large reads, not many simultaneous small writes the way PostgreSQL is.
Q: How does DuckDB integrate with pandas and Polars DataFrames?
A: DuckDB can query DataFrames in memory with zero-copy access. A statement like duckdb.sql("SELECT * FROM my_dataframe") runs directly against an existing pandas or Polars object in the same Python process, without exporting or re-loading the data.
Q: What is MotherDuck, and is it the same company as DuckDB Labs? A: MotherDuck is a separate, venture-backed company founded by Jordan Tigani that builds a managed cloud analytics platform on top of the open-source DuckDB engine. It raised a Series A at a $175 million valuation in November 2022 and a Series B at a $400 million valuation in 2023. DuckDB Labs, co-founded by creators Mark Raasveldt and Hannes Mühleisen, is the company that develops the core open-source engine; the nonprofit DuckDB Foundation holds the project's intellectual property under the MIT license.
Q: Is DuckDB faster than PostgreSQL? A: For analytical queries involving large scans, aggregations, and joins across many rows, DuckDB is typically substantially faster on the same hardware, because of its columnar storage and vectorized execution. For transactional workloads with many small, concurrent inserts and updates, PostgreSQL remains the better-suited engine.
Q: What file formats can DuckDB query without importing them first? A: DuckDB reads Parquet, CSV, JSON, and Apache Arrow files directly with SQL, including files hosted remotely on S3, Google Cloud Storage, Azure Blob Storage, or any plain HTTPS URL, without requiring a separate load or staging step first.
Q: Does DuckDB require a schema to be defined before querying a file?
A: No. DuckDB infers column types automatically from the source file, whether it is a CSV, Parquet, or JSON file, so a query can point directly at a file path or URL without any prior CREATE TABLE statement.
Q: When did DuckDB reach its 1.0 release, and why did that matter? A: DuckDB 1.0.0 shipped on June 3, 2024. Its main significance was a stability guarantee: database files created with 1.0.0 are guaranteed to remain compatible with every future DuckDB version, removing a manual-migration risk that existed in earlier releases.
Q: Can DuckDB run in a web browser? A: Yes, through DuckDB-Wasm, a WebAssembly build of the engine. It runs entirely on the visitor's device with no backend server, which is how the project's own interactive shell at shell.duckdb.org lets anyone query a Parquet file directly in-browser.
The takeaway
DuckDB succeeded by taking analytical database techniques that had existed in research and expensive commercial warehouses for decades, and packaging them into a library small enough to pip install. The columnar storage and vectorized execution do the heavy lifting; the embeddable, server-free design is what made those techniques accessible to anyone running a Python script instead of only to organizations that could afford a warehouse team. It is not a universal replacement for Spark, Postgres, or a managed data warehouse, and it was never built to be. What it replaces is the specific, common moment when someone needs a fast answer to an analytical question against a file on disk or in cloud storage, and the honest choice used to be between waiting on pandas or provisioning infrastructure they did not actually need.
Read next:
Follow new articles
Add SciHub101 to your feed reader to see new science and technology stories.
About the Author
SciHub101 is an independent science and technology publication founded and edited by Buğra Sözeri, a software engineer and app developer based in Türkiye. AI tools assist with drafting and research. Source documentation in the existing archive is uneven, and articles are being reviewed and corrected. The Editorial Team byline identifies editorial responsibility; it does not certify specialist or independent human review. Where we compare products, anything built by our own team is labelled as such — see the editorial guidelines and About page for how corrections and disclosures work.
Related Articles

Python for Data Science: Complete Guide 2026
Master Python for data science in 2026. Covers essential libraries, workflows, career paths, and learning roadmap with real examples.


Python Data Libraries Cheat Sheet 2026 — Quick Reference
Quick reference for Python data libraries 2026: Pandas, Polars, DuckDB, NumPy, Plotly. Common operations side-by-side. Bookmark for daily work.

dbt Tutorial — Data Engineering for Analysts (2026 Guide)
dbt (data build tool) explained for data analysts: SQL-based transformations, models, tests, documentation. The modern way to build data pipelines.

DuckDB vs Pandas — Which to Use for Data Analysis 2026
DuckDB vs Pandas: when to use each, performance benchmarks, SQL vs Python syntax, real examples. DuckDB is faster and handles larger data — should you switch?

SQL for Beginners — Query Databases Like a Pro
Learn SQL from scratch — SELECT, JOINs, GROUP BY, window functions, and indexes. Practical examples for PostgreSQL, MySQL, SQLite, and BigQuery.