Blog

Questions readers ask: Is ClickHouse really faster than DuckDB for analytical queries?

Analytical query speed is a property of a workload. DuckDB can lead on single-machine aggregations without being the right choice for shared production analytics. Its execution model therefore needs to fit the data, queries, and users it must serve.

How each engine's architecture shapes what it is fast at

DuckDB runs inside the process that calls it. That removes a network hop, serialization overhead, and a server round-trip from the query path. The query engine is already where the application runs. This helps explain why a small, embedded system can do well against systems built to handle more kinds of work.

Its file access model extends that advantage to data that has not been loaded into a database. DuckDB reads Parquet, CSV, and JSON directly. It can also treat Hive-style key=value path segments, such as year=2026/month=03/, as virtual partition columns when hive_partitioning=true is set or the layout is auto-detected. With the right filters, that lets it skip whole files before reading their contents.

DuckDB is optimized for a single machine and does not distribute queries across machines. Large joins and aggregations can spill to disk, but that does not remove the memory constraint under concurrent load. A poorly bounded query can exhaust the host process. Other queries sharing that process are then exposed to the same failure.

An embedded engine needs no separate serving layer, but analytics on a shared application or database host use resources that other work may need. Moving analytical computation to a separate system changes that resource relationship, but introduces another system to operate and data to keep synchronized. Neither arrangement is free of trade-offs. The architecture shapes which trade-off comes first as demand grows.

What benchmark results actually show and where each engine leads

ClickBench provides evidence that DuckDB is highly competitive on flat analytical queries. DuckDB reached the top position among open-source engines on its hot runs, completing the suite remarkably quickly on a modern laptop. Heavy aggregation queries were a particular strength. That substantial result establishes that DuckDB can lead within a relevant class of analytical work.

The conditions delimit what that result proves. The test used a single node, a web analytics dataset that fit comfortably in memory, and queries suited to analytical execution. Those conditions align with DuckDB’s design. The result supports its use for similar work, but it does not establish how a shared production service will behave under concurrent demand. A hot-run leaderboard is not a test of every deployment model.

Query shape also changes what a benchmark measures. JSONBench tests native JSON support for semi-structured data, whereas ClickBench tests flat analytical queries. Their results answer different questions. A lead on heavy aggregation over flat data does not establish a lead on JSON-heavy analysis, because the latter brings different storage and execution concerns into the comparison.

For practitioners, the useful evidence is therefore more specific than an overall position. Strong aggregation performance is relevant when aggregations dominate the actual workload. It is less decisive when the operational burden comes from simultaneous users, continuous ingestion, or data access outside the local machine. Those conditions need their own evidence.

Workloads where DuckDB's design gives it a structural advantage

Ad-hoc file analysis is a particularly strong fit for DuckDB. An analyst with a Parquet export can obtain results in seconds without provisioning a server, defining a schema, or building an ingestion pipeline. The absence of preparation is part of the performance advantage. For a one-off question, time spent making data queryable belongs in the comparison just as much as query execution time does.

That advantage extends to moderate-scale work on a single machine. For workloads under roughly 100 GB that serve one user or a small team, DuckDB often has low latency because there is no network overhead. The size guideline is not a performance guarantee. Query shape, the memory on hand, and concurrent demand still decide whether the work fits the machine.

Local, join-heavy analytical queries also belong in this workload class. DuckDB can run the analysis where the data already is, and it can spill large joins and aggregations to disk when they go beyond what fits in memory. Disk spilling remains a way to complete work on one host. This distinction becomes important when a successful local workflow starts accumulating larger datasets or more simultaneous users.

The boundary is sharper in shared or customer-facing deployments. A single heavy query can exhaust the process and affect other queries running within it. That risk changes the evaluation even when individual query latency looks attractive. Similarly, data that no longer fits on one machine exceeds DuckDB’s deployment model because there is no path to sharding its execution across nodes.

Continuous ingestion presents a different boundary again. DuckDB is a query engine. Its ability to analyze a file efficiently does not establish that it can serve as the destination for an ongoing event stream. For teams whose analytics still fit beside their existing Postgres work, embedded execution can be a good trade. Once analytics require a different resource envelope, that convenience no longer settles the choice.

How the data location question changes the calculus

Local files and an S3 data lake can hold the same data and still have different query latency. DuckDB’s in-process execution removes the application-to-engine round-trip, but it does not remove the network access required to read remote objects. S3 latency narrows the advantage over local disk. Data location can therefore change the result without any change in dataset size, query text, or user count.

A well-partitioned lake lets DuckDB use path segments to skip files that have no data the query needs. A poorly partitioned lake loses that advantage. Direct file access thus helps most when the files are laid out to match the filters the workload uses.

Postgres analytics illustrate the same principle at a different boundary. The pg_duckdb extension can accelerate heavy aggregations on existing heap tables while keeping the work on the Postgres host. This makes it relevant where the data is already present and the server has spare capacity. It does not create independent analytical resources. The host must still have room for both transactional work and the analytical queries running beside it.

Moving analytical computation elsewhere changes that relationship rather than simply changing SQL execution speed. A separate system shifts analytical resource consumption away from the transactional host, at the cost of operating another system and keeping its data synchronized. That is the broader lesson of the extension choice. Where the data is sets both the access path and which machine pays for the query, so a faster scan alone cannot decide where to run it.

A practical decision frame for matching workloads to engines

The strongest signals for DuckDB reinforce one another. Local files or a well-partitioned S3 lake, a single analyst or small internal team, and exploratory queries all reduce the value of a separate ingestion and serving layer. In a Python or notebook-based pipeline, in-process execution may already be the intended architecture. The attraction is not one isolated feature, but the alignment of preparation time, execution locality, and limited concurrency.

Postgres introduces a conditional version of that fit. Heavy aggregations on heap tables are a reason to choose pg_duckdb when the host has the resources to run them. The qualification is essential. Where transactional latency cannot absorb analytical scans, or analytics have outgrown the host, keeping computation close to the tables becomes a liability.

With multiple concurrent users and customer-facing query paths, memory isolation is needed to keep the service running as well as to help it perform. Continuous streams of events, logs, traces, or LLM completions also require data to become queryable as it arrives. JSON-heavy workloads introduce another consideration because columnar JSON storage changes the query plan. These requirements need to be assessed together, alongside dataset size.

AI and observability workloads bring several of those demands into the same infrastructure. High-write ingestion, low-latency analytical queries, and high concurrency must coexist. A quick result on one analytical query addresses only part of that requirement. This is why the engine that fits an analyst’s notebook need not be the engine that fits a shared service built around the same subject matter.

The honest outcome can also be both. DuckDB can fit local exploration or bounded analytical tasks while a separate system serves workloads with different ingestion, concurrency, or scale requirements. There is no contradiction in that arrangement.

What to do next depending on where you are in the decision

For readers whose workload aligns with DuckDB, the next step is to examine performance in ClickBench’s published results for the query shapes that resemble their own. Heavy aggregation results deserve attention when aggregation dominates the intended work. They say less about how well a workload built around semi-structured data or shared serving will run. This keeps the benchmark connected to the decision it can actually inform.

For teams using Postgres, the practical next step is to evaluate pg_duckdb on their actual heap tables and query mix. It accelerates high-cardinality GROUP BY, but the speedup does not apply uniformly across query shapes. The evaluation belongs within the host-resource budget already identified in the decision frame, because accelerating analytics is not the only requirement the database must meet.

For teams building AI or observability infrastructure, ingestion throughput, analytical latency, and concurrent use belong in the same evaluation. None can stand in for the others. The convergence of observability, LLM tracing, and analytical work makes this a consequential architectural choice, because the infrastructure must support their shared demands.

Where the requirements divide, the next step need not be a commitment to one engine for everything. The decision can remain specific to each workload, with benchmark evidence applied only where its conditions match. That preserves the value of DuckDB’s strongest results without extending them beyond what they establish.

All posts