ClickHouse cost optimization: a complete guide to cutting your bill

ClickHouse is cheap per byte and per query — which is exactly why the bill creeps up quietly. Nobody notices a single FINAL on a hot path or a staging table left behind after a migration. Multiply that across a real workload and 20–40% of a typical cluster's spend is waste you can reclaim without touching application code. This guide breaks the bill into its four real drivers and gives you the SQL to attack each one.

Where ClickHouse money actually goes

Almost every ClickHouse invoice — self-hosted or Cloud — comes down to four buckets:

• Compute burned by queries that scan far more data than they need (no PREWHERE, no partition pruning, FINAL on every read, SELECT *).
• Storage paying hot-disk prices for cold and unused data (abandoned tables, no TTL tiering, weak column codecs).
• Parts & merges — background merge CPU and I/O caused by tiny, frequent inserts (the classic "too many parts").
• Over-provisioned infrastructure — replicas and nodes sized for a peak that rarely happens.

1. Compute: stop scanning data you don't need

Compute is usually the biggest line. Start by finding the queries that read the most bytes — those are where money burns. system.query_log has everything you need.

SELECT normalized_query_hash,
       any(substring(query, 1, 80)) AS sample,
       count() AS runs,
       formatReadableSize(sum(read_bytes)) AS total_read,
       round(avg(query_duration_ms)) AS avg_ms
FROM system.query_log
WHERE type = 'QueryFinish' AND event_time > now() - INTERVAL 7 DAY
GROUP BY normalized_query_hash
ORDER BY sum(read_bytes) DESC
LIMIT 20;

The usual culprits, in order of impact: a missing PREWHERE so the filter runs after the read instead of before it; a query with no partition-key predicate so ClickHouse scans every partition; and FINAL forcing a merge-on-read across all parts. Fixing the top 10 queries here often cuts compute more than any infra change.

2. Storage: stop paying hot-disk prices for cold data

Aggregate system.parts per table to see exactly where your bytes live, then attack the two big wins: tables nobody reads (drop or archive) and old partitions on hot disk (tier them with TTL).

SELECT database, table,
       formatReadableSize(sum(bytes_on_disk)) AS size,
       sum(rows) AS rows
FROM system.parts
WHERE active
GROUP BY database, table
ORDER BY sum(bytes_on_disk) DESC
LIMIT 20;

Cross-reference that list against tables touched in query_log over 90 days — anything large that never appears is a drop or archive candidate. For data you must keep but rarely read, a TTL TO VOLUME rule moves it to cheaper object storage automatically while keeping it queryable.

ALTER TABLE events
MODIFY TTL ts + INTERVAL 90 DAY TO VOLUME 'cold';

3. Parts & merges: tame insert pressure

Frequent tiny inserts create thousands of small parts; ClickHouse then burns CPU and I/O merging them in the background — compute you pay for but never see in a query. Batch inserts (or use async inserts), and watch the part count per partition.

SELECT database, table, count() AS parts
FROM system.parts
WHERE active
GROUP BY database, table
ORDER BY parts DESC
LIMIT 20;

4. Right-size the infrastructure

Once queries and storage are clean, your peak resource needs drop — which is the moment to revisit replica count and node size. Cleaning the workload first means you right-size against real demand, not against the waste.

How much can you reclaim?

Find your number

Doing this once by hand is straightforward; keeping it clean as the cluster grows is the hard part. OptiHouse connects read-only, runs every check above on each scan, and ranks each finding by its real monthly dollar impact with the exact SQL to apply it. See the related cost optimization checklist, try the free optimizer, or start a 14-day trial.