DuckDB, ClickHouse, Druid or Pinot: How to Choose OLAP

Table of Contents

• This is not a ranking, it is a decision tree
• The comparison table
• DuckDB: when you don't need a service
• ClickHouse: the balance point for production
• Druid and Pinot: when you really do need real time at massive scale

  • Apache Druid: from ad-tech analytics to general streaming
  • Apache Pinot: born to serve, not to explore
  • The practical Druid vs. Pinot rule
• Real cases mapped
• Frequently asked questions

  • DuckDB or ClickHouse?
  • ClickHouse or Druid, which should I choose?
  • Apache Pinot vs. Druid vs. ClickHouse, which is better?
  • ClickHouse or Vertica?
  • Can I migrate from Druid or Pinot to ClickHouse later, or the other way around?
• Conclusion

This is not a ranking, it is a decision tree

The reason generic "DuckDB vs ClickHouse vs Druid vs Pinot" comparisons tend to confuse more than they clarify is that they mix two different questions as if they were one:

Question 1: do you need a service, or is a library enough? DuckDB runs inside your process. The other three are services you stand up separately, with ports, users and high availability in the mix.

Question 2: if you need a service, is your priority general-purpose analytics or latency under extreme concurrency? ClickHouse is optimized for the first. Druid and Pinot were born for the second.

With those two questions answered, the tree looks like this:

1. Are your queries launched by a single process —your backend, a notebook, an ETL script— and do the data fit comfortably on a disk? → DuckDB. You don't need anything else.
2. Do you need a shared service, with continuous writes from several sources, and is your load "aggregations over a growing events table"? → ClickHouse. It is the balance point for the vast majority of projects, from a solo maker to a mid-size team.
3. Is your priority not exploratory analytics but serving the same pre-defined query to thousands of simultaneous users with millisecond latency, fed by streaming from Kafka? → Druid or Pinot. And only if you have people dedicated to operating them.

The mistake I have seen most often is skipping step 1 by instinct —"let's set up something serious"— when 80% of the real volume fit on a single machine. And the second mistake, less frequent but more expensive, is reaching step 3 without having the load that justifies it: you end up operating a cluster of six processes for a problem ClickHouse solved with a binary.

The comparison table

	DuckDB	ClickHouse	Apache Druid	Apache Pinot
Architecture	Embedded, in-process, no server	Distributed columnar, one binary per node	Distributed columnar, specialized processes	Distributed columnar, specialized processes
Minimum deployment	A library (pip install duckdb)	1 binary	6 process types + ZooKeeper + metadata store	Controller, Broker, Server + ZooKeeper
Best use case	Local analysis, notebooks, ETL, backend of an app that queries its own Parquet/CSV files	Production events table with frequent aggregations, self-hosted	Streaming ingestion with ad-hoc exploration and hot/cold data tiering	Serving the same pre-defined query at massive concurrency with a millisecond SLA
Operational complexity	None: there is nothing to operate	Low–medium: one more service to maintain	High: requires a dedicated team	High: requires a dedicated team
Streaming ingestion	N/A	Good (Kafka engine, materialized views)	Excellent, its reason for existing	Excellent, its reason for existing
Concurrency	Low (one process)	Very good	Good	Excellent, designed for this
Flexible ad-hoc queries	Excellent	Excellent	Good	Limited: shines with patterns known in advance
When NOT to use it	You need several readers/writers to share live data, or to scale beyond one machine	You need guaranteed sub-second latency at tens of thousands of concurrent QPS	You are a small team without dedicated SREs, or your priority is free-form exploration without fixed patterns	You are a small team without dedicated SREs, or your queries constantly change shape

The cells are simplifications; the detail is in the sections that follow.

DuckDB: when you don't need a service

DuckDB was created by Mark Raasveldt and Hannes Mühleisen at CWI (Centrum Wiskunde & Informatica) in Amsterdam, with the first public version announced in 2019 at the SIGMOD conference. The original motivation is revealing: they had been trying to embed MonetDB in R and Python packages, and discovered that no existing analytical database was designed to live inside another process without assuming it was in charge. DuckDB was born to fill exactly that gap.

It is, in practice, what SQLite is for OLTP but for analytics: a library, not a service. pip install duckdb and you already have a vectorized columnar engine running inside your script, with a PostgreSQL-compatible SQL dialect. It queries Parquet and CSV directly from disk or from a URL, integrates natively with pandas and Polars, and there is no port to open or process to supervise.

Where it wins: any flow where a single person or a single process runs the queries. Exploratory data science, notebooks, the analytical backend of a small app that only needs to read its own data, ETL pipelines that transform files. For that load profile, it is hard to find a serious rival in simplicity.

Where it loses, and why that is a design, not a defect: DuckDB is fundamentally a single process. It does not serve data to dozens of concurrent readers from different machines, it does not scale horizontally, and it is not meant to receive constant writes from multiple sources at once. MotherDuck exists as a cloud layer for whoever needs to share it, but at that point you have already changed tool category.

The signal that it no longer serves you: when you need several services writing at the same time and several clients reading at the same time, without some of them blocking the others. That is where you cross over to ClickHouse.

ClickHouse: the balance point for production

If DuckDB solves "one process, my data", ClickHouse solves "a service, everybody's data". It is the option when you need analytics to stop being a script you run yourself and become something that receives writes 24/7 from your application and answers queries from your team or your customers.

I am not going to repeat its whole columnar architecture and sparse indexes here —I cover that in detail in what ClickHouse is and how to install it— but the summary for this decision is: one binary, no JVM, no mandatory ZooKeeper, that scales from a laptop to hundreds of nodes with the same engine.

Where it wins against Druid and Pinot: everything that is not the extreme case of massive concurrency with a millisecond SLA. Exploratory ad-hoc queries, reasonable JOINs, a small team without dedicated SREs, low operational cost. The vast majority of "I need real time" actually means "I need the data available in seconds, not minutes", and there ClickHouse is more than enough.

Where it loses against Druid and Pinot: if your real SLA is milliseconds with tens of thousands of identical concurrent queries —think of a product feature that hundreds of thousands of end users query at the same time— ClickHouse can fall short without considerable tuning effort, while Druid and Pinot are designed from scratch for exactly that profile.

When NOT to use ClickHouse in this context: if your data fit on one machine and a single process queries them, it is over-engineering versus DuckDB. If your SLA is milliseconds at extreme concurrency with streaming from Kafka, it is the wrong tool versus Druid or Pinot.

Druid and Pinot: when you really do need real time at massive scale

Druid and Pinot share a family: both run on the JVM, both split the system into several specialized process types, both depend on ZooKeeper for coordination, and both expect a Kafka-style ingestion pipeline feeding them continuously. Both are also notably more complex to operate than ClickHouse: that is not an opinion, it is a direct consequence of having six (Druid) or three (Pinot) distinct process types to deploy, monitor and scale separately, instead of one binary.

But they are not interchangeable with each other, because they were born to solve different problems.

Apache Druid: from ad-tech analytics to general streaming

Druid was born in 2011 inside Metamarkets, a programmatic advertising company that needed to analyze billions of events with drill-down and roll-up in real time and discovered that neither MySQL nor HBase delivered the required speed. It was opened as open source in 2012 and moved to an Apache license in 2015.

That origin —exploratory analytics over event streams, with dimensions that change and users who want to slice the data in unforeseen ways— still shapes its design. Druid explicitly separates "hot" data (recent, in memory) from "cold" data (historical, in deep storage), and that makes it strong for anyone who needs long retention with exploratory queries over the full history, not only over the latest data.

Where it wins against Pinot: flexibility for unanticipated ad-hoc queries and age-based data management. If your analyst team wants to ask new things about history without redesigning indexes, Druid gives more room.

Apache Pinot: born to serve, not to explore

Pinot was born at LinkedIn with a much narrower and more specific goal: serving features such as "who viewed your profile" to hundreds of millions of users with millisecond latency. It is no coincidence that its architecture —star-tree indexes, inverted indexes, partitioning designed for known access patterns— is optimized to run the same query shape over and over, very fast, for a lot of people at once.

Where it wins against Druid: raw concurrency on pre-defined queries. If you know in advance the shape of the queries you will serve —a product dashboard with fixed filters, a per-user metrics counter— and your priority is holding tens of thousands of simultaneous queries with minimal latency, Pinot is more finely tuned for exactly that.

Where it loses against Druid: exploratory queries with new shapes. Pinot shines when you know your access patterns and index for them; it is awkward when someone wants to ask something nobody anticipated.

The practical Druid vs. Pinot rule

If your problem is "real-time analytics facing an internal team that explores freely", lean toward Druid. If your problem is "real-time analytics facing the end user, with predictable queries and massive concurrency", lean toward Pinot. And if you are not sure which of the two scenarios describes your case, that is a fairly strong signal that you still don't need either: your real problem is probably step 2 of the tree, not step 3.

When NOT to use either: if your team is a few people with no experience operating distributed systems on the JVM, the cost of maintaining six processes coordinated by ZooKeeper does not pay off except at very large scales. It is symptomatic that several well-known companies have migrated workloads from Druid to ClickHouse citing cost reduction and simplicity, precisely because their real volume did not require the complexity they were paying for.

Real cases mapped

To bring this down from theory, four concrete scenarios and which tool they point to:

A data analyst exploring historical sales in Parquet from a notebook. DuckDB. No debate: one process, data that fit on a laptop, zero need to share state with anyone.

A B2B SaaS with a product events table that grows every day and an internal dashboard several people query. ClickHouse. Shared service, continuous writes, moderate concurrency, no need for guaranteed millisecond latency.

An ad-tech platform that ingests impressions from Kafka and needs its analyst team to slice those data by arbitrary dimensions in near real time, with months of retention. Druid. Streaming ingestion, flexible exploration, hot/cold tiering.

A social network or mass-consumer product that shows a personalized metric —"your stats this week"— to millions of simultaneous users with a strict latency SLA. Pinot. Pre-defined query, extreme concurrency, access pattern known in advance.

If your scenario does not clearly fit any of the four, go back to the decision tree at the start: almost always the right answer is the simplest option that solves today's problem, not the most impressive one.

Frequently asked questions

DuckDB or ClickHouse?

DuckDB if a single process runs the queries and your data fit on one machine: notebooks, ETL, backend of a small app. ClickHouse when you need a shared service with continuous writes from several sources and several concurrent readers. The frontier is "library" vs. "service", not performance.

ClickHouse or Druid, which should I choose?

ClickHouse for the vast majority of cases: lower operational complexity, one binary versus six process types, and more than enough performance for general-purpose analytics. Druid only if your real load is massive streaming ingestion with ad-hoc exploration over hot and cold data, and you have a dedicated team to operate it.

Apache Pinot vs. Druid vs. ClickHouse, which is better?

There is no universal "best". ClickHouse for general production analytics with low operational complexity. Druid for streaming with flexible exploration and age-based data management. Pinot for serving pre-defined queries at massive concurrency with millisecond latency. Choose by your load pattern, not by a benchmark.

ClickHouse or Vertica?

They are more different categories than they look. Vertica is a proprietary columnar MPP (with a limited community edition) aimed at large enterprises with a license budget and a DBA team. ClickHouse is open source with no license cost, deploys as a binary, and fits much better with a small team that wants self-hosting without negotiating a contract.

Can I migrate from Druid or Pinot to ClickHouse later, or the other way around?

Yes, in both directions, although it is not trivial: the ingestion models and the most idiomatic queries of each one are not a direct copy. The usual path is to start with ClickHouse for its lower operational cost and migrate to Druid or Pinot only if concurrency or the real SLA demand it in a measurable way, not in anticipation.

Conclusion

The four tools in this post are excellent at what they do, and that is precisely why the question "which is better?" is poorly posed. DuckDB wins when the work is done by one process. ClickHouse wins when you need a general-purpose service with reasonable operational complexity. Druid and Pinot win only at the extreme of massive streaming with a millisecond SLA, and only if you have someone to operate them.

For most projects starting today, the natural path is to begin with DuckDB while the data fit on one machine, and jump to ClickHouse when you need that analysis to become a shared service. If you truly reach the Druid or Pinot rung, you will know because the pain will be concrete and measurable, not an intuition that "this has to be bigger than it is".

If ClickHouse is your next step, the shortest path is what ClickHouse is and how to install it, and if you want to see the rest of the market options in more detail, the comparison of ClickHouse alternatives covers StarRocks, TimescaleDB and the cloud warehouses that this post leaves out on purpose because they do not fit the decision tree of real-time engines.