ClickHouse vs. DuckDB: When Local Wins

The trade-off

The instinct to compare DuckDB and ClickHouse as "which database wins" is the wrong frame from the start. Within DuckDB's sweet spot — data that fits comfortably on one machine — the two are often genuinely comparable in raw speed, and DuckDB sometimes wins outright. The real axis isn't speed; it's deployment model. DuckDB "does not run as a separate process, but completely embedded within a host process" — no server to install, update, or maintain, and for the analytical workloads it targets, "high-speed data transfer to and from the database" as a direct consequence of never crossing a network boundary at all. ClickHouse is a client-server system built from the ground up for the opposite shape of problem: many concurrent users, continuous high-volume ingestion, and more data than any single machine can hold, coordinated across a cluster. Trading one for the other trades away whichever half of that shape your actual workload doesn't need — and the cost of guessing wrong shows up as either needless operational burden or a system that can't do the one thing you actually needed it to do.

Both sides, steelmanned

DuckDB's case. Beyond the no-server simplicity, DuckDB's integration story is unusually tight: "the DuckDB Python package can run queries directly on Pandas data without ever importing or copying any data" — a genuine zero-copy path from a data scientist's existing in-memory dataframe straight into SQL. It's also not a toy at scale: DuckDB has processed "1 billion rows (~50 GB) on a MacBook Pro with 16 GB RAM." And on ClickBench — a benchmark ClickHouse itself created and hosts, not a DuckDB-authored one — DuckDB 1.4 LTS ranks "#1 open-source on ClickBench hot runs, trailing only Umbra, a closed-source research prototype." For a single analyst, a notebook, or an application that wants to embed real analytical SQL without operating a database service, this is a strong, legitimate case.

ClickHouse's case. DuckDB's embedded model is also its hard ceiling: it's "a single process that scales vertically" — one machine, one file, by design, with no built-in path to distribute a query across a cluster or serve many independent concurrent writers coordinating shared state. ClickHouse's whole architecture — everything from Module 3's MergeTree merges to Module 5's replication — exists to serve workloads DuckDB's model structurally can't: sustained ingestion "from Kafka around the clock," "hundreds of concurrent queries" against a shared, continuously-updating dataset, and horizontal scale into the terabyte-to-petabyte range. A customer-facing analytics product serving hundreds of paying users' dashboards simultaneously isn't a data-volume problem DuckDB is bad at — it's a concurrency-and-coordination problem DuckDB's single-process model isn't built to solve at all.

A third option: use both. These two cases aren't necessarily exclusive within one organization, and the strongest real-world answer is often "both, for different jobs" — covered as this lesson's case study below.

Decision framework

Rough, sourced thresholds — not hard cutoffs, but the shape of the reasoning a senior engineer applies:

1. How much data, and does it fit on one machine? Under roughly 10GB, DuckDB "often executes analytical queries faster than ClickHouse because it avoids network communication" entirely. The crossover where ClickHouse's distributed execution starts winning "typically occurs between 50GB and 500GB, depending on query patterns," and DuckDB stays viable for "small production workloads under 100 GB." These are data-volume signals, not verdicts — the next two questions matter as much or more.
2. How much concurrency does this actually need? DuckDB fits "small production workloads" serving "a few hundred users" in a lighter-weight sense; ClickHouse is the answer once a workload needs "hundreds of concurrent queries" against shared, live data. A dataset can be small in total size and still need ClickHouse if enough independent users are hitting it at once — concurrency, not size, is what breaks a single embedded process.
3. How is data arriving? A single-node ingestion path "struggles above 100,000 rows per second"; sustained ingestion beyond that, especially from a continuous stream like Kafka, is squarely ClickHouse's territory, not because DuckDB can't load data quickly in a batch, but because DuckDB has no continuous, concurrent-with-queries ingestion story to match.
4. Is this exploratory/embedded, or production-serving? A single analyst's notebook, an ad-hoc join before a meeting, or analytics embedded directly into an application binary is DuckDB's home ground. A dashboard or API that other people depend on being up, current, and responsive under concurrent load is ClickHouse's.

import duckdb

# No connection string, no running server process to reach.
# The engine lives inside this Python process.
duckdb.sql("""
    SELECT customer_id, sum(amount) AS total
    FROM 'transactions.parquet'
    GROUP BY customer_id
    ORDER BY total DESC
    LIMIT 10
""").show()

There's no ClickHouse equivalent of that snippet that doesn't first involve a server to connect to — a Docker container, a cloud instance, a clickhouse-client pointed at a host and port. That single difference is the entire architectural trade-off this lesson is about, compressed into whether there's a network hop between your query and your data at all.

Case study: PostHog runs both, on purpose

PostHog — an open-source product analytics company — uses ClickHouse and DuckDB simultaneously, for different jobs, rather than treating the choice as an either/or. ClickHouse is "a real-time analytical engine that powers our funnels, trends, retention, and paths over billions of events" — PostHog's core, always-on analytics product. DuckDB was added later specifically to power a managed data warehouse feature: ad-hoc, flexible querying for customers who need broader exploration than PostHog's built-in analytics provide, without PostHog having to horizontally scale infrastructure just for that exploratory access pattern. In their own words, "DuckDB is fantastic at spontaneous, smaller analytical queries as it skips client-server communication and operates on local data," while ClickHouse remains the engine for the sustained, high-volume, concurrently-queried core product. They even built a small piece of glue software, "Duckgres," that "wraps DuckDB in a Postgres server, making it compatible with anything that supports the PostgreSQL wire protocol" — a concrete illustration that "use both" isn't a hand-wave; it's an integration decision a real engineering team invested in on purpose.

Anti-pattern gallery

Standing up a ClickHouse cluster (or ClickHouse Cloud) for a single analyst's ad-hoc exploratory workload. If the data fits on a laptop and only one person needs it for an afternoon, a whole distributed system is pure operational overhead bought for a problem — concurrency, scale, continuous ingestion — that scenario doesn't have. This is the ColdStart scenario's trap, and it's a genuinely common one: reaching for "the serious database" out of habit or reputation rather than because the workload's shape requires it.

Trying to scale DuckDB horizontally to serve growing concurrent production traffic. DuckDB isn't entirely single-process in every configuration — multiple processes can read the same database concurrently in read-only mode, and DuckDB has documented, if narrower, paths for multi-process writes (a beta remote-write protocol, or a DuckLake catalog backed by Postgres for production use). What none of that adds up to is what ClickHouse provides by default: a query scheduler built for hundreds of concurrent queries, with priority, resource quotas, and horizontal read/write scaling across a cluster. Running many independent DuckDB processes behind a load balancer and expecting ClickHouse-shaped concurrency out of it means either reconstructing that coordination layer from scratch on top of DuckDB's narrower primitives, or accepting real limitations (read-only replicas, a single writer, or an added dependency like a Postgres catalog) that a workload's actual concurrency needs may not tolerate. If a workload has outgrown what those documented paths comfortably support, that's the signal to move to a system built for the multi-node, many-concurrent-writer case as a first-class design goal, not a bolted-on extension.

Treating a single benchmark's headline number as the whole answer. ClickBench's 100-million-row, single-flat-table, no-JOIN shape is a real, useful data point — and also not a stand-in for "which database should I use in production." The Misconception above is this anti-pattern's specific, common form.