ClickHouse cost optimization: a practical checklist

June 14, 2026·5 min read

#ClickHouse#cost#checklist#FinOps

If your ClickHouse bill keeps climbing, the waste is almost always in one of five places. Work through this checklist in order — the first two usually deliver most of the savings.

Horizontal bar chart of where ClickHouse cost leaks: heavy queries, cold storage, tiny parts, bad codecs, no attribution — Five places ClickHouse cost hides, in priority order (illustrative).

Compute: tune the heaviest queries

Rank queries by read_bytes in system.query_log — the top 10 usually dominate cost.
Fix broken partition pruning (no functions on the partition key).
Add PREWHERE on selective filters over wide tables.
Replace FROM ... FINAL on hot paths with argMax / LIMIT 1 BY.

Storage: reclaim what nobody reads

Find the biggest tables in system.parts.
Drop or archive tables not touched in 90+ days.
Move cold partitions to object storage with a TTL TO VOLUME rule.
Delete zombie materialized views that feed nothing.

Part hygiene: stop the merge tax

Flag tables with thousands of tiny parts in system.parts.
Batch inserts (or enable async_insert) to produce fewer, larger parts.
Watch the merge backlog in system.merges and replica lag in system.replicas.

Schema: store fewer bytes

Apply per-column codecs (Delta, ZSTD, Gorilla, ZSTD, T64).
Wrap low-distinct string columns in LowCardinality.
Add skipping indexes for frequent non-PK filters.
Order the primary key by lowest-cardinality columns you filter on most.

Attribution: know who spends what

Group query_log by user to build a simple chargeback of read volume per team or service. You cannot control spend you cannot attribute.

The whole checklist, automated

OptiHouse runs every item above on a read-only scan, ranks the findings by dollar impact, and ships each with the exact SQL. Start free or try the optimizer — and see the deep-dives in our playbooks.

A transparent teardown of a representative 2.4 PB ClickHouse cluster — FINAL on every read, missing PREWHERE, weak codecs, zombie materialized views — and the exact SQL to reclaim ~$14,800/month, read-only.

ClickHouse cost optimization: a complete guide to cutting your bill

Where ClickHouse spend actually goes — compute on bad queries, storage on cold data, parts and merge overhead — and the exact SQL to reclaim 20–40% of it, read-only and risk-free.

5 kinds of unused tables hiding in your ClickHouse

Most ClickHouse clusters carry tables nobody reads anymore — abandoned staging, zombie materialized views, backups, deprecated rollups. Here's how to find them and what they cost.