How ClickHouse processes analytical queries

The Interview Question

Interviewer: "Your team runs analytics dashboards on a table with 10 billion rows. You mentioned ClickHouse handles these queries in under a second. Walk me through what happens internally when ClickHouse executes a SELECT COUNT(*) FROM events WHERE user_id = 42 AND event_date >= '2026-01-01'. Why is it so much faster than PostgreSQL for this workload?"

This question tests whether you understand columnar storage, sparse indexing, vectorized execution, and why these architectural choices make analytical queries orders of magnitude faster than row-oriented databases. The interviewer wants to hear the mechanics, not the marketing.

What to Clarify Before Answering

You: "Before I walk through the internals, let me scope this properly..."

• "Are we talking about a single-node ClickHouse instance or a distributed cluster with shards and replicas?"
• "Should I focus on the storage engine path (how data is laid out on disk) or the query execution path (how it processes the scan), or both?"
• "Is the table using the standard MergeTree engine or one of the specialized variants like AggregatingMergeTree?"
• "Do you want me to cover the compression layer too, or stay focused on the query hot path?"

Why this matters: ClickHouse is a deep system. A single-node MergeTree query path is already complex. Adding distributed execution, materialized views, and codec selection turns this into a 30-minute conversation. Scoping shows you know the territory.

The 30-Second Answer

ClickHouse stores data column by column on disk, so a query touching 3 columns out of 200 only reads those 3 columns. Each table is divided into data parts (sorted, immutable chunks) that get merged in the background by the MergeTree engine. A sparse primary index (one entry per 8,192 rows by default) lets ClickHouse skip entire granules that cannot match the WHERE clause. Query execution uses vectorized processing, operating on blocks of 65,536 values at a time with SIMD instructions instead of row-by-row iteration. Aggressive compression codecs (LZ4, ZSTD, Delta, DoubleDelta, Gorilla) shrink columnar data 5-20x, reducing I/O. For distributed setups, a Distributed table fans queries out to shards, each shard processes its portion, and results merge on the coordinator node.

The bottom line: ClickHouse scans billions of rows per second because it combines three multiplicative optimizations. Columnar storage reduces data read by 10-50x. Sparse indexing skips non-matching granules, reducing reads by another 10-100x. Vectorized execution processes the remaining data 8-16x faster per CPU cycle. These compound: 50x × 10x × 8x = 4000x faster than naive row-by-row scanning.

The Architecture Overview

I will walk through this top to bottom. A SQL query arrives and gets parsed into an AST. The analyzer determines which columns are actually needed (column pruning) and pushes predicates down to the storage layer. The query planner builds a pipeline of transforms. The vectorized executor pulls blocks of data through this pipeline, reading only the granules that the sparse index says might match. On disk, each column lives in its own compressed file, so reading 3 columns out of 200 means 3 sequential reads instead of scanning entire rows.

The background merge process is the heart of MergeTree. New inserts create small data parts. Over time, ClickHouse merges these into larger sorted parts, improving query performance and reclaiming space from outdated rows.

Columnar Storage: Why Columns Beat Rows

Think of a row-store like a filing cabinet where each drawer contains one complete customer record: name, address, phone, email, 200 other fields. If you need to count how many customers are in California, you must open every drawer and check the state field, dragging all 200 fields through the I/O path.

A column-store is like having a separate filing cabinet for each field. The "state" cabinet contains nothing but state values, packed tightly together. To count California customers, you read one slim cabinet instead of 200 fat drawers.

The performance difference is dramatic. For a table with 200 columns and 1 billion rows, a row store reads roughly 200x more data from disk than a column store for a single-column aggregation. But it goes further than just I/O reduction.

Compression wins. Values in the same column share the same data type and often have similar patterns. A column of timestamps compresses 10-20x better than the same timestamps scattered across rows with strings and floats. ClickHouse exploits this with specialized codecs: Delta encoding for monotonically increasing values, DoubleDelta for timestamps, and Gorilla for floating-point sensor data.

CPU cache wins. When the vectorized executor processes a block of integers, those integers sit contiguously in memory. The CPU prefetcher loves this. In a row store, the integer you need is 200 fields away from the next integer you need, causing constant cache misses.

For your interview: lead with "columnar storage reduces I/O by only reading the columns the query needs," then mention compression and CPU cache benefits as second-order effects.

MergeTree Engine Family: The Heart of ClickHouse

MergeTree is not one engine. It is a family of engines that all share the same sorted, immutable data-part architecture but differ in how they handle merges. Understanding which engine to pick is one of the most practical ClickHouse decisions you will make.

How Data Parts Work

When you INSERT into a MergeTree table, ClickHouse does not modify existing data. It writes a new data part: a directory on disk containing one compressed file per column, a primary index file, and metadata. Each part is internally sorted by the table's ORDER BY key.

# Directory structure of a single data part
/var/lib/clickhouse/data/mydb/events/
  20260101_1_1_0/          # Part name: partition_minBlock_maxBlock_level
    primary.idx             # Sparse primary index
    id.bin                  # Column data (compressed)
    id.mrk2                 # Mark file (offsets into .bin)
    event_date.bin
    event_date.mrk2
    user_id.bin
    user_id.mrk2
    checksums.txt
    columns.txt
    count.txt               # Number of rows in this part

Small parts accumulate with each INSERT. The background merge process combines them into larger parts, re-sorting and compacting. This is where the "Merge" in MergeTree comes from.

The Merge Process in Detail

Understanding how merges work is essential for operating ClickHouse reliably. The merge scheduler runs continuously in the background, selecting groups of parts to combine.

The merge algorithm is similar to the compaction process in LSM-tree databases like RocksDB. ClickHouse selects parts based on a cost heuristic that considers part sizes, ages, and the number of parts in the partition. Larger parts get merged less frequently because the I/O cost of rewriting them is high.

During a merge, ClickHouse reads the source parts, merge-sorts them by the ORDER BY key, applies any engine-specific logic (deduplication for ReplacingMergeTree, aggregation for AggregatingMergeTree), compresses the output, and writes new column files. The old parts are marked inactive and deleted after a configurable delay (old_parts_lifetime, default 8 minutes).

Key operational facts about merges:

• Merge throughput is configurable via max_bytes_to_merge_at_once (default ~150 GB) and max_number_of_merges_with_ttl_in_pool (default 2)
• Mutations (ALTER TABLE UPDATE/DELETE) are implemented as merge-like operations that rewrite parts. They are not instant and can take minutes to hours on large tables
• OPTIMIZE TABLE FINAL forces all parts in a partition to merge into one part. This is expensive and should only be run during maintenance windows

Engine Variants

Sparse Primary Index and Granules

ClickHouse does not build a B-tree index on every row like PostgreSQL. Instead, it uses a sparse index that stores one entry per granule (a group of rows, 8,192 by default). This is one of the most important architectural decisions in the system.

When the query asks for WHERE user_id = 42, ClickHouse binary-searches the sparse index and determines that only granule 1 could contain matching rows. Granules 0, 2, and 3 are skipped entirely. For a table with 1 million granules, this binary search touches maybe 20 index entries. The entire sparse index for a billion-row table fits in a few megabytes of RAM.

This is fundamentally different from a B-tree. A B-tree index on 1 billion rows requires gigabytes of memory and must be updated on every insert. ClickHouse's sparse index is tiny because it only stores one value per 8,192 rows, and it never needs updating because data parts are immutable.

The ORDER BY key matters enormously. If your table is ordered by (user_id, event_date), then queries filtering on user_id get excellent granule skipping. Queries filtering only on event_date (the second key column) get no skipping at all, because the index is not sorted globally by date.

Skipping Indexes (Secondary)

For columns not in the ORDER BY key, ClickHouse offers skipping indexes (also called data-skipping indexes). These store aggregated statistics per granule:

• minmax: stores min and max value per granule
• set(N): stores up to N distinct values per granule
• bloom_filter: probabilistic membership test per granule
• tokenbf_v1: tokenized bloom filter for text search

-- Add a bloom filter index on url column
ALTER TABLE events ADD INDEX idx_url url TYPE bloom_filter(0.01) GRANULARITY 4;

These indexes do not help you find specific rows. They help ClickHouse skip granules that definitely do not contain matching data. A bloom filter for URL matching can skip 95%+ of granules for selective queries.

Partitioning vs ORDER BY

Do not confuse partitioning with the ORDER BY key. They serve different purposes:

• PARTITION BY divides the table into separate physical directories. Each partition merges independently. Common choice: PARTITION BY toYYYYMM(event_date). This lets you drop entire months with ALTER TABLE DROP PARTITION and limits merge scope.
• ORDER BY defines the sort order within each partition and determines the sparse index structure. Common choice: ORDER BY (tenant_id, event_date, event_id).

A well-designed ClickHouse table uses PARTITION BY for data lifecycle management (TTL, archival, dropping old data) and ORDER BY for query performance (granule skipping). I see teams conflate the two and end up with thousands of partitions (one per day per tenant), which creates an explosion of small parts and cripples merge performance.

Vectorized Query Execution

This is where ClickHouse really separates from the pack. Most databases process one row at a time: fetch row, evaluate WHERE clause, accumulate result, fetch next row. ClickHouse processes data in blocks of up to 65,536 values using SIMD (Single Instruction, Multiple Data) CPU instructions.

// Pseudocode: Row-at-a-time execution (traditional)
for each row in table:
    if row.user_id == 42 AND row.date >= '2026-01-01':
        count += 1

// Pseudocode: Vectorized execution (ClickHouse)
for each block of 65536 values:
    mask = SIMD_compare_eq(user_id_block, 42)     // Compare 8 values per CPU cycle
    mask &= SIMD_compare_gte(date_block, threshold) // Bitwise AND of masks
    count += popcount(mask)                          // Count set bits

The vectorized approach wins in three ways:

1. SIMD parallelism. A single AVX2 instruction compares 8 int32 values simultaneously. AVX-512 doubles that to 16. This is 8-16x throughput per CPU cycle for simple comparisons.

2. Branch elimination. Row-at-a-time execution has an unpredictable branch (if row matches...) on every row. Modern CPUs hate unpredictable branches. Vectorized execution replaces branches with bitwise mask operations that have zero branch mispredictions.

3. Tight loops. The inner loops of vectorized execution operate on contiguous arrays of a single data type. The compiler can auto-vectorize these loops, and the CPU prefetcher loads the next cache line while the current one is being processed.

ClickHouse also parallelizes across CPU cores. Each data part can be processed by a different thread, and within a single part, different granule ranges can be processed in parallel. A query on a 32-core machine can process 32 data parts simultaneously.

The Query Pipeline

ClickHouse does not use the traditional Volcano-style iterator model (where each operator pulls one tuple at a time from its child). Instead, it uses a pipeline of transforms that push blocks of data through a directed acyclic graph.

// Simplified query pipeline for:
// SELECT user_id, COUNT(*) FROM events WHERE date >= '2026-01-01' GROUP BY user_id

Pipeline:
  ReadFromMergeTree (parallel, one thread per data part)
    → FilterTransform (vectorized WHERE evaluation on blocks)
    → AggregatingTransform (hash table per thread, block-based insertion)
    → MergingAggregatedTransform (merge per-thread hash tables)
    → OutputTransform (serialize result blocks)

Each stage processes entire blocks of 65,536 rows. Multiple pipeline instances run in parallel across CPU cores. The aggregation step uses a per-thread hash table to avoid lock contention, then merges partial hash tables in a final step.

This pipeline architecture is why ClickHouse scales almost linearly with CPU cores for scan-heavy queries. If you move from 8 cores to 32 cores, you typically see a 3.5-4x speedup (not a perfect 4x due to merge overhead and memory bandwidth limits).

Distributed Tables and Sharding

For datasets that exceed single-node capacity (or require higher throughput), ClickHouse supports horizontal sharding through the Distributed table engine.

The Distributed table is a virtual table that knows which shards hold which data. When a query arrives, the coordinator rewrites it for each shard, sends the partial query, collects partial results, and merges them. For a COUNT query, each shard returns its local count, and the coordinator sums them.

Sharding key selection determines how data distributes across shards. Common choices:

• rand(): uniform distribution, good for balanced load, bad for queries that filter on a specific entity
• intHash64(user_id): hash-based, ensures all events for a user land on the same shard (enables local JOINs)
• toYYYYMM(event_date): time-based, good for time-range queries but causes hot shards on recent data

Replication

ClickHouse uses ZooKeeper (or ClickHouse Keeper, a built-in Raft-based replacement) for replication coordination. Each shard can have multiple replicas using the ReplicatedMergeTree engine family. Replicas are eventually consistent: writes go to one replica and asynchronously propagate to others through a shared log in ZooKeeper.

I recommend ClickHouse Keeper over ZooKeeper for new deployments. It eliminates a separate Java dependency, supports the same protocol, and is maintained by the ClickHouse team.

INSERT Distribution

When you INSERT into a Distributed table, the behavior depends on the internal_replication setting:

• internal_replication = false (default): The coordinator node sends the data to every replica of every shard. This is wasteful because each replica gets the same data twice.
• internal_replication = true (recommended): The coordinator sends data to one replica per shard, and each shard handles replication internally via the ReplicatedMergeTree log. This halves network traffic on writes.

For high-throughput ingestion, skip the Distributed table entirely and INSERT directly into local tables on each shard. Use the sharding key logic in your application or a load balancer (like chproxy) to route INSERTs to the correct shard. This eliminates the coordinator bottleneck and gives you full control over batching.

Compression Codecs: Squeezing Bytes

ClickHouse applies compression at the column level, and you can choose different codecs for different columns. This is a significant advantage over databases that apply one compression strategy globally.

-- Column-level codec selection
CREATE TABLE sensor_data (
    timestamp DateTime CODEC(DoubleDelta, LZ4),
    sensor_id UInt32 CODEC(Delta, LZ4),
    temperature Float32 CODEC(Gorilla, LZ4),
    status String CODEC(ZSTD(3)),
    raw_payload String CODEC(ZSTD(9))  -- High compression for rarely-read data
) ENGINE = MergeTree()
ORDER BY (sensor_id, timestamp);

Notice the double-codec pattern: DoubleDelta, LZ4 means "apply DoubleDelta first (which turns similar timestamps into small deltas), then compress those small numbers with LZ4." This pipeline approach can achieve 20x+ compression on timestamp columns.

Materialized Views: Pre-aggregation

Materialized views in ClickHouse work differently than in PostgreSQL. They are not periodic snapshots. They are INSERT triggers that transform incoming data and write it to a separate target table in real time.

-- Source table: raw events
CREATE TABLE events (
    event_date Date,
    user_id UInt64,
    event_type String,
    duration_ms UInt32
) ENGINE = MergeTree()
ORDER BY (event_date, user_id);

-- Target table for pre-aggregated hourly stats
CREATE TABLE hourly_stats (
    hour DateTime,
    event_type String,
    event_count AggregateFunction(count),
    avg_duration AggregateFunction(avg, UInt32)
) ENGINE = AggregatingMergeTree()
ORDER BY (hour, event_type);

-- Materialized view: transforms on insert
CREATE MATERIALIZED VIEW hourly_stats_mv TO hourly_stats AS
SELECT
    toStartOfHour(event_date) AS hour,
    event_type,
    countState() AS event_count,
    avgState(duration_ms) AS avg_duration
FROM events
GROUP BY hour, event_type;

When you INSERT 1 million events, the materialized view groups them by hour and event type, computes partial aggregate states, and writes maybe 100 rows to hourly_stats. Dashboard queries that previously scanned 1 billion rows now read a few thousand pre-aggregated rows.

The *State() and *Merge() function pairs are the mechanism. countState() produces a partial aggregate that can be merged later with other partial aggregates using countMerge(). This is how AggregatingMergeTree combines partial results during background merges.

Think of it like this: instead of storing "there were 1,500 clicks," the materialized view stores a partial aggregate state that says "here is a counter object representing some subset of clicks." When ClickHouse merges two parts, it merges the counter objects, producing a new counter that represents the sum. At query time, countMerge() finalizes the counter into a number. This indirection is what makes AggregatingMergeTree capable of merging partial results from different time windows without re-scanning raw data.

I find this one of the most elegant patterns in ClickHouse. It turns a seemingly expensive "compute aggregates on every query" problem into a cheap "read pre-computed results" problem, with the overhead shifted to insert time where it is amortized across billions of rows.

What Happens When Things Break

Performance Characteristics

For your interview: emphasize that ClickHouse is optimized for high-throughput analytical reads, not transactional writes. Single-row inserts are an anti-pattern. Batch everything.

How This Compares to Alternatives

I reach for ClickHouse when I need sub-second analytics on billions of rows and want to control infrastructure costs. I switch to Snowflake when the team does not want to operate database infrastructure and is willing to pay per-query pricing. I consider Druid when real-time ingestion from Kafka is the primary requirement and query patterns are mostly time-series aggregations.

One important nuance: ClickHouse is the best choice when you need consistency between ingestion and query (data is queryable immediately after INSERT returns). Snowflake has a delay between ingestion and queryability (Snowpipe can take minutes). BigQuery has similar delays with streaming inserts. If your use case requires "INSERT then immediately SELECT and see the row," ClickHouse handles this naturally while cloud warehouses require careful pipeline design.

Another consideration: ClickHouse's operational complexity is real. You manage shards, replicas, ZooKeeper/Keeper, disk capacity, merge throughput, and monitoring. Snowflake and BigQuery abstract all of this away. For teams without dedicated database operations expertise, the managed option often wins despite higher per-query cost.

Interview Cheat Sheet

• When asked "why is ClickHouse fast": "Three reasons: columnar storage means I/O reads only the columns a query needs. Sparse indexing skips granules that cannot match. Vectorized execution uses SIMD to process 8-16 values per CPU cycle instead of one row at a time."
• When asked about the MergeTree engine: "MergeTree writes immutable sorted data parts on INSERT and merges them in the background. Variants like ReplacingMergeTree and AggregatingMergeTree customize the merge behavior for different data lifecycle patterns."
• When asked about indexing: "ClickHouse uses a sparse primary index with one entry per 8,192 rows. It is tiny (megabytes for billion-row tables) and enables binary search to skip non-matching granules. Secondary skipping indexes (bloom filters, min-max) handle columns not in the ORDER BY key."
• When asked about distributed queries: "A Distributed table fans queries to shards, each shard processes locally, and the coordinator merges partial results. Sharding key choice determines whether queries can be served by one shard or must touch all shards."
• When asked about compression: "ClickHouse supports per-column codec selection. Timestamps get DoubleDelta, floats get Gorilla, strings get ZSTD. Codecs can be chained (DoubleDelta then LZ4). Good codec selection achieves 10-20x compression and directly improves query speed by reducing I/O."
• When asked about materialized views: "They are INSERT triggers, not periodic snapshots. Data is transformed and written to a target table at insert time using partial aggregate states. Dashboard queries hit the pre-aggregated table instead of scanning raw data."
• When asked about limitations: "ClickHouse is not good at point lookups, row-level updates, or transactions. It is an OLAP engine, not OLTP. Single-row INSERTs are an anti-pattern. JOINs work but are not as optimized as in PostgreSQL."
• When asked about operations: "The biggest operational challenge is the 'too many parts' problem from frequent small INSERTs. Batch writes to at least 10K-100K rows. Monitor system.parts count and merge throughput."
• When asked about partitioning: "PARTITION BY controls data lifecycle (TTL, DROP PARTITION). ORDER BY controls query performance (sparse index, granule skipping). Keep partitions coarse (monthly) and put query-relevant columns in ORDER BY."
• When asked about JOINs: "ClickHouse supports JOINs but prefers denormalized tables. For large JOINs, it uses hash join by default (right table loaded into memory). Distributed JOINs across shards work best when both tables share the same sharding key, enabling local joins."

Test Your Understanding

Quick Recap

1. ClickHouse stores data column by column, so queries read only the columns they need, reducing I/O by 10-50x compared to row stores.
2. The MergeTree engine writes immutable sorted data parts and merges them in the background. Engine variants (Replacing, Aggregating, Summing) customize merge behavior for different data patterns.
3. A sparse primary index stores one entry per 8,192 rows, enabling granule-level skipping with only megabytes of RAM for billion-row tables.
4. Vectorized query execution processes blocks of 65,536 values using SIMD instructions, achieving 8-16x throughput per CPU cycle.
5. Per-column compression codecs (DoubleDelta for timestamps, Gorilla for floats, LZ4/ZSTD for general data) achieve 5-20x compression and directly improve query speed.
6. Distributed tables fan queries across shards, with sharding key choice determining whether queries touch one shard or all shards.
7. Materialized views act as INSERT triggers for real-time pre-aggregation, reducing dashboard query scans from billions of rows to thousands.
8. The main operational challenge is the "too many parts" problem from frequent small INSERTs. Always batch writes.

Related Concepts

• Columnar storage is also used by Apache Parquet, Apache Arrow, and Snowflake's micro-partitions. Understanding ClickHouse's column format helps you reason about any columnar system.
• LSM trees share the "write immutable sorted runs and merge later" pattern with MergeTree. RocksDB and Cassandra use similar approaches for different workloads.
• Vectorized execution is a concept borrowed from MonetDB and adopted by systems like DuckDB, Velox, and DataFusion. The same principles apply wherever you see SIMD-based query processing.
• Materialized views for pre-aggregation map directly to the Lambda and Kappa architectures used in stream processing with Flink and Kafka Streams.
• Distributed tables are conceptually similar to how Vitess shards MySQL or how Citus shards PostgreSQL. The key difference is that ClickHouse's Distributed engine is a pure query routing layer with no global transaction support.
• Compression codecs like DoubleDelta and Gorilla were originally designed for time-series databases (Facebook's Gorilla paper, 2015). ClickHouse adapts these for general columnar analytics, applying the optimal codec per column based on data type and value distribution.
• Projections are ClickHouse's alternative to traditional secondary indexes. A projection stores a different sort order of the same data, so queries with different WHERE clause patterns can still benefit from ordered scans without maintaining a separate table.
• Lightweight deletes (ALTER TABLE DELETE) rewrite affected parts in the background. For real-time soft deletes, use ReplacingMergeTree with a deleted flag column and filter at query timea
• Distributed tables are conceptually similar to how Vitess shards MySQL or how Citus shards PostgreSQL. The key difference is that ClickHouse's Distributed engine is a pure query routing layer with no global transaction support.
• Compression codecs like DoubleDelta and Gorilla were originally designed for time-series databases (Facebook's Gorilla paper, 2015). ClickHouse adapts these for general columnar analytics, applying the optimal codec per column.
• Projections are ClickHouse's alternative to traditional secondary indexes. A projection stores a different sort order of the same data, so queries with different WHERE clause patterns can still benefit from ordered scans without maintaining a separate table.dapts these for general columnar analytics, applying the optimal codec per column based on data type and value distribution.analytics, applying the optimal codec per column.
• Vectorized execution is a concept borrowed from MonetDB and adopted by systems like DuckDB, Velox, and DataFusion. The same principles apply wherever you see SIMD-based query processing.
• Materialized views for pre-aggregation map directly to the Lambda and Kappa architectures used in stream processing with Flink and Kafka Streams.
• Projections store a different sort order of the same data for queries with different WHERE clause patterns.
• Lightweight deletes rewrite affected parts in the background. For real-time soft deletes, use ReplacingMergeTree with a deleted flag column.