DuckDB vs ClickHouse: which one, and when you need both

DuckDB and ClickHouse get compared because they sit near each other in every analytical benchmark: both are columnar, both are vectorised, both are very fast. The benchmark framing hides the difference that actually decides it. DuckDB is a library you put inside your program. ClickHouse is a server your programs connect to. Almost every practical difference follows from that.

I run both. ClickHouse is the analytical store behind Ansaar, feeding a live API. DuckDB is what I reach for in batch jobs and local work, and it is the subject of my book. So this is the comparison from operating them, not from a benchmark table.

The short answer

Pick DuckDB when the work happens inside one program — a transformation job, a service that owns its own queries, a notebook, a CI run — and the data arrives in batches. There is nothing to operate, and it is extraordinarily fast for that one program.

Pick ClickHouse when many clients query the same data at the same time, the data keeps arriving, and people expect the numbers to be seconds old. That is a server's job, and ClickHouse is one of the best servers for it.

If you have both shapes, run both. That's more common than either camp admits.

What each one is

DuckDB is an in-process analytical database — the usual shorthand is "SQLite for analytics". You import it into Python, Node, Go or the CLI, and it runs a columnar query engine inside that process. It reads Parquet, CSV and JSON directly, including from S3, with no load step. There is no server, no port, no cluster. Only one process may open a database file read-write, and while it does, no other process can open the file at all; any number of processes may read it if none is writing. The pattern that scales is one writer producing Parquet and many read-only readers over it.

ClickHouse is a client-server columnar database built for real-time analytics. You run it as a service (or buy ClickHouse Cloud), clients connect over the network, and it stores data in its MergeTree engine: rows land in sorted parts on disk, and background merges combine them. It is built to ingest continuously and answer aggregations over billions of rows in milliseconds, for many clients at once. It scales out across machines when one isn't enough.

At a glance

	DuckDB	ClickHouse
Shape	Library inside your process	Server that clients connect to
Concurrent writers	One process at a time	Many clients, continuously
Concurrent readers	Many, if all are read-only	Many — it's the point
Data arrives	In batches, as files	Continuously, as inserts or a Kafka stream
Freshness	As fresh as the last batch	Seconds
Scale	Up — one bigger machine	Up and out — shards and replicas
Joins and ad-hoc SQL	Strong optimiser, friendly dialect	Works, but rewards wide tables designed for known queries
Operating burden	None, but you build any serving layer	A real database to run, or a Cloud bill
Cost when idle	Nothing	The server keeps running

The question that decides it: who's asking, and how many at once

With DuckDB, the process that imports it is the only thing that talks to it directly. If fifty users need data, they don't connect to DuckDB — they call your API, and your API queries DuckDB. That's the right design for a lot of products: your service owns auth, caching and rate limits, and DuckDB is the fast engine behind it. That is the pattern in DuckDB as a SaaS warehouse, partitioned by tenant.

But notice what you've taken on. Your service is now the database server — connection handling, concurrency, back-pressure, scaling out when one machine isn't enough. For modest, cacheable load that's a small job. For hundreds of concurrent dashboard queries over data that changed thirty seconds ago, you're rebuilding ClickHouse, badly.

ClickHouse makes the opposite trade. The server is the product: many clients, concurrent queries, concurrent inserts, all handled for you. What it asks in return is that you run it.

So before reading any benchmark, count two things: how many clients query at the same time at peak, and how old the data they see is allowed to be.

Freshness: files in batches vs a stream of inserts

DuckDB's natural write pattern is a batch job: read the new data, transform it, write Parquet, exit. The data is as fresh as the last run. Every ten minutes is fine; every ten seconds from six services at once is not, because only one process may write at a time.

ClickHouse ingests continuously by design. It has a native Kafka table engine, so a topic becomes a table, and a materialised view can roll new rows into aggregates as they arrive. Querying while ingesting is normal.

There is one ClickHouse habit to learn early: insert in batches, not row by row. Each insert creates a part on disk. A stream of single-row inserts creates parts faster than the background merges can combine them, and eventually the server refuses writes with a "too many parts" error. Batch upstream, turn on async_insert, or let the Kafka engine do the batching. It's the first thing everyone hits.

SQL and joins

This is where DuckDB is the friendlier of the two.

DuckDB's SQL is close to Postgres, with conveniences on top: GROUP BY ALL, SELECT * EXCLUDE (...), FROM-first queries. Its cost-based optimiser reorders joins for you, so a five-table star schema written the obvious way runs well.

-- DuckDB: inside your process, straight off the files
SELECT tenant_id, event_type, count(*) AS events
FROM read_parquet('s3://analytics/events/dt=2026-09-*/*.parquet')
GROUP BY ALL
ORDER BY events DESC;

ClickHouse's dialect is its own. Joins work and have improved a lot, but the engine is built around wide, denormalised tables, and the decision that matters most is the table's ORDER BY. It's the sort key, it drives the sparse primary index, and it decides which queries are instant and which scan everything.

-- ClickHouse: a server-side table, sorted for the queries you serve
CREATE TABLE events
(
    tenant_id  LowCardinality(String),
    event_type LowCardinality(String),
    event_at   DateTime,
    user_id    UInt64
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_at)
ORDER BY (tenant_id, event_type, event_at);

With that sort key, "events for this tenant, this type, this week" is close to instant. "All events for this user ID" scans the table, because user_id isn't in it. In DuckDB you'd rarely think about that. In ClickHouse it's the whole design.

A few more ClickHouse edges worth knowing before you commit. UPDATE and DELETE are not cheap row operations — they're mutations that rewrite parts, and while newer versions add lighter variants, it is not a database for frequently changing rows. ReplacingMergeTree removes duplicates during background merges, so for a while a query can see both versions of a row unless you ask for FINAL. None of this is a flaw. It's the cost of the ingest speed.

What each one costs to run

DuckDB costs nothing to operate because there is nothing to operate. Pin the version, set memory_limit and temp_directory, keep one writer, and it's done — the setup that matters is short. The real cost is the one I keep coming back to: if many clients need it, you build the serving layer, and that's engineering time rather than an invoice. The honest limitations are all versions of that.

ClickHouse costs a database's worth of attention. Self-hosted, that means the servers, upgrades, backups, monitoring, and — once you replicate — ClickHouse Keeper to coordinate the replicas. It means schema design around ORDER BY and sensible partitioning. It rewards that attention with speed and a very low hardware bill for steady load. ClickHouse Cloud removes most of the operating work and replaces it with a usage bill. Either is a reasonable trade if the workload needs a server. Neither is if it doesn't.

The line is blurrier than it looks

Two things complicate the "library vs server" framing, and they're worth knowing about.

ClickHouse runs in-process too. chDB is the ClickHouse engine packaged as a library — import it, no server — and clickhouse-local is the command-line version. If your team already writes ClickHouse SQL, these give you DuckDB's shape with ClickHouse's dialect. They also inherit the in-process trade-offs: fast for one program, not a server for many.

DuckDB has a server-shaped option. MotherDuck runs DuckDB as a managed service, with read replicas for many concurrent readers. It moves DuckDB toward ClickHouse's territory for reads; it doesn't change the single-writer model, and it isn't built for continuous high-rate ingest. I cover it more in DuckDB vs Snowflake.

The pattern that runs both

The architecture I'd suggest to most teams that genuinely need a serving database is not a choice between them. It's a split:

• ClickHouse serves the product. The live tables, the customer-facing dashboards, the API that answers "what happened in the last minute".
• DuckDB does everything around it. Batch transformations, backfills, ad-hoc investigation, local development and CI against the same data.
• Parquet in object storage is the shared layer. DuckDB reads and writes it natively. ClickHouse reads it in place:

-- ClickHouse reading the same Parquet the DuckDB jobs wrote
SELECT tenant_id, event_type, count() AS events
FROM s3('https://analytics.s3.amazonaws.com/events/dt=2026-09-*/*.parquet', 'Parquet')
GROUP BY tenant_id, event_type
ORDER BY events DESC;

The practical benefit is reversibility. Nothing is trapped in either engine, a heavy backfill can run in a DuckDB job without touching the serving cluster, and if you later decide one engine is enough, the data is already in an open format the other can read.

When DuckDB is the right call

• The work runs inside one program: a transformation job, a notebook, a CI run, a service that owns its queries.
• Data arrives in batches, and minutes or hours of staleness is fine.
• The working set fits on one machine — most do.
• Concurrency is modest, or cacheable behind your own API.
• You want nothing to operate and no bill when it's idle.

When ClickHouse is the right call

• Many clients query the same data at the same time, all day.
• Data arrives continuously — events, logs, metrics, market data — and should be queryable within seconds.
• Users see the dashboards, so latency under load is a product feature.
• You'll run a database, or pay ClickHouse Cloud to.
• The data outgrows one machine, or will.

If you start on DuckDB and outgrow it

This is the path I'd recommend for most new products: start on DuckDB over Parquet, and move the serving layer to ClickHouse when concurrency or freshness forces it — not before. If Parquet in object storage was the source of truth from day one, the move is mostly schema design. ClickHouse can read the files in place, so the data doesn't have to move. The real work is choosing each table's ORDER BY for the queries you actually serve, and rewriting join-heavy queries as reads from wider tables. Budget for that, not for the data.

The long version of the DuckDB side, with runnable code, is my book, Local-First Analytics. Related reading: ClickHouse vs Snowflake, for when you've decided you need a server and are choosing which, and can DuckDB be your SaaS product's warehouse?, for where the single-node ceiling actually is.

If you'd like someone who runs both in production to look at your workload and say which one it needs — or whether it needs either — a Data Platform Audit is a week and a written roadmap you keep. The scoping call below is free.