ClickHouse in 10 seconds: 15 features worth knowing

ClickHouse has a lot of features that make your life easier, but most of them are buried somewhere deep in the docs. You usually find them after you’ve already built a workaround: a cron job, a calendar table, a window function nobody can read. I wanted to fix that, at least a little, so I made a short series called “ClickHouse in 10 seconds”. Each part shows one feature in a short animation you can grasp in about 10 seconds, plus a tiny example you can copy.

1. Column codecs

ClickHouse compresses every column with LZ4 by default, which is already good. But you can pick a codec per column, and for typed data like timestamps or sensor readings that makes a huge difference. On 100GB of synthetic sensor data, default LZ4 took 25.8GB on disk. With per-column codecs it went down to 6.5GB, 4× smaller than default and 15.5× smaller than raw. The timestamp column alone went from 1.06GB to 37.5MB.

CREATE TABLE sensors
(
    ts     DateTime CODEC(DoubleDelta, ZSTD),
    temp   Float64  CODEC(Gorilla, ZSTD),
    visits UInt32   CODEC(T64, ZSTD)
)
ENGINE = MergeTree
ORDER BY ts;

DoubleDelta works well for timestamps, Gorilla for floats, T64 for integers, and ZSTD goes on top. To be fair, that data was built to compress well. Check what your own columns do before you copy the numbers:

SELECT name,
       formatReadableSize(data_compressed_bytes)   AS compressed,
       formatReadableSize(data_uncompressed_bytes) AS uncompressed
FROM system.columns
WHERE table = 'sensors';
ClickHouse in 10 seconds #1 Column codecs events.sql ingest.sql MergeTree · UTF-8 synthetic 100 GB benchmark events · raw size default (LZ4) with codecs 100GB 25.8GB 6.5GB 4× smaller than default 1 2 3 4 5 6 7 CREATE TABLE events ( CODEC(,), CODEC(,), CODEC(,) ) ENGINE = MergeTree ORDER BY ts raw100 GB default (LZ4)25.8 GB codecs6.5 GB LZ4 · default ts DateTime DoubleDelta ZSTD 25 GB1.06 GB37.5 MB temp Float64 Gorilla ZSTD 50 GB9.09 GB3.54 GB visits UInt32 T64 ZSTD 25 GB15.64 GB2.88 GB

Docs: Column compression codecs

2. LowCardinality

Your status column probably stores “pending” 400 million times. Wrap it in LowCardinality and each distinct value is stored once in a dictionary, while rows keep small integer ids. Less disk, faster reads, and your queries stay the same.

CREATE TABLE orders
(
    id     UInt64,
    status LowCardinality(String)
)
ENGINE = MergeTree
ORDER BY id;

The rule of thumb from the docs: under 10k distinct values it usually wins, over 100k it can be slower than a plain String. Country, status, browser, plan: yes. Emails and URLs: no.

ClickHouse in 10 seconds #2 LowCardinality orders.sql ingest.sql MergeTree · UTF-8 1 2 3 4 5 6 7 CREATE TABLE orders ( id UInt64, , country LowCardinality(String) ) ENGINE = MergeTree ORDER BY id dictionaryrowseach value stored once · rows keep tiny ids status column · size on diskStringLowCardinalityrelative, not to scale 'pending'1 'shipped'2 'pending'1 'paid'3 'pending'1 'shipped'2 'pending'1 'refunded'4 'paid'3 'pending'1 dictionaryeach value once 1'pending' 2'shipped' 3'paid' 4'refunded' orders.status · 10 rows shown statusLowCardinality(String)

Docs: LowCardinality

3. LIMIT BY

“Top 3 pages per country” usually means a window function and a subquery. In ClickHouse it’s one clause:

SELECT country, page, count() AS c
FROM visits
GROUP BY country, page
ORDER BY c DESC
LIMIT 3 BY country;

It combines with a normal LIMIT too. LIMIT 3 BY country LIMIT 30 keeps 3 rows per country and 30 rows overall.

ClickHouse in 10 seconds #3 LIMIT BY top_pages.sql visits.sql SQL · UTF-8 1 2 3 4 5 SELECT country, page, count() AS c FROM visits GROUP BY country, page ORDER BY c DESC LIMIT 3 BY country top 3 pages per country · no window functions PL DE US GROUP BY country, page ORDER BY c DESCc (relative) DE '/pricing' US '/docs' DE '/docs' PL '/pricing' US '/pricing' DE '/blog' DE '/careers' PL '/docs' US '/blog' PL '/blog' US '/careers' PL '/about' LIMIT 3 BY countryLIMIT 3 BY country

Docs: LIMIT BY

4. -If combinators

Add If to any aggregate function and it takes a condition. That way you can compute several filtered metrics in a single scan, instead of running one query per metric:

SELECT
    countIf(status >= 500)              AS errors,
    uniqIf(user_id, plan = 'pro')       AS pro_users,
    avgIf(ms, path = '/checkout')       AS checkout_ms
FROM requests;

If is just one of many combinators. sumArray(arr) aggregates array elements, avgOrNull(x) returns NULL instead of nan on empty input, and uniqState(id) stores a partial result, which comes back in part 7.

ClickHouse in 10 seconds #4 -If combinators metrics.sql requests.sql SQL · UTF-8 1 2 3 4 5 SELECT (status >= 500) AS errors, (user_id, plan = 'pro') AS pro_users, (ms, page = '/checkout') AS checkout_ms FROM requests FROM requests · one pass, every row checked once status >= 500 plan = 'pro' page = '/checkout' status200planfreepage/home status503planpropage/checkout status200planpropage/docs status500planfreepage/checkout status200planfreepage/checkout status502planpropage/home status200planfreepage/docs status200planpropage/checkout one scan · three filtered aggregates · bars relative countIf uniqIf avgIf errorspro_userscheckout_ms

Docs: Aggregate function combinators

5. Incremental materialized views

A materialized view in ClickHouse isn’t a cached query that you refresh. It’s an insert trigger: every block inserted into the source table goes through the view’s SELECT, and the result lands in a target table. No refresh job needed.

CREATE TABLE hits_agg
(
    m    DateTime,
    page String,
    hits UInt64
)
ENGINE = SummingMergeTree
ORDER BY (m, page);

CREATE MATERIALIZED VIEW hits_mv TO hits_agg AS
SELECT toStartOfMinute(ts) AS m, page, count() AS hits
FROM events
GROUP BY m, page;

Merges run in the background, so the same minute can show up in more than one row for a while. Always read it with sum(hits) ... GROUP BY m, page. Also, the view only sees new inserts, so you need to backfill old rows yourself.

ClickHouse in 10 seconds #5 Incremental MVs hits_per_min.sql events.sql SQL · UTF-8 1 2 3 4 5 6 7 8 CREATE MATERIALIZED VIEW hits_per_min TO hits_agg AS SELECT toStartOfMinute(ts) AS m, page, count() AS hits FROM events GROUP BY m, page SELECT m, sum(hits) FROM hits_agg GROUP BY m eventsraw rows hits_aggper minute MVhits_per_minGROUP BY m, page INSERT INSERT INSERT INSERT 12:00/home 12:00/docs 12:01/home 12:01/pricing 12:02/home 12:02/docs 12:03/home 12:03/docs rows a dashboard query reads illustrative events hits_agg hits_agg is a SummingMergeTree: no refresh job, no rescan

Docs: Incremental materialized views

6. WITH FILL

Days with zero signups don’t exist in GROUP BY output, so your chart quietly skips them. You don’t need a calendar table to fix it. WITH FILL adds the missing days as rows with 0:

SELECT toDate(created_at) AS day, count() AS signups
FROM users
WHERE created_at >= today() - 30
GROUP BY day
ORDER BY day WITH FILL FROM today() - 30 TO today() + 1;

TO is exclusive, hence the + 1. For an hourly chart, group by toStartOfHour(ts) AS h and use ORDER BY h WITH FILL STEP INTERVAL 1 HOUR.

ClickHouse in 10 seconds #6 WITH FILL signups.sql users.sql SQL · UTF-8 1 2 3 4 5 SELECT toDate(ts) AS day, count() AS signups FROM users GROUP BY day ORDER BY day FROM today() - 14 TO today() WITH FILL missing days come back as 0 · no calendar table 18 19 20 21 22 23 24 25 26 27 28 29 30 31 signups per day · Oct relative norow norow norow norow norow 0 0 0 0 0 WITH FILLWITH FILL

Docs: ORDER BY … WITH FILL

7. -State / -Merge

You can’t add daily unique users up into monthly ones, because the same user shows up on many days. What you can do is store the intermediate state of uniq per day and merge those states later.

CREATE TABLE daily_users
(
    day   Date,
    users AggregateFunction(uniq, UInt64)
)
ENGINE = AggregatingMergeTree
ORDER BY day;

CREATE MATERIALIZED VIEW daily_users_mv TO daily_users AS
SELECT toDate(ts) AS day, uniqState(user_id) AS users
FROM events
GROUP BY day;

SELECT uniqMerge(users)
FROM daily_users
WHERE day >= today() - 30;

The last query reads 30 small rows instead of scanning a month of raw events. uniq is approximate but very accurate. If you need exact counts, use uniqExactState and uniqExactMerge, at the cost of a bigger state.

ClickHouse in 10 seconds #7 -State / -Merge daily_users.sql events.sql SQL · UTF-8 1 2 3 4 5 6 7 8 CREATE MATERIALIZED VIEW daily_users_mv TO daily_users AS SELECT toDate(ts) AS day, uniqState(user_id) AS users FROM events GROUP BY day SELECT uniqMerge(users) FROM daily_users WHERE day >= today() - 30 daily_users: AggregatingMergeTree · uniq is approximate eventsraw rows daily_usersuniqState per day Oct 27 Oct 28 Oct 29 Oct 30 Oct 31 Nov 1 Oct 2727 Oct 2828 Oct 2929 Oct 3030 Oct 3131 Nov 11 uniqMerge(users) uniques, 30 days · 1 row

Docs: AggregatingMergeTree

8. Projections

A table can only be sorted one way. If it’s sorted by time but you often filter by user_id, add a projection. ClickHouse keeps a hidden copy sorted by user_id and uses it whenever it means reading less data. Your queries don’t change.

ALTER TABLE events ADD PROJECTION by_user (SELECT * ORDER BY user_id);
ALTER TABLE events MATERIALIZE PROJECTION by_user;

New inserts are covered automatically, the second statement builds it for the parts that already exist. The cost is extra storage and writing the data twice, so add them sparingly.

ClickHouse in 10 seconds #8 Projections events.sql queries.sql MergeTree · UTF-8 1 2 3 4 5 6 7 ALTER TABLE events ADD PROJECTION by_user (SELECT * ORDER BY user_id); ALTER TABLE events MATERIALIZE PROJECTION by_user; SELECT * FROM events WHERE user_id = 42 a hidden re-sorted copy · picked automatically eventsORDER BY tstsuser_id by_userhidden copyORDER BY user_idtsuser_id 12:0117 12:0117 12:0242 12:024212:0242 12:038 12:038 12:0493 12:0493 12:0542 12:054212:0542 12:0625 12:0625 12:0761 12:0761 12:0842 12:084212:0842 12:098 12:098 12:1030 12:1030 WHERE user_id = 42 by_userORDER BY tsrows read per query rows read illustrative

Docs: Projections

9. TTL

Stop writing cleanup cron jobs. A table TTL can recompress old data, move it to cheaper disks and finally delete it, and ClickHouse does all of that in the background.

ALTER TABLE events MODIFY TTL
    ts + INTERVAL 7 DAY RECOMPRESS CODEC(ZSTD(17)),
    ts + INTERVAL 30 DAY TO VOLUME 'cold',
    ts + INTERVAL 1 YEAR DELETE;

TO VOLUME needs a storage policy with a cold volume. Deletes happen during merges, so expired rows can stay around for a while. merge_with_ttl_timeout (4 hours by default) sets how often a TTL merge can run.

ClickHouse in 10 seconds #9 TTL events.sql storage.xml MergeTree · UTF-8 1 2 3 4 5 6 7 8 CREATE TABLE events (ts DateTime, msg String) ENGINE = MergeTree ORDER BY ts TTL ts + INTERVAL 7 DAY RECOMPRESS CODEC(ZSTD(17)), ts + INTERVAL 30 DAY TO VOLUME 'cold', ts + INTERVAL 1 YEAR DELETE SETTINGS storage_policy = 'hot_cold' now +7 days +30 days +1 year RECOMPRESS TO VOLUME DELETE hot volume volume 'cold'

Docs: MergeTree TTL

10. ReplacingMergeTree

Updates in an append-only database? Just insert a new version of the row. During merges, ReplacingMergeTree keeps the row with the highest ver for each sorting key.

CREATE TABLE users
(
    id         UInt64,
    name       String,
    ver        UInt64,
    is_deleted UInt8
)
ENGINE = ReplacingMergeTree(ver, is_deleted)
ORDER BY id;

SELECT * FROM users FINAL;

Dedup runs at merge time, so without FINAL you may see old versions for a while. Deletes work the same way: insert a new version with is_deleted = 1.

ClickHouse in 10 seconds #10 ReplacingMergeTree users.sql ingest.sql ReplacingMergeTree · UTF-8 1 2 3 4 5 6 7 CREATE TABLE users (id UInt64, plan String, ver UInt32) ENGINE = ReplacingMergeTree(ver) ORDER BY id; INSERT INTO users VALUES (42, 'team', 3); SELECT * FROM users FINAL WHERE id = 42 after a mergewith FINAL, any timesame row: the highest ver per id wins users· ReplacingMergeTree(ver)idplanver part 1 42'free'ver 1 part 2 42'pro'ver 2 part 3 42'team'ver 3 background merge right now, before any mergeSELECT … FINAL 42'team'ver 3

Docs: ReplacingMergeTree

11. Skip indexes

Looking up a single trace_id in a table sorted by time means reading every granule. A bloom filter skip index lets ClickHouse skip granules that can’t contain the value.

ALTER TABLE events ADD INDEX idx_trace trace_id TYPE bloom_filter GRANULARITY 4;
ALTER TABLE events MATERIALIZE INDEX idx_trace;

EXPLAIN indexes = 1
SELECT * FROM events WHERE trace_id = 'abc123';

Like projections, a new index only covers new inserts until you materialize it. EXPLAIN indexes = 1 shows how many granules the index kept, so you can check if it actually helps.

ClickHouse in 10 seconds #11 Skip indexes events.sql explain.sql MergeTree · UTF-8 1 2 3 4 5 6 7 8 ALTER TABLE events ADD INDEX idx_trace trace_id TYPE GRANULARITY 4; ALTER TABLE events MATERIALIZE INDEX idx_trace; EXPLAIN indexes = 1 SELECT * FROM events WHERE trace_id = 'a1f9c03e' bloom_filter bloom_filter per 4 granules"definitely not here"→ skipped, never read"maybe" → readillustrative events · granulesORDER BY ts, so trace_id is scatteredillustrative WHERE trace_id = 'a1f9c03e' bloom_filterbloom_filter

Docs: Data skipping indexes

12. clickhouse-local

Got a 5GB CSV? Don’t import it anywhere. clickhouse local runs queries straight on files, with no server:

curl https://clickhouse.com/ | sh
./clickhouse local -q "SELECT status, count() FROM 'logs.csv' GROUP BY status"

The schema is inferred. The same works for Parquet, JSON lines and globs like 'logs_*.csv', and files on S3 work too through the s3() table function.

ClickHouse in 10 seconds #12 clickhouse-local > zsh — ~/logs no server running $ clickhouse local -q " SELECT status, count() FROM 'logs.csv' 'logs.parquet' s3('s3://my-bucket/logs.parquet') GROUP BY status" 404 2 500 1 200 7 # same query, other sources: FROM 'logs.parquet' FROM s3('s3://my-bucket/logs.parquet') CSVlogs.csv PARQUETlogs.parquet S3my-bucket no server · schema inferred from the filesample file

Docs: clickhouse-local

13. Query cache

Your dashboard runs the same heavy query 50 times a minute. Instead of computing it every time, cache the result on the server:

SELECT page, count() AS hits
FROM events
GROUP BY page
SETTINGS use_query_cache = true, query_cache_ttl = 300;

The first run computes the result, and for the next 5 minutes identical queries read it from the cache. Queries with non-deterministic functions like now() or today() aren’t cached by default.

ClickHouse in 10 seconds #13 Query cache dashboard.sql events.sql SQL · UTF-8 1 2 3 4 5 6 SELECT page, count() AS hits FROM events GROUP BY page ORDER BY hits DESC SETTINGS use_query_cache = true, query_cache_ttl = 300 first runrepeatscomputed, then storedserved from the cachesame query + settings, until the TTL runs outttl dashboard query cacheper query + settings eventsfull computation ttl computing… SELECT … miss SELECT …hit SELECT …hit SELECT …hit SELECT …hit SELECT …hit

Docs: Query cache

14. Parameterized views

A view that works like a function. Define a typed parameter once and pass the value when you query it:

CREATE VIEW user_events AS
SELECT * FROM events WHERE user_id = {uid:UInt64};

SELECT count() FROM user_events(uid = 42);

One definition for every user, and no SQL built from strings. From an application, send the value as a typed query parameter instead of interpolating it.

ClickHouse in 10 seconds #14 Parameterized views user_events.sql events.sql SQL · UTF-8 1 2 3 4 5 CREATE VIEW user_events AS SELECT * FROM events WHERE user_id = SELECT * FROM user_events( ) user_events(uid = 42)one view,any user idat query time eventsuser_id · eventrows out user_eventsVIEW 7'click' 13'click' 7'view' 99'click' 13'view' 42'view' 42'buy' 42'click' {uid:UInt64} uid = 42 uid = 42uid = 42

Docs: CREATE VIEW

15. Dictionaries

Joining a small countries table in every query? Make it a dictionary. It lives in memory, refreshes on its own based on LIFETIME, and every lookup is a key lookup instead of a JOIN.

CREATE DICTIONARY countries
(
    code String,
    name String
)
PRIMARY KEY code
SOURCE(CLICKHOUSE(TABLE 'countries_src'))
LAYOUT(COMPLEX_KEY_HASHED())
LIFETIME(MIN 300 MAX 600);

SELECT dictGet('countries', 'name', country_code) AS country, count()
FROM visits
GROUP BY country;

LIFETIME(MIN 300 MAX 600) reloads the data at a random moment between 5 and 10 minutes, so not all servers hit the source at once. The String key is why it uses the COMPLEX_KEY_HASHED layout.

ClickHouse in 10 seconds #15 Dictionaries countries.sql visits.sql SQL · UTF-8 1 2 3 4 5 6 7 8 9 CREATE DICTIONARY countries (code String, name String) PRIMARY KEY code SOURCE(CLICKHOUSE(TABLE 'country_src')) LAYOUT(HASHED()) LIFETIME(MIN 300 MAX 600) SELECT dictGet('countries', 'name', country) FROM visits a key lookup instead of a JOIN country_srctable DE'Germany' FR'France' PL'Poland' US'United States' JOIN countriesin memory · HASHED DE→'Germany'FR→'France'PL→'Poland'US→'United States' reloads on LIFETIME PL'Poland' DE'Germany' US'United States' visitscountry PL'Poland' FR'France' DE'Germany'

Docs: CREATE DICTIONARY

That’s all, for now

None of these features are new or secret, they’re just easy to miss. If even one of them saves you a cron job or a calendar table, the series did its job.

If you use ClickHouse from Ruby, take a look at ch_connect, my client for the native protocol.

Let's stay in touch

Follow me on X or subscribe to the newsletter for more thoughts on software engineering.