MORE secrets of ClickHouse® Query Performance

May 27, 2020
with Robert Hodges

In this webinar, Altinity CEO Robert Hodges digs into how to make queries on single-node ClickHouse servers faster, with CTO Alexander Zaitsev answering questions throughout and joining live at the end. The talk centers on the MergeTree data structure, the workhorse table engine in ClickHouse, and explains exactly how it stores data on disk: a table is split into parts, each part holds a sparse primary index plus per-column mark and data files, and the marks point into compressed blocks so the engine can read only the granules it needs.

The session is organized around three levers. First, query tuning: ClickHouse has no cost-based optimizer and no EXPLAIN PLAN, but its query log and system tables expose exactly how many parts and marks a query reads, so you can add threads with max_threads, reduce reads with filters and PREWHERE, and restructure joins to push aggregation down and cut I/O. Second, data layout, which yields the largest gains: choosing the right partitioning and keeping parts in the hundreds, designing a compact primary key and tuning index granularity, adding skip indexes, applying encodings such as LowCardinality, Delta, and DoubleDelta, and offloading work into materialized views.

The third lever, new since the previous version of this talk, is storage. ClickHouse storage policies let you organize multiple disks into volumes, build JBOD configurations for more bandwidth, and set up tiered storage that uses TTL rules to move older data from fast NVMe to cheaper high-density disks, while the OS page cache and the min_bytes_to_use_direct_io setting govern how much reading happens from memory. Robert closes with takeaways and resources, and Alexander then fields an extended Q&A on update performance, parallel and atomic materialized views, deduplication, background merges and mutations, distributed aggregation memory settings, the cluster macro, external GROUP BY, and polymorphic parts.

Here are the slides:

Key Moments (Timestamps)

Key moments generated with AI assistance.

• 0:04 – Introduction and housekeeping
• 1:43 – Speaker introductions, Altinity, and goals
• 5:13 – A quick tour of ClickHouse
• 7:45 – The MergeTree table engine and its layout
• 12:46 – Why query tuning is different, and the query log
• 20:08 – Speeding up queries: threads, reads, PREWHERE
• 29:20 – Optimizing data layout: parts and primary keys
• 34:19 – Skip indexes cut down on I/O
• 37:58 – Encodings and materialized views
• 43:09 – Storage policies, tiered storage, and the page cache
• 50:15 – Takeaways and resources
• 53:53 – Q&A with Alexander Zaitsev

Webinar Transcript

0:04 — Introduction and Housekeeping

Robert Hodges: Hi everybody, this is Robert Hodges and I’ll be your host today at our Altinity webinar on more secrets of ClickHouse query performance. Before we dive into the content, I’d first like to welcome you all. This is a very popular topic and we hope to see many of you here today. There are already something like 50 or 60 people on the call.

To make things more convenient for you, we are recording this webinar, and we will also publish the slides afterwards so you don’t have to frantically take notes. You’ll get a link that will direct you to both within about a day. Two other points: this is a complex topic, and we know people are here with questions, so there is a Q&A where you can enter questions and we will answer them as we go along, and there’ll be time at the end to dig into them as well. If you don’t get a question answered, typically we’ll answer them in the background as we go, and sometimes I’ll do them in the talk as well. Our idea is to answer as many as possible. Finally, at the end we will run a poll, as we do after all our webinars, three simple questions, just a little information that helps us tune these webinars to make sure they’re hitting the mark and gives us ideas for future ones. With that, I’d like to dive in.

1:43 — Speaker Introductions, Altinity, and Goals

Robert Hodges: My name is Robert Hodges, Altinity’s CEO. I’ve been working on databases since 1983, and ClickHouse is database number 20. I like data. I also have with me today, I don’t have a picture, but Alexander Zaitsev has joined us. He is our CTO. He’ll be in the background answering questions and will come on live at the end to answer questions as well, so you’ll probably get learned answers to your questions in the question box as the webinar goes along.

Just a little about Altinity: we’re the leading software and services provider for ClickHouse. Our goal is to make enterprises successful at building analytic applications using ClickHouse. We have a large number of customers ranging from tiny startups to some enormous companies, in fact some of the Fortune 10, and, relevant to this conversation, we’re a major committer and community sponsor in the US and Western Europe. We do a lot of work with the community, and we also do quite a bit of development. In fact, in this webinar we will talk about some features that Alexander’s team developed for ClickHouse, which are now available in community releases.

So let’s dive into the talk and talk a little about goals. We’re here to talk about performance on single-node ClickHouse servers, so we’ll be focusing on the MergeTree data structure, the workhorse data structure for tables in ClickHouse. We’ll delve into how that structure is actually organized, and we’re going to look at three general topics. First, improving your query response time by tuning the SQL. Second, getting very large gains by changing data layout. Third, which is new, increasing performance by changing the allocation of storage, the arrangement of disks, and beginning to think a little about how the OS page cache is used as well. This is a new section; we previously did this talk, but storage policies have been added to ClickHouse since we first did it last September, and we’re happy to talk about it today.

Things we won’t try to do: boost performance of sharded and replicated clusters, in other words distributed ClickHouse clusters. We have a separate webinar that delves into that complex topic in its own right, and it’s recorded and available in the references. A final thing we won’t do is teach very advanced ClickHouse performance management. For example, you can go into the code and look at what ClickHouse is doing deep in the operating system to get additional insights, but that is something other talks cover and is out of scope here. That said, I think you’ll find this sufficiently technical that even experts will learn lots of useful stuff.

5:13 — A Quick Tour of ClickHouse

Robert Hodges: Let me dive in and talk a little about ClickHouse just as a level set, then particularly focus on how MergeTree is organized. If you are new to ClickHouse, first of all, welcome, we’re glad to have you in the community. Here’s an overview of what ClickHouse is and why people like it. The way I like to explain it, and some of you have probably heard this before, is: imagine if MySQL, a popular open source relational database, got married to Vertica, a pioneering column store, and they had a baby. The result would be ClickHouse, with good things from both parents.

From the MySQL side, it understands SQL, so data are organized into tables, and it’s highly portable, everything from bare metal to the cloud and containers. We also get the open source nature; ClickHouse is licensed with Apache 2.0, which has very few restrictions on use, and it’s relatively easy to manage. From the Vertica side, the similarities are a shared-nothing architecture, so compute and storage are bound together on nodes that are built into a cluster, and we store data in columns with compression and codecs, which we’ll focus on in this talk. We do parallel and vectorized execution, parallel across nodes and vectorized on single nodes, to milk the maximum performance out of the CPUs. All hardware threads will be used if possible, and there’s a fair amount of SIMD optimization, so we’re not only using all threads but also leveraging the instruction set on the CPU.

Finally, from the data warehouse side, ClickHouse scales to many petabytes. It runs on machines as simple as the six-year-old Dell laptop I’m running this webinar on, all the way up to clusters with hundreds of nodes and tens of petabytes of data. The thing that really attracts people more than anything else is that it’s fast. The columnar storage, the fact that everything is a sequential read or sequential write, and a wealth of performance optimizations and features are what really grab people’s attention, and that’s what we’re going to talk about today: how to make it even faster.

7:45 — The MergeTree Table Engine and Its Layout

Robert Hodges: The first step is to understand the basic table type here, which is called MergeTree. The name is similar to log-structured merge tree, a famous data structure introduced by O’Neil about 30 years ago. The basic idea with a MergeTree is that it will accept data very quickly and put it into storage, and then in the background it will systematically merge it into a form that makes queries more efficient. So when you insert your data, it’s immediately queryable, but not necessarily as efficient as it could be. Over time it becomes merged into a much more efficient structure that will minimize, for example, the number of files that have to be open, and lengthen the runs of compression.

Here’s the definition of a MergeTree. It’s pretty simple. For most people on this call, you’ve seen these before: you do a CREATE TABLE and give a table engine type of MergeTree, or one of the variants, of which there are something like 12, depending on how you count. Then there are two very important properties in the definition. One is PARTITION BY, which tells us how to break up the table into parts, and we’ll look at what parts are in just a second. The second is ORDER BY, which tells you the sort order. There are also settings you can give in another sub-clause, which we don’t show here but will show examples of later; they allow you to tune the performance of the table for specific use cases.

Let’s look at what’s going on under the covers when you lay out a MergeTree. At a high level, the table is divided up into parts, which are independently stored, indexed, and sorted pieces of the table. If you look into a part, you’ll see an index called primary.idx and a bunch of columns. The basic idea is that the index is sparse, so it’s not like a B-tree index, it’s a very simple data structure that, by default, has an entry on average for every 8,000 or so rows. The columns are stored as arrays of data that are compressed and sorted using the ORDER BY given in the table definition.

Now let’s dig in deeper. What we have here is the actual structure you’ll see if you go into the directory storing a MergeTree. First there’s the primary.idx file, your sparse index, and then for every column you’ll see a file called .mrk and a file called .bin. The primary.idx sparse index contains an entry by default for every 8,192 rows; the distance between two entries is called a granule, so that’s about 8,000 rows. The .mrk files contain what we call marks, which are basically pointers that say, given granule number three, where do I start reading in the .bin file. The .bin file is a bunch of compressed blocks. So if you have the mark, you go in there, it gives you a pointer and offset into the .bin file, and you know where to start reading. Once we find, for example, the row highlighted in the primary.idx, if we know we want to read from the airline table, we can find the place to begin reading the data.

One of the cool things about ClickHouse is that the file structure is very transparent. This is the actual Linux directory name for this particular table; I can go in and see the part, which is named in a way that gives us a sense of the data it contains, so you can go in and see all this for yourself if you’re curious. If you have questions as you’re going along, please feel free to queue them up in the question-and-answer box and we’ll answer them in the background.

12:46 — Why Query Tuning Is Different, and the Query Log

Robert Hodges: Let’s turn to basic query tuning. Before we look at some queries, I’d like to give you an overview. If you’re coming here from Oracle or another proprietary data warehouse, you’ll find that things are pretty different, and there’s some bad news. The first bit is no query optimizer, by which I mean something that can evaluate the distribution of data, figure out the cost of queries, and devise a plan that gets the data in an efficient way. Not only is there no query optimizer, there’s no EXPLAIN PLAN command, so if you’re used to using EXPLAIN PLAN to get a sense of what the optimizer might do, you won’t find that in ClickHouse either. A final, really important thing in design is that often getting good performance requires you to move data around. If you’ve been in the database business a long time, you know the secret to performance is the distribution of your data in storage. That’s the fundamental way we get queries to run fast, by distributing data in a way well-suited for the questions we’re answering. What that means in ClickHouse is that by the time you find out the right distribution, you may have accumulated a petabyte of data, so you may have to do some work to move things around to get the best performance.

On the other hand, there’s some really great news. The fact that there’s no query optimizer is actually good, because ClickHouse is very transparent. What you see is what you get. The system log, which we’re going to look at in a second, is awesome; it gives you great information about what ClickHouse thinks it’s doing to run queries, and you can use that to figure out how to organize data, tune queries, and get better performance. ClickHouse also has the best system tables of any database I’ve ever used. They’re incredibly useful and particularly well structured for answering questions about performance drivers. What makes ClickHouse go fast is really simple at the bottom level: fundamentally, how much I/O do you do, and how many CPUs can you apply to it. Other things, like bandwidth on your storage devices, are important too, and we’ll talk about those. Finally, ClickHouse is constantly improving. Since September, the notion of storage policies arrived and is now at production grade, and there’s a host of other improvements as well.

The ClickHouse query log is exceedingly helpful when we dive into query performance. Here are some examples of how you can invoke it. We can use clickhouse-client, we can select from the text_log, which you can enable and configure, and you can look at the log as well, the normal thing you do in databases, like looking at traces in Oracle. The log is so useful that I’d like to do a short demo, because this is something we’re going to lean on for the rest of the presentation. I’ve fired up clickhouse-client; I’m running on a single node up in Amazon. Let me pick a database to use, run a query, and there we go. It ran, it was pretty quick, but what did it actually do?

One of the really cool features of the ClickHouse client is that we can turn on logging and get the system log for that query sent back to us along with our results. This is incredibly useful for debugging because you don’t have to figure out where the server is or have access to it. So we set this property, send_logs_level, to trace; that gives us low-level information about what ClickHouse is up to. Let’s run that query again. We got the results right here in the middle of the screen, but we also got a huge amount of useful information: the query ClickHouse saw, how many parts it read, how many rows it read, the structure of the query plan it arrived at, and a bunch of really useful detail for the individual threads used to collect I/O, so we can see how much they actually read and how quickly they did it. This enables us to learn a lot about what’s happening under the covers, and use that to tune the queries. I’ll turn that logging off now, which sets me up for a future demo. It’s something you can turn on and off at will.

20:08 — Speeding Up Queries: Threads, Reads, PREWHERE

Robert Hodges: So there’s the log. The first thing to look at is the hierarchy you get, which is basically your query pipeline. This is an example of a query on one of our favorite pet data sets, the airline on-time data. What results in the log is called the query pipeline. These pipelines are sort of hard to interpret; they’re not exactly the same as a query plan in the sense you’re used to in an EXPLAIN PLAN. What they’re really showing you is the hierarchy of the C++ classes implementing the query, but you can still get a lot of useful information out of it. For example, down at the bottom you can see this “Expression × 8,” which means there were eight parallel threads reading the table. That’s already useful, because you can tell your level of parallelization.

One of the first ways to speed up query execution is just to add more CPU to it. There’s a very useful property called max_threads that sets the number of hardware threads able to process the query. If you look at /proc/cpuinfo as a proxy for how many CPUs you have, that’s the number of threads ClickHouse has; by default it will give you half of them for the query, but you can change it yourself, either by setting it on the command line, as I did here, or in user profiles, so particular users can get more or fewer threads depending on what they’re doing. Here’s an example of the effect. This graph on the right shows the effect on a particular query as we vary the threads from two to eight. Going from two to four threads we get about a 50 percent improvement, so a lot of parallelization, and then beyond that it asymptotically falls off. What you’re seeing are some Amdahl effects: in this case we’re doing a GROUP BY and a sort, so we have to do some aggregation and sorting at the end, which is single-threaded for the most part.

The number one way to speed up a query is not so much CPU but to reduce the amount of data you read. Here’s an example where we run a query reading a large amount of data, then a similar query with a filter condition, and compare the amount of data read. Just adding a WHERE clause is something that would occur to just about anybody, but the interesting thing is that the ClickHouse log gives you very detailed information about the effect. In the query on the left we selected 355 parts, those big chunks of the table, and within those parts we read 221,393 marks, the chunks inside the arrays of data. This table has 355 parts, so we read all of them, and we read all those marks for the columns we were looking at. When we put the filter condition in, ClickHouse selected a much smaller number of marks, gave a much smaller number of parts, and read from a much smaller number of marks as a result. So ClickHouse clearly shows, when you think you’re changing something to help with I/O, you can usually tell pretty quickly; you don’t just depend on speed, you can look in the query log and ClickHouse will tell you exactly how many marks it read.

This also illustrates one of the optimizations ClickHouse is good at: in filter conditions, wherever possible it will avoid even looking into parts. If it can match your filter to the partition key, it won’t even open partitions it doesn’t need to look at. At the base level, for simple queries, execution will scale directly with I/O. Here’s an example where I change the query a little to add more filter conditions, and we can see the query response in this graph, in seconds, and the marks it reads. For a query like this, which doesn’t have complex aggregation, it’s one-for-one with the number of marks read; those lines are basically the same. So between CPU and I/O, these are two really great ways to quickly speed up your queries without changing the underlying data.

There are other things you can do. One interesting feature ClickHouse has is PREWHERE. A WHERE clause in SQL is a filter, but ClickHouse has a special syntax called PREWHERE. What it does is, even if a column is not in the primary.idx, it will scan that column and use it as an ad hoc index. It runs down that column, keeps track of all the marks it read, and then only reads those marks in when it goes to the other columns. So it’s basically an index without an index, and the feature dates to the days before ClickHouse had skip indexes, which we’ll talk about in a minute. You can see the effectiveness: PREWHERE here on the left took 0.6 seconds versus 2.8, and you can see the difference in the amount of data processed. One interesting thing about PREWHERE is that it kicks in automatically. It used to be that you would set the property optimize_move_to_prewhere, but now the default is one, so it will do it automatically, and you’ll see in the log that even with a normal WHERE condition, ClickHouse automatically moves it to PREWHERE. If you don’t like this, you can use that setting to shut it off.

29:20 — Optimizing Data Layout: Parts and Primary Keys

Robert Hodges: The final thing for queries is restructuring joins. Obviously that’s something you can do in any database, but it’s particularly effective in ClickHouse. One of the most effective ways to restructure is, sorry, one second, had to sneeze. We can restructure joins to reduce the scanning. When you think about restructuring joins in a database with a full query optimizer, that often means reordering them or playing with different join types. In ClickHouse, a particularly powerful form of restructuring is to reduce the amount of base data you read and then drag around for the rest of the query. Here’s an example: we do a join on the on-time data against a table that turns airport codes into a nice name. The query above does the join, which gets pushed down so the data is all read at the lowest, parallelized level. We can improve this enormously, by a factor of almost four, by taking that join and pulling it out, so we select from a subquery which then gets joined on airports. What’s really happening is that the airports are being joined against the aggregated data.

You can see this in the log; this is just another example of how useful the log is. If we do the join during the MergeTree scan, we end up scanning about 10 gigs of data; if we do it after the scan, the initial scan only covered about 2.6 gigs. So this is a very powerful way of reducing I/O, which is critical to performance. There are lots more ways to find out about queries. One I’ll point out is the query_log, the first of a number of really useful system tables we’ll mention today. You can turn it on and have queries automatically logged, which gives you information you can query to get more insight into what your queries are doing, particularly if you have a lot of them.

The next thing is optimizing data layout. Tuning queries is something you do if you can’t change the data, and it’s your first step, particularly if you don’t have access to the database itself. But if you do have access and the freedom to restructure your data, you can get enormous performance benefits, often many orders of magnitude. We’ll talk about a range of techniques, from ensuring an optimal number of parts to using skip indexes and materialized views. One key question is, does PARTITION BY affect performance? Here’s an example with this on-time table. There’s a column we use called flight date; we can partition by turning it into a month, so all 30 days are in the part, or we could partition by the day. Does this make a practical difference? If you’ve watched our webinars before, we rarely ask questions like that unless the answer is yes and the difference is big. I compared the two, loading the data into a table partitioned by month and one partitioned by day, and the difference is a full order of magnitude on a SELECT count. In an older version, where we had to look at the data, it would go from 0.34 to 3.2, because we were scanning a bunch of parts. The performance has changed in recent versions, but the point is that reading parts is expensive. When we did the insert, in this case we got a very large number of parts, about 14,000 in the by-day table, and then I ran an OPTIMIZE TABLE command to merge the parts down to about 10,000, which reduced the query response time significantly. Generally speaking, it’s good to keep parts in the hundreds; if you’re in the tens of thousands, you probably have a performance problem.

Another thing to think about is the primary key index structure. The ORDER BY determines the key by default, though there is also a PRIMARY KEY clause you can use. Think about the size of that key, because the index structure needs to fit in memory to work efficiently. For example, if you had very large strings in that key, sometimes doing a hash on the string is a more efficient way to build the key structure, so you don’t have the entire string. Another thing is the setting called index_granularity, the gap between entries. If that gap is big, it makes the index smaller, but it turns out that in some cases, if you make the granularity small, it can make your skip indexes much more efficient, and it also means that if you have a lot of queries that only want a single row, making this number smaller can help. So it’s a trade-off you often need to determine based on your data. The ORDER BY is also key to performance in another way: it determines your sort, so if you choose it well it will make your non-key values less random, which is good because it means better compression, better index selectivity since you’ll have larger runs with the same value, and better PREWHERE performance for the same reason. Thinking about that ORDER BY is really important, and is sometimes one of the biggest ways to improve performance, though it’s sometimes not totally obvious until you have a lot of data.

34:19 — Skip Indexes Cut Down on I/O

Robert Hodges: Skip indexes are a really important feature, somewhat new to ClickHouse. There’s a setting that is now obsolete, which I think may be scheduled for removal at some point, but in older versions you had to set this value to be able to add indexes. They work as secondary indexes; like in other databases, you alter the table and add an index, and there are a bunch of different types like ngram and set, which we’ll talk about. To get the indexes applied in older releases, you needed to run an OPTIMIZE TABLE if you already had data; adding the index did nothing on its own. I believe in current releases you can just do an ALTER TABLE update, which tricks ClickHouse into looking down the parts and adding the index without having to fully rewrite all the parts.

The interesting thing is how they affect performance. Here’s an example where we look at the log and can see PREWHERE helping to move granules. We see PREWHERE being applied, and we can also see the index has helped us drop 55 granules in one case and 52 in another. So the index is helping us skip things, not read things we don’t want to look at, and the effectiveness depends on how your data is distributed. The idea is that we’re knocking things out; if the index is effective at doing that, we’ll have less I/O and be a lot faster. Here’s a simple example, selecting values from the airline on-time table. We have a set index where we look at a carrier name that has relatively few rows, so the query response is really fast. We use another query with a carrier name where this index value, WN, is actually Southwest, which has about a seventh of the rows in this data set, and the effect is much lower because we don’t reduce the number of rows we have to read by very much; it only knocks off about eight million. So the specificity of the filter is really helpful, and the rarer the values are, the better these indexes work.

Just a quick review of the index types, if you’re not familiar with them. The minmax index checks the high and low range of data. Set indexes look for the presence of unique values in the granule, and you can control the granularity of the index so it scans a larger or smaller group of marks. We have ngram and token bloom filters and the bloom_filter; these all work similarly. For example, the token bloom filter helps you search on tags and avoid looking into marks that don’t contain the thing you’re looking for.

37:58 — Encodings and Materialized Views

Robert Hodges: Another thing you can do at the level of the table is encodings. ClickHouse by default compresses using LZ4; ZSTD is also supported. We won’t delve into those, but ZSTD will give you better compression at the cost of somewhat higher compression processing time. One really useful feature is encodings, which are basically type-aware transformations on data. We’ll look at three of them on a test table; the details aren’t important. The first is LowCardinality, which turns strings into a dictionary encoding. It works great if you have string values in the thousands, for example airport locations; there are only a few thousand worldwide, so that’s a great target for LowCardinality. Another is Delta, which turns a series of numbers into the differences between each number, and then DoubleDelta, which instead of differences is the difference in the slope of the change of those numbers.

The effects can be enormous. If we look at LowCardinality encoding in that simple test example with some generated data, I got an 89 percent reduction in the storage size for the strings over the initial size, and about a 50 percent improvement over just letting ClickHouse compress it directly. Delta encoding can have an enormous impact, up to a 99.5 percent reduction in this example, and DoubleDelta, for things that are slowly increasing or declining, can be huge; in this case it reduced the ingested data by 99.9 percent once compressed, so it basically goes down to nothing. Moreover, it’s not just storage you’re saving, it’s queries, because you’re reading less. Going back to how ClickHouse works, if you read fewer marks you go faster, and you’ll see that in your queries; in this simple example the query went four times faster. I’m proud to say that we actually contributed a couple of these encodings, DoubleDelta and Gorilla come from Altinity, and they’re increasingly widely used. This is definitely an area where there’s a lot of interest because of the benefit it offers.

One important thing as you’re beginning to play around with encodings is to use the system.columns table. This is another incredibly useful table; the query is shown here, and it will show you the level of compression you’re getting. This is the second place, after the query log, that you go: you go to system.columns and can directly see which encodings were applied, how fast they were, and how much compression you got, and as you play around with it you can see the differences very easily.

A final topic for restructuring data is that sometimes there’s a limit to what you can do within a table and you just have to change it to a different form. An obvious example is aggregations. Here we have an example looking for canceled and delayed flights, aggregating them by the date of flight and carrier. This reduces the data and normally puts it in a separate table, and in this case it reduced the response time of this query by a factor of about a hundred. Materialized views can often reduce query time by many orders of magnitude. We won’t go further into that because we have a whole webinar on it, but it’s a really great place to look as you’re thinking about boosting performance. After you’ve done optimizing the table, restructuring the data in another table is a great way to boost it. There are lots more things to think about: smaller data types, ZSTD, the decompression dictionary, sampling, and of course, as your data gets larger than a single node, you begin to shard and replicate out to large data sets, which gives you more I/O ops and the ability to ask questions across a much larger range of data.

43:09 — Storage Policies, Tiered Storage, and the Page Cache

Robert Hodges: The final thing I want to look at, and this is new in this webinar, is to think about storage and memory at the operating system level. We always thought about this, but there’s a really interesting new feature and concept you may not be aware of, called storage policies. If you’ve been using ClickHouse for a while, you know the basic way it works is that there’s a mount somewhere that ClickHouse refers to, and all the data lives there, and as we read and write things they go through the OS page cache. Generally, if you don’t do anything, the pages are read in, in this case from a hard disk, and cached in the page cache. That’s the way things were.

As of the last few months, storage policies have arrived. They’re now production-ready and being used by many people, and what they allow you to do is organize multiple disks. We call this a “disk,” but it’s really just a storage mount point; it doesn’t need to be a disk, it can be SSD or NVMe SSD, it’s just storage. We can not only have a bunch of them, we can organize them into volumes. Here we have an example of a volume containing four disks, which we call a JBOD, just a bunch of disks. What it’s doing is increasing our bandwidth: instead of scanning data across one disk and being dependent on the speed of the controller or our network access to that storage, we can now hopefully multiply it by four, and the effect can be pretty substantial.

First I should show you how you do this. Of course this is ClickHouse, so storage policies are defined in a configuration file; we won’t go into that, it’s easy to look up and we’ve talked about it in blog articles. All you do is, when you define the table, you assign it a storage policy. If you say nothing, it goes into /var/lib/clickhouse as it usually does; otherwise it looks in the storage policy. In this case I’ve given it an EBS JBOD policy with four disks, which points it to a volume with four disks, and ClickHouse will spread the data across those disks. The effect on the query can be quite substantial. Here’s a simple example: if you use Amazon, you’re probably aware that EBS has limited bandwidth per volume. As we change the number of disks and run the same query, the first case takes two seconds, with two disks it drops to 0.6, and then drops to 0.443. You might ask, that’s not linear, why? The answer is I don’t quite know, because this is Amazon and there are additional caching layers going on inside EBS, so the numbers jump around, but in general you can get really good improvements in performance. If you’re using EBS as opposed to local storage, this is a way to increase your EBS bandwidth.

One other interesting thing is the effect of caching inside the OS buffer cache. There’s a useful property, min_bytes_to_use_direct_io. When I set it to one, that’s saying anything over one byte goes straight to the storage device, skipping the buffer cache, but I can use the buffer cache by setting it to zero, which is the default. When I run these queries, you see it doesn’t matter how many disks we have; in each case the queries came back in exactly the same time, and they were faster because we were reading straight out of memory. So you can now think about how you arrange storage, and how you balance that against memory; these can have enormous effects on response time. Once again, there’s a system table that helps us track this; if you ran this query on the four-disk example, it would show you that the parts are evenly distributed across all devices. The difference for people who have used this query before is that there’s now a column called disk_name that shows you which disk your parts are living on.

Finally, this is just one example of how we can use storage by adding more volumes. It’s also possible now to do what’s called tiered storage, where, if you have time series data, most of your queries go on your most recent data; your last 24 hours is often where 95 percent of the queries go. You can have a volume that is NVMe SSD and another volume that is a bunch of HDDs, slow but high density and cheaper, and then use TTLs to move the data between them. This is something we’ve been working on for about the last nine months; it’s also production-ready and being used by a number of sites. It’s an enormous performance improvement that gives you very high-speed queries off your most recent data but allows you to store enormous volumes by automatically sliding data over to cheaper storage as time goes on. We have blog articles on it.

At this point, since we’re at the operating system level, it’s always good, when thinking about optimizations, to use the boatload of great OS utilities like iostat. When running these, I was of course running iostat, just checking to make sure I was writing on all spindles and didn’t make a mistake, to understand where the reads and writes were going. Alexey Milovidov has a wonderful talk on this, which is also in the resources; definitely check it out, and it’ll guide you to low-level performance optimization.

50:15 — Takeaways and Resources

Robert Hodges: With that, we’re pretty much done. I see we have a boatload of questions that Alexander has been really active on. I’m just going to wrap up very quickly and then we’ll take questions. Takeaways: ClickHouse performance drivers are CPU and I/O. If you can apply more CPU and read less, you’re going to do better. Generally, if you have complex queries, at some point you get Amdahl effects, but these are the two drivers. With the newer volume management possible with storage policies, we can add in changing your I/O bandwidth, which lets you get data on and off more quickly. The system query log is your friend; it’s really great information, and the fact you can pull it back to clients, even API-driven clients, is a huge help when you’re trying to diagnose what’s going on.

You can tune queries and improve response substantially, as we saw, on the order of an order of magnitude, but for really big changes, multiple orders of magnitude, restructuring tables, adding indexes, adding views, and changing your encoding is the way to go. That’s the biggest single area where you can get performance gains, and in fact when we work with customers, that’s a lot of where we spend time, because you can do multiple iterations and get huge improvements. In recent versions, as of 19.17, the latest Altinity Stable, you can now begin to optimize storage using storage policies, and these will continue, the TTL movement and other features, to get even better. Version 20.1 is approaching production readiness; we’ll probably declare victory there in a little while. So you have a panoply of features you can use to optimize storage.

For further resources, check out our blog, that’s the multi-volume storage, one of the few places it’s really documented in detail. We do a lot on performance, but materialized views and cluster performance are two other webinars you can look at. ClickHouse documentation is great, and Alexey, who’s the lead committer, deserves special attention; his talks are wonderful. You can get help on the ClickHouse Telegram and Slack channels, and you can get help from us, because that’s our job: we offer services and software to make people successful. If you want to dig deep into performance, that’s what we do for a living and we’re really good at it. We offer free one-hour consultations to look at what you’re doing and give you ideas, so feel free to take us up on that. One final thing is, we are hiring, so if you’re a database internals person or love data services, send us your resume. With that, I’m going to kick off the poll. I hope you’ll take 15 seconds to tick the boxes, and Alexander, if you want to come on audio, we can take some of the final questions, which I know you’ve been answering in the background. Thank you very much.

53:53 — Q&A with Alexander Zaitsev

Alexander Zaitsev: Hi everybody, thank you Robert so much for an excellent webinar. There were some very good questions in the background that I tried to answer, and a few unanswered. Let me start with an unanswered one so we can discuss it and I can illustrate some in more detail.

Regarding performance of updates, the performance of ALTER UPDATEs depends on two things. It depends on your WHERE condition, because ClickHouse has to evaluate the condition first, and using it, it selects the parts of the data that need to be updated. Second, it depends on the column size. If you update a column which is huge and has a lot of high-cardinality values, it may take longer compared to a column that has just a handful of values, because it’s faster to read and write back. For ALTER TABLE UPDATE, ClickHouse only touches the columns that need to be updated, plus it executes the filter condition. If you understand those concepts, you can guess what you can do for performance. Updates are asynchronous, so you just throw them out and wait a few queries for them to finish in the background, and you can always check what’s going on.

Regarding materialized view updates, it’s interesting. Initially all materialized views were executed serially, so if you have multiple materialized views for one table, ClickHouse would execute one first, then the second, and so on, in equal order, which is kind of strange. But later we added parallel processing, so now materialized views are executed in parallel, given there is enough CPU power on the machine, and written as soon as the data is available. So they execute in parallel, but the completion may not be at the same amount of time. One of the problems in ClickHouse is that materialized view execution is not atomic, so if there is any failure with one of the views, then some views might be updated and others might not. It doesn’t happen too often, only if you have some problem with the data write or something like that, but if it does, you may have inconsistent data. There are ways we deal with that, which I think we discussed in the materialized views webinar, but in general this is a big limitation in ClickHouse and we hope to fix it in the next few months, so the atomic update on materialized views is coming.

The same is true about duplicates. ClickHouse has a number of ways to deal with duplicates. You can have a replicated table that can automatically deduplicate the data, given you insert exactly the same data. Another way is ReplacingMergeTree for avoiding duplicates; ReplacingMergeTree allows you to insert multiple versions of the data and then you can use the FINAL modifier in queries, or you can OPTIMIZE the table. For example, if you inserted a bunch of data by chance and you’re sure there are duplicates, you can optimize the table. It’s not a very fast operation; it’s going to be improved soon, but right now OPTIMIZE is a single thread, which is slow, so it only works for small tables. Small in ClickHouse means not enough rows to be a problem, single-digit, suppose.

There was a question from one attendee about background operations. There are not too many locks in ClickHouse, thanks to insert-only behavior, but there are certainly background operations that can slow down queries. The most obvious example is merges: if you notice a lot of merges, then query performance may degrade. Second is mutations, which also consume resources, CPU and storage. You may need to look into those and manage background merges and mutations and different options if you hit this problem. But ClickHouse constantly improves, so if you have some problems with flow contention right now, they may already be fixed, so if you’re sure there’s a problem, just file a ticket on GitHub and we’ll try to look at it.

Regarding distributed queries, there’s a question, and there’s a setting you should enable from the very beginning if you use distributed queries. It’s called something like distributed_aggregation_memory_efficient, and it allows ClickHouse to use as little memory as possible. Without the setting, ClickHouse would pull all the data from the nodes first and then do aggregation; with the setting, it does it in small chunks. It’s going to be enabled by default soon, but for whatever reason it’s still disabled, so you need to turn it on explicitly.

The next question, regarding the cluster macro: the cluster macro is a macro defined in the macros.xml file, and if you use the Altinity operator, it automatically generates it. If you need multiple clusters, you may use any cluster name there instead of a macro, so it’s not a requirement to use the cluster macro, just a convenience. If you have multiple clusters defined, you just specify your cluster name, and that’s it; you don’t need to use a macro if you don’t need to.

Robert Hodges: So many good questions, we can keep going for a few more minutes. These are all awesome.

Alexander Zaitsev: I see a lot of repeated questions for memory settings. While I answer the question on that, Robert, you can look for the setting; it should be in the cluster performance webinar.

There’s a question regarding storing just a part of the data. If you want to store a tweet and its history, you can use a history table and a materialized view with a ReplacingMergeTree for your latest-state table. In this case you just insert your historical data once; when you have an update, you insert the record to your source table, the materialized view would propagate the update to ReplacingMergeTree, and from ReplacingMergeTree you can always get the latest row. There are some other ways to achieve the same behavior; there is a blog article on a similar topic, called something like how you can do real-time updates in ClickHouse, and you can get some ideas from it.

Then there was a question about compact parts. Actually, I’m not sure about this particular feature; probably I don’t understand the question completely. What is coming is polymorphic parts. With polymorphic parts, this is a new feature that allows you to do frequent inserts. As you know, right now every insert generates a lot of load in ClickHouse, because when you have multiple columns, every column is stored in at least two files, so if you insert one row it will generate 200 files for a 100-column table. This is very inefficient, which is why we usually recommend inserting in big chunks of data. ClickHouse can do an occasional job or store it, but sometimes you need to insert frequently in small chunks, and you know there is a buffer table, but it’s not very convenient to use and you can lose it because it’s an in-memory one. So what is coming is polymorphic parts; with polymorphic parts, ClickHouse would be able to store small inserts in a row-based format, not in a column store but in single rows, and then once it has enough data, it can convert it to the columnar format during the merge process. That will allow smaller inserts.

There was a question about max_bytes_before_external_group_by, and sometimes it seems that option is not used. Actually, there are different reasons you can get out of memory; it’s not just because you GROUP BY the data. Sometimes it’s even not possible to spill to disk. For example, if you calculate a uniq query, ClickHouse cannot spill, you need to store everything in memory, otherwise you cannot do the calculation. But for simple queries with simple aggregations and sums and groupings, it should work. What we typically recommend is to set this setting at least to half of the memory you’re allowed to use, because in general, don’t set it too high; it’s probably better to set it lower, so ClickHouse can start spilling earlier and not fill all your available memory.

Robert Hodges: Let me share my screen for the distributed_aggregation_memory_efficient setting. Can you see it? There you go. This is a great example of how the system tables are incredibly easy to use. I just went and looked for things like “distributed,” looked down, and they have the definitions. Alexander, we are well over time, so I think we’ll probably call it a day. I’d like to thank everybody for filling out the poll. I’m thinking there’s a call to do a webinar on arrays; we’ll definitely consider that, it’s been on my list for a long time. And I’m thinking we’ll do another thing based on these questions, an online office hours, which we may do at intervals, allowing you to come on and ask these kinds of questions. It’d be a great way to get people together and learn more about ClickHouse, so keep an eye out for that. You’ll see the recording and slides on YouTube. Alexander, thank you so much for answering these questions; those were very good ones.

Alexander Zaitsev: It was, we had some good questions. Good readers make a good book, and good questioners make for a good webinar.

Robert Hodges: Thank you very much everybody, hope you have a great day. Please feel free to contact us if you have additional questions; we’d be happy to do that one-hour consultation and dig deeper into your use cases. Thanks and have a wonderful day. Bye.

FAQ

What is the MergeTree engine, and how does it store data? MergeTree is the workhorse table engine in ClickHouse, named after the log-structured merge tree. It accepts inserts quickly and then merges data in the background into a more efficient form. A table is divided into independently stored, sorted parts; each part holds a sparse primary index (primary.idx) with an entry roughly every 8,192 rows (a granule), plus per-column mark files (.mrk) that point into compressed data files (.bin), so the engine reads only the granules a query needs.

How do you tune query performance without a query optimizer? ClickHouse has no cost-based optimizer and no EXPLAIN PLAN, but it is highly transparent: the query log, the text_log, and system tables show exactly how many parts and marks each query reads and how the threads performed. You can speed queries up by adding CPU with max_threads, by reducing reads through filters that let ClickHouse skip parts and marks, by using PREWHERE (which now kicks in automatically), and by restructuring joins to push aggregation down and shrink the data read.

Why does choosing the right partitioning and primary key matter so much? Reading parts is expensive, so keeping the number of parts in the hundreds rather than the tens of thousands avoids a major performance problem, and the right PARTITION BY can change query time by an order of magnitude. The primary key (usually defined by ORDER BY) should be compact enough to fit in memory, and a well-chosen ORDER BY makes non-key values less random, which improves compression, index selectivity, and PREWHERE performance. The index_granularity setting trades index size against how finely queries and skip indexes can target rows.

What are skip indexes and encodings, and when do they help? Skip indexes (minmax, set, ngram and token bloom filters, and bloom_filter) are secondary indexes that let ClickHouse skip granules guaranteed to have no matching values; they work best when the filtered values are rare. Encodings are type-aware transformations: LowCardinality turns repeated strings into a dictionary, while Delta and DoubleDelta encode numeric sequences as differences. In the examples shown, these produced reductions from roughly 89 percent up to 99.9 percent, which also speeds queries because less data is read. Use the system.columns table to measure compression as you experiment.

What are storage policies and tiered storage? Storage policies, production-ready since around the time of this webinar, let you organize multiple disks or mount points into volumes. A JBOD volume spreads data across several disks to multiply bandwidth, and tiered storage keeps recent hot data on fast NVMe SSD while using TTL rules to move older cold data to cheaper, high-density HDDs automatically. The min_bytes_to_use_direct_io setting controls whether reads go through the OS page cache, and a system table with a disk_name column shows where parts physically live.

How does ClickHouse handle updates, deduplication, and frequent small inserts? ALTER UPDATEs are asynchronous and run as background mutations whose cost depends on the WHERE condition and the size of the updated columns. Duplicates can be handled with replicated tables or with ReplacingMergeTree plus the FINAL modifier or OPTIMIZE, though OPTIMIZE is single-threaded and best for small tables. For frequent small inserts, which otherwise generate many files, polymorphic (compact) parts store small inserts in a row-based format and convert them to columnar format during merges. At the time of the talk, materialized view execution was being made parallel and atomic.

© 2026 Altinity, Inc. All rights reserved. Altinity®, Altinity.Cloud®, and Altinity Stable® are registered trademarks of Altinity, Inc. ClickHouse® is a registered trademark of ClickHouse, Inc. Altinity is not affiliated with or associated with ClickHouse, Inc. Kubernetes, MySQL, and PostgreSQL are trademarks and property of their respective owners.