ClickHouse answers one question at a scale where most engines stop answering: given a few billion rows, what does the aggregation look like right now. The answer arrives in seconds because the engine was designed as a column store with vectorized execution, and it refuses to be something else just as deliberately. Teams that deploy it for analytics get a system that turns nightly reports into interactive dashboards, and teams that deploy it as the primary store for an operational workload get a system that treats their updates as foreign objects and their point lookups as embarrassments. This article builds both halves honestly: the column mechanics that produce the speed, the table engine vocabulary that encodes query patterns, the mutation model for updates, the batch discipline for inserts, and the boundary where a row store must stand in front.
Column storage and why the scan is an order of magnitude faster
A row store reads a row whole, which is the right shape when the transaction needs the whole entity. A column store reads a column whole, which is the right shape when the query needs one field of every row, and analytical queries are exactly that shape. A query that averages response time over a billion request rows reads one column of four, and the bytes moved shrink accordingly. The second multiplier is compression: values in a column share type and often share patterns, timestamps are close together, status codes repeat, and the compression codecs shrink typical analytical data by a factor of ten or more, which turns disk bandwidth into effective bandwidth. The third is vectorization: the engine processes values in blocks through tight loops, which modern CPUs execute at rates row-at-a-time interpreters cannot approach.
The structure underneath is the MergeTree family. Data arrives into parts, sorted by the table's declared key, and background merges fold parts into larger parts without rewriting history. The declared sort key acts as a sparse index, the engine skips blocks whose key ranges cannot match a predicate, and partitions declared at table creation act as a coarser skipping layer over whole blocks of time. The combination is why a query over one day of a year of metrics reads one day: the partition is skipped to, the sort key skips within it, and the columns read only the fields named. The same query on a row store is a full scan with a filter, and the difference is not a tuning constant, it is the architectural gap this article is about.
The table engine choice that encodes the query pattern
The DDL is where the query pattern is written down, and the two declarations that matter are the sort key and the partitioning. A metrics table is the canonical example:
CREATE TABLE request_metrics
(
service LowCardinality(String),
ts DateTime,
latency_ms UInt32,
status UInt16
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (service, ts)
TTL ts + INTERVAL 90 DAY;The ORDER BY is the index: queries that filter by service and time range scan narrow key ranges, and queries that do not mention the leading column scan everything, which is the earlier Cassandra lesson restated for a different engine. The PARTITION BY bounds both the pruning and the merge work, monthly partitions keep part counts modest while giving time bounded queries a cheap first cut. The TTL is the retirement policy, rows simply cease to exist on schedule, with no delete job anywhere, which on append only data is the only sane retirement mechanism.
The engine family encodes semantic choices about duplicates and aggregation. Plain MergeTree keeps every row as inserted. ReplacingMergeTree deduplicates rows by key in the background, and the deduplication is eventual: merges collapse what has merged so far, queries that must see the collapse use FINAL or a GROUP BY, and the pattern is right for upsert shaped feeds where exactly now is not a promise. SummingMergeTree pre-aggregates numeric columns on merge, the right engine for counters that arrive as deltas and are queried as totals. CollapsingMergeTree and its versioned variant handle streams that emit pairs of change rows. Each choice is a commitment to a query shape, and the wrong engine is a rewrite, which is why the query list from the first section of the article about access patterns belongs in front of this DDL before it is written.
Mutations and the asynchronous cost of UPDATE
The engine treats UPDATE and DELETE as mutations, operations that rewrite the affected parts in the background, and this treatment is the clearest statement of what the engine is for. A mutation is submitted and returns before any data has changed:
ALTER TABLE request_metrics
UPDATE latency_ms = 0 WHERE status = 0;The statement enqueues, the parts rewrite asynchronously, the progress is visible in the system.mutations table, and the query keeps returning the old values until the rewrite passes them. Mutations are heavy by construction: each one reads and rewrites whole parts, holds merge bandwidth that ordinary background merges need, and on a large table a single unfiltered mutation is an overnight statement. Used for the rare correction, the backfill of a new column, or a compliance scrub, mutations are the right tool. Used as a routine update path, they stack into a queue the merges cannot drain, and the monitoring shows exactly that, a mutation queue that grows while part counts grow behind it.
Deletes have the cheaper cousin: ALTER TABLE DELETE, the lightweight delete, marks rows with a mask that filters them from reads without rewriting parts immediately, at the cost of some read overhead until background processing consolidates. The operational rule that survives review is that neither mechanism is an update path. The data that changes routinely belongs in a row store, and the analytical store receives the resulting immutable facts, which is the boundary this article closes with.
Small inserts and the parts problem that punishes naive pipelines
Every insert produces a part, and parts must merge. The engine absorbs bursts happily, but a producer that inserts one row at a time, or one thousand rows per second in tiny packets, produces parts faster than merges can fold them, and the failure is visible and named: too many parts, an error the engine raises when a partition's part count crosses its safety threshold, after performance has already degraded. The discipline is batching, and the numbers are generous: inserts in the hundreds of thousands of rows are routine, and the guidance in practice is to aim for batches measured in at least tens of thousands of rows, or at minimum one insert per second per table rather than per event.
Pipelines reach that batching through accumulation. An application buffer, a Kafka topic with a batch consuming bridge, or the engine's own async_insert mode, which collects small inserts server side and writes them as proper parts:
SET async_insert = 1;
SET wait_for_async_insert = 0;The mode trades a small write latency for part hygiene, and it is the pragmatic answer for fleets of producers that cannot be re-educated. The monitoring belongs on the system.parts table and the merge counters in system.metrics, because part count per partition is the leading indicator of every insert path disease, and it degrades quietly long before the error makes it official.
The workloads that belong to a row store in front
The engine's refusals are consistent, and they outline the architecture that uses it well. Point lookups by identifier are the first refusal: reading one row by key through a column store pays the deserialization of blocks meant for scans, and a primary key lookup that a row store answers from a B-tree in microseconds is a poor fit. High frequency updates of existing rows are the second refusal, the mutation model from earlier. Transactions are the third: there is no multi statement transaction with rollback, the unit of atomicity is the insert, and a batch either lands or does not. Per row CRUD with arbitrary predicates, the operational spine of most products, is therefore the fourth refusal by composition.
The architecture that respects all four refusals is the duet: the operational database holds entities and transactions, and the analytical store holds the immutable, aggregated history. Data flows one way, batched, through a pipeline that transforms operational changes into analytical facts. ClickHouse queries the events, the logs, the metrics, the behavioral records, and the row store queries the account, the order, the balance, and nothing in this article argues with that division. The teams that violate it do not get a slower system, they get an architecture fighting itself: a mutation queue standing in for an update path, and a too many parts error standing in for a write load the design never promised to absorb.
Materialized views as the pre aggregation that answers twice as fast
Between the raw table and the dashboard sits the materialized view, the mechanism that answers the hardest queries before they are asked. A materialized view in this engine is a continuous transformation: every insert into the source table feeds the view's target table, and the target is an ordinary table with its own engine, its own sort key, and its own storage. The dashboard reads the small pre aggregated table instead of folding billions of rows on every load:
CREATE MATERIALIZED VIEW metrics_daily
ENGINE = SummingMergeTree
ORDER BY (service, day) AS
SELECT service, toDate(ts) AS day,
count() AS requests,
sum(latency_ms) AS total_latency
FROM request_metrics
GROUP BY service, day;The pipeline does the aggregation once, at write time, and every read of metrics_daily touches thousands of rows instead of billions. The trade is the standard one from the NoSQL discussion: the view is a commitment to a query shape, a new question means a new view and a backfill, and the views that nobody queries cost merge bandwidth for nothing. Joins deserve their sentence in the same breath: the engine executes them, hash joins at memory speed, but it prefers denormalized wide tables where the fact carries its dimensions, and the pipeline that joins at ingest time ships faster dashboards than the one that joins per query.
Operations and the adoption checklist
The operational surface of a production deployment is modest once the design constraints are respected. Replication is the ReplicatedMergeTree family with Keeper coordinating replica sets, and the replication is of parts, asynchronous, and tolerant of the batch timing that the engine expects. Monitoring watches merge bandwidth, part counts per partition, mutation queue depth, and the query latency percentiles, and the alerts on those four cover the failure modes this article walked through. The adoption review that prevents all of them fits in one list:
- Write the dominant queries before the DDL and let them dictate ORDER BY, PARTITION BY and the engine family;
- Keep updates and deletes rare, scheduled, and reviewed, never a routine application path;
- Batch inserts by design, producers or async_insert, and monitor part counts per partition;
- Use TTL for retention instead of delete jobs, and size the retention to the partition grain;
- Keep a row store in front of operational data and reserve the column store for immutable facts;
- Rehearse the restore and replica rebuild, because parts are the recovery unit and the drill is short.
The engine earns its place by being extreme about one shape of question, and its refusals are the same extremity read from the other side. The team that points it at analytical volume gets interactive answers where the alternatives run overnight, and the team that points it at an OLTP workload gets a lesson in column storage, delivered as a mutation queue that never drains.
The replication story deserves its closing sentence in the operations context, because it mirrors the insert discipline. ReplicatedMergeTree tables attach to a Keeper coordinated cluster, inserts land on one replica and replicate as parts, and the replication is asynchronous by the same design logic as the whole engine, analytical reads tolerate milliseconds of lag while writes stay fast. The cluster that follows the batch discipline, the mutation restraint and the part monitoring from this article inherits a repair story that is equally quiet, because parts are immutable, a replica rebuild is a copy of parts, and the rebuild runs while the cluster serves queries without anyone's Sunday being involved.