Why analytics on ClickHouse MergeTree degrades as data grows
In ClickHouse, a MergeTree table stores data not as a single array but as a set of sorted parts: rows are distributed by the sorting key, and inside each part there is a sparse primary index — marks every few thousand rows.
While the volume is small, the planner uses these marks to quickly cut off unnecessary ranges, and a query reads exactly what was requested.
As data volume grows, the mechanics break down: if filters in reports are not on the sorting key, neither the primary index nor monthly partitioning helps, and every query turns into a full table scan.
The second effect is background merges. The more inserts in small batches, the more parts need to be merged, the more often the server hits the limit of concurrent merges and responds with an error about too many parts.
Degradation of analytical queries is nonlinear: slow reports start taking minutes instead of seconds, and heavy aggregations grouped by a high-cardinality key fail on memory when the intermediate result does not fit within the per-query limit.
The storage bottleneck here is almost always the same: the table schema does not match how it is queried. Raw events lie in one layer, each report build recalculates aggregates from scratch, and data volume growth directly hits response time. Let's break down how this is addressed.
What a well-designed storage schema gives: effect on timing and volumes
In ClickHouse, the physical order of rows is set by MergeTree: the sorting key in ORDER BY determines which data blocks a query reads at all, not just how quickly it filters.
When a report queries by time and customer, a key made of these two fields turns a full scan into reading a narrow range.
On typical analytics, a daily report over a large table speeds up by an order of magnitude — from tens of seconds to fractions of a second, and this is not a one-time optimization but a property of the schema.
Partitioning by month together with a TTL policy makes storage manageable: outdated partitions are deleted entirely, without rewriting the table and without hurting inserts.
Columnar compression plus selecting a codec for the nature of the columns — separately numeric, separately dictionary-based via LowCardinality — gives 4–8x compression relative to the original volume. The registry stops growing linearly with the event stream.
Another effect is a class of failures that goes away. With a wrong key, the number of small parts grows, background merges cannot keep up with inserts, and ingestion starts failing with an error about too many parts.
Correct granularity of partitions and the key removes this scenario: inserts proceed steadily, merges fit into the background window without competing for disk.
Storage load is distributed predictably: queries read less data, so analytics does not block ingestion. In total, this is the measurable result of rebuilding the schema — stable report response time on growing volume and no degradation under peak write load.
Options for organizing analytical storage: comparison of approaches
The storage schema in ClickHouse MergeTree determines where the aggregate is calculated — on the fly on each select or in advance at write time. This affects report response time and the amount of recalculation that will be needed when a metric changes.
Below are three schemas that are realistically chosen between at the start of an analytical contour.
| Option | When it fits | Limitations |
|---|---|---|
| One wide table | Slices are still being refined, metric composition changes often | Heavy selects, expensive drill-down by small dimensions |
| Layers with aggregates | The list of reports is stable, response speed matters | Aggregates are rebuilt when logic changes |
| Separate tables per slice | Several teams with their own slices and their own loading pace | More objects, loading is duplicated, reconciliation is harder |
In practice, the options are combined: a wide table stores events, materialized views maintain aggregated layers for frequent slices, partitions are cut by event date, and record lifetime is set by a cleanup rule along the retention horizon. Then a new slice is adding a view, not reloading the entire storage.
A guideline when choosing is this: if a report query reads an order of magnitude more rows than it returns, the aggregate should be moved to a separate layer; if there are more than five slices and they live their own lives, split them into tables.
Partitioning, sorting key, and record lifetime remain common and are configured once for the entire contour.
Stages of setting up an analytics storage
Setting up MergeTree begins not with choosing an engine, but with understanding which queries to the storage are executed every day.
The order of work is practically the same for any implementation: first we measure, then we design, and only then we test under a load close to real. Below is the sequence our engineers follow.
- Audit of queries and load profile. We collect a list of regular reports and one-off selects, look at timing measurements for each, note which fields are filtered and grouped most often. The result is a list of queries that are actually worth optimizing and those that can be left alone.
- Schema design. We determine the composition of tables, column types, and separation into layers: raw data, intermediate and precomputed aggregates. This removes repeated heavy computations on every access to the reporting dashboard.
- Sorting key selection. The order of columns in the key decides whether a query falls into a narrow read range or will scan the entire table. We test options on real selects from the audit, not by intuition.
- Storage setup. We split data by dates, set lifetimes and separate rules for rarely queried periods. Storage volume stops growing uncontrollably, while history for the required horizon remains available, and old slices are read without degradation.
- Precomputation of recurring reports. We move the most frequent aggregations into materialized views so dashboards open in seconds instead of recalculating millions of rows on every access.
- Testing under real load. We run typical scenarios simultaneously, as they will be in operation: we look at response time, memory and disk consumption, behavior under peak write load. Here we also check that rollback to the previous schema proceeds without data loss.
- Handover to operation. We provide maintenance regulations, monitoring settings, and documented backup and recovery procedures so the team can support the storage independently.
Set of artifacts: table schemas, configurations, regulations
We hand over not only a working cluster, but also a complete set of artifacts by which it can be developed and maintained by your own team. Everything lies in your repository and in version control — with a change history, not 'verbal agreements with the previous contractor.'
- Table schemas — DDL scripts with the MergeTree engine: sorting key, partitioning, TTL rules, and materialized views. For each table, a comment explaining why exactly this key was chosen and what will break if it is changed.
- Storage configurations — part merge parameters, per-query memory limits, replication and distributed table settings, enabled compression codecs per column.
- Access policies — roles and permission levels: separate accounts for write services, analysts, and the on-call engineer; quotas for heavy selects; for some teams — read-only access.
- Maintenance regulations — partition merge schedule, control of part count growth, procedure for replica lag, and rules for rolling back to the previous schema.
- Data flow diagram — where data comes from and at what frequency, where it is buffered, what to do when the source is delayed or there are duplicates.
- Monitoring and alerts — list of metrics, thresholds, and the list of those who receive notifications, with priorities.
- Operations manual — typical operations, analysis of common failures, and the boundaries beyond which an engineer must be called.
- Decision log — which schema and storage options were considered, which were rejected and why: this saves time during the next enhancement.
The set is designed so that a new engineer on the team can understand the storage from the documents, not from chat messages. If expansion is needed, we come to an already prepared knowledge base.
Case: how analytical queries on streaming data were accelerated
A product service with a continuous event stream: tens of millions of records per day were stored in one ClickHouse table without a clear sorting key. Funnel reports took minutes to build, some queries timed out, and the registry grew by about a third every month.
Starting point
- Report response time — 9x above target: analysts waited for the result and recalculated slices manually.
- Share of failed queries — 12% during peak hours, meaning failures occurred exactly in the most loaded windows.
- Registry volume grew faster than the event stream itself: no one cleaned out old partitions.
What was changed
We re-marked the table: partitioning by date, sorting key by tenant and event type, so that report filters fall within block boundaries and less data is read.
Heavy aggregates were moved into materialized views with precomputation, inserts were switched to batch writes, and for historical data we applied a retention policy with automatic cleanup.
What became
Report response time decreased 12x — the export stopped being a separate analyst task. The share of failed queries went to zero, the active registry volume decreased threefold, while rollback to any of the latest partitions remains available. We add new event types using the same schema, without reloading the table.
How does MergeTree differ from other ClickHouse engines?
In ClickHouse, the engine is specified when creating a table and determines the storage format, available selects, and behavior on insert.
MergeTree is not one engine but a family with common mechanics: data is written as sorted parts, a sparse index is built by the sorting key, and background merges enlarge parts and collapse duplicates.
The Log family (Log, TinyLog, StripeLog) is simpler: a column is a separate file, there is no index and no merges. Such tables are cheap to write and suitable for dictionaries and intermediate buffers, but a select by key reads the entire file, and concurrent writes are limited.
Replication in ReplicatedMergeTree goes through a coordinator: nodes exchange not files but descriptions of parts and operations, so a lagging replica catches up via the journal.
This is not the same as distributed access: replicas are copies of the same data, while a Distributed table spreads a query across shards and collects the result.
The difference within the family is also significant: ReplacingMergeTree keeps the latest version of a row, SummingMergeTree sums values during merge, CollapsingMergeTree marks deletions.
The choice of engine changes not only speed but also which data will end up in the table after merges.
The practical criterion is simple: if you need point reads and merges — take MergeTree; if you need deduplication on insert — a variant with versions; if you need data on several nodes — replicated tables under Distributed. For small dictionaries, the Log family is honestly cheaper.
Technical doubts before rebuilding storage
Rebuilding a schema on a running storage is a question not of 'will it break or not' but of the order of operations and rollback points. Below are answers to three engineering objections that almost always come up before the start.
Will data be lost during table rebuilding?
The rebuild goes through new partitions: records are duplicated into the target structure, verified row by row using checksums, and only after convergence is read switched over. The old structure remains on disk until the end of verification — rollback takes minutes.
Data migration is performed in batches, so a separate downtime window is not required: the storage continues serving queries.
Will reports and exports have to be rewritten?
Usually not. We preserve field names and types, and when granularity changes, we add a layer of views with the previous contract for analytics.
We check report compatibility by comparing exports before and after the rebuild: discrepancies beyond tolerance are caught in the test contour, not in production.
What will happen to the running production?
We carry out the rebuild on a replica, and switch traffic during the minimum-load window. Background tasks are resource-limited so they do not take throughput away from live queries.
Implementation risks are closed in stages: first partitioning and compression, then transition to the target structure, with a pre-agreed rollback plan.
Let's discuss the task and come back with an estimate and plan
To make the implementation estimate not 'by eye' but to the point, we need your input data. Describe the task in a few words — what analytics is needed, who builds reports and how often — and attach technical context on load and queries. Send what you have: a draft schema, an example export, a list of fields.
- Where the data comes from: a queue, a log collector, a change data capture from an operational database — at what frequency and in what format events arrive.
- Load profile: how many rows per day and per month, average and peak stream, required retention depth.
- Typical queries: 5-10 of the most frequent with filters, groupings, and period — they determine the sort order in the key and partitioning.
- Data freshness: acceptable delay between an event and its appearance in a report — seconds or hours; this determines insert frequency and the set of built-in aggregates.
- Fault tolerance: whether replicas are needed, how many data copies, whether there are separate nodes for cluster coordination.
- Current state: where it will be deployed, how much disk space, what has already been tried and what bottlenecks were seen — heavy merges, bloated part count, slow table joins.
Then, based on these inputs, we assemble a work plan: table schema, key and partition order, retention and thinning policies, maintenance regulations — and an implementation estimate by stages. The next step is to discuss the task and come back with a plan and timelines.