ClickHouse has earned a well-deserved reputation as one of the fastest analytical databases available today. Its columnar storage, vectorized query execution, and aggressive compression make it a natural choice for teams dealing with hundreds of millions to trillions of rows. But speed is not a guarantee — it is a potential. Whether that potential is realized depends almost entirely on how you design your schema, write your queries, and operate your infrastructure.
Engineers who come to ClickHouse from traditional row-oriented databases like PostgreSQL or MySQL, or from systems like Elasticsearch and Splunk, often carry mental models that simply do not translate. The result is a system that feels slower than expected, consumes more memory than it should, or buckles under real production load. Most of these problems are completely avoidable with the right approach — and most of them are fixable once you know what to look for.
This guide walks through seven of the most common ClickHouse performance mistakes, explains why each one happens, and gives you concrete, tested strategies for resolving them. Whether you are building a new ClickHouse deployment or optimizing an existing one, these patterns will help you get significantly more out of the database.
1. Choosing the Wrong Partition Key
The partitioning scheme is the single most consequential schema decision you will make in ClickHouse. It controls how data is physically divided on disk into segments called parts, and it directly determines how many files ClickHouse must open to answer any given query.
A common mistake — especially for teams coming from multi-tenant relational systems — is to partition by a high-cardinality identifier such as a customer ID, a device ID, or a region code. The logic seems reasonable: if each tenant lives in its own partition, you can drop it cleanly for GDPR compliance or data retention purposes. The problem is that high-cardinality partitioning creates an enormous number of small parts. In a ClickHouse table with 109 columns, each small part means 109 pairs of files on disk. Multiply that across thousands of tenants and hundreds of time intervals, and a simple full-table scan can require opening tens of thousands of files before it returns a single row.
The reliable rule of thumb is to partition by time. If you hold 30 years of data, partition by year. If you hold 90 days, partition by month or week. If you hold real-time streams for a few days, partition by day. The goal is to arrive at roughly a thousand parts for the entire table — not thousands of parts per month. Time-based partitioning tends to produce consistently sized partitions because events tend to arrive at roughly uniform rates over time.
If you need tenant isolation, the right mechanism is the ORDER BY (primary sort key), not the partition key. By including the tenant identifier as the first column in your sort key, ClickHouse stores all data for that tenant contiguously within each partition. Queries filtering by tenant are extremely fast because they read a compact, contiguous slice of storage. The trade-off — that removing a tenant requires a mutation rather than a partition drop — is almost always worth it for the performance gain.
Additional schema optimizations worth layering on top of partitioning include choosing appropriate data types (prefer UInt32 over Int64 where possible, use LowCardinality(String) for columns with limited distinct values), applying per-column compression codecs such as Delta for monotonically increasing values or Gorilla for floating-point time series, and switching from the default LZ4 compressor to ZSTD when your CPU has idle headroom and your disk IO is the bottleneck.
2. Inserting Rows One at a Time
If you learned SQL on a transactional database, you are probably accustomed to inserting rows individually: INSERT INTO events VALUES (...). In PostgreSQL or MySQL, this is perfectly normal. In ClickHouse, it is one of the fastest ways to degrade performance.
Every insert in ClickHouse creates a new part on disk. That part is immediately queryable, but it is small. ClickHouse runs background merge threads that continuously look for small, adjacent parts and combine them into larger ones. This process is called compaction, and it is essential to long-term query performance. The problem is that compaction consumes real CPU time and IO bandwidth — the same resources your queries need. When small inserts arrive faster than the background merges can keep up, parts accumulate. ClickHouse will begin throttling inserts when the part count crosses configurable thresholds, and in severe cases, the table effectively becomes unavailable for writes. This phenomenon is sometimes called write amplification because each inserted row may be rewritten several times before the data reaches a stable, fully compacted form.
The fix is straightforward: always batch inserts. A single insert of one million rows creates one part. One million single-row inserts create one million parts. Aim for at least 100,000 rows per insert operation, and size your batches so that the resulting parts are in the range of tens to hundreds of megabytes on disk.
When batching is not possible — for example, when data arrives from log shippers, IoT agents, or application code that cannot accumulate state — ClickHouse’s async insert feature is the right solution. When async_insert = 1 is enabled for a user or session, ClickHouse buffers incoming single-row or small-batch inserts in memory and flushes them as a single, large part after a configurable delay (typically 100 to 500 milliseconds) or after the buffer reaches a configurable size. From the application’s perspective, the insert returns immediately. From ClickHouse’s perspective, one merged write hits the storage layer. For Kafka-based ingestion using the Kafka table engine, this buffering is handled natively by the engine itself, so async inserts are neither needed nor applicable in that context.
3. Writing Queries That Fight the Storage Layout
ClickHouse’s sparse primary index is designed to let the engine skip large chunks of data when the query’s filter matches the sort key. If your query does not align with the sort key, ClickHouse must scan every part for every column involved — and no amount of hardware will make an unindexed full-table scan fast when the table has a hundred columns and billions of rows.
Before optimizing any query, run EXPLAIN indexes = 1 to see how many granules (8,192-row chunks, by default) are being scanned versus skipped. If ClickHouse is reading the entire table for a query that you expect to be selective, check whether your filter columns appear in the sort key. If they do not, consider whether a secondary (skip) index — such as a minmax index for range queries or a Bloom filter index for low-selectivity string lookups — is appropriate for your access pattern.
A second common query mistake involves aggregations with large, high-cardinality group-by keys. Aggregation in ClickHouse works by building hash tables in memory, one slot per unique group-by key combination. If you are grouping by a composite key with millions of distinct values, the hash tables can grow to gigabytes. This forces ClickHouse to either spill to disk (which is slow) or abort the query with an out-of-memory error.
One highly effective mitigation is to replace exact aggregate functions with approximate ones when precision is not critical. Replacing uniqExact(user_id) with uniq(user_id) — which uses a HyperLogLog data structure internally — can reduce memory consumption by four to five times while returning a result that is accurate to within about 2%. For cardinality estimation at scale, the precision trade-off is almost never noticeable in a dashboard context.
Join performance deserves its own attention. ClickHouse performs best when the right-hand side of a join fits entirely in memory. If your join involves a large dimension table, consider preloading it as a ClickHouse dictionary. Dictionaries are loaded into server memory and remain resident across queries, making repeated lookups essentially free. For ad-hoc joins that cannot be restructured, restructuring the query so that aggregation happens in a subquery first — reducing the result set before the join executes — can dramatically reduce both memory use and execution time.
4. Ignoring the system.query_log Table
One of ClickHouse’s most underused capabilities is its suite of system tables. The system.query_log table records every query executed on the server, including the query text, execution time, memory consumption, number of rows and bytes read, and whether the query used an index. If you are trying to optimize ClickHouse performance and you are not regularly querying system.query_log, you are working blind.
A simple diagnostic query to identify your top ten slowest queries in the last 24 hours looks like this:
SELECT
query,
query_duration_ms,
read_rows,
read_bytes,
memory_usage
FROM system.query_log
WHERE type = 'QueryFinish'
AND event_time >= now() - INTERVAL 1 DAY
ORDER BY query_duration_ms DESC
LIMIT 10;Beyond raw query time, the read_bytes and memory_usage columns are invaluable. A query that reads vastly more bytes than you expect is almost certainly suffering from poor index utilization or an inappropriate schema. A query with unexpectedly high memory usage is likely performing large aggregations or carrying too much state through a join.
Similarly, system.parts provides detailed information about every part in every table: its size on disk, the number of rows it contains, and how many times it has been merged. Running a query against system.parts to check average and maximum part counts is a fast way to confirm whether your partitioning strategy is producing well-sized parts or an explosion of small ones.
ClickHouse also writes detailed query execution profiles to the server logs when the log_queries = 1 setting is enabled, including information about which skip indexes were consulted, how many granules they eliminated, and where time was spent during execution. Pairing system table queries with log inspection gives you a complete picture of what is happening inside the engine — something few other databases provide so transparently.
5. Underprovisioning Resources and Skipping Capacity Planning
ClickHouse is notably efficient, and it is easy to assume that a small server will handle a production workload because benchmarks run quickly on a development machine. This assumption fails in production because ClickHouse servers simultaneously handle four categories of load: queries, inserts, background merges, and mutations (updates and deletes). Each of these competes for CPU, RAM, and IO bandwidth.
A sudden spike in insert volume — from a Kafka consumer catching up after downtime, for example — can trigger a wave of background merge activity that saturates disk IO just as your busiest query period begins. A large mutation that rewrites a significant portion of a table can consume all available write bandwidth for minutes or hours. These interactions are difficult to anticipate without testing under realistic, concurrent load.
The most effective capacity planning exercise is to run your expected peak workload on a single representative node, watch the resource utilization across all four categories simultaneously, and optimize until the node handles the load comfortably. Only then should you add replicas and shards. Starting with a cluster and expecting sharding to solve fundamental resource problems on individual nodes is a common mistake that leads to expensive, over-provisioned deployments that still perform poorly.
On the replication side, the coordination layer — ClickHouse Keeper (the successor to Apache ZooKeeper) — deserves separate attention. Keeper tracks the state of every replicated part on every node and must be consulted on every insert to a replicated table. If Keeper is slow — because its underlying disk has high latency, because it is running on a shared node that is CPU-saturated, or because the number of outstanding requests has grown too large — insert performance degrades cluster-wide. In extreme cases, Keeper unavailability causes tables to go read-only entirely.
Keeper health should be monitored continuously. Key metrics to watch are outstanding request queue length, fsync latency (Keeper relies heavily on synchronous disk writes), and the time between the Keeper leader and its followers. Provisioning Keeper on dedicated nodes with low-latency SSD storage and reserving generous CPU headroom is an investment that pays off consistently under load.
6. Treating ClickHouse Like a Transactional Database
ClickHouse is an OLAP (Online Analytical Processing) engine optimized for bulk reads and bulk writes. It is not designed for the access patterns common to transactional (OLTP) systems, and applying OLTP patterns to ClickHouse is a reliable path to poor performance.
The most common manifestation of this mistake is frequent, row-level updates. In PostgreSQL, updating a single row by primary key is trivially cheap. In ClickHouse, what appears to be a simple update is actually an ALTER TABLE ... UPDATE mutation — a background operation that rewrites entire parts to apply the change. Triggering this frequently, or triggering many overlapping mutations, creates sustained background IO load that competes with queries and inserts. If your workload genuinely requires frequent individual-row updates, ClickHouse is likely not the right primary store for that dimension of the data. A common pattern is to keep rapidly-changing state in a fast key-value store like Redis and join it to ClickHouse analytical data at query time using a dictionary.
A related mistake is using small, highly selective queries as if ClickHouse were a key-value store: SELECT * FROM events WHERE event_id = 'abc123'. ClickHouse can answer this query, but it is not what the engine is optimized for. A point lookup by a non-sort-key column requires a full partition scan. Even with secondary indexes, the overhead per row retrieved is high relative to a dedicated key-value system. Reserve ClickHouse for what it does best: aggregation and analytical filtering over large row ranges.
7. Migrating from Full-Text Search Engines Without Adapting the Data Model
Teams migrating from full-text search systems like Elasticsearch or Splunk frequently underestimate the extent to which those systems’ performance depends on inverted indexes — data structures that, by definition, ClickHouse does not use as a primary storage mechanism.
An inverted index stores, for each unique token, the list of documents that contain it. This makes arbitrary token lookups extremely fast regardless of which field the token appears in. ClickHouse’s sparse primary index works entirely differently: it stores the minimum and maximum values of the sort key at fixed intervals of 8,192 rows. A query that filters on a column not in the sort key must scan every granule in every part, which is acceptable for bulk analytics but can be painfully slow for ad-hoc log search.
Several mitigation strategies exist. The first and most important is to identify the columns most frequently used in search filters and ensure they appear in the sort key, which enables granule-level skipping. For columns with high cardinality that are searched as substrings, ClickHouse’s native full-text inverted index (currently in beta as of ClickHouse 24.x) can help, though it can be large and should be evaluated carefully on realistic data volumes. Bloom filter skip indexes are a more mature option for exact-value lookups and set-membership checks; they require tuning of the false-positive rate and granularity, but when configured correctly they can eliminate large fractions of the data from consideration. Finally, for the common pattern of searching logs for a specific string value, ClickHouse’s LIKE operator benefits from SIMD instruction acceleration on modern x86 hardware and is faster in practice than its reputation suggests.
The deeper lesson here is that successful migrations from full-text search systems require explicitly mapping the search patterns your users depend on to ClickHouse’s indexing capabilities — before you migrate production data, not after. Build a test dataset that reflects real query patterns, run the queries users care about, and measure whether the response times are acceptable. This testing phase is not optional; it is where migrations succeed or fail.
Putting It Together: A Performance Optimization Checklist
The seven mistakes above share a common theme: ClickHouse performance is not automatic. The engine rewards teams that understand how it stores data, how its indexes work, and how its background processes compete for resources. The following checklist summarizes the key decisions to validate before and after deploying a ClickHouse system:
- Schema: Partition by time with a target of roughly 1,000 parts per table. Use the sort key (ORDER BY) to enforce tenant isolation and query selectivity rather than the partition key.
- Inserts: Batch to at least 100,000 rows per operation. Use async inserts for architectures that cannot batch on the client side. Use the Kafka table engine’s native buffering for Kafka-based pipelines.
- Queries: Align filters with the sort key. Prefer approximate aggregates (
uniq,quantile) over exact ones when the use case permits. Use dictionaries for frequently joined dimension tables. Restructure joins to reduce data volume before the join executes. - Observability: Query
system.query_logregularly for slow queries. Querysystem.partsto validate part counts. Enablelog_queries = 1for index utilization details. - Resources: Test at realistic concurrency and data volume before scaling out. Monitor Keeper queue depth and fsync latency continuously. Provision for peak load, not average load.
- Workload fit: Route frequent single-row updates to a purpose-built store. Avoid using ClickHouse as a key-value lookup engine for non-sort-key columns.
- Migration: Map source system search patterns to ClickHouse index types explicitly. Test on real data at realistic concurrency before migrating production traffic.
Conclusion
ClickHouse is genuinely capable of scanning hundreds of millions of rows in under a second, even on modest hardware. The engineers who achieve those numbers are not relying on magic — they have internalized how the engine works and made deliberate decisions at every layer of the stack. The engineers who are disappointed by ClickHouse performance have typically carried assumptions from other systems that do not apply here.
The good news is that virtually every ClickHouse performance problem is diagnosable and fixable. The system tables give you the data you need. The architecture, once understood, makes the right decisions obvious. Start with real data, test under realistic load, and iterate on the schema and queries until the numbers satisfy your requirements. That process, more than any single optimization, is what separates fast ClickHouse deployments from slow ones.
At ChistaDATA, we work closely with engineering teams at every stage of the ClickHouse journey — from initial schema design to production performance tuning. If your deployment is not delivering the performance you expect, reach out to our team or explore our managed ClickHouse service to see how we can help.
