# ClickHouse for system design interviews > ClickHouse is a column-oriented SQL database built for analytical queries over large datasets. It fits event analytics, observability, and reporting systems where you ingest many records and repeatedly filter, group, and aggregate them rather than update individual rows. Source: https://sysround.com/tech/clickhouse Updated: 2026-11-05 ## How it works ClickHouse stores columns separately, so a query can read only the fields it needs. Compression reduces storage and read traffic, while vectorized execution processes batches of values instead of interpreting one row at a time. This favors aggregations over many records, not transactional point lookups. The MergeTree family is the core storage model. Inserts produce immutable data parts, with rows sorted within each part by the table's ORDER BY key. Background merges combine parts, so batching writes matters: many tiny inserts create excessive parts and merge work. A MergeTree primary index is sparse: it records key values at intervals rather than pointing to every row. Queries use the sort order and index to skip granules, then read matching column data. Partition pruning and optional data-skipping indexes can reduce reads further, but neither substitutes for a sort key aligned with common filters. Incremental materialized views process newly inserted blocks and write transformed results into another table. AggregatingMergeTree can store mergeable aggregate states, such as partial counts or distinct-user sketches. These views are ingestion-time transformations, not automatically maintained relational views that recompute when source rows change. In a conventional self-managed cluster, a Distributed table routes inserts and fans queries out to shard-local tables; the coordinator combines partial results. ReplicatedMergeTree replicates parts within each shard using coordination metadata in ClickHouse Keeper or ZooKeeper. ClickHouse Cloud commonly uses SharedMergeTree with shared object storage, so its storage and compute scaling model differs from this topology. ## What it gives you in a design - **Columnar analytical SQL.** Supports filtering, grouping, joins, window functions, and analytical aggregates. It is useful when many queries scan a subset of columns across a large event history. - **Sort-key-based data skipping.** A well-chosen ORDER BY key reduces the data read for recurring query patterns. For tenant-scoped time-range queries, explicitly discuss whether tenant ID or time should lead the key. - **Batched and asynchronous ingestion.** Clients can send batches directly or use asynchronous inserts to buffer small writes on the server. Acknowledgment settings determine whether success means buffered acceptance or a completed flush, so they belong in the durability discussion. - **Incremental materialized views.** Precompute rollups during ingestion to reduce repeated aggregation work. Keep raw events when you need to rebuild results after logic changes, correct late data, or investigate discrepancies. - **Exact and approximate aggregates.** Offers both exact and approximate approaches for tasks such as distinct counting and quantiles. State the acceptable error and memory budget rather than choosing an approximate function without explaining the trade-off. - **Retention and storage lifecycle rules.** TTL rules can expire data or move it between configured storage tiers. Expiration is background work rather than a precise deletion deadline, which matters for retention and privacy requirements. - **Analytical joins and dictionaries.** Joins and dictionaries support enrichment with dimension data. Their memory use, distribution, refresh behavior, and freshness requirements determine whether to join at query time or denormalize during ingestion. ## Scaling, replication and failure Treat sharding, table partitioning, and sorting as separate decisions. Sharding distributes data across servers; PARTITION BY divides a table into manageable groups of parts; ORDER BY controls physical sorting within parts. A tenant-based shard key can localize queries, but a large tenant can overload one shard. Distributed queries execute work on shards and combine results at a coordinator. Mergeable aggregates often scale well, while high-cardinality GROUP BY operations, large joins, and global sorts can consume substantial memory and network bandwidth. Adding shards does not eliminate coordinator bottlenecks or automatically rebalance existing data in a conventional cluster. ReplicatedMergeTree replication is asynchronous by default, and reads from another replica can lag a successful insert. Insert quorum and sequential-consistency settings can strengthen specific read/write paths at an availability or latency cost, but they do not create general cross-shard serializable transactions. Define the read path and acknowledgment boundary before promising read-after-write behavior. During failures, a Distributed table may have unavailable shards, delayed delivery, or replica lag. Decide whether incomplete results are acceptable, and expose them explicitly rather than silently presenting partial totals as complete. Monitor part counts, merge backlog, replication queues, ingestion lag, and query memory; a durable upstream log can support replay after ingestion failures. ## Limits and defaults worth knowing - **Primary-key uniqueness:** MergeTree primary keys do not enforce uniqueness. Duplicate prevention or reconciliation must be designed separately. - **Sparse-index granularity:** The default index_granularity is 8,192 rows. Adaptive byte-based granularity can make granules smaller; both row and byte settings are configurable. - **Traditional mutation completion:** ALTER TABLE UPDATE and DELETE mutations are asynchronous by default. Waiting behavior is configurable, and rewriting affected data can create substantial background work. - **TTL timing:** TTL deletion is applied through background processing. It does not guarantee that expired rows disappear exactly at their expiration timestamp. - **ReplacingMergeTree deduplication:** Rows with the same sorting key are reconciled during merges, optionally using a version column. Background merges provide no fixed completion deadline; queries may need FINAL or explicit latest-version logic. ## Use it when - The design needs interactive SQL aggregations over a large stream of append-heavy events. - Dashboards repeatedly filter by time, tenant, service, campaign, or another dimension that can guide the sorting key. - Raw events and precomputed rollups must coexist so results can be investigated and rebuilt. - The workload includes observability, product analytics, experiment analysis, or advertising reports, with a clearly defined freshness target. - A transactional database remains the source of truth, while ClickHouse serves a separate analytical read model. ## Avoid it when - The critical path requires multi-row transactions, uniqueness constraints, or strongly consistent inventory and balance updates. - Most requests are individual key lookups or frequent in-place changes to small records rather than analytical scans. - Correctness depends on immediate duplicate removal or immediate completion of every update and deletion. - The workload is small enough that an existing relational database can meet the analytical requirements without another ingestion pipeline and operational system. ## What interviewers ask about it ### How would you choose the partition key, sorting key, and shard key? Start from retention operations, common query predicates, and tenant skew. Use coarse time partitions when they help lifecycle management, choose a sorting key that skips data for frequent filters, and choose a shard key that balances load without forcing every query to fan out. ### How do you prevent retries from inflating counts? Distinguish bounded insert-block deduplication from event-level deduplication: an event ID column alone does not enforce uniqueness. Use a deliberate replay and deduplication strategy, and remember that ReplacingMergeTree does not undo contributions already sent into an incremental materialized-view rollup. ### What does an acknowledged insert guarantee? It depends on the ingestion path: synchronous inserts, asynchronous inserts, Distributed forwarding, and replication quorum have different acknowledgment boundaries. State whether success means buffered, stored on the target, or accepted by a configured replica quorum, then explain the durability settings and retry behavior. ### How do you handle late events and corrected records? Use event time for analytical windows and retain enough raw history to recompute affected results. An incremental materialized view processes new insert blocks; it does not automatically reverse an earlier contribution when a source row is later replaced or deleted. ### What happens if every producer sends one row per insert? Small synchronous inserts can create many small parts, increasing metadata and merge work until ingestion is throttled or rejected. Batch at the producer or use asynchronous inserts, and configure acknowledgment behavior so buffering does not silently weaken the durability contract. ### Can a dashboard show an exact, current global total? Only after you define a completeness boundary across ingestion, replicas, and shards. A query can compute an exact aggregate over the rows it sees while still missing delayed events, so expose a watermark or freshness indicator and separate provisional totals from finalized results. ## Common mistakes - Treating PRIMARY KEY as a uniqueness constraint or assuming ReplacingMergeTree removes duplicates before every query. - Using high-cardinality partitions, such as one partition per user, instead of distinguishing lifecycle partitioning from sorting and sharding. - Assuming materialized views automatically correct historical rollups after source updates, replacements, or deletions. - Putting ClickHouse on a payment or inventory correctness path because it supports SQL, without addressing transaction and consistency requirements. ## Alternatives - **PostgreSQL:** Wins when transactional correctness, constraints, frequent row updates, and moderate analytical volume matter more than distributed columnar scans. - **Apache Druid:** Consider it for time-oriented event analytics where segment-based ingestion, ingestion-time rollup, and its serving architecture fit the access patterns. - **Apache Pinot:** Consider it for user-facing real-time analytics with highly concurrent filtered queries and indexing requirements that match its architecture. - **BigQuery or Snowflake:** Consider a managed warehouse for broad analytical workloads and simpler infrastructure operations when its freshness, concurrency, and pricing model fit. ## Interview questions where it comes up - [Design an ad click aggregator](https://sysround.com/questions/design-ad-click-aggregator): ClickHouse can serve campaign aggregates, but duplicate events, late arrivals, and final billing reconciliation require explicit correctness rules. - [Design Google Analytics](https://sysround.com/questions/design-google-analytics): It fits event storage, tenant-scoped dashboards, distinct-user queries, and rollups while exposing trade-offs around session corrections and replay. - [Design a metrics monitoring system like Datadog](https://sysround.com/questions/design-metrics-monitoring-system): It can store and aggregate high-volume measurements, with cardinality, retention, ingestion lag, and alert-path freshness as key design concerns. - [Design a distributed tracing system](https://sysround.com/questions/design-distributed-tracing): It can support analytical searches over spans when sorting keys, trace lookup paths, retention, and high-cardinality attributes are designed deliberately. - [Design an A/B testing platform](https://sysround.com/questions/design-ab-testing-platform): It can join and aggregate exposure and outcome events for experiment analysis, while assignment and attribution correctness remain separate responsibilities. - [Design a top K trending system](https://sysround.com/questions/design-top-k): It can query time-bounded event aggregates and rollups for rankings, but window expiration and replay-safe counting still need explicit treatment. ## FAQ ### What is ClickHouse used for? ClickHouse is used for analytical SQL over large datasets, including event analytics, logs, metrics, and reporting. Its columnar storage and execution model make it a strong fit for filtering and aggregating many records while reading relatively few columns. ### Is ClickHouse a replacement for PostgreSQL? Usually not for an application's transactional database. PostgreSQL is generally the better fit for constraints, transactional updates, and operational records, while ClickHouse can receive events or change data for analytical queries. Using both requires an explicit plan for lag, replay, and schema changes. ### Does ClickHouse support updates and deletes? Yes, but their mechanics and cost depend on the table engine, operation, and version. Traditional mutations can rewrite affected data, and lightweight operations have different visibility and cleanup behavior. Support for updates does not make a MergeTree table behave like a row-oriented transactional database. ## Related concepts - [Columnar storage](https://sysround.com/concepts/columnar-storage) - [Database sharding](https://sysround.com/concepts/database-sharding) - [Database replication](https://sysround.com/concepts/replication) - [Materialized views](https://sysround.com/concepts/materialized-views) - [Data deduplication and chunking](https://sysround.com/concepts/data-deduplication) --- SysRound (https://sysround.com) is an AI practice tool for system design interviews: a voice or typed AI interviewer, a shared whiteboard, then feedback on every skill and a model answer. Its feedback is AI-generated and can be wrong.