Coreframe Labs

Automated performance audit

Performance audits for ClickHouse

Teams migrate to ClickHouse for the scan speed and then write queries that defeat it. The engine's advantage comes from reading only the columns and only the granules a query needs — and it is easy to write SQL that forfeits both. We benchmarked ClickHouse against PostgreSQL and TimescaleDB on a one-billion-row dataset before migrating, and took a critical query from 42,000ms to 380ms; most of that gap was query shape, not the engine.

Check a file now — free

Paste one file. The free tier runs a single security pass and returns up to three findings, each with the line of code that evidences it. Nothing is stored, nothing is used for training, and detected secrets are redacted before analysis.

Paste one file0 / 400 lines

Not stored. Not trained on. Secrets redacted before analysis.

What we find most often in ClickHouse

Each of these is a real pattern, with the code that causes it and the change that fixes it.

1. SELECT * against a wide table

Symptom
The query reads hundreds of gigabytes to aggregate two columns.

The pattern

SELECT * FROM events WHERE event_date >= today() - 7;

Why it bites
ClickHouse stores each column separately, so cost is proportional to the columns you name. `SELECT *` forfeits the entire point of a columnar store — on a 200-column events table it reads a hundred times more data than the aggregate needs.

The fix

SELECT event_date, count() AS events
FROM events
WHERE event_date >= today() - 7
GROUP BY event_date;

2. Filtering on a column outside the ORDER BY key

Symptom
The filter is highly selective, yet the query scans every part.

The pattern

-- ORDER BY (event_date, user_id)
SELECT count() FROM events WHERE session_id = 'abc123';

Why it bites
The sparse primary index only skips granules for a prefix of the ORDER BY key. `session_id` is not in it, so ClickHouse cannot exclude any granule and reads the whole table regardless of how few rows match.

The fix

-- Add a skip index for the secondary access pattern...
ALTER TABLE events
  ADD INDEX idx_session session_id TYPE bloom_filter GRANULARITY 4;

-- ...or include the date so the primary key can prune first:
SELECT count() FROM events
WHERE event_date >= today() - 30 AND session_id = 'abc123';

3. The large table on the right side of a JOIN

Symptom
The JOIN exhausts memory or spills to disk.

The pattern

SELECT u.name, e.count
FROM users AS u
JOIN events AS e ON u.id = e.user_id;   -- events is the billion-row table

Why it bites
ClickHouse builds a hash table from the right-hand table in memory. Putting the large table there means materializing a billion rows before a single output row is produced — the opposite of the intended plan.

The fix

-- Small table on the right; aggregate before joining.
SELECT u.name, e.cnt
FROM (
  SELECT user_id, count() AS cnt FROM events GROUP BY user_id
) AS e
JOIN users AS u ON u.id = e.user_id;

An automated pass reads one file. An audit reads the system.

The tool above is deliberately narrow — one file, one dimension, one pass. The issues that actually take systems down live between files: in the trust boundary nobody documented, the query pattern that only emerges at scale, the cache that was correct until the second tenant arrived. That is what the Coreframe Technical Audit is for — five business days, a written report, a live findings call, and a remediation proposal you can execute with or without us.

£1,500–£2,500 solo · £3,000–£5,000 funded startup · £3,000–£6,000 SME

See what the full audit covers →

Related audits

PostgreSQLperformanceRedis cachingperformanceDjango ORMperformance
All stack audits →