ClickHouse vs Druid vs Pinot

These three are compared because they are the open-source answers to the same request: sub-second aggregations over billions of fresh events, usually straight off Kafka. They overlap on maybe 80% of features — columnar storage, compression, real-time ingestion, SQL — so the feature matrix will not decide it. What decides it is the query population. ClickHouse is a fast, general SQL database you shape with tables and sort keys; it shines when tens to hundreds of concurrent queries ask varied, ad-hoc questions with joins. Druid and Pinot are serving systems you shape with ingestion specs and indexes; they shine when thousands of concurrent users ask the same few questions and each must answer in under 100–300ms. The one question: is this for a few hundred analysts and engineers exploring data, or for thousands of end users loading the same dashboard?

The Verdict#

Default: ClickHouse, because most teams have the internal-analytics problem and ClickHouse is the simplest to run and the most flexible to query. Choose Pinot when the product is user-facing analytics at thousands of QPS with per-tenant queries or upserts; choose Druid when time-series slicing with ingest-time rollup is the core and you already have the expertise.

🎯 Staff Move: "Before I pick an engine I want the query population: how many concurrent queries, how predictable, what p99. For an internal observability store with a few hundred engineers I'd take ClickHouse. For a merchant dashboard serving two million merchants at 10K QPS with a 200ms budget, I'd take Pinot with a tenant-first sort and a star-tree."

When the non-default wins:

• ClickHouse for user-facing analytics wins when concurrency is in the low hundreds of QPS, queries hit materialized views, and a result cache absorbs repeats — one engine for internal and external use is cheaper to run than two.
• Druid over Pinot wins when the team already runs it, and when ingest-time rollup plus compaction of time-series data are the main cost levers rather than per-column index variety or upserts.
• A cloud warehouse instead of all three wins when the users are analysts, concurrency is low, and seconds of latency are fine. Do not run a real-time OLAP cluster for a weekly report.

At a Glance#

Throughput and latency numbers are orders of magnitude on modern hardware with sensible schemas; any of the three can be 10× slower on a schema that ignores its sort key or partitioning.

Numbers to bring:

How They Actually Differ#

1. A Database vs a Serving System#

ClickHouse is a database: you CREATE TABLE ... ENGINE = MergeTree ORDER BY (tenant_id, event_time), insert with SQL, and query with SQL. Its genius is raw scan speed — vectorized execution over compressed columns, often reading billions of rows per second per node — plus a sparse primary index that skips granules outside the sort-key range. You design for it by choosing the sort key and partitioning, then adding materialized views for the expensive aggregates.

Druid and Pinot are serving systems: you write an ingestion spec or table config that declares the time column, dimensions, metrics, rollup or indexes, and the system builds immutable segments, distributes them across servers and routes queries through brokers that scatter and gather. Their genius is predictable latency under concurrency: indexes, segment pruning and pre-aggregation keep each query's work bounded, so 5,000 QPS of tenant-scoped queries does not melt the cluster.

🎯 Staff Insight: ClickHouse's answer to concurrency is "scan faster and cache". Druid's and Pinot's answer is "do less work per query". At 50 QPS the first wins on simplicity; at 10,000 QPS of near-identical queries the second wins on cost.

2. How Each Pays for Pre-Aggregation#

The rollup ratio is the cost model. 1 billion events/day with (minute, country, device, campaign) at 200 × 5 × 10,000 combinations may collapse 20× — or barely at all if a dimension like user_id sneaks in. Measure it; do not assume it.

3. Freshness, Updates and Correctness#

All three consume Kafka at least once, so duplicates are possible on retries. Their correction stories differ sharply:

• ClickHouse deduplicates identical insert blocks on retry and offers ReplacingMergeTree to keep the latest row per key — but only when parts merge, at an unspecified time. Queries that must be exact use FINAL (costly) or aggregate with argMax. Lightweight deletes and updates exist; heavy mutation workloads remain an anti-pattern.
• Druid treats data as append-only segments per time chunk. To fix a day, you re-ingest that day; the new segment version atomically replaces the old. Corrections take minutes to hours and are batch jobs.
• Pinot has the only exact streaming upsert of the three: an in-memory map from primary key to the latest record, older versions masked at query time. The price is that the input topic must be partitioned by the key, partitions are fixed at table creation, and heap grows with live keys — roughly keys × (key bytes + 24), so 500M live keys is ~16 GB before replication.

4. Operating Model#

ClickHouse's operational surface is one binary per node plus ClickHouse Keeper (Raft-based and ZooKeeper-protocol compatible) for replication metadata. The pain points are known: too many small inserts creating "too many parts", manual resharding when a cluster grows, and heavy queries from one user starving others without quotas.

Druid has the largest surface: separate coordinator, overlord, broker, router, historical and ingestion processes, plus a metadata database, deep storage and a coordination service. That separation is a strength at large scale — historicals, brokers and ingestion scale independently, and deep storage makes historicals replaceable — and a tax at small scale. Pinot sits between: controller, broker, server and minion processes, with ZooKeeper via Apache Helix for cluster state, and deep storage for segment backup.

Where Each One Breaks#

The production surprise for each:

• ClickHouse: it is so fast on day one that nobody adds quotas, and the first ad-hoc SELECT * over a year of logs takes down the dashboards.
• Druid: the system is healthy, the data is stale. Ingestion succeeded but segments were never handed off and loaded.
• Pinot: the upsert table that was a pilot with 10M keys has 1B keys a year later, and heap is now the capacity limit.

Incident Sketch: The Ad-Hoc Query That Took Down the Dashboards#

t=0        An engineer runs a GROUP BY over 12 months of raw events, no tenant filter
t=+5s      Query reads 40 TB across all shards; every core busy; memory near limit
t=+20s     Customer dashboard queries on the same replicas queue; p99 from 150ms to 25s
t=+2min    Dashboard API times out; support tickets begin
t=+6min    On-call kills the query; queue drains in 90 seconds

Prevention: separate replicas (or a separate cluster) for ad-hoc and serving traffic, per-user quotas on memory and execution time, and a rule that user-facing queries must filter on the leading sort-key column. Owner: platform for isolation, product for query review.

Incident Sketch: Stale Druid Dashboard, Green Ingestion#

t=0        Metadata database fails over; coordinator loses its connection briefly
t=+1min    Ingestion tasks keep consuming Kafka and publishing segments to deep storage
t=+2min    Coordinator stops assigning new segments to historicals
t=+45min   Dashboards show data 45 minutes old; ingestion lag metric reads zero
t=+50min   Coordinator restarted; load queue drains; data catches up

Detection that would have caught it: a freshness probe that queries the newest timestamp through the broker every minute, plus segment load-queue length and unavailable segment count. Lesson: measure freshness where users read, not where data is written.

Cost and Operations#

Back-of-envelope: 1 TB/day of raw events compressed 10× is 100 GB/day; 90 days hot is 9 TB, which fits 3 replicas of a handful of large NVMe nodes in any of the three. The engines diverge on compute for concurrency: serving 10K QPS from ClickHouse means replicas and caches sized for scans, while Pinot or Druid serve it from indexes and rollups with fewer cores per query.

Assumptions: compressed columnar storage on SSD, 30–90 days hot, list cloud prices; order of magnitude only.

🧭 Principal Insight: The cost line that grows forever is not storage. It is every new dimension a product manager adds to a user-facing dashboard — it changes rollup ratios, index memory and query cost for all tenants. Make dimension additions a reviewed change with a cost estimate.

Switching Later#

Moving the user-facing path to a second engine, in order:

1. Keep Kafka as the shared source; the new engine consumes the same topics from the retained offset.
2. Backfill history from raw files in object storage into offline or batch-ingested segments.
3. Shadow the dashboard API: issue every query to both engines, compare results and latency per tenant.
4. Cut over tenant cohorts, largest last, with a per-tenant flag to roll back.
5. Keep the original engine for internal exploration; delete the duplicated serving tables.

The one-way doors:

1. Discarding raw events after rollup. Keep raw data in object storage (Parquet, 30–90 days or longer) so any engine can be rebuilt.
2. Pinot upsert table partitioning. The partition count is fixed at creation; changing it means a new table and a backfill.
3. Exposing engine-specific SQL to customers. Once customers write queries against your engine's dialect, you cannot switch engines without breaking them. Expose an API, not the SQL.

How Real Companies Chose#

Cloudflare — ClickHouse Replacing a Postgres Rollup Pipeline#

Cloudflare replaced its 2014-era analytics pipeline — a PostgreSQL rollup database and a 12-node Citus cluster fed by thousands of lines of aggregation code — with ClickHouse, after its DNS team had proven it. The new pipeline handled ~6M HTTP requests per second on average (8M peaks) with 36 ClickHouse nodes and 3× replication, reduced stored data from about 60.5 PiB to 18.5 PiB, and raised API throughput from 15 to about 40 queries per second (Cloudflare Blog).

Staff insight: Note the query rate: tens of QPS over enormous data. That is ClickHouse's sweet spot — huge volume, modest concurrency — and the sentence that shows you know where each engine wins.

Confluent — Druid Chosen over Pinot and ClickHouse#

Confluent runs Druid behind its cloud monitoring dashboards, alerting, stream lineage and metrics API, as well as internal billing and control-plane workflows: over three million events per second ingested and over 250 queries per second, with 7 days on historicals and 2 years in S3. In 2019 it evaluated Druid, Pinot and ClickHouse and chose Druid for its documentation at the time, in-house expertise, and proven handling of high-cardinality time-series metrics (Confluent Blog).

Staff insight: The deciding factors were documentation and expertise, not benchmarks. For engines this close in capability, the team's ability to operate it is a legitimate tiebreaker — say so.

Uber — Pinot for User-Facing Real-Time Analytics#

Uber scaled Apache Pinot from a few use cases to a multi-cluster, all-active deployment powering hundreds of use cases over terabyte-scale data with millisecond latency — including restaurant-manager dashboards for Uber Eats, city operations dashboards, and backend services that need fresh analytics, with per-region QPS growing 30× (Uber Engineering). Separately, Uber moved its logging platform from Elasticsearch to ClickHouse, citing a single ClickHouse node ingesting ~300K logs per second and hardware cost cut by more than half (Uber Engineering).

Staff insight: One company, two engines, chosen by query population: Pinot where thousands of external users hit dashboards, ClickHouse where engineers query logs. That is the whole comparison in one org chart.

Follow-Ups to Expect#

What to Say in the Interview#

"The deciding factor is query population, not features. A few hundred engineers running ad-hoc SQL is ClickHouse; tens of thousands of users loading the same dashboard with a 200-millisecond budget is Pinot or Druid."

"I'll sort by tenant then time, roll up to one-minute grain at ingest, and keep raw events in object storage for 30 days, so any rollup decision is reversible by replay."

"Ingestion is at-least-once in all three, so I'll name the dedup mechanism — Pinot upserts if the dashboard must reflect refunds exactly — and keep billing numbers in the warehouse."

"Every new dimension on a customer-facing dashboard is a permanent cost for every tenant, so I'd make adding one a reviewed change with a rollup-ratio estimate."

Related Guides#

• Real-Time OLAP: ClickHouse, Druid & Pinot — internals, indexes, rollups and failure modes in depth
• Design Ad Click Aggregation — a classic real-time aggregation workload
• Design a Metrics Platform — high-cardinality time series at scale
• Time-Series Databases — when a TSDB fits better than general OLAP
• Kafka — the ingestion source for all three
• Batch and Stream Pipelines — hybrid realtime/offline tables and backfills
• Hot Keys — the large-tenant problem in its general form
• Elasticsearch — when the query is text search, not aggregation