ClickHouse
ClickHouse is an open-source columnar engine built for very fast aggregation over huge tables, often serving real-time analytics directly behind a product. Interviewers ask about it where sub-second latency matters and where its MergeTree design imposes rules a warehouse would not.
on this pageshowhide
guide
overview
~1 minClickHouse is an open-source, column-oriented database built to aggregate billions of rows fast enough to sit directly behind a dashboard or a product feature. Interviewers use it to test whether you can live with its rules: data lands as immutable sorted parts, deduplication and rollups happen during background merges rather than at write time, and a table's sort key decides almost every query's cost. A good ClickHouse answer says what happens on disk, when it happens, and what the query has to do about the gap. The hub follows a table's life. [Storage engines](/topics/db-clickhouse-storage-engines) covers the MergeTree family and which variant solves which modelling problem. [Partitioning and indexing](/topics/db-clickhouse-partitioning-indexing) explains how the sort key, the partition key and skipping indices narrow a scan. [Data ingestion](/topics/db-clickhouse-ingestion) is the write path, where batching decides whether the merge pipeline keeps up. [Materialized views](/topics/db-clickhouse-materialized-views) turn inserts into pre-aggregated tables. [The SQL dialect](/topics/db-clickhouse-sql-dialect) holds the arrays, combinators and join modes that make queries efficient, and [query performance](/topics/db-clickhouse-query-performance) is where you read plans and system tables to explain a slow one. Junior rounds ask what a part is and what the engine gives you over simpler ones. Senior and principal rounds become design questions: choosing a sort key, modelling rows that change, sizing inserts, and diagnosing memory or merge pressure. Start with the MergeTree storage model and the sort key; ingestion and materialized views only make sense once parts and merges are clear.
primer
### Parts, not rows A MergeTree table is a set of **parts**: immutable directories of sorted, compressed column files. Each insert adds new ones. Nothing is updated in place. A background process keeps combining small parts into larger ones, and almost every ClickHouse rule traces back to that cycle — how often you insert, how you delete, how you correct data. ### Merges finish the job later Several engines do their real work only when parts merge: collapsing duplicates, summing measures, combining aggregate states. Until a merge touches the relevant rows, a query can see the raw, unmerged version, and merges never reach across partitions. Interviewers want you to say out loud that these engines are **eventually** consistent with their intent, and to name what a correct read does in the meantime. ### The sort key is the index `ORDER BY` fixes the physical order inside each part, and the primary index is **sparse**: one entry per block of rows, not per row. Pruning works best through a leading prefix of that key, so choosing and ordering its columns is the central schema decision. Partitioning is a coarser tool for managing data by whole units, and skipping indices help only when values are already clustered. ### Pre-aggregate on the way in A ClickHouse materialized view reacts to each inserted block and writes a transformed result elsewhere; it never re-reads the source. Paired with **aggregate states** — partial results that can be combined later — it lets a rollup table grow incrementally while staying correct across many inserts. ### Write in bulk, read wide The engine is tuned for large, infrequent inserts and scans over few columns of many rows. Frequent tiny writes, point updates and row-at-a-time lookups fight the design, and answers that treat ClickHouse like a transactional database lose the round.
- MergeTree
- The core table engine family: data stored as sorted, compressed, immutable parts that background merges combine. Indexing, partitions, TTL and replication belong to this family.
- Data part
- An immutable directory of per-column files plus index files, created by one insert block or one merge. Never modified, only merged into larger parts or removed.
- Sorting key
- The ORDER BY expression of a MergeTree table. It sets row order inside every part and, by default, the columns of the primary index.
- Sparse primary index
- An index holding one entry per granule rather than per row, small enough to stay in memory and used to skip whole granules.
- Granule
- The smallest block of rows ClickHouse reads, set by index_granularity. Pruning decisions are made per granule, never per row.
- Partition key
- The PARTITION BY expression grouping parts into independent units that can be dropped, detached or expired whole. Merges stay within one partition.
- Data-skipping index
- A secondary structure storing a per-block summary, such as min/max or a bloom filter, so blocks that cannot match a filter are skipped.
- ReplacingMergeTree
- A MergeTree variant that keeps one row per sorting key, optionally the highest version, but only once a merge brings duplicates together.
- AggregatingMergeTree
- A MergeTree variant that stores aggregate-function states and combines rows sharing a sorting key during merges; the usual target of a rollup view.
- Aggregate function combinator
- A suffix that changes an aggregate function's behaviour, such as -If for a condition or -State and -Merge for storing and finishing partial results.
- Materialized view
- In ClickHouse, an insert trigger: its SELECT runs on each newly inserted block and writes the output into a separate target table.
- FINAL
- A query modifier that applies the engine's merge logic at read time, returning merged results at the cost of extra CPU and memory.
- Mutation
- An ALTER UPDATE or ALTER DELETE that rewrites the affected parts in the background; heavy, asynchronous and unsuited to frequent row changes.
Trace one batch of events. The client sends a large INSERT; each block is split by partition and every piece becomes a new part, sorted by the table's sort key and written column by column with its sparse index. Any materialized view attached to that table runs against the same block, and its output becomes a new part in the view's target table — so one client insert can create several parts across tables. Background merges then combine parts per partition, and engines such as ReplacingMergeTree or AggregatingMergeTree apply their logic while they merge. A read walks the same structures in reverse: partition pruning removes whole groups of parts, the primary index narrows each remaining part to matching granules, skipping indices drop more, and only the requested columns are decompressed. The engine then aggregates across many threads, and on a cluster a Distributed table sends the query to each shard and merges the partial results on the node that received it. Every section attaches to that path: engines and keys define the parts, ingestion sets how fast they arrive, views and state combinators decide what is precomputed, and performance work finds which stage costs the most. A small rollup shows the ideas meeting — a sort key that serves the filter, a view writing partial states, and a read that finishes them: ```sql CREATE TABLE events (ts DateTime, site_id UInt32, user_id UInt64) ENGINE = MergeTree PARTITION BY toYYYYMM(ts) ORDER BY (site_id, ts); CREATE TABLE daily_users (day Date, site_id UInt32, users AggregateFunction(uniq, UInt64)) ENGINE = AggregatingMergeTree ORDER BY (site_id, day); CREATE MATERIALIZED VIEW daily_users_mv TO daily_users AS SELECT toDate(ts) AS day, site_id, uniqState(user_id) AS users FROM events GROUP BY day, site_id; SELECT day, uniqMerge(users) AS users FROM daily_users WHERE site_id = 42 GROUP BY day ORDER BY day; ``` The final query still groups, because several unmerged parts may each hold a partial state for the same day; the merge function combines them whether or not a background merge has run.
- Storage Engines →
Parts, merges and the MergeTree variants are the model every other section assumes, including why some engines only deduplicate eventually.
- Partitioning & Indexing →
How the sort key, sparse index and partitions decide what a query reads; the schema choice interviewers probe hardest.
- Data Ingestion →
The write path: batching, async inserts and the Kafka engine, and why too many small inserts break a table.
- Materialized Views →
Insert-time rollups and their surprises, once parts and inserts are clear.
- SQL Dialect & Functions →
Combinators, arrays, join strictness and FINAL: the idioms that make queries efficient rather than merely portable.
- Query Performance →
Reading plans and system tables to diagnose slow or memory-hungry queries, which draws on every earlier section.
Treating ReplacingMergeTree as a unique constraint: duplicates survive until a merge meets them, so a correct read needs
FINALor anargMax-style query.Describing a ClickHouse materialized view as a cached query that refreshes; a standard one only transforms newly inserted blocks and never sees existing rows.
Sending many small inserts per second and then blaming the server for "Too many parts"; batch on the client or enable async inserts instead.
Picking a fine-grained partition key such as a daily one for modest data, which multiplies parts and merges without speeding up queries the sort key already serves.
Putting a high-cardinality column first in
ORDER BYwhen most queries filter on something else; pruning depends on the leading key columns.Scheduling
OPTIMIZE TABLE ... FINALor frequent mutations as routine maintenance; both rewrite whole parts and do not scale with data volume.Adding a skipping index to a column whose values are scattered across the table and expecting it to prune anything.
Raising the memory limit first when a query fails on memory, instead of finding the high-cardinality GROUP BY or oversized join side that caused it.
Interviewers expect you to place ClickHouse among analytical systems, not only tune it. Against cloud warehouses such as Snowflake and BigQuery, the usual trade is latency and cost control for convenience: ClickHouse rewards careful schema and sort-key design with sub-second queries at high concurrency, while a warehouse accepts looser modelling and offers richer joins and updates with less tuning. Against real-time OLAP engines such as Apache Druid and Apache Pinot it competes for the same user-facing analytics workload; the conversation there is about ingestion model, operational footprint and SQL completeness. It is not a replacement for a transactional database such as PostgreSQL, which stays the system of record while ClickHouse holds a copy shaped for analysis. In practice it sits downstream of an event stream or object store. Kafka feeds it through the Kafka table engine or an external connector, files arrive from S3-compatible storage through table functions, and change data capture brings rows over from operational databases. Grafana and BI tools read from it; it runs self-hosted or as a managed service from ClickHouse, Inc. A defensible choice names the workload: append-heavy events, wide scans and aggregations favour ClickHouse; frequent row updates, strict constraints and complex multi-way joins point elsewhere.
explore
- Storage Engines6 questions
- Partitioning & Indexing7 questions
- SQL Dialect & Functions7 questions
- Materialized Views6 questions
- Data Ingestion6 questions
- Query Performance6 questions
questions
page 2 of 2In ClickHouse, why is OPTIMIZE TABLE ... FINAL a poor routine deduplication strategy?
basics
~20 sIt merges every partition down to a single part, rewriting the whole table's data on each run, and leaves parts too large for normal background merges. Use query-time FINAL or argMax instead, and keep OPTIMIZE for one-off, per-partition cleanups.
Your ClickHouse table must reflect updates to existing rows — how do you choose between ReplacingMergeTree, CollapsingMergeTree and mutations?
basics
~20 sPrefer insert-only upserts with ReplacingMergeTree plus a version column, resolved with FINAL or argMax at read time. Mutations rewrite whole parts and do not scale for frequent updates; small mutable dimensions belong in a dictionary.
When loading into ClickHouse, how do Native, Parquet and JSONEachRow formats differ in cost?
basics
~20 sNative is ClickHouse's own columnar block format and needs almost no parsing, so it is the cheapest to ingest. Parquet is columnar and compact but must be decoded and converted. JSONEachRow is the most expensive: text parsing and type conversion per field.
In ClickHouse, when should attributes live in a Map column rather than a Tuple or separate columns?
basics
~20 sUse Map only for genuinely dynamic, sparse keys: it is stored as parallel key and value arrays, so reading one key reads them all. Use a Tuple for a fixed small group of related fields, and plain columns for any attribute you filter or group on regularly.
What does a ClickHouse refreshable materialized view (REFRESH EVERY) do differently from a standard one?
basics
~20 sA refreshable materialized view re-runs its whole SELECT on a schedule and atomically replaces the target table's contents, instead of triggering per inserted block. That makes full recomputation, joins and non-incremental logic possible, at the cost of recomputing everything each period.
When would you lower index_granularity below the ClickHouse default of 8192 rows?
basics
~20 sLower it only when queries are highly selective on the sorting key and the 8192-row granule is the dominant read cost — typically narrow tables serving near-point lookups. The price is a larger in-memory primary index, more marks, and worse compression on wide scans.
When does adding a ClickHouse projection speed up a query, and what does it cost?
basics
~20 sA ClickHouse projection stores an alternative sorted or pre-aggregated copy of a table's data inside the same parts, and the optimizer reads it instead of the base data when a query matches it. It costs storage, insert and merge work, and only helps queries whose shape it fits.
How does ClickHouse's CollapsingMergeTree use its sign column to retract a row?
basics
~20 sEach row is written twice: once with sign = 1 as a state row, later with sign = -1 as an identical cancel row. A background merge cancels the pair. Queries must use sum(metric * sign) because merges are eventual.
showing 31–38 of 38