ClickHouse is one of the fastest open source columnar databases for real-time analytics, and it powers observability platforms, product analytics, and large-scale reporting workloads across the industry. Yet raw speed can be deceptive. Many teams adopt ClickHouse, see blazing benchmarks on day one, and then watch ClickHouse performance degrade as data volume grows, queries multiply, and anti-patterns creep in. The truth is that most ClickHouse performance problems are self-inflicted. They come from schema decisions, ingestion habits, and configuration choices that quietly sabotage the engine.
In this guide, the ChistaDATA engineering team walks through 20 things you should never do with ClickHouse if you care about keeping ClickHouse performance healthy at scale. Each anti-pattern includes the reasoning behind it and, where useful, a corrected example so you can apply the fix immediately. Whether you run a single node or a large sharded cluster, avoiding these mistakes will protect your ClickHouse performance and save you from painful production incidents. For deeper guidance, see our ClickHouse consulting services and the ChistaDATA engineering blog. You can also review the official ClickHouse documentation for reference.
Why ClickHouse Performance Depends on What You Avoid
ClickHouse is a column-oriented, MergeTree-based system. It is optimized for large batch inserts, sequential reads of sorted data, and vectorized query execution. When you fight against that design, the database still works, but it works slowly. Understanding the failure modes is the fastest route to consistently good ClickHouse performance. Below are the 20 anti-patterns we see most often during ChistaDATA consulting engagements.
1. Do Not Insert Rows One at a Time
The single most common way to destroy ClickHouse performance is row-by-row inserts. Every insert into a MergeTree table creates a new data part on disk. Thousands of tiny inserts create thousands of tiny parts, forcing the background merge process to work overtime and slowing every subsequent query. Always batch your inserts into blocks of at least a few thousand to hundreds of thousands of rows.
-- Bad: one row per statement, thousands of small parts INSERT INTO events (id, ts, payload) VALUES (1, now(), 'a'); INSERT INTO events (id, ts, payload) VALUES (2, now(), 'b'); -- Good: a single large batch INSERT INTO events (id, ts, payload) VALUES (1, now(), 'a'), (2, now(), 'b'), (3, now(), 'c'); -- ... up to tens of thousands of rows per batch
2. Do Not Ignore the ORDER BY Key
The ORDER BY clause in a MergeTree table defines the physical sort order and, by default, the primary index. A poorly chosen sorting key means ClickHouse cannot skip irrelevant granules, so queries scan far more data than necessary. Choose columns that appear in your most frequent WHERE filters, ordered from low to high cardinality.
CREATE TABLE events
(
tenant_id UInt32,
event_date Date,
user_id UInt64,
payload String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (tenant_id, event_date, user_id);
3. Do Not Over-Partition Your Tables
Partitioning is a data-management feature, not a query-acceleration feature. Partitioning by day, or worse by hour, on a high-volume table creates an explosion of parts and directories that cripples merges and metadata operations. In most cases, partitioning by month is more than enough to keep ClickHouse performance smooth while still allowing efficient data retention with DROP PARTITION.
4. Do Not Use Nullable Columns Everywhere
Nullable columns carry an extra bitmap to track null values, which increases storage and slows reads. If a column is almost always populated, use a default value instead of Nullable. Reserve Nullable for columns where the distinction between “empty” and “unknown” genuinely matters to your analytics.
-- Avoid unnecessary Nullable
CREATE TABLE metrics
(
metric_name String,
value Float64 DEFAULT 0, -- not Nullable(Float64)
ts DateTime
)
ENGINE = MergeTree
ORDER BY (metric_name, ts);
5. Do Not Run SELECT * on Wide Tables
ClickHouse reads only the columns your query touches. That is the entire point of a columnar engine. When you write SELECT *, you force the database to read every column from disk, decompress it, and move it through the pipeline. On a wide table this can multiply I/O tenfold. Always select only the columns you actually need.
-- Bad SELECT * FROM events WHERE tenant_id = 42; -- Good: read only what you need SELECT event_date, user_id FROM events WHERE tenant_id = 42;
6. Do Not Rely on Mutations for Frequent Updates
ClickHouse is not a transactional OLTP database. ALTER TABLE … UPDATE and DELETE are implemented as mutations that rewrite entire data parts in the background. Running them frequently generates enormous I/O and destroys ClickHouse performance. If you need mutable data, model it with ReplacingMergeTree, CollapsingMergeTree, or an aggregating pattern instead of constant mutations.
-- Instead of frequent UPDATE mutations, use ReplacingMergeTree
CREATE TABLE user_state
(
user_id UInt64,
status String,
version UInt64
)
ENGINE = ReplacingMergeTree(version)
ORDER BY user_id;
7. Do Not Forget About Data Skipping Indexes
When your queries filter on a column that is not part of the sorting key, ClickHouse may scan the whole table. Secondary data skipping indexes such as minmax, set, and bloom_filter let the engine prune granules and dramatically improve ClickHouse performance for those queries. Add them deliberately for high-value filter columns.
ALTER TABLE events ADD INDEX idx_payload payload TYPE bloom_filter(0.01) GRANULARITY 4;
8. Do Not Use High-Cardinality Columns as the First Sort Key
Placing a nearly unique column such as a UUID or a raw timestamp with millisecond precision first in ORDER BY defeats the primary index. The index sparse marks become useless for pruning because almost every granule contains a matching value. Lead with low-cardinality columns and push high-cardinality columns to the end of the key.
9. Do Not Join Huge Tables Without a Plan
ClickHouse joins are powerful but memory-hungry. The right-hand table of a JOIN is loaded into memory to build a hash table. Joining two massive tables can exhaust RAM and stall the cluster. Filter aggressively before joining, keep the smaller table on the right, and consider dictionaries for lookup-style joins to preserve ClickHouse performance.
-- Prefer a dictionary lookup over a heavy JOIN for reference data
SELECT
e.user_id,
dictGet('users_dict', 'country', e.user_id) AS country
FROM events AS e
WHERE e.event_date = today();
10. Do Not Ignore LowCardinality for Repetitive Strings
Columns that store a small set of repeated string values, such as status codes, country names, or event types, benefit enormously from the LowCardinality wrapper. It replaces string values with a dictionary-encoded representation, cutting storage and speeding up filtering and grouping. Skipping it leaves easy ClickHouse performance gains on the table.
CREATE TABLE logs
(
ts DateTime,
level LowCardinality(String),
service LowCardinality(String),
message String
)
ENGINE = MergeTree
ORDER BY (service, ts);
11. Do Not Store Everything as String
Dumping numbers, dates, and IPs into generic String columns is wasteful. Native types like UInt32, DateTime, IPv4, and Enum compress better, filter faster, and enable specialized functions. Modeling data with the correct type is one of the cheapest ways to protect ClickHouse performance long term.
12. Do Not Overuse FINAL
The FINAL modifier forces ClickHouse to merge parts at query time to return the fully collapsed or deduplicated result. It is convenient but expensive, and on large tables it can make queries orders of magnitude slower. Use it sparingly, restrict it with tight filters, or design aggregations that avoid needing FINAL at all.
-- Expensive on large data SELECT * FROM user_state FINAL WHERE user_id = 123; -- Better: aggregate to resolve the latest version explicitly SELECT argMax(status, version) AS status FROM user_state WHERE user_id = 123;
13. Do Not Neglect Compression Codecs
ClickHouse compresses data by default, but the default codec is not always optimal. Time-series columns often benefit from Delta or DoubleDelta combined with a general codec like ZSTD. Choosing the right codec per column shrinks storage and reduces the I/O that dominates ClickHouse performance for scan-heavy queries.
CREATE TABLE sensor_data
(
ts DateTime CODEC(DoubleDelta, ZSTD(1)),
value Float64 CODEC(Delta, ZSTD(1))
)
ENGINE = MergeTree
ORDER BY ts;
14. Do Not Let Parts Pile Up Without Merges
Background merges consolidate small parts into larger, more efficient ones. If your ingestion rate outpaces merges, or you disable merges, part count grows unbounded and the “too many parts” error eventually blocks inserts. Monitor system.parts, size your batches correctly, and give the server enough background pool threads to keep merges healthy.
-- Watch part counts per table SELECT database, table, count() AS parts FROM system.parts WHERE active GROUP BY database, table ORDER BY parts DESC;
15. Do Not Query Without Partition or Primary Key Pruning
Queries that omit any predicate on the partition key or the leading sort columns force a full scan. Always include the filters that let ClickHouse prune partitions and granules. A query bounded by a date range and a tenant identifier reads a tiny fraction of the data compared to an unbounded scan, which is central to good ClickHouse performance.
16. Do Not Create Too Many Materialized Views on One Table
Materialized views in ClickHouse fire on every insert into the source table. Each view adds write amplification because the inserted block is processed again for every view. A dozen materialized views on a hot ingestion table can turn one insert into a heavy cascade and throttle throughput. Consolidate logic and keep the number of triggered views reasonable.
17. Do Not Ignore max_memory_usage and Query Limits
Unbounded queries can consume all server memory and take the node down, hurting every other workload. Set sensible memory limits, use max_execution_time, and apply quotas per user. Guardrails protect cluster-wide ClickHouse performance from a single runaway analytical query.
-- Apply a per-query memory ceiling SET max_memory_usage = 10000000000; -- 10 GB SET max_execution_time = 60; -- seconds
18. Do Not Use ClickHouse as a Key-Value Store
Point lookups that fetch a single row by a unique key are exactly what OLTP databases are built for and exactly what ClickHouse is not. A columnar engine reading a single row still pays the cost of seeking into sorted, compressed columns. If your workload is dominated by high-frequency point reads, pair ClickHouse with a proper key-value store rather than punishing ClickHouse performance.
19. Do Not Skip Replication and Backups Until It Is Too Late
A cluster that has never been tested for failover is a cluster waiting to fail. While backups are about durability rather than raw speed, an unhealthy or improperly balanced ReplicatedMergeTree setup can create replication lag and uneven load that degrade ClickHouse performance. Design replication, sharding, and backups from day one, and verify recovery regularly.
CREATE TABLE events_replicated
(
tenant_id UInt32,
event_date Date,
user_id UInt64
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/events', '{replica}')
PARTITION BY toYYYYMM(event_date)
ORDER BY (tenant_id, event_date, user_id);
20. Do Not Fly Blind Without Monitoring
The final anti-pattern that destroys ClickHouse performance is a lack of visibility. If you are not watching query duration, memory, part counts, merge activity, and disk usage, problems will surprise you in production. ClickHouse exposes rich system tables such as system.query_log, system.metrics, and system.asynchronous_metrics. Instrument them, alert on them, and treat them as your early warning system.
-- Find your slowest recent queries
SELECT
query_duration_ms,
read_rows,
memory_usage,
substring(query, 1, 80) AS q
FROM system.query_log
WHERE type = 'QueryFinish'
ORDER BY query_duration_ms DESC
LIMIT 10;
How ChistaDATA Helps You Protect ClickHouse Performance
Avoiding these 20 mistakes is the foundation of a healthy deployment, but real-world systems are rarely simple. Schema design, capacity planning, sharding strategy, and query tuning all interact, and a change that helps one workload can hurt another. This is where disciplined observability and expert review pay for themselves many times over. The ChistaDATA team specializes in ClickHouse performance engineering, from initial architecture to 24×7 ClickHouse production support.
If you take only one lesson from this list, let it be this: ClickHouse rewards batch-oriented, column-aware, sort-key-driven thinking. When you insert in large batches, model your data with the right types, choose a smart ORDER BY key, partition conservatively, and monitor relentlessly, ClickHouse performance stays exceptional even as your data grows into the billions and trillions of rows.
Understanding the Root Cause of ClickHouse Performance Loss
Before diving deeper into remediation, it helps to internalize why these anti-patterns matter so much. ClickHouse stores data in a columnar layout, splitting each column into granules and compressing them independently. Every query planner decision, every merge, and every index lookup is built around the assumption that data arrives in large sorted batches and is read in bulk. When teams treat ClickHouse like a traditional row store, they break that assumption and pay for it in latency. The reason ClickHouse performance collapses so predictably is that the same handful of habits repeat across nearly every struggling deployment: too many small inserts, poorly chosen keys, unbounded queries, and no monitoring to catch the drift early.
Another subtle contributor is the mismatch between developer intuition and columnar reality. Engineers coming from PostgreSQL or MySQL naturally reach for point updates, single-row inserts, and normalized joins. Those instincts are correct for OLTP systems and disastrous for analytical columnar engines. Retraining that intuition, so the whole team thinks in batches, denormalized wide tables, and sort-key-aware queries, is often the single biggest improvement a company can make to sustained ClickHouse performance.
A Practical Checklist to Preserve ClickHouse Performance
It is useful to translate the 20 anti-patterns into a repeatable review process. Before shipping a new table or query to production, walk through a short mental checklist. Are inserts batched into large blocks rather than trickled in one row at a time? Does the ORDER BY key start with the columns most commonly used in WHERE filters, moving from low to high cardinality? Is partitioning coarse enough, typically monthly, to avoid an explosion of parts? Are string columns with few distinct values wrapped in LowCardinality, and are numeric and date fields stored with native types instead of generic strings?
Continue the review by asking whether the query reads only the columns it truly needs, whether it prunes partitions and granules with appropriate filters, and whether expensive constructs like FINAL and large JOINs are used deliberately rather than by habit. Confirm that memory and execution-time guardrails are in place so a single query cannot destabilize the node. Finally, ensure that monitoring is wired into system.query_log and system.parts so regressions surface quickly. Teams that run this checklist consistently rarely suffer the slow, mysterious decline in ClickHouse performance that plagues deployments left on autopilot.
Scaling Without Sacrificing ClickHouse Performance
As data grows from gigabytes to terabytes and beyond, the cost of every anti-pattern multiplies. A schema mistake that is invisible on a million rows becomes a production outage at ten billion rows. This is why the disciplines above are not optional niceties but the core of operating ClickHouse at scale. Sharding distributes data across nodes to parallelize scans, and replication protects against hardware failure, but neither can rescue a design that ignores the sorting key or drowns the merge scheduler in tiny parts. Good architecture and good habits compound together to keep ClickHouse performance predictable as your workload expands.
The teams that succeed with ClickHouse treat performance as a continuous practice rather than a one-time setup. They benchmark representative queries, watch part counts and merge times, review slow query logs weekly, and revisit schema decisions as access patterns evolve. That ongoing attention, combined with the avoidance of the 20 mistakes described here, is what separates a ClickHouse deployment that stays fast for years from one that quietly degrades until every dashboard feels slow.
Frequently Asked Questions About ClickHouse Performance
What is the fastest way to improve ClickHouse performance?
The fastest single improvement for most teams is switching from row-by-row inserts to large batched inserts. Because every insert creates a data part, batching dramatically reduces part count, lightens the merge workload, and immediately improves ClickHouse performance across the board. After that, tightening the ORDER BY key and eliminating SELECT * on wide tables usually deliver the next biggest gains.
Does partitioning improve ClickHouse performance?
Partitioning is primarily a data-lifecycle tool for efficient retention and bulk deletion, not a general query accelerator. Over-partitioning actually harms ClickHouse performance by creating too many parts. Partition conservatively, usually by month, and rely on the sorting key and data skipping indexes for query pruning instead.
Why does my ClickHouse query get slower over time?
Gradual slowdowns usually trace back to rising part counts from small inserts, unbounded mutations, or growing data that no longer fits the original schema assumptions. Monitoring system.parts and system.query_log will reveal the trend early. Addressing the underlying anti-pattern, rather than adding hardware, restores ClickHouse performance far more reliably.
Should I use FINAL to get correct results?
Use FINAL only when necessary and always with tight filters, because it forces expensive merge-on-read work that can devastate ClickHouse performance on large tables. Whenever possible, resolve the latest version explicitly with aggregate functions like argMax so the engine avoids the full merge.
When should I bring in ClickHouse performance experts?
If your cluster is growing quickly, if queries are slowing despite added hardware, or if you are designing a mission-critical analytics platform, expert review pays for itself. ChistaDATA provides architecture reviews, tuning, and 24×7 support focused entirely on keeping ClickHouse performance excellent at any scale.
Conclusion
ClickHouse is extraordinarily fast, but that speed is a contract. Honor the engine’s design and it will reward you with sub-second analytics over massive datasets. Violate it with row-by-row inserts, careless SELECT *, constant mutations, over-partitioning, or unbounded queries, and you will steadily destroy ClickHouse performance until the system feels sluggish and fragile. Use these 20 anti-patterns as a checklist. Audit your schemas and queries against them, fix what you find, and keep monitoring so new problems never take root. Your future self, and your users, will thank you for protecting ClickHouse performance before it ever becomes a production emergency.
