ClickHouse best practices: 12 rules for fast, cheap, stable analytics

ClickHouse is fast by default, which is exactly why teams stop thinking about it — until a table hits a billion rows, a dashboard times out, or the storage bill doubles. Most ClickHouse performance is decided at design time, in choices that are cheap to make right and expensive to undo. Here are twelve rules that separate a cluster that scales smoothly from one that needs a rescue at 10 TB.

1. Pick the narrowest data type that fits

Every byte you store is a byte you read on every scan. Use UInt32 instead of UInt64 when values fit, Date (2 bytes) instead of DateTime (4) when you don't need time-of-day, Float32 over Float64 for metrics, and never store an enumerable string as raw String. This discipline alone often cuts table size by 30–50% with no downside.

2. Reach for LowCardinality on repetitive strings

LowCardinality(String) dictionary-encodes repeated values into integers. For any column with fewer than ~10,000 distinct values — country, status, event type, plan — it shrinks storage and speeds up filtering and grouping. Leave genuinely high-cardinality columns (URLs, user agents) as plain String; the dictionary would only add overhead.

3. Design ORDER BY around your queries, not your intuition

ORDER BY defines the physical sort order and the sparse primary index — the single biggest lever on read performance. Lead with the columns you filter on most, go low-to-high cardinality where you can, and put timestamps last for time-series. Then prove it: EXPLAIN indexes = 1 on real queries shows how many granules survive pruning. Designing for an imagined query mix is the most common subtle mistake in ClickHouse.

ORDER BY (tenant_id, event_type, event_time)

4. Insert in big batches — this is non-negotiable

Every INSERT creates a new immutable part, and merges have to clean them up. Insert row-by-row and you drown the merge pool, inflate part count, and eventually hit 'Too many parts'. Aim for tens of thousands to a few hundred thousand rows per insert — buffer in your app or a queue and flush on a size or time threshold.

5. When you can't batch, use async inserts

Sometimes you can't control batch size — many independent producers, serverless functions, IoT. For that, let the server batch for you.

SET async_insert = 1;
SET wait_for_async_insert = 1;  -- 1 = wait for flush (safer), 0 = fire-and-forget

6. Use materialized views to precompute, not to ETL

A ClickHouse materialized view is not a cached result — it is an insert trigger. When rows land in the source table, the view's SELECT runs over just that block and writes into a target table, usually an aggregating engine. The canonical pattern is roll-ups that turn billion-row scans into thousand-row ones.

CREATE MATERIALIZED VIEW events_daily_mv
TO events_daily AS
SELECT toDate(event_time) AS day, tenant_id, event_type,
       count() AS events, sum(revenue) AS revenue
FROM events
GROUP BY day, tenant_id, event_type;

Keep the SELECT simple and deterministic, aggregate into AggregatingMergeTree or SummingMergeTree, and remember the view only sees new inserts — it won't backfill existing data unless you populate it explicitly.

7. Add projections for alternate sort orders

When one ORDER BY can't serve every query, add a projection — a second physical copy sorted differently, which ClickHouse maintains automatically and picks transparently when it helps. Projections cost storage and write amplification, so use them deliberately for your few highest-value secondary access patterns.

ALTER TABLE events ADD PROJECTION by_user
( SELECT user_id, count(), sum(revenue) GROUP BY user_id );
ALTER TABLE events MATERIALIZE PROJECTION by_user;

8. Use data-skipping indexes for what the primary key can't help

When you frequently filter on a column not in ORDER BY, a skip index can prune granules without restructuring the table. Use minmax for columns loosely correlated with the sort order, and bloom_filter / tokenbf_v1 for high-cardinality equality and text search. They help only when filtered values are clustered — test with EXPLAIN, because a skip index that prunes nothing is pure overhead.

9. Partition for lifecycle, not for speed

We'll say it again because it's the most expensive mistake in the ecosystem: PARTITION BY is for managing data lifecycle, not accelerating queries. Default to none or monthly (toYYYYMM), never a high-cardinality column. Pair coarse partitioning with TTL for automatic cleanup.

ALTER TABLE events MODIFY TTL event_time + INTERVAL 90 DAY;

10. Avoid mutations and FINAL on the hot path

ALTER TABLE ... UPDATE/DELETE rewrites whole parts asynchronously — fine for occasional GDPR deletes, terrible as a regular workflow. SELECT ... FINAL forces merge-time dedup at query time, which is expensive on large tables. Model updates and duplicates with the right engine and aggregate with argMax(value, version) instead of relying on FINAL.

11. Make JOINs the small-table-on-the-right kind

ClickHouse loads the right table into memory to build a hash table, so the right side should always be the smaller one. Joining a billion-row fact with a small dimension is fast; the reverse invites MEMORY_LIMIT_EXCEEDED. For frequently joined small dimensions, a Dictionary with dictGet() is faster and lighter than a JOIN.

12. Treat your schema as a living thing you monitor

The best teams don't design a schema once and forget it. They watch a few health signals continuously: part-count pressure, the slowest queries in query_log, and storage growth by table.

FAQ

What is the single most impactful ClickHouse optimization?

Choosing the right ORDER BY for your query patterns. It determines the sparse primary index and physical sort order, which together drive how much data every query reads. Nothing else moves the needle as much.

Materialized view or projection?

Use a materialized view to precompute aggregates into a separate roll-up table (great for dashboards). Use a projection when you need the same data in a different sort order without managing a second table yourself — projections are maintained and chosen automatically.

Do I need partitioning to make queries fast?

No. Partitioning is for data lifecycle, not speed — the primary key already skips data far more finely. Most tables are best with no partition key or a monthly one.

See which rules your cluster is breaking

These twelve checks are the manual version of an audit. OptiHouse connects read-only, runs all of them on every scan, and ranks the highest-impact fixes first so you spend your time on the expensive things. Try the free optimizer or start a 14-day trial.