1. What DuckDB is, and why it exists

Ten guides on this site point at DuckDB before it has a guide of its own. Spark’s chapter 15 says “under ~100 GB, DuckDB or Polars wins decisively — and this is the most common misuse of Spark today.” ClickHouse’s chapter 15 calls it “the most commonly overlooked alternative.” MinIO, PostgreSQL, MySQL, MongoDB and Iceberg all name it as the thing you reach for when the data is large but not that large.

Ten guides agreeing about a tool none of them is about is worth a guide.

The one-sentence version

DuckDB is SQLite for analytics.

That comparison is the official one, it is accurate, and it is worth unpacking because both halves carry weight. (SQLite has its own guide if you want the other half in depth.)

   "SQLite"     → it runs INSIDE your process.
                  No server. No port. No daemon.
                  No connection string. A library
                  you import, and a file on disk.

   "for         → it is COLUMNAR and VECTORISED.
    analytics"    Built for "scan a billion rows,
                  touch four columns, aggregate"
                  rather than "fetch this one row
                  by primary key".

Every other database in this site’s Databases section is a server. You start it, it listens, you connect to it over a socket, and it lives a life independent of the programs that use it. PostgreSQL, MySQL, ClickHouse, Cassandra, MongoDB — all servers.

DuckDB is not a server, and almost everything distinctive about it follows from that.

The gap it noticed

In 2018 the analytical tooling landscape had a hole in the middle of it.

   DATA SIZE        WHAT YOU USED

   a few MB         pandas. SQLite. A spreadsheet.

   ~1 GB            pandas, and it started hurting.
                    SQLite, and it was slow because
                    it is row-oriented.

   ~50 GB           ...you stood up a cluster?
                    You paid for a warehouse?
                    You gave up and sampled?

   1 TB+            Spark, Hive, a warehouse. Fine.
                    That is what they are for.

The 1 GB to 1 TB band had no good answer. It was too big for pandas — which needs several times the data size in RAM and is single-threaded for most operations — and too small to justify a distributed system, a cluster to size, a warehouse bill, or a data engineer to run any of it.

So people did one of three things, all bad:

Sampled the data and drew conclusions from 1% of it.
Stood up a Spark cluster to process forty gigabytes, spending three of the four minutes on startup.
Waited. A pandas groupby that took eleven minutes, run four times a day, is most of a working week per month.

DuckDB’s founding observation is that a single modern machine is far larger than anyone had noticed. A laptop in 2018 had eight cores and 16 GB of RAM. A cloud VM you can rent by the hour has 64 cores and 512 GB. If you use those properly — columnar storage, vectorised execution, all the cores, and spilling to disk when memory runs out — the band that “needs a cluster” starts a great deal higher than the industry assumed.

Where it came from

CWI Amsterdam — the Centrum Wiskunde & Informatica, the Dutch national research institute for mathematics and computer science. The same database group that produced MonetDB, one of the first column stores, and much of the foundational research on vectorised query execution.

Hannes Mühleisen and Mark Raasveldt started it around 2018. The motivation was directly observational: they kept watching data scientists struggle with exactly the middle band above, and kept watching people install a database server just to run one analytical query over one file.

   THE ANNOYANCE THAT STARTED IT

   A researcher has a 20 GB CSV.
   They want to run one GROUP BY.

   Options in 2018:
     - pandas: out of memory
     - SQLite: works, takes an hour, it is
       a row store doing a full scan
     - Postgres: install a server, create a
       user, create a table, define a schema,
       COPY the file in, THEN run the query
     - Spark: install a JVM and a cluster

   All of that, to answer one question about
   one file.

The name comes from Wilbur, Hannes’s pet duck. The logo is Wilbur. This is not a metaphor for anything; the duck was simply there.

MIT licence, which matters more than it sounds. The intellectual property sits with the DuckDB Foundation, a non-profit, specifically so that the project cannot be bought and closed. DuckDB Labs is the company that employs the core developers and sells support. MotherDuck, founded in 2022 by Jordan Tigani — who had been an early engineer on Google BigQuery — is a separate company building a hosted, hybrid DuckDB service.

DuckDB 1.0 arrived in June 2024, and its headline feature was not performance. It was storage format stability — a promise that a database file written by one version would be readable by the next. Before 1.0, upgrading meant exporting and re-importing. That release is the moment DuckDB stopped being a research project you used for analysis and became something you could put in production.

The argument that “big data is dead”

In February 2023 Jordan Tigani published an essay under that title, and it is the clearest statement of DuckDB’s thesis even though he wrote it before joining the ecosystem full time.

The argument, compressed:

   1. Most organisations do not have big data.
      The median query in most warehouses
      scans a few gigabytes.

   2. Data sizes grow more slowly than
      hardware does. A machine you can rent
      today has more RAM than a whole Hadoop
      rack in 2012.

   3. Most queries touch RECENT data. You may
      store ten years; you query last month.

   4. Therefore: the distributed system you
      are paying for is solving a problem
      you do not have.

This site’s other guides broadly agree with him, from their own side. Spark’s chapter 15 says the crossover is somewhere between 100 GB and 1 TB and that people are routinely surprised which side they are on. ClickHouse’s chapter 15 says to reach for a server when you need concurrency or continuous ingestion — not merely when the data is large.

The honest version is not “distributed systems are unnecessary.” It is: the threshold moved, nobody updated their instincts, and the default choice is now wrong far more often than it is right.

What it actually is, mechanically

Five properties, each of which gets a chapter:

1. IN-PROCESS. A library. `import duckdb`,
   or link libduckdb. Queries run on your
   thread stack, in your address space.
   (chapter 2)

2. COLUMNAR STORAGE, in a single file — or
   in no file at all, purely in memory.
   (chapter 3)

3. VECTORISED EXECUTION. Operators pass
   2048-row column chunks to each other, not
   single rows. (chapter 4)

4. IT READS FILES DIRECTLY. Parquet, CSV,
   JSON — local, or over HTTPS, or in S3 —
   with no import step at all. (chapter 5)

5. IT SPILLS TO DISK. A query bigger than
   RAM slows down rather than failing.
   (chapter 10)

Point 4 is the one that changes how people work, and it is easy to under-read. This is a complete, valid DuckDB program:

SELECT country, sum(amount)
FROM 's3://events/2026/*/*.parquet'
WHERE event_type = 'purchase'
GROUP BY 1
ORDER BY 2 DESC;

There is no database. No CREATE TABLE, no load, no schema declaration, no server, no catalog. The files stay where they are. DuckDB reads the Parquet footers, works out which files and which row groups it needs, reads only the two columns the query mentions, and answers. Chapter 5 is that mechanism, and chapter 9 is the pushdown that makes it fast.

What it is not

Being clear early, because DuckDB is used wrongly in a specific and predictable direction.

It is not a server, and it is not multi-user. One process writes at a time. There is no network protocol, no authentication, no user accounts, no roles. If ten people need to query the same data concurrently, DuckDB alone is not the answer — chapter 11 is honest about exactly what “concurrent” means here.

It is not OLTP. No high-rate single-row inserts, no thousands of small transactions per second. It is a column store; writing one row means touching every column. Use PostgreSQL or SQLite for that, and DuckDB to analyse what they hold.

It is not a replacement for a warehouse at scale. Above a few terabytes with many concurrent users, ClickHouse, Snowflake, BigQuery and Trino exist for reasons that remain valid.

It is not distributed. One process, one machine. There is no cluster mode and this is a deliberate design decision, not a missing feature. MotherDuck adds a remote half; the engine itself does not shard.

It is not a storage system. The single-file format is real and useful, but DuckDB is at its best pointed at Parquet and Iceberg tables that other tools also read. Chapter 15 argues you should usually let it be a query engine rather than a home for your data.

The mental model to carry

1. IT IS A LIBRARY. The database runs in your
   process. There is no "connect to" — there
   is only "open".

2. COLUMNS, NOT ROWS. Storage, execution and
   every performance property follow from
   this.

3. VECTORS OF 2048. Not one row at a time,
   not the whole column at once. The middle
   size, which fits in CPU cache.

4. FILES ARE TABLES. Parquet, CSV, JSON,
   local or remote, queried in place. The
   import step is optional and frequently
   unnecessary.

5. THE SQL IS DELIBERATELY NICER. GROUP BY
   ALL, SELECT * EXCLUDE, nested types,
   trailing commas. Chapter 6 is a list of
   small kindnesses.

6. ONE WRITER. Many readers inside that
   process. This is the real constraint and
   it decides most of chapter 15.

7. IT WILL SPILL. Larger-than-memory queries
   get slower; they do not get killed. This
   is newer than most people's impression of
   it.

Point 7 is the one that changes the decision most often, because a great many people’s mental model of DuckDB was formed around 2021, when a query that exceeded RAM simply failed. Modern versions hash-join, aggregate and sort out of core. Chapter 10 shows what that costs and where the remaining sharp edges are.

Why DuckDB in 2026

Because the alternative you would otherwise pick is usually heavier than the problem.

What DuckDB is genuinely the best answer for:

   ✓ analysis on one machine, up to a
     surprising size
   ✓ querying Parquet, CSV or JSON in place,
     locally or in object storage
   ✓ the data-science inner loop — Python,
     Arrow, notebooks, zero-copy DataFrames
   ✓ embedding an analytical engine in an
     application or a CLI tool
   ✓ tests and CI for data pipelines, where
     spinning up a warehouse is absurd
   ✓ a local development target for
     [dbt](/dbt/01-what-and-why/)
   ✓ reading a table format — Iceberg, Delta —
     from a laptop
   ✓ in the browser, via WebAssembly, which
     no other engine here can do at all

What it is not for:

   ✗ many concurrent users → ClickHouse, a
     warehouse, Trino
   ✗ OLTP → Postgres, SQLite
   ✗ genuinely distributed data → Spark,
     Trino, a warehouse
   ✗ streaming → Flink, Kafka Streams
   ✗ shared mutable state across processes →
     any server

And the reason to read this even if you never ship DuckDB: it is the clearest, smallest, most readable implementation of ideas every analytical system on this site uses. Vectorised execution, morsel-driven parallelism, pushdown, hash-join spilling, cardinality estimation — ClickHouse, Spark, Trino and every warehouse do all of these, at a scale that makes them hard to observe. DuckDB does them on your laptop, in one process, where EXPLAIN ANALYZE fits on a screen.

Understanding DuckDB’s query engine is the cheapest available route to understanding all of theirs.

What to take from this chapter

DuckDB is SQLite for analytics — in-process rather than a server, and columnar and vectorised rather than row-oriented.
It was built for the gap between pandas and a cluster: the 1 GB to 1 TB band, which in 2018 had no good answer and where people sampled, over-provisioned or waited.
From CWI Amsterdam — Mühleisen and Raasveldt, ~2018, out of the MonetDB group. Named after a duck. MIT licensed, with the IP held by a non-profit foundation so it cannot be closed.
1.0 (June 2024) mattered for storage stability, not speed — that is when it became something you could keep data in.
The “big data is dead” argument: data grows slower than hardware, most queries touch recent data, and the median warehouse query scans a few gigabytes. The threshold moved and instincts did not.
Five mechanical properties: in-process, columnar single file, vectorised execution, files queried in place, and spilling to disk.
Files are tables. A complete DuckDB program can be one SELECT over s3://.../*.parquet with no database at all.
It is not a server, not multi-user, not OLTP, not distributed, and not really a storage system.
Carry seven ideas: a library not a server; columns not rows; vectors of 2048; files as tables; deliberately nicer SQL; one writer; and it spills rather than dying.
Read it even if you will not ship it — it is the most legible implementation of the execution ideas every other analytical engine here uses.

Next: the in-process model — what disappears when there is no server, and what you give up along with it.

Written by Sepuri Sai Krishna