ClickHouse® Performance Master Class – Tools and Techniques to Speed up any ClickHouse App

Recorded: April 23 @ 08:00 am PDT
Presenters: Mikhail Filimonov, Senior Software Engineer, and Alexander Zaitsev, Co-Founder & CTO @Altinity

This performance master class is presented by Altinity co-Founder and CTO, Alexander Zaitsev, and Altinity ClickHouse® Engineer Mikhail Filimonov. It is aimed at developers and DBAs who work with ClickHouse and want systematic tools for diagnosing and fixing performance problems at any scale.

The session begins by framing what “slow” means in different contexts: a single slow query, a cluster struggling under concurrent load, slow inserts, or Keeper-induced latency. The presenters emphasize that performance problems must be measured and compared on real production data rather than synthetic staging data.

The core toolbox covered includes the ClickHouse query log and its ProfileEvents fields for per-query resource breakdown, the clickhouse-client --print-profile-events flag for interactive profiling, queries against system.query_log grouped by normalized_query_hash to identify the costliest query patterns cluster-wide, and the comparison technique for A/B testing two query intervals or two query variants. The EXPLAIN family of statements, particularly EXPLAIN indexes and EXPLAIN ESTIMATE, is covered for understanding how ClickHouse plans to read data and how many granules and parts will be accessed.

The I/O section establishes that excessive data reading is the most common performance problem. Full table scans due to missing or wrong primary key conditions, bad ORDER BY choices, aliasing surprises, and non-monotonic functions in WHERE clauses are all discussed with examples. The PREWHERE mechanism is explained as a staged reading optimization that ClickHouse applies automatically in recent versions.

The aggregation section covers GROUP BY optimizations including pushing computations out of aggregation loops, dictionary injectivity flags, data type selection for GROUP BY keys, and the approximate aggregate functions hierarchy. The JOIN section covers when to replace joins with denormalization or dictionaries, how to restructure queries to aggregate before joining on a smaller data set, and how to push filter conditions before the join to reduce the data volume passed through the join.

Distributed query optimization covers the importance of pushing work to shards, the dangers of distributed joins, and data locality as a design principle for efficient per-shard aggregation. The session briefly introduces the new query analyzer in ClickHouse 24.3 as a significant step toward a cost-based optimizer.

The RAM and cache section explains ClickHouse’s cache hierarchy from the OS page cache through mark and index caches to the query cache, emphasizing that unused RAM is never wasted in Linux because it goes to the page cache. It covers how to monitor mark cache hit rates and how the query cache can dramatically speed up repeated dashboard queries. Finally, the concurrency section addresses thread management, query queuing, load balancing across replicas, and per-query overhead reduction techniques for high-QPS workloads.

Here are the slides:

Key Moments (Timestamps)

Key moments generated with AI assistance.

• 03:36 – Welcome and introductions
• 05:36 – What is ClickHouse? Quick overview
• 07:33 – Defining “slow”: single query, concurrency, inserts, Keeper
• 10:12 – Plan of attack for single query optimization
• 11:09 – Tools for query optimization: benchmarks, query log, EXPLAIN, trace log
• 14:36 – Benchmarking principles: real data, real load, isolate background activity
• 16:32 – Reading ProfileEvents in the ClickHouse client
• 18:28 – Comparing two query runs using profile events
• 22:11 – Finding slow queries: mapping system bottlenecks to query log fields
• 24:04 – Using normalized_query_hash to group and rank query patterns
• 26:10 – I/O as the primary bottleneck: reading too much data, reading too slowly
• 29:43 – Full scans: EXPLAIN indexes, EXPLAIN ESTIMATE, force_primary_key flag
• 33:08 – Common aliasing trap causing full scans
• 35:30 – Fixing full scans: missing WHERE conditions, bad ORDER BY choices
• 39:27 – PREWHERE optimization: staged column reading
• 42:58 – Slow disk, S3 latency, and aggressive compression trade-offs
• 44:19 – GROUP BY and aggregation optimization
• 51:39 – JOIN optimization: denormalization, dictionaries, aggregate-before-join
• 57:01 – Distributed query optimization: push to shards, data locality, distributed joins
• 59:35 – New query analyzer in ClickHouse 24.3
• 1:02:12 – RAM and caches: page cache, mark cache, index cache, query cache
• 1:08:54 – Concurrency optimization: thread limits, queuing, load balancing, query overhead

Webinar Transcript

[03:36] – Welcome and Introductions

Alexander: Hi, everybody. Today, with you, it’s me, Alexander Zaitsev. I’m co-Founder of Altinity, and I have been working with ClickHouse® since 2016. With me here is Misha Filimonov, who is our principal ClickHouse engineer at Altinity and has also worked with ClickHouse performance for many years. Performance is a very difficult topic. We’ll try to get deeper in some areas and just scratch the surface on others, but I think it’s going to be interesting for everybody.

We are database geeks. We love databases, we love ClickHouse, and we love developing high-performance applications with ClickHouse. This webinar is mostly for developers who work with ClickHouse and want to understand how to optimize performance, and maybe for DBAs as well. Our goal is to give you tools and machinery you can use later with your own applications in order to troubleshoot and improve performance.

[05:36] – What Is ClickHouse?

Alexander: A quick overview of what ClickHouse is. First of all, it’s an SQL database that talks SQL, and we’ll use a lot of SQL examples today. It runs everywhere, from a laptop to huge cloud providers, on bare metal, on any virtualization system. You can run it on Android or iPhone if you want. It doesn’t make a lot of sense, but it’s possible. It uses a shared-nothing architecture, which drives a lot of performance characteristics both for a single node and the cluster itself. It is columnar, which is also very important. Internally, it uses vectorized execution and a lot of different algorithmic optimizations and parallelization at all levels. It tries to be as fast as possible for every single query. And of course it scales to many petabytes.

Performance problems are typical for any size ClickHouse system. You can hit them on a small application and also on a huge cluster.

[07:33] – Defining “Slow”

Alexander: Let’s talk about performance. ClickHouse is very fast, but sometimes it may go slow in certain cases. The definition of slowness can be very different depending on the use case or your view of performance. What does slow mean? Does it mean a single query is slow? Does it mean you have a lot of multiple concurrent queries and the system becomes slow and unresponsive under load? Do you use a single-node ClickHouse or a cluster? Maybe your query is fast on a single node but once you go to a cluster it starts being slow. Do you mean data latency by slow, meaning it is slow to insert, not to query? And what is your definition of slow: maximum time, some median, some percentile? These are very important questions to ask yourself before trying to optimize anything.

Usually when somebody says “my ClickHouse is slow,” there is some particular query that requires attention or optimization. If a query is slow, there is some bottleneck. It could be IO, which is the most typical case in databases for decades, because databases are data-intensive and IO is often a bottleneck. It could be CPU if you have a lot of computations. It could be RAM if your query needs to do a lot of memory-intensive operations like joins or aggregations. It could be network if your query has to read a lot of data over the network. It could be background operations, merges, mutations, or backups affecting your cluster. And it could be Keeper slowing down your insert operations.

[10:12] – Plan of Attack for Single Query Optimization

Alexander: Let’s start with single query optimization because this is usually the simplest thing to optimize. The plan of attack is: first, find the slow query. Then understand whether the query itself is slow or whether other workloads are slowing it down. Then understand inside the query what is slow: is it reading a lot of data, or is it processing data inefficiently? Once you understand the reason, optimize.

[11:09] – The Toolbox: Query Log, EXPLAIN, Trace Log

Alexander: What tools do you have? First and foremost, benchmarks. You always need to benchmark your query before and after optimizations. That is the only way to understand if you are making progress. ClickHouse has a very convenient query log where you can find query times, and it also exposes ProfileEvents which records a lot of internal metrics about what happened during query execution: how many files were opened, how much data was read from disk, and so on.

In addition to profile events in the query log, there are log tables like system.metric_log and system.asynchronous_metric_log that allow you to see ClickHouse server statistics as a time series, useful for monitoring and detecting changes in cluster behavior. There is also the EXPLAIN statement, which ClickHouse has in several forms, each showing something different about query planning, execution, and estimates. We’ll show examples. Finally, there are logs. You can enable trace-level logging in the clickhouse-client with SET send_logs_level='trace', which will print the log for any query you execute. For advanced users, there is the trace log which allows you to map query execution to internal system calls and see which ClickHouse functions were called how many times.

[14:36] – Benchmarking Principles

Alexander: It is essential to benchmark on real data. We often hear from users that queries work fine on staging but once they go to production they don’t work or work very slowly. It’s important to understand that you need to have the same data in your test and production environments. If the data size is different, your performance metrics will be different.

It’s also important to benchmark whether a particular query is always slow or just sometimes, because it may be fast in certain cases and slow in others, which may point to some other process running on your production cluster. All operations running together with queries may affect performance. When you are doing profiling, make sure there are no merges, heavy mutations, or backups running that may saturate your disk.

There are tools to simplify the testing. clickhouse-benchmark, which is part of the ClickHouse distribution, allows you to run a single query or multiple queries in a loop with multithreading if necessary. Don’t use it in production: only in test and staging, because it may produce very high load.

[16:32] – ProfileEvents in the ClickHouse Client

Misha: When looking at particular queries, we can look on different aspects. The most obvious basic statistics are execution speed, memory usage, and bytes read, and they are printed in the ClickHouse client after every query run. There is even more detailed information about query execution in the ProfileEvents in the query log. But you can also see those directly in the ClickHouse client by passing the --print-profile-events and --profile-events-delay-ms=-1 flags. Every query execution will then show the profile events it generated. For example, you can see how many microseconds of CPU user time the query spent. For very simple queries it won’t be much, but for heavier ones these events give you a breakdown of exactly where time was spent.

[18:28] – Comparing Two Query Runs

Misha: The same technique can be used to compare two different queries, or the same query run at two different times. You can take two query IDs, one that ran fast and one that ran slow, and run a comparison query against the query log. Here’s an example: the same query ran twice. First time it took about three seconds; the second time it took about 0.1 seconds. By looking at the comparison output, you can see that the first run had about 300 mark cache misses, meaning it had to read marks from disk, and it spent 31 million microseconds waiting for those marks. The second run had everything in the mark cache and was much faster. You can also see from the S3-related profile events that the first run was reading data from S3 while the second was not.

The same technique can be used to compare two time intervals, for example one hour where the system behaved well and a subsequent hour where it ran badly. You can compare those two intervals using a query against system.query_log grouped by normalized_query_hash, and it will show you a query-by-query breakdown of how much IO, CPU, and memory each query pattern used in each period. The same technique applies when testing setting changes: compare before and after by looking at the breakdown by particular query patterns.

[22:11] – Finding Slow Queries: Mapping Bottlenecks to Query Log Fields

Misha: Usually when I need to find problematic queries, I start by looking at system metrics, whether from Prometheus, some commercial tool, Zabbix, or embedded ClickHouse tools like system.metric_log. By looking at those basic monitoring tools you can identify where the bottleneck is: 100% CPU usage, high disk throughput, memory going out, and so on.

There’s a mapping from those bottlenecks to particular fields or profile events in the query log. For example, CPU usage maps to the profile event OSCPUVirtualTimeMicroseconds. Disk throughput maps to read and write bytes. RAM maps to memory usage. The only somewhat indirect one is LA or load average: if CPU usage is low but load average is high, it often means the number of queries or threads rather than a particular query is the problem.

Once you’ve identified the bottleneck, you can write a query against system.query_log that groups all queries by normalized_query_hash. That field removes all constants and specific values from WHERE conditions and calculates a hash of the resulting normalized query. This lets you group similar queries. You can then see: was it a single query that ran once and consumed a huge amount of CPU time? Or was it one query pattern that ran a million times, each execution very fast, but together burning a lot of resources? Once you group by that hash, you can compare both cases. The Altinity Knowledge Base has even more complex examples that collect all metrics together.

[26:10] – I/O as the Primary Bottleneck

Misha: Before going too deep into specific profile events, one of the main problems you can have with queries is IO. It’s so typical: if a query is slow, most probably it’s just not optimal from the IO perspective. It’s either reading too much data or reading too slowly. Good queries don’t read too many gigabytes or terabytes. They typically read with good speed, usually more than a few gigabytes per second. More than 1 GB/s read speed is tolerable.

Here’s an example: the same query rewritten three ways. The first version executed in 4 seconds, processed two billion records, and read 28 GB at 7 GB/s. The second version processed two and a half billion rows but read almost a terabyte of data. That’s because it was reading all columns instead of just the needed ones. It took 160 seconds. The third version read the same amount of data as the first but at only 100 MB/s instead of 7 GB/s, making it even slower than the second. That slow read speed was caused by a column that was compressed very aggressively, making decompression the bottleneck.

So the root causes for too much IO are: reading all columns when you need only a few (ClickHouse is columnar so you control this), full table scans due to not using indexes, or slow decompression from overly aggressive compression.

[29:43] – Full Scans: EXPLAIN Indexes, EXPLAIN ESTIMATE, and Force Flags

Misha: If the query is doing a full scan, how do you see what’s happening? You can use two variants of the EXPLAIN statement: EXPLAIN indexes and EXPLAIN ESTIMATE. The same data is also in the logs if you send logs level to trace.

There are some flags you can toggle to test whether your query uses the primary key. If you toggle force_primary_key and your query starts failing, it means your query was not using the primary key, which is a problem. If you toggle force_index_by_date (despite the unfortunate name, this actually tests the partition key), and your query starts failing, it means you have no conditions on your partition key.

EXPLAIN indexes shows what conditions ClickHouse was able to extract from your query, how they were transformed in the execution plan, and how many granules they filtered. For example, a condition on flight_date might reduce the granules to read from 24,000 down to 540, while conditions on the partition key might not help at all if they’re not present. EXPLAIN ESTIMATE shows how many rows, marks, and parts ClickHouse plans to read. By default, one mark in ClickHouse groups 8,000 rows.

[33:08] – The Aliasing Trap

Misha: Here’s a very common ClickHouse-specific problem. People coming from other databases write queries like WHERE date = toDate(something) but also define an alias called date in the same query. In ClickHouse, aliases can be redefined everywhere. So instead of using the actual date column for filtering and taking advantage of the index, ClickHouse sees the alias and applies the transformed expression first, bypassing the index entirely. The result is a full scan. The fix is to avoid redefining aliases that shadow actual column names, or to be explicit about which expression you mean.

[35:30] – Fixing Full Scans

Alexander: If you have a full scan, the most obvious thing is that you’re missing a WHERE condition on the primary key. Maybe you have a condition on some other field but missed the primary key condition. Just add that.

Maybe you’ve picked a bad ORDER BY. It’s quite typical when people start using ClickHouse to create a wrong ORDER BY, for example ordering by a unique ID just like in transactional databases. In ClickHouse, a good ORDER BY typically looks like ORDER BY (tenant_id, category_id, event_id). Understanding how to pick an ORDER BY / PRIMARY KEY / PARTITION BY for the MergeTree family is essential. If you’ve chosen a bad ORDER BY initially, the only way to fix it is to recreate the table. There’s no way to alter an existing ORDER BY arbitrarily.

Sometimes very complex logical expressions with long chains of AND/OR can be rewritten into simpler form. For example, a long field = 1 OR field = 2 OR field = 3 is often much better expressed as field IN (1, 2, 3). Some forms work much better with ClickHouse’s index analyzer than others. There are cases where there’s an eight times performance difference between equivalent queries simply because one form is index-friendly and another is not.

Also, be aware of non-monotonic functions. If you use a hash function as a key, ClickHouse’s index analyzer is not transparent for non-monotonic functions. Only monotonic functions are transparent for index analysis. In that case, you need to use the exact expression you used in the primary key.

[39:27] – PREWHERE Optimization

Misha: Sometimes you don’t have a full scan but still read a lot of data. One reason is that the amount of data after filtering is simply very large. In that case, the obvious sign is that you need to pre-aggregate data using projections or pre-aggregation tables.

Another reason is inefficient column reading. ClickHouse can use different strategies for executing the same query. It can read one column first, apply the condition, narrow the range, and then read the remaining columns. Or it can read all columns together and apply conditions on the fly. The former strategy is called PREWHERE and it’s much better when the first column is selective and cheap to read.

Fortunately, ClickHouse enables PREWHERE optimization by default in recent versions. It now intelligently pushes conditions into PREWHERE automatically, applying them in a waterfall-like fashion: apply first condition to first column, reduce the range, read the sub-range of the second column, reduce further, and so on. This is a very smart way of doing it. But sometimes even in newer versions this strategy can misbehave. In that case, you can write explicit PREWHERE conditions, which will disable the automatic optimizer, or toggle the relevant flags to disable it manually.

CTEs (Common Table Expressions) are also worth mentioning: most CTEs in ClickHouse are not cached. If you reference the same CTE multiple times, it runs multiple times. Be aware of this pattern.

[42:58] – Slow Disk, S3, and Compression Trade-offs

Misha: Other reasons why reads can be slow: the disk itself may be slow, saturated by backups, merges, or mutations running in the background. S3 can introduce larger latencies, which forces you to add caches. And sometimes people find the best compression algorithm, not thinking about decompression speed. ZSTD at levels above 3 is usually a no-go for production: it is really slow. Prefer simple things. Generally, if you have a simple query, ClickHouse is able to apply all the optimizers it needs. The more complex the conditions and schema you create, the harder it is to nail down the problem.

[44:19] – GROUP BY and Aggregation Optimization

Misha: Sometimes the problem is not the amount of data you read but what you do with it. For GROUP BY optimization, a few key things.

First, it’s better to do calculations on top of aggregate function results rather than applying them to every row. For example, SUM(10 * column) is the same as 10 * SUM(column), but the second is more efficient because you multiply by 10 once instead of on every single row.

Second, dictionaries: if you use dictGet in a GROUP BY, ClickHouse can use two different strategies. It can group by the column before applying dictGet (cheaper) or it can apply dictGet to every row first and then group. You control this behavior by marking whether the dictionary is injective. If it is injective (one-to-one mapping), ClickHouse knows it can group first and apply dictGet to the group results. Mark your dictionaries as injective where appropriate.

Third, data types matter for GROUP BY keys. Complex data types are more expensive to hash. Sometimes using a different column or a simpler type yields better performance.

Fourth, the approximate aggregate function hierarchy: uniqCombined is cheaper than uniq, which is cheaper than uniqExact. Use the least precise function that meets your accuracy requirements.

There are also lower-level things like two-level GROUP BY that ClickHouse can use to parallelize aggregation differently. When in doubt, simplify your queries: ClickHouse prefers simple things.

Also make decisions about what to compute at insert time versus query time. If you need to repeatedly do the same computation over millions or billions of rows on every query, compute it once at insert time. If you do it at query time, every single query repeats that work. Pre-compute and store, and query time becomes much cheaper.

[51:39] – JOIN Optimization

Alexander: ClickHouse does not have a cost-based optimizer for joins, so it is fairly conservative about how it performs them. Until recently it could only do hash joins, which limits performance and the size of tables that can be joined. Even after merge join was introduced, there were initial performance issues.

The first thing to ask yourself is: do you need the join at all? Joins are always expensive. There are a couple of ways to avoid them. First is denormalization: instead of having lots of key columns and joining, you store values directly in the big table. Since ClickHouse is a column store, extra columns are cheap compared to other databases. Second is dictionaries, which you can think of as hash maps always kept in RAM. They help in two ways: the data is always cached in memory, and you don’t need a join at all, simplifying query processing.

For joins that are unavoidable, here’s a key optimization: aggregate before joining. Here’s an example: a query on the NYC taxi data joining to get zone names for the top zones by passenger count runs in 40 ms in the naive form. In the optimized form, you first aggregate by location ID (a fast integer operation on the large table), which produces a much smaller result set, and then join that against the zones table. This is four times faster because the join operates on a tiny number of rows and uses integer keys rather than strings.

Another important optimization: filter before joining. Until recently, ClickHouse couldn’t push down conditions on joined tables effectively. Instead of relying on push-down, filter the joined table first, extract the IDs, and use a WHERE condition on the ID column only. This technique produced a 15x speedup in one example, because instead of joining the full table and then filtering, you filter first and then join on a tiny subset.

[57:01] – Distributed Query Optimization

Alexander: Distributed queries are much harder to optimize because you need to optimize at two levels: how the local query works, and how the distributed query works. These are very different.

When a distributed query executes, you need to push as much processing as possible to the shards. The initiator node should do as little work as possible. If you have complex CTEs or subqueries that can’t be pushed to shards, everything ends up serialized on the initiator, which is very inefficient.

Be very careful with joins involving two distributed tables. ClickHouse by default doesn’t allow joins with two distributed tables, but users tend to override this and get into trouble. It can affect performance by 100x if done incorrectly.

Data locality is a very important design principle. How your data is distributed among shards has a major impact. For example, if you put all data about a certain user on a single shard, you can calculate uniques independently per shard and then sum the results, which is much more efficient than when user data is spread randomly across shards and each shard must build a full hash map that then needs to be merged across all shards.

[59:35] – New Query Analyzer in ClickHouse 24.3

Misha: The new query analyzer introduced in ClickHouse 24.3 is a really big change. It changes the way ClickHouse works with queries internally. Before, it was mostly working at the syntax tree level. Now it has a much higher-level view of the query, which makes possible a lot of cool optimizations, including potentially a cost-based optimizer for joins in the future. It also sorts out a lot of different small problems with ClickHouse’s alias syntax that wasn’t fully standard SQL.

In short: if you had problems with joins or with complex alias handling, upgrading to 24.3 and using the new analyzer will most probably solve them. That said, it is still relatively new. It is production-ready, but you can still face some small problems. When upgrading to 24.3, start using the analyzer, see how your complex queries behave, and if you encounter issues you can always turn it off.

[1:02:12] – RAM and Cache Management

Alexander: ClickHouse uses a lot of RAM when running and has a lot of internal caches. The cache hierarchy from bottom to top includes: the OS page cache, the uncompressed block cache (small, not very useful), the mark cache and index cache (ClickHouse’s internal metadata about how data is stored, used to locate binary data efficiently), and the query cache for caching full query results. For object storage, always use the filesystem cache because it will not only help performance but also reduce costs by reducing API calls.

Always try to run ClickHouse with more RAM than needed for your queries. Extra RAM is not wasted. Linux will use it for the page cache, and data read recently stays in memory. You can see the effect of the page cache by turning it off with min_bytes_to_use_direct_io = 1, which disables page cache usage. In one test, this made a query three times slower. The difference can be much more dramatic in practice. Keep page cache utilization as high as possible.

The mark cache and index cache are used to locate binary data during queries. Without them, ClickHouse would do extensive IO operations just to find where the binary data is before even starting to read it. In a well-tuned system, the mark cache hit rate should be 99% or higher. If it’s 80% or 50%, your queries are inefficient and you can speed them up just by making sure there’s enough RAM for the mark cache.

The query cache, added about a year before this webinar, allows you to cache full query results. If you run the same query for the first time it may take several seconds. On the second run it can take one millisecond because the result is taken from the query cache. You can tune the query cache at the profile or session level: TTL (default 60 seconds), minimum number of runs before a query gets cached, minimum execution time before caching. These are worth tuning for dashboards and other repetitive query patterns.

Summary on memory: more is always better. Unused RAM goes to the page cache. Never use swap with ClickHouse because it will cause sluggishness. Consider disabling swap completely. ClickHouse is not very good at memory management under extreme pressure, so it’s still possible that very memory-intensive queries or multiple concurrent memory-intensive queries can kill the server, but the ClickHouse team continuously works to reduce this risk.

[1:08:54] – Optimization for Concurrency

Misha: Sometimes the problem is not with a particular query but with the number of queries. Having too many concurrent queries is usually not ideal because ClickHouse creates real threads for every query execution. If you have too many, you’ll have a lot of threads, a lot of context switches, high lock contention, and suboptimal performance. Having fewer queries running simultaneously is better.

If you have a really high number of queries, each individual query should be very fast and not use too many resources, because otherwise they will interfere with each other. One useful approach for high-QPS workloads is to enable query queuing: instead of increasing the number of concurrent execution slots, you queue incoming queries so they execute one after another within a controlled degree of parallelism. This helps the system survive load spikes without creating the chaos that too many simultaneous threads cause.

You also need to be careful with the number of threads per query. By default ClickHouse allows a single query to use as many threads as there are CPU cores. With 16-core CPUs that means 16 threads per query, and scaling up the number of concurrent queries makes the total thread count explode. If you have many small fast queries, decrease max_threads to 1 or use the max_threads_for_read concurrent soft limit. Check the documentation on concurrent_threads_soft_limit_ratio_or_count for this.

For load balancing with many concurrent queries, you generally want more replicas, and using distributed tables in those scenarios can be painful. Instead of querying through distributed tables, prefer smart sharding and smart load balancing on your backend layer, because a distributed query becomes at minimum two queries: the distributed one and the local one per shard. With hundreds of concurrent queries, doubling them is a serious problem.

Step back and think about whether there are ways to avoid the load. Can you do caching on the application side? Can you spread background jobs more evenly in time rather than having all of them fire together? If you still have this situation, you must review and benchmark every query change carefully, because this is a balanced system where any change can have unexpected effects. And always have a Plan B: throttling, serving cached data, disabling non-critical jobs, or dynamic cluster scaling for when the load doubles unexpectedly.

For very high QPS scenarios, per-query overhead can work against you. The execution pipeline that actually runs the query is often only a small fraction of total query time when queries are very fast. The rest goes to parsing, analyzing conditions, running optimizers, logging, and closing buffers. To reduce this overhead: simplify queries as much as possible, disable query logging for a percentage of queries or entirely for the fastest ones, and consider disabling optimizers that aren’t needed for simple queries where they provide no benefit.

[1:16:25] – Summary and Resources

Alexander: This is an endless topic. We could continue for two more hours with examples from our experience, and typically this is what our engineers do every day for our customers. But we’ll probably continue this performance series in future webinars.

Before then, check the ClickHouse documentation, which has a lot of insight on different settings. We often publish technical articles on the Altinity blog focused on performance with hints that can help with optimization. Check the Altinity Knowledge Base, which has examples specifically for finding slow queries, comparing queries before and after, as well as many other interesting things you can do with ClickHouse system tables.

You can also find us in the ClickHouse Slack and Altinity workspace. If you want a more interactive session, we have Altinity ClickHouse training which covers advanced topics about ClickHouse administration, tuning, and performance optimization. If you’re not sure if ClickHouse is for you or you have a problem you want to solve right now, feel free to sign up for a free consultation. You can talk to us and we can dig into your use case and probably help with performance optimization.

FAQ Section

Q: What is the best way to find the slowest queries on a ClickHouse cluster?

A: Start by monitoring high-level system metrics such as CPU usage, disk throughput, and memory usage to identify what kind of bottleneck you have. Then use system.query_log to drill into specific query patterns. Group queries by normalized_query_hash, which strips constants from WHERE clauses and hashes the resulting normalized query shape. This lets you compare groups like a single expensive query that ran once versus a high-frequency pattern that ran a million times. Both the handy queries for system.query_log in the Altinity Knowledge Base and the compare query_log for two intervals query are excellent starting points. Sort by total CPU time, disk bytes, or memory usage depending on which system resource is the bottleneck.

Q: How do I know if my ClickHouse query is doing a full table scan?

A: Use EXPLAIN indexes to see which conditions ClickHouse was able to extract from your query and how many granules it plans to read. If the number of granules to read is close to the total granule count of the table, you have a near-full scan. You can also toggle force_primary_key = 1: if your query fails, it means ClickHouse was not using the primary key. Similarly, force_index_by_date = 1 tests whether partition key pruning is happening. The most common reasons for full scans are: a missing WHERE condition on the leading columns of the ORDER BY key, an ORDER BY designed like a transactional database (e.g., ordering by a unique ID rather than by the columns you actually filter on), or an aliasing issue where a column name is shadowed by a redefined alias in the same query.

Q: How should I choose ORDER BY and PRIMARY KEY for a MergeTree table?

A: The ORDER BY defines how data is sorted within each part, which directly determines which queries can use the sparse index efficiently. The leading columns of ORDER BY should be the columns most commonly used in WHERE filters, starting with the lowest-cardinality filter columns (such as a tenant ID or category) and progressing to higher-cardinality ones. The guidance on how to pick an ORDER BY / PRIMARY KEY / PARTITION BY in the Altinity Knowledge Base covers this in detail. A bad ORDER BY choice is painful to fix later because the only option is to recreate the table with the correct definition.

Q: When should I use dictionaries instead of JOINs?

A: Dictionaries are always-in-memory key-value hash maps. Use them instead of joins when you need to look up values from a dimension table that is small enough to fit in RAM, changes infrequently, and is queried very frequently. They are especially effective for resolving IDs to names, applying reference data, or doing multi-tenant lookups. Dictionaries eliminate the overhead of building a hash map at query time (because it’s already built and in memory), and when marked as injective, they enable ClickHouse to push GROUP BY optimization so that grouping happens before the dictionary lookup rather than after it. Denormalization, meaning pre-joining the data at insert time, is another good alternative for data that doesn’t change.

Q: How can I reduce memory usage in GROUP BY queries?

A: Several approaches help. Use approximate aggregate functions where exact answers aren’t required: uniqCombined is cheaper than uniq, which is cheaper than uniqExact. Use simple, compact data types for GROUP BY keys: complex types are more expensive to hash. Move computations outside the aggregation loop: 10 * SUM(col) is cheaper than SUM(10 * col) because the multiplication happens once rather than on every row. Mark dictionaries as injective so ClickHouse can group before applying the dictionary lookup. The Altinity Knowledge Base has detailed GROUP BY tricks with concrete examples.

Q: What is the query cache and when should I use it?

A: The query cache, available since around ClickHouse 23.x, stores the full result of a query in memory. If the same query is run again within the TTL (default 60 seconds), ClickHouse returns the cached result in approximately one millisecond rather than re-executing the query. It is most effective for repetitive dashboard queries or reporting queries where the underlying data changes slowly relative to the refresh interval. You can tune TTL, minimum execution time before caching, and minimum run count at the profile or session level. It is complementary to the mark cache and OS page cache: the query cache avoids re-executing SQL entirely, while the other caches avoid re-reading data from storage.

© 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.