ClickHouse Concepts

• The MergeTree family is a log-structured design applied to analytics. An insert writes a new immutable part whose rows are sorted by the table's ORDER BY. A background process merges parts into larger sorted parts, exactly as an LSM tree merges runs (LSM Trees: Why Some Engines Favour Writes, Compaction: The Merge That Pays for Cheap Writes).
• The primary index is sparse: one entry per granule of a fixed row count, holding the sort-key values at that granule's boundary. A query with a predicate on a prefix of the sort key binary-searches this index down to a range of granules and reads only those. Because the index is sparse it fits in memory even for very large tables, and because it is sparse it can never address a single row (The Index, Derived from First Principles). So the sort key is the index. There is no secondary structure doing the heavy lifting; skipping indices exist and are best-effort hints layered on top. This is the single most important structural fact about the system and the reason the sort order dominates its design (Clustering and Sort Order).
• PARTITION BY is a data-management concept here, not the primary performance one: it decides which rows can be dropped or detached as a unit and which parts may merge together. Using it the way a warehouse partition column is used — fine-grained, to make queries fast — produces many small non-mergeable parts and makes things worse (Partitioning).
• Sorting also drives compression. Adjacent rows in sort order have similar values, so run-length and delta encodings have something to work with; a column that varies randomly between adjacent rows compresses in proportion to its own entropy and no more (Why Analytical Data Compresses, Dictionary, Run-Length, Delta and Bit Packing).
• The specialised engines — replacing, summing, aggregating, collapsing — perform their transformation at merge time. Rows with the same sort key are collapsed when the parts holding them are merged, and not before. A query issued between the insert and the merge sees the pre-collapse state (Deduplication).
• Execution is vectorized: operators process batches of column values rather than rows, which is what lets a scan saturate memory bandwidth and use the CPU's wide instructions (Vectorized Execution, Columnar Execution). A cluster adds sharding and replication. A distributed table fans a query out to shards and merges the results; a shard is where the data lives, and the sharding key is a placement decision with the same character as a distribution key elsewhere (Partitioning and Sharding).

data/store/table=events/
  all_202608_1_1_0/          <- one insert: one part, sorted by ORDER BY
    tenant_id.bin  event_type.bin  event_time.bin  payload.bin
    primary.idx              <- one entry per granule, not per row
  all_202608_2_2_0/          <- next insert: another part
  all_202608_1_2_1/          <- background merge of the two, still sorted
  all_202607_1_9_2/          <- a previous month: separate partition,
                                 never merged together with 202608

granule = a fixed number of consecutive rows
primary.idx holds the sort-key values at each granule boundary,
so a predicate on a prefix of the sort key binary-searches to a
granule range and the engine reads only those granules.

• Choose the sort key from the predicates your queries actually carry, ordered from lowest cardinality to highest: the tenant or dimension that almost every query fixes, then the next most common filter, then time. This one decision does more for query cost than everything else combined (Clustering and Sort Order). Keep the sort key short. Every additional column narrows granule boundaries a little and costs compression and merge work; three or four columns is a normal answer and ten is a smell.
• Insert in batches. One large insert per interval creates one part; a thousand single-row inserts create a thousand parts and hand the merge machinery a problem it cannot win (Batch Ingestion).
• Partition coarsely — a month is a common answer — and treat partitioning as the unit of retention and deletion rather than as a query optimisation (Data Retention).
• Model wide and denormalized. This engine is built for large flat event tables with dimensions folded in or served from in-memory dictionaries, not for a normalised star schema joined at query time (Denormalization on Purpose, Star Schema).
• Treat merge-time engines as an eventual transformation. If a consumer cannot tolerate seeing pre-collapse rows, write the query so it aggregates correctly regardless — deduplicate in the query, or read through a view that does — rather than assuming the merge has happened (Idempotent Data Pipelines).
• Give the ingestion path a buffer that batches on your behalf if producers cannot. A small aggregating stage in front is far cheaper than tuning a server drowning in parts (Streaming Ingestion).

• An insert of a batch is atomic at the part level: the part becomes visible in full or not at all, so readers never see half a batch (Atomic Publish).
• There are no multi-statement transactions across tables in the sense an OLTP database means it. A pipeline that needs several tables to change together has to design for that itself (Transactions and ACID).
• Merge-time engines guarantee that duplicates or aggregates will eventually be collapsed. They do not guarantee when, and there is no point at which a query is promised to see the collapsed state unless it asks for it explicitly (Deduplication).
• Ordering within a part is guaranteed by the sort key. Ordering of *results* is guaranteed only by an explicit ORDER BY in the query — the storage order is a physical property, not a result contract (Event Time).
• On a cluster, replication is asynchronous by default, so a read served by a replica may not include the most recent insert. That is a freshness property to design around, not a defect (Replication and Read Scaling).
• Completeness and correctness are guaranteed by nothing here. The engine aggregates whatever it holds, very quickly (Data Quality).