10 good reasons to use ClickHouse

I gave this talk at ClickHouse Meetup #35 in Istanbul on 19 November 2019. The title promised ten reasons; the deck has eleven numbered slides, because I could not stop. This is the talk as an article, with each reason explained properly instead of as a slide bullet, and with a note on what has changed since.

• Slides: presentations.clickhouse.com/2019-meetup35
• Video: YouTube, also on clickhouse.com/videos

The “since 2019” notes were checked against ClickHouse 26.4 (clickhouse local); every SQL snippet below was run there.

The premise

The talk opened with two facts to keep in mind. Data grows faster than computing power. And the Unix timestamp was, that week, past 1.5 billion (1574114808 when I wrote the slide). The point of the second one is a sense of scale: a table with a billion rows is not an exotic object, and it was already ordinary in 2019. The question was never “how do I store it” but “how do I answer a question about it before the person asking gives up”.

My claim was that I had used many database systems for fifteen years and kept ending up on ClickHouse, even for workloads where it does not fit perfectly. These are the reasons.

1. Speed

Inserts are instant, selects are fast, and billions of rows answer in under a second. The mechanism is the same as it is today: data is stored by column, sorted by the table’s ORDER BY, and a query reads only the columns and the row ranges it needs. Inserts are cheap because each one just writes a new immutable part to disk; merging happens later in the background.

Since 2019. The speed story did not change in kind, but it gained more places to look for it: projections, data skipping indexes, a query cache, and a much faster join story (joins, memory and algorithms). The “inserts are instant” part has a caveat I under-sold: every insert creates a part, so tiny inserts hurt. That is the subject of insert batching and too many parts, and ClickHouse now ships a server-side fix, asynchronous inserts, which are on by default in 26.4:

SELECT name, value FROM system.settings WHERE name = 'async_insert';

async_insert	1

2. Scalability

Two slides, two directions. Up: a single query uses every core of the machine, thanks to vectorised execution and parallel processing. Out: tables can be sharded across hosts, and a Distributed table makes a cluster look like one giant database to the client. I claimed the scaling was linear, and for the right queries it comes close.

Since 2019. The honest update is that “scale out” now has two meanings. The classic one (shards and replicas you manage) is unchanged. The new one is ClickHouse Cloud, where storage lives in object storage and compute scales separately, using a cloud-only table engine that is not part of the open-source server. Replication coordination can also be done without ZooKeeper now, with ClickHouse Keeper, a built-in replacement (see replication internals).

3. Compression

Compression is per column, which is why it works so well: a column holds values of one type and often similar values. Two knobs matter. Encodings re-map values into a layout that compresses better, and a general compression algorithm then squeezes the result.

Encoding	Good for
LowCardinality	strings with few distinct values (up to roughly ten thousand, per the slide)
Delta	difference between consecutive values
DoubleDelta	slowly changing sequences such as timestamps
Gorilla	floats that rarely change
T64	integers that occupy a small range of a wide type

On the algorithm side it is a trade: LZ4 is fast with a modest ratio, ZSTD is slower with a better ratio. You can mix them per column:

CREATE TABLE codecs_demo
(
    a UInt64  CODEC(Delta, ZSTD),
    b Float64 CODEC(Gorilla),
    c Int64   CODEC(DoubleDelta),
    d UInt64  CODEC(T64, LZ4),
    e LowCardinality(String)
)
ENGINE = MergeTree
ORDER BY a;

Since 2019. Everything on the slide still exists and still works as written above (that statement ran unchanged on 26.4). What grew is the guidance around it: see choosing codecs.

4. Production ready

Two arguments. Fault tolerance: tables can be replicated across hosts, and every replica accepts writes (the slide called it multi-master replication). And a track record: the deck showed a list of companies using it, and the largest example I had, Yandex Metrica, then ran, by the numbers on the slide, a cluster of 374 servers holding more than 13 trillion rows, 20 billion events per day, 17 PB of data (2 PB compressed).

Those figures are the 2019 ones, quoted from the slide; I have not re-checked them and they are certainly out of date.

Since 2019. This is the reason that aged best, and the one that needs the least argument now. In 2019 you needed a slide of logos to make the case; today ClickHouse is a default answer for analytics and observability workloads, backed by ClickHouse, Inc. (spun out of Yandex in 2021) and a very active community.

5. Integration

ClickHouse can use other systems as tables and can be used by almost anything: it reads JDBC sources, talks to other ClickHouse servers without configuration, and calls REST services. The slide’s summary was “ClickHouse integrates into anything (almost)”. Mechanically, this is table engines and table functions.

SELECT * FROM url('https://api.github.com/repos/ClickHouse/ClickHouse', JSONEachRow, 'name String') LIMIT 1;

ClickHouse

Since 2019. This is where the largest growth is. On 26.4 there are 74 table engines, 89 table functions and 108 input/output formats in the system tables. Engines that did not exist, or that I would not have named in 2019, include Iceberg, DeltaLake, Hudi, AzureBlobStorage, MongoDB and Redis, next to the older S3, Kafka, PostgreSQL, MySQL, ODBC and JDBC. See ClickHouse and S3 and the HTTP interface.

6. Awesome table capabilities

The slide listed seven features. In order:

• Enums. A column holds one of a fixed set of names but stores a small integer. The slide contrasted it with a seven-branch CASE over a day number.
• Partitioning. Each partition is stored separately; you can detach, attach or drop one instantly.
• Arrays. A column can be an array of any type, which gives you a flexible model for things like per-entity metric lists.
• Nested columns. Parallel arrays that behave like a table inside a row, such as the orders of a customer.
• TTL tables. Rows are removed (or moved) automatically by a rule on row data, such as “delete after six months”.
• Materialized views. They aggregate on insert. The slide quoted a benchmark of the AggregatingMergeTree technique that cut the response time of a last-point query from 4.5 s to 20 ms, more than 200x.
• Live views. A view you subscribe to: when the condition is met, you get a notification.

CREATE TABLE enum_table (day Enum8('SUNDAY' = 0, 'MONDAY' = 1, 'TUESDAY' = 2)) ENGINE = Memory;
INSERT INTO enum_table VALUES ('MONDAY'), ('SUNDAY');
SELECT day, toInt8(day) AS n FROM enum_table ORDER BY n;

SUNDAY	0
MONDAY	1

CREATE TABLE ev (ts DateTime, v UInt32)
ENGINE = MergeTree
ORDER BY ts
TTL ts + INTERVAL 6 MONTH;

Since 2019. Mixed news, and the most interesting one for this list:

• Enums, partitioning, arrays, nested, TTL and materialized views are all still here. For the last two, see materialized views basics, pre-aggregation and lightweight delete and TTL. Materialized views also gained a time-driven sibling, the refreshable materialized view.
• Live views are gone. CREATE LIVE VIEW is a syntax error on 26.4 and LiveView is not in system.table_engines. The setting allow_experimental_live_view exists but defaults to 0 and the statement no longer parses. A related experimental engine, WindowView, is listed but is behind allow_experimental_window_view (also 0). I will come back to this in the closing section, because live views are what the second talk in this series used and had to give up.
• New data types arrived that the slide could not have shown: JSON (a real column type with typed subcolumns), Variant, Dynamic, QBit and Time64.

SELECT '{"a":{"b":[1,2]}}'::JSON AS j, j.a.b;

{"a":{"b":[1,2]}}	[1,2]

See working with JSON.

7. Better SQL, plus

This was the longest section of the talk and the one people remembered. ClickHouse SQL is SQL with a lot of useful additions. A few I showed:

Conditional aggregates. countIf and sumIf replace a CASE inside every aggregate.

SELECT countIf(number % 3 = 0) AS div3, sumIf(number, number % 2 = 0) AS even_sum, count() AS total
FROM numbers(10);

4	20	10

Arrays. arrayJoin turns an array into rows and groupArray does the opposite. Beyond them: indexOf, arrayDistinct, arrayReverse, arrayConcat, has, hasAll and many more. See arrays and ARRAY JOIN.

Lambdas. Higher-order functions take an inline lambda: arrayMap, arraySum, arrayCount, arrayAll, arraySort, arrayFill. The slide’s example was a Euclidean distance between two vectors, written with nothing but lambdas:

SELECT sqrt(arraySum(x -> x * x, arrayMap((a, b) -> a - b, [1., 2., 3.], [4., 6., 3.]))) AS dist;

5

Reusable expression names. An alias defined in one expression can be used in the next, in the same SELECT, which is what makes long computations readable:

SELECT number * 2 AS dbl, dbl + 1 AS plus_one, plus_one * plus_one AS sq FROM numbers(3);

0	1	1
2	3	9
4	5	25

system.numbers, formats, ORDER BY a condition. You can generate any amount of test rows from numbers(N), switch the output format per query, and sort by a boolean to put matching rows first:

SELECT number FROM numbers(6) ORDER BY number % 2 = 0 DESC, number;

0
2
4
1
3
5

Since 2019. Every snippet above ran unchanged on 26.4. What changed is the amount of it: system.functions has about 1,800 entries on 26.4, including the L2Distance and cosineDistance family that makes the lambda example above a one-liner (L2Distance([1., 2., 3.], [4., 6., 3.]) also returns 5), arrayFold, and the SQL-level building blocks for vector search (see vector similarity search). Window functions were a gap in 2019 and are now covered (basics, frames).

The new analyzer, on by default (enable_analyzer = 1 on 26.4), changed how names are resolved under the hood, and I found it the hard way: see the second talk. Migration notes are in moving to the new analyzer.

8. REST capabilities

ClickHouse is a REST server: the HTTP interface on port 8123, plus FORMAT JSON, is the whole story, and it makes ClickHouse trivially usable from any language and any tool that speaks HTTP. And it is a REST client: SELECT ... FROM url(...) turns an endpoint into a local table, as the snippet under reason 5 shows.

Since 2019. Nothing here has been removed; it is simply more complete. See the HTTP interface.

9. The command line

The slide opened with a cartoon of what every programmer does to time a query: take the time, run the SQL, calculate the elapsed time, run it again for the row count, go to the system tables for the data size, realise the SQL was cached so the numbers are wrong, start over, and finally give up and run it in production anyway. The reply was clickhouse-client, which reports elapsed time, rows read, rows per second and MB per second after each query. I called that “a lot of useful information”, and I meant it: it removes a whole category of guessing.

Since 2019. The surprise of the last seven years is clickhouse local: the same engine, no server, reading files in place. It appears on my honourable mentions list, and everything in this article was tested with it. The tutorial on running SQL scripts in batch shows more.

10. Great ecosystem

Open source, free of charge, a good community, and constantly improving. In 2019 that last bit was a statement of faith.

Since 2019. It is now a statement of fact, and the project is maintained by a company that has made it its business. The change is visible in the pace of releases: a new version every month, each with a long changelog.

11. Close the gap effectively

The bonus reason. Data is growing faster than computing power, so we have to close the gap efficiently. The big data ecosystem of the time answered with many tools, each excellent at one thing. My argument was the Unix one in reverse: it is easier to learn and operate one tool that does most things well enough than a stack of tools each designed to do one thing well.

Since 2019. I still believe it, and the interesting evidence is that the “one tool” has grown a vector index, a JSON type, full-text search settings (enable_full_text_index is set to 1 on 26.4), lakehouse table formats and an object-store storage layer. Whether that is a feature or a hazard is the real debate.

Honourable mentions

The talk ended with a list of things I did not have time for: machine learning functions, data sampling, rich text functions (search, replace, regular expressions, n-grams), UUID, domain-name and other helper functions, external dictionaries, geo functions, clickhouse-local, and distributed DDL. All are still there. On 26.4 the system function table lists stochasticLinearRegression, catboostEvaluate, naiveBayesClassifier, ngramDistance, multiSearchAny and geoDistance. For dictionaries, see dictionaries in ClickHouse.

I closed with a question, “how come ClickHouse was born in Yandex and became so good?”, and my own answer: because it was born from a need, at Yandex Metrica. A database built by the people who run the workload gets the defaults right.

Seven years later

Looking at the deck now, three things stand out.

What held up. The core argument is unchanged: column storage, sorted parts, cheap inserts, background merges, and a SQL dialect that treats arrays and lambdas as normal. Of the eleven reasons, I would change the words of none.

What was wrong or incomplete. I sold “inserts are instant” without the batching caveat; the cost of one small insert is a part, and a part is a long-term bill. I also listed live views as a feature. They were experimental then, and today the engine is gone. That is a reminder that “experimental” in the changelog means what it says, and that a talk is a snapshot: the SQL games talk, three years later, was written on top of exactly that feature and had to be rewritten when it was removed (ClickHouse SQL games).

What I could not have predicted. In 2019 the closing question was how a database got so good. In 2026 the more interesting question is how it got so wide. A table engine for Iceberg, a vector index, a native JSON type, a cloud service with separated storage and compute: none of them was on the slide, and all of them are reasons to use ClickHouse that an updated version of this talk would include. The reasons I gave were about a fast analytical database. The reasons now are about a platform, and the honest follow-up talk is “ten good reasons to be careful”.