Cruxly › System design › Databases by Name: Internals and Terms

ClickHouse and columnar OLAP: MergeTree, parts, insert batching and Cloudflare

fast scans because the data is sorted and sparse-indexed, fragile when you insert one row at a time

ClickHouse answers aggregate queries over billions of rows because every insert becomes a sorted, compressed part, and a sparse index lets a query skip most of them. This lesson goes below the one-paragraph MergeTree description in Storage engines: B-trees, LSM-trees, row vs column: parts and merges, granules, key order, merge-time semantics, and two Cloudflare cases, one design and one outage.

Parts, merges and why inserts must be batched

Every insert into a MergeTree table creates at least one new immutable part on disk, and a background process merges parts into bigger ones. The write path is therefore a merge pipeline, and the number of inserts per second decides whether the pipeline keeps up.

The MergeTree docs describe a part as the unit an insert produces, stored in either Wide format (one file per column) or Compact format (columns together, used for small parts). Parts are sorted by the table's sort key. The merges docs add that merging happens per partition: parts from different partitions are never merged together. Each merge raises a part's level by one (new parts start at level 0), merging continues until a part reaches roughly 150 GB compressed (max_bytes_to_merge_at_max_space_in_pool), a server runs several merge threads (background_pool_size), and a merged-away part stays on disk as inactive until old_parts_lifetime, which defaults to 8 minutes. These are from the ClickHouse merges page, read October 2026.

If you have read Storage engines: B-trees, LSM-trees, row vs column, the shape is the LSM one: a cheap sequential write now, a compaction bill later. The difference is that nothing in ClickHouse absorbs small writes for you by default. There is no memtable that collects a thousand single-row inserts into one flush. Each insert query is its own part unless you batch it yourself or ask the server to.

What the docs say to do. The ClickHouse bulk-insert page gives three numbers:

Rule	Value	Why
Minimum batch	at least 1,000 rows per insert	below this the part overhead dominates
Ideal batch	10,000 to 100,000 rows	fewer, larger parts, less merge work
Insert rate	about one insert query per second	higher rates only with asynchronous inserts

The page states the consequence in plain terms: too many insert queries per second and the background merging cannot keep up with the number of new parts.

Arithmetic, not a ClickHouse number. Take Cloudflare's 6,000,000 requests per second (2018 post). Inserting one row per request is 6,000,000 parts per second. Inserting 100,000-row batches is 60 inserts per second, and 10,000-row batches is 600. The same rows, three orders of magnitude fewer parts. Even the batched numbers are far above "one insert per second", which is why a fleet of consumers each flush on their own schedule and the data lands spread over many nodes (Cloudflare used 106 Go consumers; 6,000,000 / 106 is about 56,600 rows per second per consumer, again arithmetic, not a Cloudflare figure; the 6 million is the HTTP request rate and the post's 11 million rows per second counts all pipelines).

Async inserts move the batching to the server. The ClickHouse asynchronous-inserts page (read October 2026) lists the defaults:

Setting	Default	Meaning
`wait_for_async_insert`	1	acknowledge after the buffer is flushed to storage
`async_insert_max_data_size`	100 MiB	flush when the buffer reaches this
`async_insert_busy_timeout_max_ms`	200 ms (1,000 ms on ClickHouse Cloud)	longest wait before a flush
`async_insert_max_query_number`	450	flush after this many queued inserts
`async_insert_use_adaptive_busy_timeout`	on since 24.2	shortens the wait when data is frequent, lengthens it when sparse
`async_insert_busy_timeout_min_ms`	50 ms	floor for the adaptive timeout

A flush happens when the first of the size, time or query-count limits is hit.

:::warn Fire-and-forget async inserts lose errors With wait_for_async_insert=0 the server acknowledges the insert once it is buffered, before it reaches storage. The docs say errors then surface only at flush time, in server logs and the system.asynchronous_inserts table, with no dead-letter queue, so a client can keep writing into a failing pipeline without noticing. The docs recommend async_insert=1 with wait_for_async_insert=1 unless losing data is acceptable. :::

When merges lose, ClickHouse pushes back in two steps. Per partition, once active parts exceed parts_to_delay_insert (default 1,000) inserts are deliberately slowed so merges can catch up; once they exceed parts_to_throw_insert (default 3,000) the insert fails with a "Too many parts" exception. The docs say the throw default was 300 before version 23.6, and that raising it makes the error rarer at the cost of slower SELECTs and a later warning when something like low disk space stops merges. The count is of active parts in a single partition, which is one more reason not to over-partition (section 4).

:::key Verified October 2026: check on your version parts_to_delay_insert 1,000 and parts_to_throw_insert 3,000 are the defaults on the ClickHouse MergeTree settings reference as read in October 2026; a server can override them. Read the live values from system.merge_tree_settings before you write a runbook around a number. :::

What experts say in the room. The argument is "the write path is the merge path". Nobody tunes ClickHouse writes by looking at insert latency; they look at whether parts are being created faster than they are merged. The number is 1,000 minimum, 10,000 to 100,000 ideal, about one insert per second. The failure is a service that does one insert per request, which works in a demo and fails under load.

What a senior checks. Active parts per partition and the merge backlog, the insert rate per table, whether any producer still sends single rows, and, if async inserts are on, whether anyone set wait_for_async_insert=0 without writing down the data-loss budget.

Sparse primary index, ORDER BY and granules

In MergeTree the ORDER BY clause is the index. The primary index holds one entry per granule, not per row, so it is tiny, but it only helps queries that filter on the leading key columns.

The MergeTree docs set index_granularity to 8,192 rows by default. Rows in a part are stored in sort-key order and grouped into granules; the primary index stores the key values of the first row of each granule (a "mark"). Because it is one entry per granule, the docs note it is small enough to stay in memory. A granule is also the smallest unit ClickHouse reads: it must read the whole granule even for one matching row, and the docs note that reading one primary-key range can pull in up to index_granularity * 2 extra rows per data block. A granule holds between 1 and index_granularity rows depending on row width (index_granularity_bytes caps its size; check its default on your version).

ORDER BY versus PRIMARY KEY. ORDER BY defines the sort key. If you do not write PRIMARY KEY, it equals the sort key. If you do write it, it must be a prefix of the sort key. A primary key is not unique: two rows can have the same key, which is why this is not the B-tree primary key from Storage engines: B-trees, LSM-trees, row vs column. The docs also say a longer primary key slows inserts and uses more memory, while extra columns do not slow SELECTs.

The evidence for key order. The ClickHouse sparse-primary-index guide (read October 2026) uses an 8.87 million row table and shows the effect with real counts:

Filter	Key	Search used	Rows read
`UserID = 749927693`	`(UserID, URL)`	binary search, 19 steps, 1 of 1,083 marks	about 8,190
`URL = 'http://public_search'`	`(UserID, URL)`	generic exclusion, 1,076 of 1,083 marks	about 8.81 million (the whole table, nearly)
same `URL` filter	`(URL, UserID)`	binary search, 39 marks	about 319,000
`UserID = 112304`	`(URL, UserID, IsRobot)`	high-to-low cardinality	about 7.92 million
same filter	`(IsRobot, UserID, URL)`	low-to-high cardinality	about 20,300

Arithmetic: 8,870,000 / 8,192 is about 1,083 granules, matching the guide. The last two rows are the same query on the same data, 7.92 million rows against 20,300, a factor of about 390 purely from key order.

Why low cardinality goes first. Binary search on marks works when the filter is on the first key column. For a later column ClickHouse falls back to generic exclusion search, which can only skip a granule if the earlier key columns are similar across it; that works when the column in front has few distinct values (4 for IsRobot in the guide) and fails when it has millions. Key order also drives compression: the guide measured the UserID column at 11.24 MiB under one key and 877.47 KiB under the other (compression ratios 3 and 39, a ratio of about 13 between the two sizes by our arithmetic), because sorting by a low-cardinality column first puts similar values next to each other. The guide's advice for a UUID-like column is to put it last in a compound key.

Rows read versus rows returned. That is the number to watch. A query that returns 100 rows but reads 8 million is a key-design bug. The fix is rarely a bigger machine. It is a different ORDER BY, or a second copy of the data in another order (section 3).

What experts say in the room. "ORDER BY is your index, and you get one per copy of the data." The numbers are 8,192 rows per granule and one mark per granule. The failure is filtering on a late key column and silently scanning the table. Argument you will actually hear: lead with the column almost every query filters on (a tenant or customer id, per the docs' advice not to partition by it) versus lead with the lowest-cardinality column for compression. Teams often do both: the tenant first, a coarse time bucket next.

What a senior checks. Rows read versus rows returned in the query log for the top 20 queries by frequency, a key review driven by the filter columns of those queries (not by the schema), and the compressed size of the biggest columns, which tells you whether the sort order is working for compression.

Skipping indexes, projections and Cloudflare's granularity trick

When the sort key cannot serve a query, ClickHouse gives you three escape hatches: data skipping indexes, projections (another copy in a different order), and a smaller granule. Each trades storage or insert cost for fewer rows read.

Data skipping indexes are per-granule summaries stored beside the data, used for columns that are not in the sort key. The MergeTree docs list minmax (minimum and maximum per granule), set(max_rows) (up to that many distinct values per granule; 0 stores all), and bloom_filter([false_positive_rate]) with a default false-positive rate of 0.025. The index has its own GRANULARITY, default 1, meaning one index entry per table granule. An older family, ngrambf_v1 and tokenbf_v1, is marked deprecated for full-text search in favour of the text index. A skip index helps only if matching values cluster: a bloom filter on a column whose values are scattered randomly across every granule rules out nothing.

Projections are defined at part level and behave like a materialized view that the engine picks automatically. A projection stores the same rows in a different order or a pre-aggregated form, so it is the "second index" the sparse-index guide recommends. It costs storage and insert work. The MergeTree docs add one trap: projections are not used for a SELECT with FINAL, which matters for the dedup tables in section 4.

Granularity tuning, Cloudflare's version. Cloudflare's March 2018 post on HTTP analytics describes setting index_granularity per table: the default is 8,192, the raw requests table used 16,384 (its queries scan large ranges, so a coarser index is fine), and the aggregated tables used 32. The post says dropping the aggregate tables to 32 cut query latency by about 50 percent and raised throughput about three times. The reasoning is the granule arithmetic above: an aggregate table has few rows and queries fetch small slices, so reading 8,192 rows to find 20 is waste, and a finer index costs only a little more memory because the table is small. It is a Cloudflare result on their 2018 workload and ClickHouse version; treat it as a technique to test, not a default to copy.

Tool	Cost	Use when
Skip index	small index data, no extra copy; useless if values are scattered	filter column is not in the key but correlates with it
Projection	extra copy of the data, slower inserts	a second hot query shape needs a different order or pre-aggregation
Smaller granule	bigger index, more memory	small tables of aggregates, point-like reads
New table via a materialized view	full copy, you maintain it	access paths differ so much that one table cannot serve both

:::key Verified October 2026 Skip-index defaults (bloom_filter false-positive rate 0.025, GRANULARITY 1) and index_granularity 8,192 are from the ClickHouse MergeTree docs as read in October 2026. Cloudflare's 16,384 and 32 are from its 2018 post. :::

What experts say in the room. "Do not add an index to fix a key problem." The numbers are 0.025 and 8,192. The failure is a skip index that never skips, which you only see by comparing granules selected with and without it in the query plan. A fair argument is projection versus separate table: projections are automatic and keep parts consistent with the base data, a separate table with a materialized view gives you control over its own engine and TTL.

What a senior checks. For each added index, granules dropped in EXPLAIN and the storage it adds, the insert-rate impact of every projection, and whether any query using FINAL silently bypasses projections.

Partitioning, TTL and the merge-time semantics

ClickHouse resolves several things only when parts merge: TTL, deduplication in ReplacingMergeTree, and summing in SummingMergeTree. Between merges the table can show expired rows, duplicates and unsummed rows, and your queries must either tolerate that or pay at query time.

Partitioning is not an index. The MergeTree docs are blunt: most tables do not need a partition key, and partitioning does not speed up queries (ORDER BY does that). If you partition, monthly granularity is generally enough, the docs say not to over-partition, and not to partition by client id or name; put that column first in ORDER BY instead. The mechanism is the one from section 1: merges happen within a partition, so a partition per client means parts that can never be merged with each other and a part count that grows with the number of clients. A partition does have one genuine use: dropping a whole partition is cheap, which is how you expire monthly data without rewriting parts.

TTL is applied during merges. The docs say TTL expiry is evaluated during background merges; when ClickHouse detects expired data it schedules an off-schedule merge, and merge_with_ttl_timeout controls how often these run (a low value causes many resource-heavy merges; check the default on your version). The page also says expired rows can still appear in SELECT results until a merge removes them. So TTL is a storage-reclaim mechanism, not a guarantee of deletion at a time. If a compliance rule says data must be unreadable after 30 days, a TTL alone is not that control.

ReplacingMergeTree deduplicates at merge time, by ORDER BY key. Per the ReplacingMergeTree docs:

Duplicates are rows with the same ORDER BY key (not PRIMARY KEY). "Deduplication occurs only during a merge", at an unpredictable time, so duplicates can remain; OPTIMIZE can force a merge, but it reads and writes a lot of data and the docs advise against relying on it.
Without a ver column the last inserted row wins; with ver, the highest version wins, and on a tie the last inserted row wins.
is_deleted (1 for a delete, 0 for state) works only together with ver. Raise the version on every change, deletes included. By default the delete row is kept so that late-arriving lower-version rows are still superseded; physically removing delete rows needs the experimental cleanup setting or OPTIMIZE ... FINAL CLEANUP.
FINAL deduplicates at query time. The docs' example shows a count of 200 without FINAL and the correct 100 with it (the docs say the plain count varies with merges). The page does not quantify the cost of FINAL, so no number is given here.

The right mental model is "eventual dedup, never a uniqueness constraint". Two concurrent writers can both insert the same key and both rows exist until a merge. A COUNT(*) without FINAL can be wrong in the high direction; that is the standard bug report. Choices: pay for FINAL, aggregate explicitly in queries to pick the latest version, or insert only immutable facts and dedup upstream.

SummingMergeTree and AggregatingMergeTree are the same idea applied to numbers: rows with the same key are combined at merge time. Cloudflare's design, covered in section 5, is built on that.

:::warn Counts that are wrong until a merge Any table engine in the Replacing, Summing or Aggregating family can return partially merged data to a plain SELECT. Treat every dashboard query on such a table as needing either FINAL, an explicit aggregate (sum, argMax), or an accepted error. Do not discover this from a customer's invoice. :::

What experts say in the room. "ClickHouse resolves things at merge time." The number is monthly at most for partitions. The failure is a count that is high until someone adds FINAL. The argument is whether to model updates as ReplacingMergeTree at all, or to append events and compute the latest state in queries or a materialized view.

What a senior checks. Merge lag (how old are the unmerged parts?), TTL lag (oldest row past its expiry time), the number of partitions, and a written statement of which tables are eventually deduplicated and what each consumer must do about it.

Cloudflare 2018: choosing ClickHouse for HTTP analytics

Cloudflare replaced a Postgres plus Citus pipeline with ClickHouse in 2018 because the old design topped out at about 15 queries per second and the new one stored the same year of data in roughly one fifteenth of the space. The numbers are Cloudflare's own estimates for 2018, on a version and workload that have both moved on.

The source is Cloudflare's March 2018 post, "HTTP Analytics for 6M requests per second using ClickHouse". A later Cloudflare and ClickHouse post exists (2026); nothing from it is used here.

The old pipeline, as the post describes it. Kafka (106 brokers, replication factor 3, 106 partitions) fed 106 Go consumers, one per partition, which aggregated and wrote rollups into a single Postgres instance (no replicas or backups, per the post) that rolled minutes into hours, days and months. A Citus cluster (a main node plus 11 workers, replication factor 2) served the Zone Analytics API. The post says the API struggled beyond 15 queries per second, so hard rate limits were set for the largest users. Cloudflare tried Flink first, and the post says it could not keep up with the per-partition ingestion at 6 million requests per second.

Why ClickHouse. The post lists column orientation, horizontal scalability, fault tolerance, performance and space efficiency, and notes the DNS analytics pipeline already ran on it. Cloudflare engineers contributed upstream: topK, sumMap, and a SummingMergeTree optimisation the post says made map merges about 7x faster.

Scale after the move (2018).

Quantity	Value
Cluster	36 ClickHouse nodes, replication factor 3
Request rate	6 million per second average, up to 8 million peak
Inserted rows	11 million per second across all pipelines, about 47 Gbps
Consumers	106 Go Kafka consumers (no longer aggregating)
Query load	about 40 per second average, peaks near 80, load tests about 150
Migration	one-off Go ETL, couple of days, over 60 billion rows from Citus, with consistency checks

Arithmetic, not Cloudflare's: 11,000,000 rows per second over 36 nodes is about 305,000 rows per second per node if spread evenly, ignoring replication; 47 Gbps over 11 million rows is about 534 bytes per inserted row on the wire.

The storage comparison, one year at 6 million requests per second, at $100 per TiB (Cloudflare's estimate).

Format	Average record	One year	Cost
Cap'n Proto, raw	1,630 B	273.93 PiB	$28M
Cap'n Proto with zstd	360 B	60.5 PiB	$6.2M
ClickHouse, replication factor 3	36.74 B	18.52 PiB	$1.9M

Our arithmetic: 273.93 / 18.52 is about 14.8, and 60.5 / 18.52 is about 3.3. The ClickHouse figure includes three replicas, so one copy is about 6.2 PiB. Three reasons the ClickHouse figure is smaller, all visible in the post: it stores pre-aggregated rollups rather than raw requests (the average "record" is 36.74 bytes), columnar storage compresses similar values together, and sort order puts similar values adjacent (section 2). The 14.8x is not "ClickHouse compresses 14.8x better"; most of it comes from not storing the raw rows.

The schema decision. Cloudflare's first design was eight materialized views on a ReplicatedAggregatingMergeTree, one per breakdown (totals, colo, status, content type, country, threat type, browser, IP class); joining them gave a query over 300 lines, so it was rejected. The chosen design was SummingMergeTree with nested Map columns (similar to the earlier Postgres hstore layout), a new sumMap function for cross-shard summing, and a separate ReplicatedAggregatingMergeTree view for uniques because Summing does not merge aggregate states. The post also splits out a Colo view, since Colo queries were about 5 percent of usage and dashboard queries 95 percent, so the Colo key would not slow the dashboard. Pre-aggregating at insert time is how it served tens of heavy queries per second on a multi-million-row-per-second stream.

What experts say in the room. The argument: pre-aggregation plus compression beats raw storage by an order of magnitude, and that is a modelling decision, not a database setting. The honest caveat is concurrency: this system serves tens of queries a second to dashboards, not thousands to end users. The failure is unbounded query concurrency or per-query memory.

What a senior checks. Query concurrency limits and per-query memory limits, replica lag, and what happens when a node is replaced: the post notes ClickHouse did not throttle recovery in 2018, which complicated their node-by-node replacement. Check the current docs for your version rather than assuming it still holds.

Cloudflare 18 November 2025: when a ClickHouse permissions change took down the proxy

On 18 November 2025 a database permissions change made a metadata query in ClickHouse return duplicate rows, a generated Bot Management feature file doubled in size and passed a 200-feature limit, and the proxy that loaded it panicked. The database kept running. The contract between the database and its consumer broke.

The source is Cloudflare's post "Cloudflare outage on November 18, 2025". All times are UTC, from that post.

Mechanism. Distributed tables in the default ClickHouse database read underlying tables in a database called r0, through a shared system account. Users already had implicit access to r0. At 11:05 Cloudflare made that access explicit so users could see r0 metadata in system tables such as system.columns, and rolled the change out gradually across the cluster. A feature-generation job ran a query on system.columns for the http_requests_features table, selecting name and type, ordered by name, and, the post stresses, not filtering by database name. After the grant, the query also returned the columns of the r0 tables, which the post calls "effectively more than doubling the rows in the response". Each row is one feature, so the generated feature file ("that feature file, in turn, doubled in size") carried a large number of duplicate feature rows, and it was propagated to every machine.

The limit. The Bot Management module preallocates memory for a fixed number of runtime features. The post says the limit was 200 and about 60 were in use. The oversized file exceeded it, and the proxy (the new FL2 engine, Rust) hit an unhandled error: the post quotes called Result::unwrap() on an Err value, a panic, which surfaced as 5xx errors for customers on FL2. Customers on the old FL engine saw no errors but got a bot score of zero on all traffic.

:::key How the file passed 200, in the post's own numbers The limit was 200 features, "well above our current use of ~60 features". The permissions change made the query return more than double its rows, the file doubled in size, and the post says "the bad file with more than 200 features" is what hit the limit. Doubling 60 gives about 120, so the post's figures do not by themselves give the bad file's exact count; retell only the numbers it states (the duplicated rows, the 200 limit, "more than 200"). :::

Why it flapped. The file was regenerated every five minutes by a query that ran on whichever cluster node handled it. While the grant change was only on part of the cluster, a run on an updated node produced a bad file and a run on a not-yet-updated node produced a good one, so the network recovered and failed repeatedly. The post says this looked like an attack at first. Once every node had the change, every run produced a bad file and the system settled into failing.

Timeline.

Time (UTC)	Event
11:05	Access-control change deployed
11:20	Significant failures to deliver core traffic begin
11:28	Customer HTTP errors seen as the bad file reaches customer environments
11:31, 11:32, 11:35	First automated test detects it, investigation starts, incident call created
13:04 to 13:05	Workers KV and Access bypass reduces impact
13:37	Work focuses on rolling back to a last-known-good feature file
14:24	Creation and propagation of new feature files stopped, old file tested
14:30	Main impact resolved, correct file deployed globally
17:06	All services normal

Arithmetic: 11:20 to 17:06 is 5 hours 46 minutes, the total incident duration; from the 11:05 change it is 6 hours 1 minute; 11:20 to 14:30, the main-impact window, is 3 hours 10 minutes.

What Cloudflare said it would change. From the post: treat Cloudflare-generated configuration files with the same hardening as user-generated input, add more global kill switches, stop core dumps and error reports from overwhelming system resources, and review failure modes across all core proxy modules.

Transferable lessons (not from the post). First, a schema or permission change in a database is an API change for everything that queries its metadata; review it against consumers, not only against the database. Second, an unfiltered query against a shared system table is a latent contract, because its result set can change when someone else's grant changes. Third, a hard cap that fails by panicking turns "too big" into "down"; a loader that keeps the last good file and alerts would degrade instead. Fourth, partial rollouts of a change in a distributed system produce flapping, which looks like an attack or a network fault and costs diagnosis time.

What experts say in the room. "The database was fine; the contract with its consumers was not." The numbers are 200 against about 60 and 5 hours 46 minutes. The failure is an unfiltered query plus a hard limit plus an unhandled error. The argument you will have is who owns the check: the team that changes grants, the team that generates the file, or the team that loads it. The answer in a mature organisation is all three.

What a senior checks. Does every generated config have a size and cardinality check and a last-known-good fallback? Is there a canary that loads the generated artifact before it fans out? Do metadata queries name the database explicitly? Are schema and permission changes reviewed against downstream readers?

Running it: concurrency, memory and recovery

ClickHouse is built for a few heavy queries, not thousands of light ones, and its operational risks are concurrency, per-query memory and recovery traffic. Capacity planning starts from queries per second and rows read per query, not from rows stored.

Two Cloudflare numbers set the scale for "few". The old Citus design struggled beyond about 15 queries per second. The ClickHouse design averaged about 40, peaked near 80 and was load-tested to about 150 (March 2018 post). That is a very large dataset served at tens of queries per second, which is the typical analytical shape. If your product needs 5,000 queries per second from end users, ClickHouse is not automatically the wrong answer, but the design question changes: you precompute, cache, and put a serving layer in front, which is what pre-aggregation in section 5 is. This is a design question, not a claim by any company.

Reading the cost of a query. The cost is roughly granules read times the work per granule. The sparse-index numbers in section 2 are the model: 8.81 million rows against 20,300 for the same logical query. At 1,000 concurrent queries the first would saturate any cluster and the second would not. Projects that "tested fine" at 5 concurrent queries often fail on key design first, not on cluster size.

Replication and recovery. Cloudflare ran every table at replication factor 3 (36 nodes) and said in 2018 that ClickHouse did not throttle recovery, which complicated replacing nodes one at a time. The post does not say more than that, so the risk to treat as real is recovery traffic competing with live queries on the surviving replicas. That is my inference from the post, a 2018 one; check what your version does and test a replacement under load before you need one.

Batching and durability. The two knobs that decide data loss are section 1's wait_for_async_insert and what your Kafka consumers acknowledge. Cloudflare's design put Kafka in front: consumers read, batch and insert, and offsets advance after the insert. That is a common pattern, not something the post details beyond the consumer count, so verify your own acknowledgement order.

Where this fits in a larger design. Storage engines: B-trees, LSM-trees, row vs column says mixed workloads usually use two engines with replication between them. ClickHouse is normally the second one: the transactional store stays the system of record, a change stream or Kafka feeds ClickHouse, and a stale or duplicated analytic row is acceptable because the source can rebuild it. Using it as the only copy of money data is a different decision, because dedup is eventual, and deletes are merge-time, none of which is the guarantee ACID, precisely describes. That is the design question, with no company attribution.

What experts say in the room. The argument is analytic engine as a copy versus as a source of truth. The numbers: 15, 40, 80, 150 queries per second from Cloudflare's 2018 post. The failure: unbounded concurrency or a query that reads the world.

What a senior checks. Concurrency and per-query memory limits, a rehearsal of replacing a node under load, the backfill plan if the copy diverges from the source, and a written freshness expectation for every dashboard.

How to say it in the interview

"ClickHouse is a column store whose MergeTree engine writes each insert as an immutable sorted part and merges parts in the background. So I batch: the bulk-insert docs say at least 1,000 rows per insert, ideally 10,000 to 100,000, and about one insert per second, or I turn on async inserts. The primary index is sparse, one entry per 8,192-row granule by default, so ORDER BY is my real index and the low-cardinality column goes first. Deduplication and TTL happen at merge time, so I never treat them as constraints. Cloudflare ran 36 nodes on 6 million requests per second in 2018, and in 2025 a metadata query against it doubled a config file and took the proxy down."

Checklist

Every producer batches to 10,000 or more rows per insert, or uses async inserts with wait_for_async_insert=1
Active parts per partition and merge backlog are graphed and alerted
ORDER BY was chosen from the filter columns of the top queries, low cardinality first, and rows read versus returned is tracked
Every extra skip index or projection was justified by granules skipped in the query plan and its storage and insert cost
Partitioning is none or monthly, never by customer
TTL lag is monitored and no compliance control relies on TTL alone
Each Replacing, Summing or Aggregating table has a written rule: FINAL, explicit aggregate, or accepted error
Query concurrency and per-query memory limits are set, and a replica replacement was rehearsed under load
Any file or config generated from ClickHouse query results has size, count and schema validation plus a last-known-good fallback
Metadata queries against system tables name the database explicitly, and grants changes are reviewed against their consumers

Practice this interactively