15. When to use DuckDB, and when not

Nine other guides on this site recommend DuckDB. This is the chapter that says where they are wrong to.

The question

Does more than one process need to write, or do many people need to query it at the same time?

If yes, you want a server, and DuckDB is not one. If no, DuckDB is very likely the answer and the alternatives are heavier than the problem.

Notice what that question is not about. It is not about data size. Size is the second question and it is the one everybody asks first.

   THE DECISION, IN ORDER

   1. Do multiple PROCESSES write?
        yes → a server. Stop here.

   2. Do many USERS query concurrently,
      each expecting fast answers?
        yes → ClickHouse, a warehouse, Trino.
              Stop here.

   3. Does the data fit on one machine?
      (which now means: up to several
       terabytes on disk, with spilling)
        no → Spark, Trino, a warehouse.

   4. Otherwise
        → DUCKDB.

The comparisons

DuckDB vs pandas

The most common actual substitution, and it is not close.

	DuckDB	pandas
Parallelism	all cores	mostly one
Memory needed	~1× data, spills	3–10× data, no spill
Larger than RAM	yes	no
Reads Parquet/S3	directly	via pyarrow
Language	SQL	Python
Ecosystem	growing	enormous
Interop	zero-copy Arrow	it is the ecosystem

Use both. duckdb.sql(...).df() does the heavy aggregation in DuckDB and hands pandas a small result, which is exactly the right division of labour. The mistake is doing a groupby over 40 million rows in pandas when one line of SQL would have done it in a tenth of the time and a fifth of the memory.

DuckDB vs Polars

The genuinely close comparison, and the one people ask about most.

   BOTH:  columnar, vectorised, multi-core,
          Arrow-native, lazy, spill to disk,
          embedded, fast.

   DIFFERENT:

   DuckDB   SQL first. A DATABASE — it has a
            catalog, tables, transactions, a
            persistent file format, indexes,
            extensions, and it can ATTACH
            other databases.

   Polars   A DATAFRAME LIBRARY. Expression
            API first (SQL exists but is not
            the point). No persistent
            storage of its own. Composes
            beautifully with Python.

   PREFER DUCKDB WHEN
     ─ the logic is naturally SQL
     ─ you want to persist tables, not just
       transform frames
     ─ you need joins across many sources
     ─ multiple languages must read the same
       thing
     ─ the ecosystem matters: dbt, BI tools,
       JDBC, WASM

   PREFER POLARS WHEN
     ─ the logic is naturally a chain of
       transformations
     ─ you want type checking and IDE
       completion over your pipeline
     ─ you are already deep in a Python
       dataframe codebase
     ─ the expression API expresses it more
       clearly than SQL would

Honestly: they are both excellent and the choice is mostly taste and existing code. They interoperate at zero cost through Arrow, so “both” is a legitimate answer and a common one — Polars for the transformation chain, DuckDB for the joins and the persistence.

DuckDB vs ClickHouse

	DuckDB	ClickHouse
Shape	a library	a server
Concurrent users	~1 process	hundreds
Continuous ingestion	no	yes
Distributed	no	yes
Ops burden	none	real
Single-query speed	comparable	comparable
Materialised views	limited	a core feature
Setup	pip install	a cluster

This is the cleanest split on this page. ClickHouse’s own chapter 15 says to reach for it “when you need a server: concurrent users, continuous ingestion, or data beyond one machine” — and that is exactly right, in both directions.

They are frequently used together: ClickHouse as the serving layer, DuckDB for local exploration of the same Parquet the pipeline produced.

DuckDB vs SQLite

   SQLITE     row store. OLTP. Millions of
              small transactions. Point
              lookups. Concurrent readers
              with WAL mode. Embedded
              everywhere on earth.

   DUCKDB     column store. OLAP. Full scans,
              aggregations, joins over
              millions of rows.

   SELECT * FROM t WHERE id = 12345
     → SQLite. Instant.

   SELECT country, sum(amount)
   FROM t GROUP BY 1     (40M rows)
     → DuckDB. 100× faster.

They are not competitors and using both is normal — SQLite for the application’s state, DuckDB to analyse it. DuckDB can ATTACH a SQLite file directly (chapter 9), which makes that a one-liner.

DuckDB vs PostgreSQL

Not a competitor. PostgreSQL is a transactional server with concurrency, durability, replication and a security model. DuckDB is an analytical library.

The overlap is one specific case: analytical queries over data that lives in Postgres. Postgres does them slowly because it is a row store (its chapter 15 says so). The options are to ATTACH from DuckDB (chapter 9 — but pushdown is limited, so pull a filtered slice), to export to Parquet on a schedule, or to use pg_duckdb to put DuckDB’s engine inside Postgres.

DuckDB vs Spark

Covered from the other side in Spark’s chapter 15, which is blunt: under ~100 GB, DuckDB or Polars wins decisively, and this is the most common misuse of Spark.

   THE CROSSOVER is 100 GB to 1 TB, and it
   depends on the query and the machine.

   ABOVE IT, or if the logic is not SQL,
   or if you need ML at scale, or you are
   maintaining lakehouse tables as a WRITER
     → Spark.

   BELOW IT
     → DuckDB, and it is not close on either
       wall-clock time or cost.

Measure. People are routinely surprised which side they are on.

DuckDB vs Trino

   TRINO     a cluster. Fifty connectors.
             Sophisticated per-connector
             pushdown. Many concurrent users.
             An organisation's query layer.

   DUCKDB    one process. Six-ish connectors.
             Basic pushdown. One analyst.

Chapter 9 has the detail. The one-line version: DuckDB federates for one person, once. Trino federates for a company, permanently.

DuckDB vs a warehouse

   SNOWFLAKE / BIGQUERY / DATABRICKS

   ✓ concurrency, governance, sharing, SSO,
     no machine to think about, elastic
     scale, a support contract
   ✗ cost, per query and per hour
   ✗ your data lives somewhere else
   ✗ development and CI cost money too


   DUCKDB

   ✓ free, instant, local, and CI is a pip
     install
   ✗ one process, no governance, no sharing

The strongest combined pattern on this page: a warehouse for production and shared serving, DuckDB for development, CI and local analysis — same dbt models, dbt-duckdb locally and the warehouse adapter in production (chapter 12).

The four ways it is misused

1. As a shared database

   ✗ a .duckdb file on an NFS share, opened
     by six services.

   → the lock fights, or the file corrupts,
     or readers see stale snapshots.

Network filesystems and DuckDB’s locking are a bad combination, and the architecture is wrong regardless. Chapter 11’s answer: shared files in object storage, engines disposable.

2. As an OLTP store

   ✗ an API that INSERTs one row per request.

   → a column store writing single rows, a
     transaction per insert, and a WAL append
     per transaction.

Use SQLite or Postgres, and DuckDB to analyse what they hold.

3. As the system of record

   ✗ the only copy of the data is a .duckdb
     file on one box.

   → one file, one machine, no replication,
     no point-in-time recovery, and a backup
     story you have to build yourself.

Keep the source data in Parquet in object storage and treat the .duckdb as a derived artefact (chapter 13). If it can be rebuilt in four minutes, it does not need a backup strategy.

4. As a streaming sink

   ✗ a consumer writing every Kafka message
     into DuckDB.

   → thousands of tiny transactions, and one
     process holding the lock.

Batch to Parquet, or use Iceberg with a proper streaming writer, and point DuckDB at the result.

Where it is uniquely right

   ✓ ANALYSIS ON ONE MACHINE, at a size
     people assume needs a cluster
   ✓ QUERYING FILES IN PLACE — Parquet, CSV,
     JSON, local or S3
   ✓ THE DATA SCIENCE INNER LOOP — Arrow,
     notebooks, zero-copy
   ✓ EMBEDDING analytics in an application
     or a CLI
   ✓ TESTS AND CI for data pipelines — the
     most obviously correct and least used
     application
   ✓ dbt LOCAL DEVELOPMENT
   ✓ READING A TABLE FORMAT from a laptop
   ✓ SERVERLESS analytics — Lambda plus
     Parquet in S3
   ✓ IN THE BROWSER, via WASM, which nothing
     else here can do

The argument in one paragraph

DuckDB is what you get when someone takes twenty years of column-store research seriously and then refuses to build a server around it. Everything good about it — no operations, no connection string, zero-copy handoff to Arrow, files as tables, a query engine in a browser tab — comes from that refusal, and so does everything limiting about it: one writer, no users, no security model, nothing running in the background. The mistake is not choosing DuckDB; the mistake is choosing it for a problem whose defining feature is that several things need to write at once, and then spending six months building a server around a library that deliberately is not one. For everything else — and “everything else” is a much larger fraction of real analytical work than the industry’s tooling reflects — it is faster than what you were going to use, it costs nothing, and it installs in four seconds.

What to take from this chapter

• The deciding question is concurrency, not size: does more than one process write, or do many users query at once? If yes, you want a server.
• Against pandas it is not close — all cores, ~1× memory, spills, reads Parquet directly. Use both: aggregate in DuckDB, hand pandas the small result.
• Against Polars it is genuinely close. DuckDB is a database (catalog, tables, transactions, extensions, ecosystem); Polars is a dataframe library with a better expression API. They interoperate at zero cost, so “both” is legitimate.
• Against ClickHouse the split is clean: reach for a server when you need concurrent users, continuous ingestion, or more than one machine.
• SQLite and DuckDB are complements, row store and column store; DuckDB can ATTACH a SQLite file.
• Against Spark, the crossover is 100 GB to 1 TB — measure. Against Trino: DuckDB federates for one person once, Trino for a company permanently.
• Warehouse in production, DuckDB for development and CI is the strongest combined pattern here, via dbt adapters.
• Four misuses: a shared file on NFS, OLTP, the system of record, and a streaming sink.
• It is uniquely right for one-machine analysis, files in place, the notebook loop, embedded analytics, CI, dbt locally, table formats from a laptop, serverless, and the browser.
• Everything good and everything limiting comes from the same refusal to build a server.

One chapter left: the reference card.