How ClickHouse Works: Architecture, Query Processing, and Data Storage Explained

ClickHouse is a column-oriented analytical database built to scan, filter, aggregate, and compress large datasets efficiently. Understanding how ClickHouse works means understanding three connected layers: its MergeTree-based storage layout, its parallel query pipeline, and its distributed architecture for replication and sharding. This guide explains the internals practitioners need for schema design, ingestion, and query tuning.

What happens when data lands in ClickHouse?

When rows are inserted into a MergeTree-family table, ClickHouse sorts them by the ORDER BY key and writes immutable data parts. In common wide parts, each column is stored separately, with marks pointing to granules, usually 8,192 rows by default; the sparse primary index stores one mark per granule rather than one entry per row. This layout lets ClickHouse read only required columns and skip irrelevant granules instead of scanning entire rows. (clickhouse.com)

Textual storage diagram:

• Table
  • Partition, such as month 202608
    • Part A, immutable and sorted by ORDER BY
      • Column files: event_time, user_id, event_type, amount
      • Mark files: byte offsets into compressed column blocks
      • Primary index: one entry per granule
      • Skipping indexes and checksums

The “segments” ClickHouse scans are best thought of as selected ranges inside parts: granules, marks, and compressed column blocks. Compression works well because values of the same type sit together. ClickHouse commonly uses LZ4 by default, supports ZSTD, and can apply type-aware codecs such as Delta, DoubleDelta, Gorilla, T64, and dictionary-style LowCardinality before general compression. (clickhouse.com)

Practical schema example:

CREATE TABLE events ( event_time DateTime CODEC(DoubleDelta, ZSTD), customer_id UInt64 CODEC(Delta, ZSTD), event_type LowCardinality(String), amount Float64 CODEC(Gorilla, LZ4) ) ENGINE = MergeTree PARTITION BY toYYYYMM(event_time) ORDER BY (customer_id, event_time);

Use ORDER BY for the most common filters and range scans, not for uniqueness. Use PARTITION BY for lifecycle operations, such as dropping a month, not as a substitute for the primary index. Avoid high-cardinality partitions because they create too many parts.

MergeTree engines define storage behavior

The MergeTree family is the core persistence layer. Standard MergeTree stores sorted immutable parts. Variants add behavior during background merges: ReplacingMergeTree can collapse versions, SummingMergeTree can pre-aggregate numeric values, and AggregatingMergeTree stores aggregate states. These choices move work between write time, background merges, and read time.

Background merging is central. Inserts create many small parts; merge tasks combine compatible parts into larger sorted parts, rebuild indexes, apply TTL rules, and remove obsolete data. The trade-off is deliberate: fast appends and efficient reads, at the cost of asynchronous cleanup and occasional merge pressure. Avoid forcing OPTIMIZE FINAL routinely, because it can create expensive large merges that the background scheduler would otherwise manage gradually. (clickhouse.com)

Textual merge diagram:

• Insert batch 1 creates part p1
• Insert batch 2 creates part p2
• Insert batch 3 creates part p3
• Background merge reads p1, p2, p3
• New part p4 is written sorted and indexed
• Old parts become inactive and are later removed

TTL features extend this process. A table can expire rows, move older data to another storage volume, or roll up data after a time threshold. TTL is powerful for observability and event data, but it is not immediate deletion; it is enforced through merges.

How does ClickHouse skip so much data?

ClickHouse skips data through partition pruning, sparse primary indexes, and data skipping indexes. First it can eliminate partitions. Then it uses the primary index over sorted granules to find ranges that may match. For filters outside the primary key, skipping indexes such as min-max, set, Bloom filter, text, or vector-style indexes can prune more granules when the column’s distribution supports it. (clickhouse.com)

Practical tuning example:

ALTER TABLE events ADD INDEX idx_event_type event_type TYPE set(100) GRANULARITY 4; EXPLAIN indexes = 1 SELECT count() FROM events WHERE customer_id = 42 AND event_type = ‘checkout’;

Use EXPLAIN indexes = 1 to confirm that partitions, parts, and granules are actually skipped. Sampling can also reduce scan cost when approximate answers are acceptable, but it works best when the table is designed with an appropriate sampling expression.

The query pipeline turns SQL into parallel work

A ClickHouse query moves through parser, analyzer, planner, and executor stages. The parser builds syntax from SQL. The analyzer resolves identifiers, functions, aliases, types, and semantic rules. The planner builds a query plan with operations such as reads, filters, joins, aggregation, sorting, and limits. The executor turns that plan into a pipeline of processors that pass column blocks through CPU-efficient transformations. (presentations.clickhouse.com)

Textual query pipeline diagram:

• SQL text
• Parser creates syntax tree
• Analyzer resolves names and types
• Planner creates logical and physical steps
• Executor runs processors in parallel
• Partial results are merged
• Final result is formatted and returned

Vectorized execution means operators process batches of column values rather than one row at a time. Parallelism comes from reading multiple parts, columns, granule ranges, shards, and pipeline lanes concurrently. Settings such as max_threads control per-query CPU parallelism; lowering it may improve cluster throughput under concurrency, while raising it may reduce latency for large scans. (clickhouse.com)

Memory management matters because aggregations, joins, sorts, decompression buffers, and distributed result merging can grow quickly. Use query limits, spill settings where appropriate, and system tables such as system.processes and query logs to observe peak memory and threads.

Replication and sharding scale the architecture

Replication is commonly implemented with ReplicatedMergeTree. Each replica stores a copy of the shard’s data, while ZooKeeper or ClickHouse Keeper coordinates metadata, part names, replication logs, and leadership-like coordination tasks. ClickHouse Keeper is ClickHouse’s coordination service compatible with the ZooKeeper protocol. (learn.clickhouse.com)

Replication example:

CREATE TABLE events_local ON CLUSTER analytics ( event_time DateTime, customer_id UInt64, event_type LowCardinality(String) ) ENGINE = ReplicatedMergeTree(‘/clickhouse/tables/{shard}/events’, ‘{replica}’) PARTITION BY toYYYYMM(event_time) ORDER BY (customer_id, event_time);

Sharding splits data across nodes. A Distributed table acts as a logical entry point that routes queries and inserts to local tables on shards. Distributed query processing pushes filters and partial aggregation down to shards, then merges intermediate results on the initiating node. The trade-off is scale and fault tolerance versus operational complexity: shard keys, network cost, replica lag, and coordinator availability all matter.

Caches, background tasks, and operational trade-offs

ClickHouse uses several caching mechanisms, including filesystem page cache, mark and index caches, uncompressed data cache in some workloads, compiled expression caches, and an opt-in query cache for repeated deterministic SELECT queries. Caches help most when queries repeat or touch the same marks and columns; they cannot fix a poor sort key or excessive SELECT *. (clickhouse.com)

Best practices:

• Design schema around real filters: put high-value, commonly filtered dimensions early in ORDER BY, followed by time for range scans.
• Keep ingestion batched: insert reasonably sized batches instead of many tiny inserts to reduce part pressure.
• Use partitions for retention: monthly or daily partitions are common, but choose based on data volume.
• Add skipping indexes only after measuring: they help selective predicates, not every column.
• Select only needed columns: columnar storage rewards narrow reads.
• Pre-aggregate when dashboards repeat expensive groupings: materialized views and aggregate engines can move work to ingestion.
• Inspect plans: use EXPLAIN, query logs, system.parts, and memory metrics before changing settings.

ClickHouse’s design choices favor analytical speed: immutable columnar parts, sparse indexes, vectorized execution, and distributed partial aggregation. The trade-offs are equally important: updates are asynchronous or merge-driven, joins and high-cardinality aggregations need memory discipline, and clusters require careful coordination. When schema, ingestion, and query patterns align with these internals, ClickHouse can turn very large event and metric datasets into interactive analytical results.

 

Need Expert Help with ClickHouse?

Diacto provides specialized ClickHouse consulting services to help you optimize your architecture, fine-tune query performance, and manage large-scale data ingestion. Whether you are building from scratch or scaling an existing cluster, our experts are here to help. Reach out to Diacto today for professional ClickHouse consulting.