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';
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.