Advanced ClickHouse, explained interactively

Beyond the basics

The foundational ClickHouse lesson covered columnar storage, the MergeTree sparse index, parts and merges, and the engine family. This lesson assumes all of that and digs into the machinery that makes a production deployment fast and cheap: how columns are compressed, and the features that lean on good compression - materialized views, projections and data-skipping indexes, and SharedMergeTree cloud scaling.

Compression is the spine, so we start there. A columnar analytics engine is almost always limited by how many bytes it must pull off disk, so shrinking each column turns an I/O-bound scan into much cheaper CPU-bound work: the processor decompresses a small block instead of waiting on a large read. Everything else in this lesson is, in one way or another, about reading fewer bytes.

The effect is large in practice. On the public ClickBench benchmark ClickHouse typically stores data around 9-10x smaller than row-oriented databases, and production deployments often report 15-20x compression on real event and telemetry data (figures are illustrative and vary by workload). Less data on disk is the foundation every later feature builds on.

The compression pipeline has two stages

A ClickHouse column is compressed in two stages. Stage 1 is an optional column codec that rewrites the raw values into a lower-entropy form - storing differences, XOR-ing adjacent floats, or transposing integer bits. Stage 2 is a general-purpose compressor that squeezes that result down to the bytes that land on disk. You declare both in one CODEC(...) clause, and the chain is applied left-to-right on write and right-to-left on read.

Which general compressor runs by default depends on where ClickHouse runs: self-managed and open-source builds default to LZ4 (very fast, modest ratio), while ClickHouse Cloud defaults to ZSTD at level 1, trading a little CPU for noticeably smaller files. Either way the two stages work together: a well-chosen codec lowers the entropy so much that the general compressor wins big. Toggle the codecs below to see how each one reshapes the same column before that second stage runs.

Choosing codecs and a compressor

So which codec, and which compressor? It depends entirely on the column. The general compressor is the broad choice: LZ4 decompresses very fast (several GB/s per core) with a modest ratio, while ZSTD is typically about 30% smaller on disk but roughly 2-3x slower to decompress, and it takes a level from 1 to 22 (e.g. ZSTD(3)). LZ4 is the self-managed default and ZSTD(1) the ClickHouse Cloud default. On top of that you stack a column codec matched to the data:

• Delta / DoubleDelta for sorted, slowly-changing or monotonic integers and timestamps.
• Gorilla, FPC, and ALP for floating-point columns: Gorilla XORs adjacent values and suits slow-moving gauges and metrics; FPC uses a predictor and fits correlated scientific or financial floats; and ALP (Adaptive Lossless floating-Point) is the newest, added in ClickHouse 26.3 LTS (March 2026).
• T64 and GCD for integer columns with a narrow range (T64 bit-transposes a block, a frame-of-reference idea) or a common divisor (GCD divides one out).
• LowCardinality - not strictly a codec but a column wrapper - dictionary-encodes columns with few distinct values (countries, statuses, device types), replacing repeated values with small integer keys. It pays off most when a column holds below roughly 10,000 distinct values per block.

Two things are close to free compression: a good ORDER BY and LowCardinality on repetitive columns. Sorting clusters similar values next to each other, so a low-cardinality prefix column compresses with near run-length efficiency and every codec has less to encode. The lab below combines a column profile, a codec stack, and the sort toggle so you can feel the trade-offs.

Materialized views: roll-ups on insert

The cheapest query is one you never run. A ClickHouse materialized view is insert-triggered and incremental: as rows land in the base table, the view computes a small roll-up and appends it to a target table - usually an AggregatingMergeTree. The view stores partial aggregate states with the -State combinators (sumState, uniqState, quantileState), and you finalise them at read time with the matching -Merge combinators. Because the maintenance happens incrementally at insert time, this is the scalable default for keeping aggregates fresh.

The payoff is that dashboard queries read a handful of pre-aggregated rows instead of scanning every event. Insert a few batches and compare the cost of a raw scan with the cost of reading the view.

Projections and data-skipping indexes

The sparse primary index only helps filters on the sorting key. For everything else, ClickHouse has two ways to read fewer granules. Data-skipping indexes (minmax for ranges, set for low-cardinality equality, bloom_filter for high-cardinality equality) store a per-granule summary so the engine can skip granules that cannot match. Projections store the same data again in an alternate sort order or pre-aggregated, inside the table, and the optimiser picks the best one automatically.

Both cost extra storage and write amplification - the second copy or the index has to be written and merged too. Pick a query and watch which structure serves it.

The optimiser keeps getting sharper about projections: recent 26.x releases let aggregate projections be used through views, improved in-order and optimiser handling, and added a commit_order projection (26.4), so the planner picks a projection automatically in more cases than before.

Scaling in the cloud: SharedMergeTree

For high availability on your own hardware, ReplicatedMergeTree keeps a full copy of the data on every replica and coordinates them through ClickHouse Keeper (the built-in ZooKeeper replacement). It is shared-nothing: N replicas means N copies of the data, and adding a replica means copying the whole dataset. It remains the right choice for self-managed, shared-nothing clusters.

ClickHouse Cloud changes the shape with SharedMergeTree, now GA and the default engine for every table type in Cloud (and in BYOC on AWS). It separates storage from compute: data lives once on object storage and stateless compute nodes attach to it. Replication is asynchronous and leaderless, coordinated through ClickHouse Keeper - and because Keeper holds only metadata, there is no replica-to-replica data copying. Storage stays flat no matter how many nodes you add, you can run hundreds of replicas, and you scale compute up or down with no resharding or rebalancing. Specialised counterparts exist too, such as SharedReplacingMergeTree.

New readers spin up instantly with no data copy, and parallel replicas split a single query across nodes - a trick both engines support. Toggle the engine and slide the replica count.

More advanced features worth knowing

A few more capabilities round out a production deployment:

• TTL tiering and recompression. A TTL clause can move old partitions to cheaper storage and recompress them - for example, keep recent data on fast disks with LZ4, then TTL ts + INTERVAL 30 DAY RECOMPRESS CODEC(ZSTD(12)) to shrink cold data with maximum-ratio ZSTD. The same mechanism can drop data once it expires.
• Refreshable materialized views. Where incremental views react to each insert, a refreshable materialized view rebuilds on a schedule (REFRESH EVERY 1 HOUR or REFRESH AFTER) - useful for full-table recomputations or joins that cannot be maintained incrementally. They have been non-experimental since version 24.10, support an APPEND mode, can be chained with DEPENDS ON (dbt-style dependencies), and are monitored through system.view_refreshes.
• Native vector search. ClickHouse has a built-in vector_similarity HNSW index for approximate-nearest-neighbour search in plain SQL, GA since version 25.8, with quantization (bf16/int8/binary) and pre- and post-filtering. It is its own topic - see the vector search and RAG lesson for how embeddings, ANN indexes, and retrieval fit together.

Together with compression, these features are why a single ClickHouse cluster can serve real-time dashboards, ad-hoc analytics, and increasingly semantic search from one copy of the data.

SQL recipes

The DDL and maintenance commands behind the ideas above. Per-column codecs, changing a codec in place, inspecting what compression actually achieved, an incremental roll-up, and a projection with a skipping index and TTL recompression.

-- Stack a column codec on the general compressor, per column.
-- Stage 1 = the column codec; stage 2 = the general compressor.
-- The second slot defaults to LZ4 (self-managed) or ZSTD(1) (ClickHouse Cloud).
CREATE TABLE events (
    ts       DateTime              CODEC(DoubleDelta, ZSTD(1)),  -- regular timestamps
    user_id  UInt32               CODEC(Delta, LZ4),            -- sorted, slowly changing
    price    Decimal(10, 2)       CODEC(T64, LZ4),              -- narrow integer range
    gauge    Float64              CODEC(Gorilla, ZSTD(1)),      -- slow-moving float
    reading  Float64              CODEC(ALP, ZSTD(1)),          -- ALP: adaptive float codec (26.3 LTS); FPC also fits floats
    country  LowCardinality(String),                            -- dictionary-encoded
    event    LowCardinality(String)
)
ENGINE = MergeTree
ORDER BY (toDate(ts), user_id)     -- sorting makes the codecs work harder
PARTITION BY toYYYYMM(ts)
SETTINGS index_granularity = 8192;

-- Change a column's codec in place, then rewrite parts to apply it.
ALTER TABLE events
    MODIFY COLUMN price Decimal(10, 2) CODEC(GCD, ZSTD(3));

-- MODIFY COLUMN only affects parts written afterwards; force a rewrite
-- of existing parts so the new codec is applied everywhere.
OPTIMIZE TABLE events FINAL;

-- Per-column: compressed vs uncompressed bytes and the ratio.
SELECT
    name,
    formatReadableSize(sum(data_compressed_bytes))   AS compressed,
    formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed,
    round(sum(data_uncompressed_bytes)
        / sum(data_compressed_bytes), 1)             AS ratio
FROM system.columns
WHERE table = 'events'
GROUP BY name
ORDER BY sum(data_compressed_bytes) DESC;

-- Per-part: how compression and merges are doing.
SELECT name, rows, formatReadableSize(bytes_on_disk) AS on_disk
FROM system.parts
WHERE table = 'events' AND active
ORDER BY modification_time DESC;

-- Incremental roll-up: AggregatingMergeTree + state combinators.
CREATE TABLE events_daily (
    day        Date,
    country    LowCardinality(String),
    revenue    AggregateFunction(sum, Decimal(10, 2)),
    uniq_users AggregateFunction(uniq, UInt32)
) ENGINE = AggregatingMergeTree
ORDER BY (day, country);

CREATE MATERIALIZED VIEW events_daily_mv TO events_daily AS
SELECT toDate(ts) AS day, country,
       sumState(price)    AS revenue,   -- store partial states (-State)
       uniqState(user_id) AS uniq_users
FROM events
GROUP BY day, country;

-- Finalise the states at read time with -Merge.
SELECT day, country,
       sumMerge(revenue)     AS revenue,
       uniqMerge(uniq_users) AS users
FROM events_daily
GROUP BY day, country;

-- A projection serves an alternate order / pre-aggregation.
ALTER TABLE events ADD PROJECTION by_country (
    SELECT country, toDate(ts) AS day, sum(price)
    GROUP BY country, day
);
ALTER TABLE events MATERIALIZE PROJECTION by_country;

-- A data-skipping index prunes granules for a non-sort-key filter.
ALTER TABLE events
    ADD INDEX idx_country country TYPE set(100)        GRANULARITY 4,
    ADD INDEX idx_user    user_id TYPE bloom_filter(0.01) GRANULARITY 1;

-- TTL tiering: recompress cold data with maximum-ratio ZSTD.
ALTER TABLE events
    MODIFY TTL ts + INTERVAL 30 DAY RECOMPRESS CODEC(ZSTD(12));

Key takeaways

• Compression runs in two stages: an optional column codec lowers entropy, then a general compressor squeezes the result - LZ4 (the self-managed default) for speed, ZSTD (the ClickHouse Cloud default, at level 1) for size.
• Match the codec to the column: Delta / DoubleDelta for sorted integers and timestamps, Gorilla, FPC, or ALP for floats, T64 / GCD for narrow-range integers, and LowCardinality for repetitive values.
• A good ORDER BY and LowCardinality are close to free compression - sorted, low-cardinality columns give codecs far less to encode.
• Materialized views (AggregatingMergeTree, -State / -Merge) keep roll-ups cheap; projections and data-skipping indexes read fewer granules at the cost of storage and write amplification.
• SharedMergeTree is GA and the default engine in ClickHouse Cloud: it separates storage from compute, with one copy on object storage, stateless readers added instantly with no resharding, and leaderless replication coordinated by Keeper. ReplicatedMergeTree remains the self-managed, shared-nothing option.

Check your understanding

Five quick questions.