Performance & Optimization

ClickHouse is fast by default, but with the right techniques you can make it 10-100x faster. Learn the three golden rules of ClickHouse optimization.

🏆 The Three Golden Rules

Read Less Data

Avoid Unnecessary Work

Filter First, Process Later

🧒 ELI5: The Grocery Store

Rule 1: Don't walk through every aisle if you only need milk. Go straight to the dairy section (= select only needed columns).

Rule 2: Don't pick up every item to check the price. Only pick up what you're buying (= avoid SELECT *).

Rule 3: Check your shopping list BEFORE entering the store, not after filling your cart (= filter in WHERE before GROUP BY).

🔍 EXPLAIN & Query Analysis

-- See the query execution plan
EXPLAIN SELECT country, count() FROM events GROUP BY country;

-- See how much data was read
SELECT country, count()
FROM events
GROUP BY country
SETTINGS send_logs_level = 'trace';

-- Check query log for slow queries
SELECT
    query,
    round(query_duration_ms / 1000, 2) AS duration_sec,
    formatReadableSize(read_bytes) AS data_read,
    read_rows
FROM system.query_log
WHERE type = 'QueryFinish'
ORDER BY query_duration_ms DESC
LIMIT 10;

⚡ Optimization Techniques

1. Select Only Needed Columns

-- ❌ BAD: Reads ALL columns
SELECT * FROM events WHERE country = 'US';

-- ✅ GOOD: Reads only 2 columns
SELECT event_type, revenue FROM events WHERE country = 'US';

2. Use ORDER BY Key in WHERE

-- Table: ORDER BY (event_date, user_id)

-- ✅ FAST: Uses primary key (reads 0.1% of data)
SELECT * FROM events WHERE event_date = '2024-01-15';

-- ❌ SLOW: country is NOT in ORDER BY (full scan)
SELECT * FROM events WHERE country = 'US';

3. Partition Pruning

-- Table: PARTITION BY toYYYYMM(event_date)

-- ✅ Reads only January partition
SELECT count() FROM events
WHERE event_date >= '2024-01-01' AND event_date < '2024-02-01';

-- Check which partitions were read:
SELECT partition, sum(rows) AS total_rows
FROM system.parts
WHERE table = 'events' AND active
GROUP BY partition;

4. Use LowCardinality

-- Before: 10 seconds for 1B rows
country String

-- After: 1 second for 1B rows (10x faster!)
country LowCardinality(String)

5. Projections (Pre-sorted Views)

-- Main table sorted by (date, user_id)
-- But you often query by country...

ALTER TABLE events
ADD PROJECTION by_country (
    SELECT *
    ORDER BY country, event_date
);

-- Now queries filtering by country are FAST too!
-- ClickHouse automatically uses the projection

💡 Projections = Multiple Sort Orders

Projections let you have the same data sorted in multiple ways. It's like having the same book organized both alphabetically AND by date. The trade-off: more disk space (2x for each projection).

📊 Common Anti-Patterns

Anti-Pattern	Why It's Bad	Fix
`SELECT *`	Reads all columns	Select only needed columns
Frequent UPDATEs	Rewrites entire parts	Use ReplacingMergeTree or append-only
Too many small INSERTs	Creates too many parts	Batch inserts (1000+ rows)
JOINing large tables	Right table loaded to memory	Denormalize or use Dictionaries
Wrong ORDER BY	Full table scans	Put filtered columns first
Over-partitioning	Too many small files	Aim for 1M+ rows per partition

Advanced SQL Materialized Views

October 2026 practice

Checked against the ClickHouse documentation in October 2026. The JSON type has been production-ready since version 25.3. ClickHouse Cloud rewrites a MergeTree create onto the matching SharedMergeTree engine.

Read system.query_log for the bytes and rows a query touched. A projection stores another order or a small aggregate so a different filter does not scan the base ORDER BY. Add it, then MATERIALIZE it. Projections do not replace a bad ORDER BY for the main query. Avoid FINAL on a large ReplacingMergeTree. Partition by a coarse date, not by user id.

ALTER TABLE events ADD PROJECTION by_type
(
    SELECT event_type, count()
    GROUP BY event_type
);
ALTER TABLE events MATERIALIZE PROJECTION by_type;