Why ClickHouse Is So Fast: 7 Design Choices, Measured on 1 Billion Rows

Re-checked on 26.8 LTS · September 2026

We re-ran the lab on ClickHouse 26.8 LTS and read the 26.9 notes. Here is what held and what moved

The numbers in the body were taken on ClickHouse 26.7.2.1. Since then 26.8 became the LTS line (announced 10 September 2026: 98 new features, 128 performance optimisations, 556 bug fixes) and 26.9 landed on 24 September (56, 135 and 464). So on 26 September we did the boring but necessary thing: same generator, same 1,000,000,000 rows, same 2 vCPU / 7 GB / NVMe-class box, ClickHouse 26.8.11.7 LTS, query condition cache off, mark and uncompressed caches dropped before every first run, server-side medians of three from system.query_log.

Then we went through every sentence in the body that names a version, a default or a feature and checked it against the binary’s system.settings and system.merge_tree_settings, and against the 26.9 release call.

Short version: the seven reasons why ClickHouse is so fast are the same seven. None of them was replaced, and nothing in 26.8 or 26.9 makes any of them less true. What changed is the constants. Every full-scan query on this table got faster on the same hardware, which is the plainest evidence for why ClickHouse is so fast that we can offer, automatic column statistics now prune parts without any DDL from us, and lazy materialization is a real eighth reason why ClickHouse is so fast on ORDER BY ... LIMIT that the original article did not measure.

We kept the 26.7 figures in the body so the plans, tables and prose stay consistent with each other; the table below is the side-by-side.

One thing worth admitting up front: the first 26.8 load ran out of disk at about 650 million rows, because outdated parts from the background merges had not been cleaned up yet and the box only has 30 GB free. We set old_parts_lifetime to 30 seconds on the table, dropped the one partition that had received a partial batch, reloaded it from the generator and checked that the count was exactly 1,000,000,000 before measuring anything. If you reproduce this on a small machine, budget disk for roughly twice the final table size during the load; none of it changes why ClickHouse is so fast, but it will change whether you get to find out.

Table 0. The 26.7 lab against the 26.8 LTS re-run, or why ClickHouse is so fast on the same box a release later. Same generator, same machine class, server-side medians of three runs from system.query_log, first runs with caches cold.

What we checked against 26.8 and 26.9, what we corrected, and what it says about why ClickHouse is so fast

Statistics first. The body says the 26.7 build maintains part-level column statistics automatically, and that is now stronger than we wrote: on 26.8 the binary reports materialize_statistics_on_insert = 1 (for tables up to 25 GiB), use_statistics_for_part_pruning = 1 and auto_statistics_types = basic, uniq_v2. That is why ClickHouse is so fast on the one-tenant-one-day query now, touching 2 granules instead of 7, and why the second-key-column query halved. The 26.9 release call goes further and answers min, max and count straight from statistics (8 to 24 ms became 2 ms in their example). We did not re-measure that one.

Lazy materialization next. It arrived in 25.4 and is on by default; 26.8 extends it to Parquet on object storage and to local files (the release post’s S3 example drops from about 6.7 GB read to 1.5 GB). On our billion rows it took a SELECT * top-10 from 11,479 ms to 3,197 ms and cut peak memory from 8.6 GB to 0.6 GB; on a wide top-N this is now a large part of why ClickHouse is so fast. The planner stops applying it above query_plan_max_limit_for_lazy_materialization = 10000 rows, which is worth knowing before you paginate with large offsets.

Query condition cache. The body said “the 26.x line adds” it. That was sloppy: it shipped in 25.3 and has been on by default since; 26.8 also applies it to TopK queries (use_query_condition_cache_for_top_k = 1). We corrected the sentence. The advice stands, and it matters for anyone measuring why ClickHouse is so fast: benchmark with it off, run production with it on, and do not report a warm second run as if it were the first.

Aggregation and joins. 26.8 ships an adaptive parallel GROUP BY (enable_adaptive_aggregator = 1), packed string keys, a multi-threaded final merge and, per the changelog, a 2.4x faster DISTINCT on sorted prefixes; joins get IEJoin for inequality conditions, a parallel_full_sorting_merge algorithm and statistics-driven planning that ClickHouse measured at 29 % on TPC-H. 26.9 adds a faster LEFT SEMI JOIN (0.50 s to 0.37 s in the release call).

The composite LowCardinality GROUP BY that we flagged as a limit improved from 25 s to 18.3 s, which is less than we hoped for from the packed keys; it remains the slowest shape in this article, a reminder that why ClickHouse is so fast is always a per-query-shape question, and the limits section still applies.

Parallel replicas and merges. enable_parallel_replicas = 0 is still the open-source default on 26.8.11.7, so level-3 parallelism, the cluster half of why ClickHouse is so fast, stays opt-in exactly as the body says; 26.9 extends parallel replicas to Merge tables with distributed aggregation. Merges now choose codecs adaptively (26.8), and 26.9’s k-way merge is 7 to 8 % faster on multi-column keys with up to 40 % less CPU for FINAL, which matters for the ReplacingMergeTree patterns in our archival work more than for this table.

Two smaller corrections, and one non-result. The skip-index table said text supersedes tokenbf_v1 and ngrambf_v1; the accurate statement is that the 26.x documentation marks those two as deprecated and lists text as the current full-text type, so the table now says that. And on JIT: compile_expressions = 1 is the default, but on this two-core box it made an arithmetic filter slightly slower (4,011 ms on, 3,757 ms off). We are not going to draw a conclusion from one query on two cores except the one the settings table already draws, which is to measure rather than assume, even when the question is why ClickHouse is so fast rather than whether.

The codec result is the one that surprised us. Tuned per-column codecs shrank the full table from 15.83 GiB to 8.70 GiB, which on a disk-bound box we expected to make scans faster. It made them slower, because ZSTD and T64 decoding is CPU work and this machine has two cores to give.

On the 16- and 32-core nodes we run for customers the trade usually goes the other way, but that is a claim about other hardware, so treat the storage number as measured and the speed number as something to test on your own core count. The engine is the same either way; why ClickHouse is so fast per byte depends on whether the box has CPU to spare for decoding.

Checked on 26 September 2026 against ClickHouse release 26.8, the 26.9 release call, the 2026 changelog, GitHub releases (v26.9.2.8-stable and v26.8.11.7-lts, 22 September 2026), the MergeTree documentation and the 25.3 and 25.4 release posts. Settings were read from the 26.8.11.7 binary itself.

The lab table

The billion-row table

The table models a multi-tenant SaaS event stream: 500 tenants, 20,000 users each, a six-month window, 17 event types with a realistic skew towards page views, 40 countries, 4 device classes, 300 URL paths, HTTP status codes at a 95% success rate, and latency and payload columns with a long tail. Every value is a deterministic hash of the row number, so anyone can regenerate the identical table and check every ClickHouse performance figure below to the row; the generator is in the appendix.

sql · ClickHouse 26.7 · the table

CREATE TABLE lab.events
(
    tenant_id    UInt16,
    event_time   DateTime,
    event_date   Date MATERIALIZED toDate(event_time),
    user_id      UInt32,
    event_type   LowCardinality(String),
    country      LowCardinality(String),
    device       LowCardinality(String),
    url_path     LowCardinality(String),
    http_status  UInt16,
    latency_ms   UInt16,
    bytes_out    UInt32
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (tenant_id, event_time)
SETTINGS index_granularity = 8192;

-- 20 inserts of 50,000,000 rows from numbers_mt(), then OPTIMIZE TABLE ... FINAL
-- load: 1,000,000,000 rows in 498.6 s = 2.01 M rows/s on a single 2-thread insert stream

SELECT count() AS parts, sum(rows) AS rows, sum(marks) AS granules,
       formatReadableSize(sum(data_compressed_bytes))   AS compressed,
       formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed,
       formatReadableSize(sum(primary_key_size))        AS primary_index,
       formatReadableSize(sum(marks_bytes))             AS marks_on_disk
FROM system.parts
WHERE database = 'lab' AND table = 'events' AND active;

┌─parts─┬───────rows─┬─granules─┬─compressed─┬─uncompressed─┬─primary_index─┬─marks_on_disk─┐
│    40 │ 1000000000 │   122127 │ 15.84 GiB  │ 23.29 GiB    │ 570.72 KiB    │ 2.15 MiB      │
└───────┴────────────┴──────────┴────────────┴──────────────┴───────────────┴───────────────┘

Three facts from that output shape everything that follows about why ClickHouse is so fast on this table. The table is 15.84 GiB against 7 GB of RAM, so full scans are reading from disk, not from cache. The primary index for a billion rows is 570 KiB, one entry per 8,192-row granule, and it is loaded lazily on first use. And the default LZ4 codec achieved only 1.47x on this deliberately random data, which is a floor we will raise to 2.68x in design choice 4 and which real telemetry, as LogHouse’s 16x shows, leaves far behind.

Design choice 2

Design choice 2: a sparse primary index that reads 7 granules out of 122,127

A B-tree carries one entry per row. ClickHouse carries one entry per granule, the first sort-key tuple of every 8,192 rows, which is why 570 KiB indexes a billion rows. Because rows inside a part are physically sorted by the key, a predicate on the leading key columns becomes a binary search over that list and the result is a set of granule ranges. The mark file then seeks each named column directly to the compressed block that holds the first selected granule.

sql · EXPLAIN indexes = 1 · one tenant, one day

EXPLAIN indexes = 1
SELECT count(), avg(latency_ms)
FROM lab.events
WHERE tenant_id = 42
  AND event_time BETWEEN toDateTime('2026-03-10 00:00:00') AND toDateTime('2026-03-10 23:59:59');

ReadFromMergeTree (lab.events)
  Parts: 5 | Granules: 7
  Indexes:
    Min-Max      Condition: true                 Parts: 40/40   Granules: 122087/122087
    Partition    Condition: true                 Parts: 40/40   Granules: 122087/122087
    Statistics   Keys: event_time, tenant_id     Parts: 5/40    Granules: 4873/122087
    PrimaryKey   Keys: tenant_id, event_time     Parts: 5/5     Granules: 7/4873
                 Search Algorithm: binary search
    Ranges: 5

-- median of 3 runs: 8 ms

Two layers cooperate here. Part-level column statistics, which the 26.7 build maintains automatically, discard 35 of the 40 parts because their event_time range cannot contain 10 March; the primary key then binary-searches the 4,873 granules of the five survivors down to 7. Eight milliseconds against a billion rows, and the table was never in memory. If one measurement explains why ClickHouse is so fast for tenant-scoped analytics, it is this one.

Table 3. Five predicates, one table, and the ClickHouse performance range they span. The same 106 rows cost 28 ms or 2,875 ms depending on whether the query also names the first sort-key column. Nothing else changed.

The last two rows are the most instructive thing on this page for anyone designing a schema. A predicate that follows the sort-key prefix is resolved by the index; a predicate that does not is a full scan of that column, however selective it is. This is the single largest lever in ClickHouse performance engineering, and it is set at CREATE TABLE time, which is why ClickHouse is so fast for teams that chose their ORDER BY carefully and merely adequate for teams that did not.

Design choice 4

Design choice 4: per-column codecs, measured on 99.5 million rows

A column stream holds values of one type, often sorted or slowly varying, and ClickHouse lets each column declare its own codec chain: a shape-aware codec (Delta, DoubleDelta, Gorilla, T64, GCD, FPC) followed by a general-purpose one (LZ4 by default in open-source ClickHouse, ZSTD by default in ClickHouse Cloud). We copied the first 99.5 million rows of the events table into a second table with codecs chosen per column and compared, because compression is the least-discussed part of why ClickHouse is so fast and often the cheapest to improve.

sql · per-column codecs on the same 99,532,800 rows

CREATE TABLE lab.events_codec
(
    tenant_id    UInt16                 CODEC(Delta(2), ZSTD(1)),
    event_time   DateTime               CODEC(Delta(4), ZSTD(1)),
    event_date   Date MATERIALIZED toDate(event_time),
    user_id      UInt32                 CODEC(ZSTD(1)),
    event_type   LowCardinality(String) CODEC(ZSTD(1)),
    country      LowCardinality(String) CODEC(ZSTD(1)),
    device       LowCardinality(String) CODEC(ZSTD(1)),
    url_path     LowCardinality(String) CODEC(ZSTD(1)),
    http_status  UInt16                 CODEC(T64, ZSTD(1)),
    latency_ms   UInt16                 CODEC(T64, ZSTD(1)),
    bytes_out    UInt32                 CODEC(T64, ZSTD(1))
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (tenant_id, event_time);

┌─table────────┬─compressed─┬─uncompressed─┬─ratio─┐
│ events (LZ4) │ ~1.58 GiB  │ 2.32 GiB     │  1.47 │   -- pro-rated from the full table
│ events_codec │ 887.10 MiB │ 2.32 GiB     │  2.68 │
└──────────────┴────────────┴──────────────┴───────┘

Table 5. Codec effect per column, the ClickHouse performance lever that costs nothing at query time. event_time is the lesson: a timestamp column that LZ4 could not compress at all shrank 4.2x once told it was a sequence of deltas.

The codec table was also faster to query, not slower, a ClickHouse performance result that runs against the usual intuition about ZSTD. The same avg(latency_ms), count() ran in 155 ms on the ZSTD table against 186 ms on the LZ4 slice, and the 17-way GROUP BY in 358 ms against 413 ms.

On the 20-million-row table in our sibling article, which fitted in page cache, LZ4 won by 20% because the scan was CPU-bound; here the table does not fit and the scan is bound by bytes moved, so the smaller table wins. Which regime a production node is in is a measurement, not an opinion, and system.parts_columns on your own data is where the answer lives.

Design choice 6

Design choice 6: parallelism over mark ranges, from two cores to three clouds

ClickHouse parallelises at three multiplicative levels, and the unit of work at every level is the same object: a range of marks inside a part. A thread, a shard and a replica each take mark ranges; none of them introduces a new kind of coordination, which is why ClickHouse is so fast to scale out as well as up. On our two cores the first level is the only one we can show, and it is close to ideal; it is also why ClickHouse is so fast on a cluster for the same reason it is fast on one node.

Table 7. ClickHouse performance scaling across threads on identical mark ranges. Each thread runs a private pipeline and a private aggregation hash table; the two are merged once at the end.

The ClickHouse performance blast radius of turning any level up is memory: one aggregation hash table per thread, per replica. Raise max_threads or enable parallel replicas together with max_memory_usage and max_concurrent_queries, stage on one replica, and read system.query_log.memory_usage at p99 before rolling out.

Limits

Where ClickHouse is not fast, on the same billion rows

Understanding why ClickHouse is so fast tells you exactly where it is not, and our lab produced three examples without looking for them. A point filter on a column outside the sort key is a full column scan: 2,875 ms for 106 rows in Table 3, against 28 ms when the query also names the tenant.

The second example is a ClickHouse performance edge inside the aggregation engine. A GROUP BY on two LowCardinality columns at once, country and device, took 25 seconds where the single-key GROUP BY took 3, because the aggregation falls off the fixed-width fast path onto a serialised composite key; the fix is a precomputed view or a single derived key, and it is the kind of thing a profile finds in a minute and a benchmark table never shows.

The third example is aggregation cost rather than I/O. A 95th-percentile latency across 160 country-device groups took 27 seconds because a billion values were sampled into 160 quantile states; that query belongs in a materialized view, not on a dashboard.

The structural limits of ClickHouse performance are the ones we measured on the 20-million-row sibling table in the row-store comparison: a point lookup by primary key at 5 to 9 ms against 0.1 ms in PostgreSQL, and a single-row UPDATE as an 8-second mutation against 0.65 ms. Hash joins build the right-hand table in memory per query, so a billion-row fact-to-fact join without dictionaries, denormalisation or grace_hash exhausts max_memory_usage. If a workload is mostly point reads and row-level writes, no ClickHouse performance tuning rescues it; it belongs on a row store with CDC into ClickHouse for the analytical half, and MinervaDB’s PostgreSQL and MySQL practices exist for exactly that split.

Reproduce

Reproduce the billion rows

The generator below produces the identical table on any ClickHouse 26.x, including chDB, so every claim here about why ClickHouse is so fast can be checked rather than believed. On two vCPU it ran at 2.01 million rows per second and finished in 498.6 s; OPTIMIZE FINAL took a further 156 s. Budget 16 GiB of disk plus one partition of headroom for the merge. Test on a copy of your own data before drawing conclusions for production, and keep a tested restore path before touching any schema that serves traffic.

sql · deterministic generator for 1,000,000,000 rows (run in 20 chunks of 50 M)

INSERT INTO lab.events
SELECT
    toUInt16(1 + intHash32(number) % 500)                                   AS tenant_id,
    toDateTime('2026-01-01 00:00:00') + toIntervalSecond(intDiv(number, 64)) AS event_time,
    toUInt32(tenant_id * 100000 + intHash64(number) % 20000)                AS user_id,
    ['page_view','page_view','page_view','page_view','page_view','page_view','click','click',
     'api_call','api_call','search','login','purchase','error','logout','signup','export']
        [1 + intHash32(number * 7) % 17]                                     AS event_type,
    ['US','US','US','IN','IN','DE','GB','BR','FR','JP','CA','AU','SG','NL','ES','IT','MX','KR','ID','AE',
     'SE','PL','TR','ZA','AR','CH','BE','TH','MY','PH','VN','NG','EG','SA','IL','IE','AT','DK','NO','FI']
        [1 + intHash32(number * 11) % 40]                                    AS country,
    ['ios','android','web','web','web','desktop'][1 + intHash32(number * 13) % 6] AS device,
    concat('/api/v1/', ['orders','users','catalog','cart','search','checkout','profile','reports','events','billing']
        [1 + intHash32(number * 17) % 10], '/', toString(intHash32(number * 19) % 30)) AS url_path,
    multiIf(intHash32(number * 23) % 1000 < 950, 200,
            intHash32(number * 23) % 1000 < 985, 404,
            intHash32(number * 23) % 1000 < 995, 500, 302)                   AS http_status,
    toUInt16(8 + intHash32(number * 29) % 240 + if(intHash32(number * 31) % 40 = 0, 1200, 0))     AS latency_ms,
    toUInt32(180 + intHash32(number * 37) % 4000 + if(intHash32(number * 41) % 25 = 0, 60000, 0)) AS bytes_out
FROM numbers_mt(0, 50000000)          -- then 50000000, 100000000, ... up to 950000000
SETTINGS max_insert_threads = 2, max_threads = 2;

OPTIMIZE TABLE lab.events FINAL;

-- the toggles that isolate each design choice
SELECT ... SETTINGS use_query_condition_cache = 0;   -- always, for first-run numbers
SELECT ... SETTINGS use_skip_indexes = 0;            -- choice 3
SELECT ... SETTINGS max_block_size = 1024;           -- choice 5
SELECT ... SETTINGS optimize_move_to_prewhere = 0;   -- choice 5
SELECT ... SETTINGS max_threads = 1;                 -- choice 6

Further reading

Further reading

• Columnar databases vs row-based databases, measured on 20 million rows: the same mechanisms beside PostgreSQL 16, with the row-store side of every comparison above.
• How Cloudflare uses ClickHouse at quadrillion-row scale and A quadrillion rows across three clouds: scaling LogHouse, the published deployments quoted in Table 1.
• ClickBench, the independent benchmark whose methodology (cold run, then two hot runs, each reported) is the one to copy.
• ClickHouse documentation: a practical introduction to primary indexes, the mark-selection algorithm behind Figure 2.
• ClickHouse documentation: incremental materialized views, including the -State and -Merge combinator contract used in design choice 7.
• ChistaDATA University: instructor-led ClickHouse training, including MergeTree internals and ClickHouse performance engineering modules.

ChistaDATA Inc. is not affiliated with ClickHouse, Inc. ClickHouse® is a registered trademark of ClickHouse, Inc. Third-party scale figures are quoted from the linked publications. Lab behaviour was verified on open-source ClickHouse 26.7; test every change in staging before applying it to production and maintain a tested disaster-recovery posture.