15. When to use ClickHouse — and when not to

ClickHouse is a specialist. Within its specialism it is extraordinary — frequently 100× faster than a general-purpose database on the same hardware. Outside it, it is not merely slower; it is missing features you would consider mandatory.

Unlike MongoDB, which degrades gracefully when misused, ClickHouse fails fast and obviously. Try to use it as an application database and you discover within days that there are no transactions and updates rewrite whole files. That is a mercy: you find out immediately rather than in two years.

What ClickHouse is genuinely excellent at

Scanning and aggregating enormous tables. Billions of rows per second per server. This is the headline and it is real.

Compression. 10:1 on ordinary data, far more on well-modelled time-series. Your storage bill and your I/O both drop by an order of magnitude.

Real-time analytics on fresh data. Data is queryable seconds after insertion. Unlike a batch warehouse, there is no nightly load window — ClickHouse serves an operational dashboard over data that arrived a moment ago.

Time-series at scale. Sorted storage, delta codecs, TTL tiering, native window and time functions. Metrics, logs, traces, events, financial ticks.

Cost. This is underrated. A ClickHouse cluster on commodity hardware routinely does the work of a cloud warehouse costing many times more, because compression and column pruning mean less hardware. For a company with a large, steady analytical workload, the difference is a budget line.

SQL and BI compatibility. Grafana, Metabase, Superset, Tableau, and every JDBC/ODBC tool connect. Analysts do not need to learn a new language.

Ingesting from everywhere. Kafka, S3, HTTP, files, PostgreSQL, MySQL, MongoDB — as native table engines. You can often skip an ETL tool. Where you cannot — a transformation that is genuinely not SQL, or data far larger than one cluster — that is Spark’s remaining territory, and its chapter 15 is honest that a columnar database wins on every axis for SQL over tables.

Genuinely open source. Apache 2.0, and it has stayed that way through the period when several other systems in these guides changed licence.

The workloads it fits

• Product and web analytics — the original use case.
• Observability — logs, metrics, traces at volume. ClickHouse underlies several commercial observability products, and the OpenTelemetry Collector ships a ClickHouse exporter, so it is a supported destination rather than a custom integration — see chapter 10 of the OpenTelemetry guide.
• Real-time dashboards over event streams.
• Financial market data — ticks, order books, with ASOF JOIN doing real work.
• IoT and telemetry.
• Ad tech — impressions, clicks, attribution, where volume is enormous and latency requirements are tight.
• Security analytics — SIEM-style querying over event logs.
• Customer-facing analytics — the “insights” tab in a SaaS product, where a query must return in 200 ms over a tenant’s full history.

The common thread: append-mostly data, queried by scanning and aggregating over large ranges, where the answer is a summary rather than a record.

Where you should use something else

1. As your application’s primary database

This is the case that matters most, so it comes first.

ClickHouse has no transactions, no rollback, no foreign keys, no unique constraints, no efficient single-row updates, and no efficient point lookups. Every one of those is mandatory for an application database.

The pattern that works is both: PostgreSQL owns the application state; events flow to ClickHouse for analytics. That is not a compromise — it is the correct architecture, and it is what most companies running ClickHouse actually do.

Use PostgreSQL, MySQL or MongoDB for the application, and ClickHouse alongside.

2. Frequent updates or deletes of individual rows

Mutations rewrite entire data parts (chapter 6). Lightweight DELETE helps, and it is still not a row-level update.

If your workload naturally involves changing individual records often — a CRM, an order management system, anything with mutable state as its core — this is a fundamental mismatch, not a tuning problem.

ReplacingMergeTree handles a moderate change rate as a CDC sink. It does not handle a transactional update workload.

3. Many small inserts you cannot batch

Chapter 6’s rule. Async inserts help substantially, and they do not make one-row-per-request architecture a good idea at high rates.

If you genuinely cannot batch — thousands of independent producers, no buffering layer — put Kafka in front and let ClickHouse consume from it. That is the standard architecture, and the Kafka guide covers the producing side.

4. High-concurrency point lookups

ClickHouse is built for a moderate number of large queries, not thousands of tiny ones. Each query wants many threads; max_concurrent_queries defaults to 100 for a reason.

“Get the user record for user 4471,” ten thousand times a second, is a job for PostgreSQL, Redis, or a key-value store. ClickHouse will do it in milliseconds rather than microseconds, and it will not do it ten thousand times a second alongside your analytics.

5. Complex multi-table relational queries

Chapter 10 was honest about joins: the right table goes in memory, and you are the join planner.

A workload of five-way joins across large tables with unpredictable shapes is what a relational warehouse’s cost-based optimiser exists for. Snowflake and BigQuery do this better.

Denormalise and use dictionaries and ClickHouse is superb. Insist on a star schema with large dimensions joined every which way and you will fight it.

6. Small data

Under a few hundred gigabytes, PostgreSQL with good indexes — or DuckDB — will serve you well with far less operational surface. ClickHouse’s advantages appear at scale; below it, you are paying the costs without collecting the benefits.

ClickHouse versus the alternatives

	ClickHouse	PostgreSQL	DuckDB	Druid / Pinot	Snowflake / BigQuery	Elasticsearch
Model	columnar OLAP	row OLTP	embedded columnar	columnar OLAP	cloud warehouse	inverted index
Scale	petabytes	terabytes	one machine	petabytes	petabytes	terabytes
Scan speed	excellent	poor	excellent (small)	very good	very good	moderate
Ingest latency	seconds	instant	n/a	sub-second	minutes	seconds
Concurrency	moderate	high	single-process	high	high	high
Joins	limited	full	full	very limited	full	limited
Transactions	no	yes	limited	no	yes	no
Updates	expensive	cheap	cheap	very limited	cheap	expensive
Ops burden	moderate	moderate	none	high	none	high
Cost at scale	low	moderate	trivial	high	can be very high	high
Licence	Apache 2.0	PostgreSQL	MIT	Apache 2.0	proprietary	SSPL/Elastic/AGPL

DuckDB deserves a serious look and is the most commonly missed alternative. It is an embedded columnar engine — SQLite for analytics — with excellent SQL, no server, and no operations at all. For datasets that fit on one machine (which now means hundreds of gigabytes), for local analysis, for notebooks, and for querying Parquet in place, DuckDB is often simply the better tool. Reach for ClickHouse when you need a server: concurrent users, continuous ingestion, or data beyond one machine.

Druid and Pinot target sub-second latency at very high query concurrency for user-facing analytics. They ingest from Kafka with lower latency than ClickHouse and are considerably harder to operate — more components, more moving parts. ClickHouse has closed much of the latency gap while remaining far simpler; evaluate honestly against your actual concurrency and latency requirements.

Snowflake and BigQuery separate storage from compute, scale elastically, and require no operations. They are excellent, and they cost per query or per second of compute. For a large, steady workload, ClickHouse on your own hardware is often dramatically cheaper. For a spiky, unpredictable workload, elastic billing may be cheaper. Do the arithmetic with your real query volume.

Elasticsearch overlaps for log analytics, and the comparison is instructive. Elasticsearch indexes every term to find documents; ClickHouse scans columns to compute aggregates. For “show me the logs containing this error message,” Elasticsearch wins. For “count errors per service per minute over six months,” ClickHouse wins by a wide margin and uses a fraction of the storage. Many teams run both, or migrate log analytics to ClickHouse and keep Elasticsearch for search. See the Elasticsearch guide, which makes the same recommendation from the other side.

The honest limits

Even when ClickHouse is right:

No transactions. Design around it.

Updates and deletes are bulk operations. Design around it.

The ORDER BY key is effectively permanent (chapter 5). Changing it means rewriting the table.

Joins need your attention. Order matters, memory matters, distribution matters.

Materialised views do not backfill and can block inserts if they error.

Keeper is a hard dependency for replicated tables — a single point of ingestion failure.

It releases very fast. Features move; defaults change; the backward-incompatible section of the changelog is not decorative. Track LTS unless you have a reason.

Fewer experienced operators than PostgreSQL. Hiring and Stack Overflow answers are both thinner.

The failure mode of a bad schema is dramatic — a query that should read 8,000 rows reads 40 billion. There is no gentle degradation.

The decision

ClickHouse is the right answer when you can say yes to most of these:

□ The data is append-mostly — events, logs, metrics, facts
□ Queries scan and aggregate large ranges rather than fetching records
□ You can batch inserts, or put Kafka in front
□ Updates and deletes are rare, or expressible as new versions
□ You do not need transactions on this data
□ The dataset is large enough — hundreds of GB and up — to justify it
□ Queries have a common filter dimension to sort by (tenant, service, host)
□ Someone will own the schema decisions in chapters 2, 5 and 11

Six or more: ClickHouse is an excellent choice. Fewer than four — particularly if “append-mostly” and “no transactions” are the failures — you want PostgreSQL, and possibly DuckDB.

And stated fairly: when the workload fits, nothing else is close on price-performance. A single server answering aggregate queries over a hundred billion rows in under a second, with your data compressed tenfold, on hardware you already own, is a genuinely remarkable thing. It is worth the schema discipline it demands.

The mistake is not choosing ClickHouse. It is choosing it instead of an operational database rather than alongside one.

What to take from this chapter

• Excellent at: scanning and aggregating enormous tables, compression, real-time analytics on fresh data, time-series, cost, SQL and BI compatibility, and ingesting from everywhere.
• Wrong for: being your application database, frequent row updates, unbatched small inserts, high-concurrency point lookups, complex multi-table relational queries, and small data.
• The correct architecture is both: PostgreSQL or MongoDB owns state, ClickHouse owns analytics, with Kafka or CDC between them.
• DuckDB is the most commonly overlooked alternative — no server, no operations, excellent up to one machine. Its chapter 15 draws the same line from the other side and agrees: what a server buys you is concurrency, not capacity.
• Druid/Pinot for sub-second high-concurrency; Snowflake/BigQuery for elastic and hands-off; ClickHouse for steady high-volume workloads where cost matters.
• For log analytics specifically, ClickHouse beats Elasticsearch on aggregation and storage; Elasticsearch beats it on free-text search.
• Its real limits: no transactions, bulk-only mutations, a permanent ordering key, joins that need your attention, and a fast release cadence.
• Score the checklist. Six or more is a strong fit.

One chapter left: the reference card.