ClickHouse vs. Druid vs. Pinot

Learning Objectives

Distinguish ClickHouse, Apache Druid, and Apache Pinot along the axes that actually differ between them — deployment topology, query-time join support, and mutability — rather than treating "real-time OLAP" as one interchangeable category
Match a stated workload's query pattern (ad hoc analyst SQL, versus a fixed set of queries served to a huge concurrent audience, versus a stream requiring row-level correction) to the system whose architecture actually fits it
Build and defend a choice among the three for a stated workload, using named architectural trade-offs rather than category reputation
Diagnose "picked it because it says real-time OLAP" as a decision failure distinct from picking the objectively worst system — the failure of treating a category label as a specification

Why This Matters

The the-olap-landscape lesson put ClickHouse, Apache Druid, and Apache Pinot in the same box: real-time OLAP, defined by continuous ingestion and sub-second serving. That box was the right level of detail for a first pass — all three really do share that shape, against data warehouses and lakehouses. But "same category" is not "same system," and an engineer who stops at the category label makes a specific, recurring mistake: reaching for whichever of the three they've heard of, because the marketing all says the same thing — fast, real-time, scalable — without checking whether this workload's actual query pattern matches the one the chosen system was architected around. Ship Druid or Pinot for a BI team that needs ad hoc, exploratory joins across a normalized schema, and you'll spend weeks fighting a query-time join model that all three of these systems' own documentation tells you not to lean on. Ship ClickHouse for a product feature exposed directly to millions of end users, each hitting a small, fixed set of dashboard queries thousands of times a second, and you may be fighting a different battle: a query-execution model built around fewer, heavier queries, not massive fixed-query fan-out. This lesson is about the three axes that actually separate these systems, so the choice is made on architecture, not on which name showed up first in a search.

The trade-off

Every one of these three systems could, in principle, be bent to serve any of the workloads in this lesson — that's what makes the wrong choice so easy to not notice until it hurts. The trade this lesson is actually about: time spent checking a workload's real shape — who's running the queries, how many of them, whether the query set is fixed or exploratory, whether rows need correcting after the fact — against three systems' documented architectural choices, versus the much faster shortcut of picking whichever "real-time OLAP" name is most familiar and discovering the mismatch only once the workload is live. Druid and Pinot were both built, from their first design decisions, around a specific shape: a large, mostly-fixed set of queries served to a large, often public, concurrent audience, with strong opinions about how joins and mutability should be handled to keep that promise. ClickHouse was built around a different center of gravity: full, general-purpose SQL — including query-time joins — operated as a single binary with fewer moving parts by default. None of these are worse engineering. They're different bets about what "real-time OLAP" workloads look like, and the bet each system made is visible directly in its architecture.

The alternatives

ClickHouse, steelmanned. You already know ClickHouse's shape from Modules 2 through 5: a single server binary running the MergeTree engine family, full SQL including joins as a first-class citizen (hash join is the default algorithm, with automatic build-side reordering as of ClickHouse 24.12), and no required external coordination service unless you opt into Replicated* engines for multi-node durability — a plain MergeTree table on a single node needs nothing but ClickHouse itself. That means the same system that answers a product dashboard's fixed queries can also answer an analyst's next unplanned JOIN without a second system, a schema migration, or a query-time restriction on what SQL is allowed. The cost of that generality is that ClickHouse doesn't make the same hard architectural bet Druid and Pinot make in the next two sections — it doesn't split ingestion, storage, and serving into independently-scaled process types from day one. Every query instead draws its worker threads from one shared, server-wide thread pool (bounded by max_thread_pool_size), and if concurrent queries' thread demands exceed that shared pool, later queries queue for a thread rather than running on capacity reserved just for them. That's a real, citable architectural difference from Druid's and Pinot's separate query-serving process types (the next two sections) — not yet a claim about which is faster under load, which this lesson doesn't have a benchmarked source for and won't assert.

Apache Druid, steelmanned. Druid splits its responsibilities into distinct, independently-scalable process types by design: Coordinators manage where data lives and keep it balanced, Overlords control ingestion and assign work to MiddleManagers, Historicals serve queries against data that's been committed to deep storage (durable, off-node storage such as S3 or HDFS, separate from the machines answering queries), and Brokers accept incoming queries and fan them out to the right Historicals. That split exists so ingestion load and query-serving load never compete for the same resources — a spike in one doesn't starve the other, which matters a great deal at the multi-tenant scale Druid targets. Druid also supports rollup: pre-aggregating rows that share the same dimensions and timestamp granularity at ingestion time, which can shrink stored row counts by orders of magnitude in exchange for losing the ability to query individual raw events on rolled-up columns. The cost: that same process split means a real Druid deployment is, from the start, a distributed system with several coordinated services plus a required ZooKeeper quorum (Druid's own docs recommend a dedicated 3- or 5-node ZooKeeper cluster for high availability) and a deep storage layer — more operational surface than a system that can still be one binary on one box.

Apache Pinot, steelmanned. Pinot makes a similar process-separation bet — Controllers, Brokers, and Servers, coordinated via Apache Helix (a cluster-management layer that tracks and reacts to the intended state of the cluster) on top of ZooKeeper, with segments persisted to a deep store — but its defining design center is different from Druid's: Pinot was built inside LinkedIn specifically to power member-facing product features like "Who Viewed My Profile," where the audience querying the system isn't a handful of internal analysts but every member of a huge product, hitting a small number of predictable queries continuously. Pinot also has a real answer to a gap both ClickHouse and Druid handle awkwardly: native upsert tables, where a primary key lets a newly-ingested row transparently replace an older one, made visible to queries via an in-memory bitmap update rather than a background compaction a query has to wait for. That upsert capability isn't free: it requires partitioning the source stream by primary key, it's available in full form only on real-time tables, and the star-tree pre-aggregation index — one of Pinot's other headline features — can't be used on a partial-upsert table. And joins, while supported, are explicitly routed through a separate multi-stage query engine rather than Pinot's original single-stage design, which is itself a signal of how central query-time joins were to Pinot's founding shape.

The join question, specifically

This is worth pulling out on its own because it's the single fastest way to discover you picked the wrong one of these three: how each system wants you to handle a query that combines two tables.

ClickHouse treats joins as ordinary SQL — you write one, ClickHouse's default hash join builds an in-memory table from the smaller side and streams the other side past it, and non-memory-bound alternatives exist for join sizes too large for that (already covered in this course's denormalization-tradeoffs and choosing-a-mergetree-engine lessons). Druid's own documentation is explicit that this is not the model to lean on: "whenever possible, for best performance it is good to avoid joins at query time," recommending instead that data be joined before it's loaded into Druid, or that small, slow-changing dimension tables be modeled as lookups — pre-broadcast key-value mappings held on every query server — rather than as join datasources. Druid's native join mechanism, when you do use it, only supports equality conditions, doesn't reorder joins to optimize them, and requires every table except the left-most "base" table to fit within a configured broadcast memory footprint. Pinot supports a fuller set of join types (inner, left, right, full, cross, semi, anti, and even as-of joins) but only through its multi-stage engine, a separately-documented execution path from Pinot's original single-stage query model — which is Pinot's own way of saying joins are supported, not that they're the default, idiomatic shape of a Pinot query the way they are in ClickHouse.

The pattern underneath all three: the more a system's architecture is built around serving a fixed, known set of queries to a huge, concurrent audience, the more that system pushes you toward resolving joins before serve time — pre-joined tables, lookups, denormalization — because a query-time join is exactly the kind of unpredictable, potentially-expensive work that architecture is built to avoid. ClickHouse, built around a more general SQL surface from the start, doesn't push back on query-time joins the same way.

Decision framework

The order a senior engineer actually works through this, for one workload:

Who is running the queries, and how many of them are running at once? A handful of internal analysts running unpredictable, exploratory SQL is a different problem from millions of end users each hitting one of a dozen dashboard queries thousands of times a second. The second case is the shape Druid and Pinot's process-separation architectures — and Pinot's founding use case specifically — were built to serve.
Is the query set fixed and known in advance, or genuinely exploratory? A fixed, small query set can be served well by a system that wants dimension tables pre-joined and lookups pre-broadcast (Druid, Pinot). Genuinely ad hoc SQL, including joins nobody anticipated, is where ClickHouse's more general query-time join support avoids a redesign every time a new question comes up.
Does an already-ingested row ever need to be corrected in place, and does the application need that correction reflected immediately? Pinot's native upsert tables answer this directly, with the partitioning and real-time-only caveats noted above. ClickHouse answers a similar need with ReplacingMergeTree/CollapsingMergeTree plus FINAL (covered in Module 5) — eventually consistent by default, immediately consistent at the cost of merge-time compute when FINAL is used. Druid's rollup-and-append model is the least suited of the three to frequent row-level correction.
How much operational surface can this team actually run? A plain ClickHouse MergeTree table needs nothing beyond the ClickHouse binary. Replicated* tables need Keeper. Druid and Pinot both need a real distributed system on day one: several independently-scaled process types plus a ZooKeeper quorum, before a single query has been served. That's not a flaw in Druid or Pinot's design — it's the direct cost of the process separation that gives them their concurrency isolation — but it's a cost a two-person team should weigh honestly against a workload that doesn't yet need that isolation.
Does the answer actually require choosing one system for the whole pipeline? Just as Module 5 showed a single ClickHouse pipeline composing multiple MergeTree engines for different requirements, it's common in practice for a raw event stream to feed more than one of these systems for different consumers — an analyst-facing ClickHouse table and a member-facing Pinot table from the same Kafka topic, for instance — rather than forcing one system to be good at every audience at once.

Cold Start — No AI

You're the engineer on a mid-size social platform's growth team. Product wants to launch a "Your Year in Review" feature: every user gets a personalized page showing a fixed set of about fifteen metrics (posts made, likes received, top interaction, etc.), computed from that user's own event history. At launch, this will be hit by essentially the platform's entire user base within a two-week window — potentially hundreds of thousands of concurrent page loads, each running the same fifteen queries with a different user ID as the filter. Separately, your data science team wants to be able to run new, unplanned exploratory SQL — including joins against a user-attributes table — against the same underlying event data, to find patterns for next year's feature.

Before reading further or asking an AI assistant anything: decide whether these two needs are served by the same system or different ones, which of ClickHouse, Druid, or Pinot you'd put behind the "Year in Review" page specifically, and what you'd tell the data science team to use instead if you don't pick the same system for them. Write your reasoning down before moving on.

Reflection: Where were you tempted to ask an AI "which real-time OLAP database is best" as a single question covering both needs? That framing invites one answer for a scenario that's actually two different workloads wearing the same "real-time analytics" label — a massive-fan-out, fixed-query product feature, and an exploratory, join-heavy analyst workload. Would a quick AI answer have separated those two needs itself, or would it have handed you one system and left you to discover the mismatch later?

Where were you tempted to reach for AI just now? What made you resist, or give in?

Reasoning Prompt

A fintech company is building a real-time fraud-signals dashboard for its internal risk team (roughly 30 analysts) and, separately, a "spending insights" feature shown directly inside its consumer mobile app to its full user base (several million users, each viewing their own insights on demand, with brief bursts of concurrent traffic around bill-pay dates). The risk team's dashboard needs unpredictable, ad hoc joins across transactions, merchant, and device-fingerprint tables — they're actively hunting for new fraud patterns and don't know next week's query shape. The consumer feature needs a small, fixed set of per-user aggregates, and if a user disputes a transaction and it's reversed, the reversal has to be reflected in their insights within seconds, not after a background merge. Reason through which of ClickHouse, Druid, or Pinot (or which combination) you'd recommend for each half of this system, and defend it against a colleague who proposes running everything on a single Druid cluster "since it's already real-time and we don't want two systems to operate."

Write your reasoning before revealing the model answer.

Model Answer⌄

A reasonable design uses two systems, not one, and the colleague's "avoid two systems" instinct, while a fair operational concern, is answered by looking at what each half of the workload actually needs rather than by defaulting to whichever system is already real-time.

Risk team's fraud dashboard → ClickHouse. This is exploratory, unplanned, join-heavy SQL from a small number of internal users — the opposite of the fixed-query, massive-fan-out shape Druid's architecture is built around. Druid's own documentation explicitly recommends against leaning on query-time joins for exactly this reason; forcing this workload onto Druid would mean either pre-joining transactions, merchants, and device fingerprints before ingestion (defeating the "unpredictable, ad hoc" requirement) or fighting a join engine that isn't the idiomatic center of that system. ClickHouse's general-purpose SQL, including joins as a first-class default, fits this workload's actual shape.
Consumer spending-insights feature → Pinot. Millions of end users, a small fixed set of per-user aggregate queries, and — critically — a same-instant correction requirement when a transaction reverses. That correction requirement is the deciding detail: Pinot's native upsert tables (primary-key-based, partitioned by that key) answer "reflect this correction within seconds" directly, whereas Druid's rollup-and-append model is the weakest of the three at frequent row-level correction, and ClickHouse would need ReplacingMergeTree/FINAL or an equivalent pattern to get comparable immediacy. Pinot's founding use case — member-facing features serving a huge, concurrent audience a fixed query set — is a close architectural match to this half of the system, not just a category match.
Answering the "why not one system" objection directly: the two halves of this workload disagree on the two axes that matter most here — query predictability (fixed vs. exploratory) and correction latency (needs same-instant upsert vs. doesn't) — and no single one of these three systems is simultaneously the best fit for "unpredictable analyst joins" and "same-instant per-user correction at huge fan-out." Operating two systems is a real cost, but it's a cost paid for actually fitting both workloads, versus one system doing both jobs adequately and neither one well.

The shape of the reasoning matters more than the specific picks: name which axis (audience size and query predictability, or mutability latency) is driving each half of the decision, rather than reaching for "real-time OLAP" as if the label settles it.

Case studies

A good fit: Pinot at LinkedIn, the founding case study. Pinot wasn't retrofitted onto a member-facing use case — it was built for one. LinkedIn's own engineering blog describes Pinot as the backend for "Who Viewed My Profile" and similar features, serving analytics directly to the site's own members rather than to internal analysts. That's the massive-fan-out, fixed-query shape Pinot's architecture — and specifically its upsert and star-tree indexing features — was built to serve well, and it's a good fit precisely because the workload matches the system's founding design center, not because Pinot is generically "the fast one."

A bad fit: treating "real-time OLAP" as a single interchangeable slot. A team building an internal BI tool for ad hoc revenue analysis picks Druid because "we need real-time analytics" appeared in the project brief, and Druid was the first real-time OLAP system a team member had used before. Weeks in, every new analyst question requires either a schema change to pre-join a new dimension into the fact table, or a lookup table maintained by hand — friction that wouldn't exist had the team recognized "ad hoc, unpredictable, join-heavy" as the ClickHouse-shaped half of this lesson's decision framework rather than reaching for whichever "real-time" name was familiar.

Anti-pattern gallery

Choosing by category membership instead of architectural fit. "It's real-time OLAP, and I need something real-time" skips every question this lesson's decision framework actually asks. All three systems in this lesson satisfy that one shared trait; they disagree sharply on everything else that determines whether a specific workload will be pleasant or painful to run.

Fighting a system's documented recommendation instead of routing around it. Repeatedly forcing query-time joins into Druid, against its own documentation's explicit guidance to pre-join or use lookups instead, is a sign the workload's real shape wants ClickHouse's join model — not a sign Druid needs more tuning.

Treating operational simplicity as free. Picking Druid or Pinot for a workload that doesn't yet need their concurrency isolation, and then discovering the cost of running several coordinated process types plus a ZooKeeper quorum for a workload a single ClickHouse binary would have served, is the mirror image of the previous anti-pattern: paying real operational cost for isolation properties the workload never asked for.

Summary

ClickHouse, Apache Druid, and Apache Pinot all satisfy the "real-time OLAP" definition from Module 1 — continuous ingestion, sub-second serving — but that shared category hides three real architectural differences this lesson names directly: deployment topology (ClickHouse can run as one binary with no required coordination service until you opt into replication; Druid and Pinot are distributed systems by design, splitting ingestion, storage, and serving into separate process types coordinated through a required ZooKeeper quorum), query-time join support (ClickHouse treats joins as ordinary, first-class SQL; Druid's own documentation recommends avoiding query-time joins in favor of pre-joining or lookups; Pinot supports a fuller join surface but only through a separate multi-stage engine), and mutability (Pinot's native, primary-key-based upsert tables answer same-instant row correction directly; ClickHouse answers a similar need with ReplacingMergeTree/FINAL, eventually consistent by default; Druid's rollup-and-append model is the least suited to frequent correction). The decision framework this lesson teaches — audience size and query predictability first, mutability needs second, operational capacity third — replaces "which real-time OLAP system is best" with the actually-answerable question of which system's founding architectural bet matches the workload in front of you, and it's entirely normal for one pipeline to feed more than one of these systems for genuinely different audiences.

Quiz

1. An internal analytics team needs to run frequent, unplanned SQL queries — including new joins against tables nobody anticipated needing to join last month — over a dataset of moderate size. According to this lesson, which system's architecture fits this best, and why?
2. A product feature will be shown directly to millions of end users, each running the same dozen fixed queries filtered by their own user ID, with bursts of high concurrency. Per this lesson's decision framework, what does this detail about the query pattern suggest?
3. A workload requires that when a transaction is reversed, the correction is reflected in a user-facing aggregate within seconds — not after a background process runs. Which system does this lesson identify as having a direct, native answer to this specific requirement?
4. A two-person startup team needs a real-time analytics system for a moderate-scale internal dashboard and wants to minimize the number of services they operate. What does this lesson's operational-surface question in the decision framework suggest about Druid and Pinot for this team?
5. A colleague argues: 'Let's just run our whole analytics stack — the ad hoc analyst dashboards and the millions-of-users product feature — on one Druid cluster, since it's already real-time and we don't want to operate two systems.' What does this lesson suggest is the flaw in that reasoning, independent of whether operating two systems is annoying?