Advanced ClickHouse Guide: Optimization, Materialized Views and Real Tricks

How to get the most out of ClickHouse: ORDER BY, materialized views, projections, codecs, TTL, S3 tiering and slow-query diagnosis. With real SQL.

Fundamental principle: read less data
1. The sort key: the decision that determines everything
2. Materialized views: move the work to insert time
3. Projections: the same table sorted two ways
4. Compression codecs: the most undervalued tuning
- Measure before and after
- The data type is also compression
5. Skip indexes: use them carefully
- The important warning
6. TTL and tiering: data that cleans itself up
7. Insertion: the mistake that sinks everyone
8. Diagnosis: finding what's wrong
9. Smarter queries
My optimization checklist
A final reflection on optimizing
Frequently asked questions

Fundamental principle: read less data

Everything that follows is a variation on the same idea. ClickHouse isn't fast because it processes data quickly —it does that too— but because it processes much less data than you think.

Every query you launch tells you exactly how much it read:

6 rows in set. Elapsed: 0.021 sec. Processed 1.14 million rows, 8.32 MB

That line is your main metric. If your table has 500 million rows and a query processes 500 million, you haven't optimized anything: you're doing a very fast full scan. The goal is always to bring that number down.

The tools to bring it down, in order of impact: the sort key, materialized views, projections, skip indexes and partitioning. In that order.

1. The sort key: the decision that determines everything

If you take only one thing from this post, let it be this section.

How the sparse index works

ClickHouse has no per-row B-tree indexes. It physically sorts the data on disk by your ORDER BY and stores a mark every 8,192 rows (a granule), noting the key value at that position. The full index takes a few megabytes per terabyte.

When you filter by a column that's in the ORDER BY, ClickHouse looks at that index, identifies which granules could contain matching values, and reads only those. Everything else goes untouched.

The consequence is direct: a column only speeds up your query if it's in the prefix of the ORDER BY. If your key is (evento, fecha, usuario_id), filtering by evento is blazing fast, filtering by evento AND fecha too, but filtering only by usuario_id forces a full scan. Order matters just like in a Postgres composite index, but the consequences are much bigger.

The three rules for choosing the ORDER BY

Rule 1: first the columns you always filter by. Look at your real queries, not hypothetical ones. If 95% carry WHERE tenant_id = ?, tenant_id goes first.

Rule 2: at equal usage, lower cardinality first. A column with 6 distinct values (country) groups the data better than one with 50,000 (usuario_id). Putting high cardinality first fragments the granules and ruins both compression and block skipping.

Rule 3: the time column almost always goes in, but rarely first. It's tempting to put timestamp at the start because "everything is filtered by date". It's usually better to have (tenant_id, evento, fecha) than (fecha, tenant_id, evento), because the date is already fairly correlated with the natural insertion order.

An example of a well-designed table:

CREATE TABLE eventos
(
    tenant_id   UInt32,
    fecha       Date,
    timestamp   DateTime CODEC(Delta, ZSTD(1)),
    evento      LowCardinality(String),
    pais        LowCardinality(String),
    usuario_id  UInt32 CODEC(Delta, ZSTD(1)),
    url         String CODEC(ZSTD(3)),
    duracion_ms UInt32 CODEC(T64, ZSTD(1))
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(fecha)
ORDER BY (tenant_id, evento, fecha, usuario_id);

Separating the primary key from the sort key

A detail few people use: you can have a long ORDER BY and a shorter PRIMARY KEY.

ENGINE = MergeTree
ORDER BY (tenant_id, evento, fecha, usuario_id)
PRIMARY KEY (tenant_id, evento)

The ORDER BY controls the physical order (and therefore compression), while the PRIMARY KEY controls what's stored in the in-memory index. If your sort key has five columns and you only filter by the first two, this reduces the index's RAM usage without losing anything.

How to verify your key works

Turn on the trace and watch how many granules get discarded:

SET send_logs_level = 'trace';

SELECT count() FROM eventos WHERE tenant_id = 42 AND evento = 'purchase';

In the logs you'll see lines like:

Key condition: (column 0 in [42, 42]), (column 1 in ['purchase', 'purchase'])
Selected 3/48 parts by partition key, 3 parts by primary key, 14/61035 marks by primary key

14/61035 marks means you read 0.02% of the table. That's a well-chosen key. If you see 61035/61035, your filter isn't using the index.

2. Materialized views: move the work to insert time

This is the feature with the most impact and the most misunderstood.

A materialized view in ClickHouse isn't a cache or a table that refreshes periodically. It's an insert trigger: every time a block of data arrives at the source table, the query runs over that block and the result is written to the destination table. The original data is never read again.

This has a critical implication: a materialized view only sees data inserted after it's created. The historical data has to be backfilled by hand.

The standard pattern

Three pieces: a source table, a destination table for aggregates, and the view that connects them.

-- 1. Destination table with aggregation states
CREATE TABLE eventos_diarios
(
    tenant_id       UInt32,
    fecha           Date,
    evento          LowCardinality(String),
    pais            LowCardinality(String),
    total           UInt64,
    usuarios_unicos AggregateFunction(uniq, UInt32),
    duracion_media  AggregateFunction(avg, UInt32)
)
ENGINE = SummingMergeTree
ORDER BY (tenant_id, fecha, evento, pais);

-- 2. The materialized view that feeds it
CREATE MATERIALIZED VIEW mv_eventos_diarios
TO eventos_diarios
AS
SELECT
    tenant_id,
    fecha,
    evento,
    pais,
    count()                 AS total,
    uniqState(usuario_id)   AS usuarios_unicos,
    avgState(duracion_ms)   AS duracion_media
FROM eventos
GROUP BY tenant_id, fecha, evento, pais;

Notice uniqState and avgState. The -State suffix stores the intermediate state of the aggregation instead of the final result. That's what lets you combine aggregates from different blocks correctly.

To query, you use the -Merge suffix:

SELECT
    fecha,
    evento,
    sum(total)                    AS eventos,
    uniqMerge(usuarios_unicos)    AS usuarios,
    avgMerge(duracion_media)      AS duracion
FROM eventos_diarios
WHERE tenant_id = 42
  AND fecha >= today() - 30
GROUP BY fecha, evento
ORDER BY fecha;

Why this matters so much: this query reads a few thousand pre-aggregated rows instead of hundreds of millions of raw rows. The difference isn't 2x, it's two or three orders of magnitude. And because the calculation happens at insert time, the cost is distributed over time instead of concentrated at the moment your user looks at the dashboard.

A common mistake: using avg() instead of avgState() and then averaging averages when querying. That gives wrong results because the mean of means isn't the mean. Aggregation states exist precisely to avoid this.

Backfilling history

Since views only capture new data, after creating the view you backfill backwards with an INSERT ... SELECT over the same query:

INSERT INTO eventos_diarios
SELECT
    tenant_id, fecha, evento, pais,
    count()                 AS total,
    uniqState(usuario_id)   AS usuarios_unicos,
    avgState(duracion_ms)   AS duracion_media
FROM eventos
WHERE fecha < today()
GROUP BY tenant_id, fecha, evento, pais;

Do it in date ranges if the table is large, so you don't blow up memory.

Refreshable materialized views

For a few versions now there's also the refreshable variant, which does re-run the full query periodically:

CREATE MATERIALIZED VIEW mv_resumen
REFRESH EVERY 1 HOUR
ENGINE = MergeTree ORDER BY fecha
AS SELECT ... ;

Useful when you need JOINs with dimension tables that change, something incremental views handle poorly. More expensive, but much simpler to reason about.

3. Projections: the same table sorted two ways

Here's the classic problem: your ORDER BY is optimized for filtering by tenant_id, but you also need fast queries filtering by url. You can only have one physical order.

Projections solve this by storing an additional copy of the data sorted differently, inside the same table. ClickHouse picks which to use automatically.

ALTER TABLE eventos ADD PROJECTION proj_por_url
(
    SELECT
        url,
        fecha,
        count(),
        avg(duracion_ms)
    GROUP BY url, fecha
);

-- Materialize it for existing data
ALTER TABLE eventos MATERIALIZE PROJECTION proj_por_url;

Now a query that groups by url will use the projection without you changing the SQL. Verify it with EXPLAIN:

EXPLAIN indexes = 1
SELECT url, count() FROM eventos WHERE fecha >= today() - 7 GROUP BY url;

Projections vs. materialized views. The obvious question. My criterion:

Use projections when you want the same data with a different order or a derived aggregation of the same table, and you value that it's transparent (you don't change queries, consistency is automatic).

Use materialized views when you need to transform the data, write to a table with a different TTL, chain several stages or combine several sources. And when the destination table should survive independently.

The cost of projections is storage (you store the data twice) and insert speed. Don't add five projections to a table that receives constant inserts.

4. Compression codecs: the most undervalued tuning

Fewer bytes on disk = less I/O = faster queries. Compression isn't a storage-savings topic, it's a performance topic.

ClickHouse applies LZ4 by default and it works well, but tuning per column gives real gains. The official documentation recommends, in order of importance:

ZSTD as the base. It offers the best compression ratios and ZSTD(1) is a good default for most types. Going above ZSTD(3) rarely pays for the insert cost.

Delta for date and integer sequences. Works very well with monotonic sequences or with small differences between consecutive values. Timestamps and auto-incremental IDs are the canonical case. If the result of the first derivative isn't small enough, try DoubleDelta.

Delta improves on ZSTD. They combine well: CODEC(Delta, ZSTD(1)) usually beats either one separately.

LZ4 if it ties with ZSTD. If you get comparable compression, prefer LZ4 because it decompresses faster and uses less CPU. In practice ZSTD wins by a wide margin in most cases.

T64 for small ranges or sparse data. Effective when the range of values within a block is small. Avoid it with random numbers.

Gorilla for sensor-type floats. Designed for meter readings with small variations.

In practice:

CREATE TABLE metricas
(
    timestamp  DateTime      CODEC(Delta, ZSTD(1)),
    sensor_id  UInt32        CODEC(Delta, ZSTD(1)),
    temperatura Float32      CODEC(Gorilla, ZSTD(1)),
    estado     LowCardinality(String),
    payload    String        CODEC(ZSTD(3))
)
ENGINE = MergeTree
ORDER BY (sensor_id, timestamp);

Measure before and after

SELECT
    name,
    formatReadableSize(sum(data_compressed_bytes))   AS comprimido,
    formatReadableSize(sum(data_uncompressed_bytes)) AS sin_comprimir,
    round(sum(data_uncompressed_bytes) / sum(data_compressed_bytes), 2) AS ratio
FROM system.columns
WHERE table = 'metricas'
GROUP BY name
ORDER BY sum(data_compressed_bytes) DESC;

Sort by compressed size and attack only the two or three biggest columns. Tuning the codec of a column that takes 3 MB is wasted time.

A note on compact parts: if you see zeros in compressed_size, it's because your parts are compact instead of wide (happens with small inserts). It's controlled by min_bytes_for_wide_part and min_rows_for_wide_part.

The data type is also compression

Before touching codecs, review types. A UInt8 instead of an Int64 for a 0-to-100 value is a free 8x reduction. LowCardinality(String) for columns with fewer than ~10,000 distinct values. DateTime instead of String for dates. The ClickHouse documentation shows that just by optimizing types and the sort key, the same dataset went from 50 GB to 25 GB compressed.

5. Skip indexes: use them carefully

Data skipping indexes let you skip blocks by filtering on columns that aren't in the ORDER BY. They sound like the universal solution and almost never are.

ALTER TABLE eventos ADD INDEX idx_pais pais TYPE set(100) GRANULARITY 4;
ALTER TABLE eventos MATERIALIZE INDEX idx_pais;

Main types:

minmax stores the minimum and maximum per block. The cheapest to apply. Ideal for approximately ordered columns and for range filters.

set(N) stores up to N distinct values per block. Good with low cardinality within each block but high global cardinality.

bloom_filter for testing membership in large sets of values. Works on arrays and maps.

text is the real inverted index for full-text search. It's the recommended one today; the old tokenbf_v1 and ngrambf_v1 are marked as deprecated.

The important warning

The official documentation is explicit on this and it bears repeating: the natural impulse to add an index to a column you query a lot is usually wrong in ClickHouse.

A skip index only helps if there's strong correlation between the primary key and the indexed column. If that column's values are scattered randomly throughout the table, every block will contain some and nothing will get skipped. You'll have paid the index cost (on insert and on query) for zero benefit.

Before adding a skip index, try in this order: change the ORDER BY, add a projection, or create a materialized view. Skip indexes are the last resort, not the first.

A case where they do shine: rare but important values. A set index on error_code in a logs table lets you skip the vast majority of blocks with no errors.

6. TTL and tiering: data that cleans itself up

Automatic retention is one of ClickHouse's best operational features and one of the least used.

Automatic deletion

ALTER TABLE eventos MODIFY TTL fecha + INTERVAL 90 DAY;

Data older than 90 days disappears in background merges. No cron, no script, no supervision.

Aggregation as data ages

More interesting: reduce the resolution of old data instead of deleting it.

-- The TTL grouping key must be a prefix of the primary key
CREATE TABLE metricas_sensores
(
    timestamp   DateTime CODEC(Delta, ZSTD(1)),
    sensor_id   UInt32   CODEC(Delta, ZSTD(1)),
    temperatura Float32  CODEC(Gorilla, ZSTD(1)),
    estado      LowCardinality(String)
)
ENGINE = MergeTree
ORDER BY (sensor_id, toStartOfHour(timestamp), timestamp);

ALTER TABLE metricas_sensores MODIFY TTL
    timestamp + INTERVAL 7 DAY
        GROUP BY sensor_id, toStartOfHour(timestamp)
        SET temperatura = avg(temperatura);

Per-second data for a week, per-hour after. You keep the useful history taking a fraction of the space.

Watch out for this detail, because it's where everyone fails the first time: ClickHouse requires that the columns in the TTL's GROUP BY be a prefix of the primary key. If your table is sorted by (sensor_id, timestamp) and you group by toStartOfHour(timestamp), you'll get the error TTL Expression GROUP BY key should be a prefix of primary key. The fix is to include the rounded expression in the ORDER BY itself, as in the example above.

S3 tiering

The trick that makes long retention viable at low cost. Configure an S3 disk and a storage policy:

<!-- /etc/clickhouse-server/config.d/storage.xml -->
<clickhouse>
  <storage_configuration>
    <disks>
      <s3_frio>
        <type>s3</type>
        <endpoint>https://mi-bucket.s3.eu-west-1.amazonaws.com/clickhouse/</endpoint>
        <access_key_id>TU_ACCESS_KEY</access_key_id>
        <secret_access_key>TU_SECRET_KEY</secret_access_key>
      </s3_frio>
    </disks>
    <policies>
      <caliente_frio>
        <volumes>
          <caliente><disk>default</disk></caliente>
          <frio><disk>s3_frio</disk></frio>
        </volumes>
      </caliente_frio>
    </policies>
  </storage_configuration>
</clickhouse>

And apply it:

ALTER TABLE eventos MODIFY SETTING storage_policy = 'caliente_frio';

ALTER TABLE eventos MODIFY TTL
    fecha + INTERVAL 30 DAY TO VOLUME 'frio',
    fecha + INTERVAL 365 DAY DELETE;

The last 30 days on local SSD (fast), the rest on S3 (cheap), and everything gets deleted at one year. For a small project this is what turns "I keep 30 days because I can't pay more" into "I keep two years for pennies". Queries over cold data are slower, obviously, but they still work and they're the ones run the least.

7. Insertion: the mistake that sinks everyone

The number one problem in production isn't slow queries, it's Too many parts.

Each INSERT creates a new part on disk. Parts are merged in the background, but if you insert faster than they merge, they pile up until the server rejects writes.

Rule: insert in batches

At least 1,000 rows per insert; ideally between 10,000 and 100,000. Buffer in your application and flush by size or by time (whichever happens first).

Alternative: async inserts

If your architecture doesn't let you buffer easily —for example, many processes writing single events— let ClickHouse do it for you:

SET async_insert = 1;
SET wait_for_async_insert = 1;

ClickHouse buffers in a server-side buffer and flushes in batches. With wait_for_async_insert = 1 the client waits for confirmation of the real write (safer); with 0 it gets immediate confirmation (faster, but you can lose data if the server crashes before the flush).

Tune the buffer behavior with async_insert_max_data_size and async_insert_busy_timeout_ms.

Diagnosis

SELECT
    table,
    count()                                  AS num_parts,
    sum(rows)                                AS filas,
    formatReadableSize(sum(bytes_on_disk))   AS tamano
FROM system.parts
WHERE active AND database = 'creacosas'
GROUP BY table
ORDER BY num_parts DESC;

If a table has hundreds of active parts, your insertion pattern is the problem.

And no, OPTIMIZE TABLE ... FINAL isn't the solution. It forces a full merge that's very expensive on large tables and doesn't fix the cause. The official documentation explicitly recommends avoiding it.

8. Diagnosis: finding what's wrong

The query log is your best tool

ClickHouse logs every query with its metrics:

SELECT
    query_duration_ms,
    formatReadableQuantity(read_rows)    AS filas_leidas,
    formatReadableSize(read_bytes)       AS bytes_leidos,
    formatReadableSize(memory_usage)     AS memoria,
    substring(query, 1, 120)             AS consulta
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_time > now() - INTERVAL 1 HOUR
ORDER BY query_duration_ms DESC
LIMIT 20;

Sort by duration and attack the worst ones. Compare read_rows with the table total: if they're close, your filter isn't using the index.

EXPLAIN

EXPLAIN indexes = 1
SELECT ... ;

It shows you which indexes and projections are being used and how many granules get discarded at each step.

PREWHERE

ClickHouse usually applies this optimization automatically, but you can force it. PREWHERE reads the filter columns first, discards rows, and only then reads the rest of the columns:

SELECT url, payload_grande
FROM eventos
PREWHERE evento = 'error'      -- read only this column first
WHERE duracion_ms > 5000;

It's especially useful when you filter on a small column and select large columns.

Query cache

For dashboards with identical repeated queries:

SELECT ... SETTINGS use_query_cache = 1;

Configure the TTL globally. It isn't a silver bullet —it only helps if queries repeat literally— but on a dashboard that several people look at once, it saves a fair amount.

9. Smarter queries

Approximate aggregation when exactness isn't critical. uniq() uses HyperLogLog and is much faster and lighter on memory than uniqExact(). For a "unique users" dashboard, a 0.5% error is irrelevant.

Dictionaries instead of JOINs. If you JOIN repeatedly against a small dimension table, load it as an in-memory dictionary:

CREATE DICTIONARY dic_paises (
    codigo String,
    nombre String
)
PRIMARY KEY codigo
SOURCE(CLICKHOUSE(TABLE 'paises'))
LAYOUT(COMPLEX_KEY_HASHED())
LIFETIME(3600);

SELECT dictGet('dic_paises', 'nombre', pais) AS pais_nombre, count()
FROM eventos GROUP BY pais;

A dictGet is a lookup in an in-memory hash: orders of magnitude faster than a JOIN.

Combinator functions. -If, -Array, -Merge avoid whole subqueries:

SELECT
    fecha,
    countIf(evento = 'purchase')                      AS compras,
    countIf(evento = 'signup')                        AS registros,
    avgIf(duracion_ms, evento = 'pageview')           AS duracion_pageview
FROM eventos
GROUP BY fecha;

A single pass over the data instead of three.

windowFunnel for conversion funnels. Computing a funnel in standard SQL is painful. Here it's a function:

SELECT
    nivel,
    count() AS usuarios
FROM (
    SELECT
        usuario_id,
        windowFunnel(3600)(
            timestamp,
            evento = 'pageview',
            evento = 'signup',
            evento = 'purchase'
        ) AS nivel
    FROM eventos
    WHERE fecha >= today() - 30
    GROUP BY usuario_id
)
GROUP BY nivel
ORDER BY nivel;

My optimization checklist

The order in which I attack a slow ClickHouse, by impact/effort ratio:

Look at system.query_log and find the genuinely slow queries. Don't optimize blind.
Check whether the ORDER BY serves your most frequent filters. If not, redo the table. It hurts, and it gives the most result.
Review data types. LowCardinality, integers of the right size, remove unnecessary Nullable.
Create materialized views for the aggregations that feed dashboards.
Verify the insertion pattern. Count active parts; batch if there are too many.
Apply codecs to the two or three biggest columns.
Add projections for the second most common access pattern.
Configure TTL and tiering so storage cost doesn't grow out of control.
Skip indexes, only if all of the above wasn't enough and there's real correlation.

Steps 1 to 4 usually solve 90% of problems. If you're on step 9 often, the problem is probably in the schema design, not in the lack of indexes.

A final reflection on optimizing

The best optimization I've ever done in ClickHouse was deleting a table.

We had a raw events table that nobody queried directly: everyone used the materialized views. It was there "just in case", taking up most of the disk and slowing down merges. We put a 30-day TTL on it, and everything —inserts, queries, backups— improved.

The temptation in ClickHouse is to tune. There are so many levers —codecs, indexes, projections, merge settings— that it's easy to spend weeks adjusting parameters for a 15% gain. Almost always there's a design decision that gives a 10x and is in plain sight: a poorly chosen sort key, a materialized view that doesn't exist, or data you shouldn't be storing.

Optimize the design before the parameters. And always measure: the "processed N rows" line at the end of each query tells you the truth, no matter how elegant your configuration is.