ClickHouse Cost Tuning
Overview
Reduce ClickHouse Cloud costs through storage optimization, compression tuning,
TTL policies, compute scaling, and query efficiency improvements. This skill walks
the bill from top driver to fix: identify what you actually pay for, then apply the
codec, TTL, compute, and query changes that move the number.
Deep copy-paste queries for every step live in
references/implementation.md; end-to-end scenarios
live in references/examples.md.
Prerequisites
- ClickHouse Cloud account with billing access
- Understanding of current data volumes and query patterns
Instructions
Step 1: Understand what you pay for
ClickHouse Cloud bills on four axes — and the biggest one is usually compute,
not storage, because ClickHouse compresses data 10-20x.
| Component | Pricing Model | Key Driver |
|---|
| Compute | Per-hour per replica | vCPU + memory tier |
| Storage | Per GB-month | Compressed data on disk |
| Network | Per GB egress | Query result sizes |
| Backups | Per GB stored | Backup retention |
Step 2: Find the top cost driver
Break storage down by table, then by column, to find bloated data. The starter
query — full breakdowns in references/implementation.md:
SELECT database, table,
formatReadableSize(sum(bytes_on_disk)) AS compressed_size,
round(sum(data_uncompressed_bytes) / sum(bytes_on_disk), 1) AS compression_ratio
FROM system.parts WHERE active
GROUP BY database, table ORDER BY sum(bytes_on_disk) DESC;
A column with a low compression ratio (e.g. 2x on a text/JSON blob) is your lever.
Step 3: Improve compression
Apply codecs matched to the data shape — ZSTD(3) for JSON/text, Delta, ZSTD
for sequential IDs, DoubleDelta, ZSTD for timestamps — then OPTIMIZE ... FINAL
to re-merge. Full codec cheat sheet and verification queries in
references/implementation.md.
Step 4: Expire and tier old data with TTL
Add TTL to delete or move cold data automatically, or drop whole partitions for an
immediate one-time reclaim. See the tiered hot/cold/delete TTL pattern in
references/implementation.md.
Step 5: Cut compute cost
Enable auto-scaling and idle suspension in the Cloud Console, cap per-query cores
and memory (max_threads, max_memory_usage), and batch small writes with
async_insert. Exact settings in references/implementation.md.
Step 6: Make queries cheaper
Find the most-scanned queries in system.query_log, replace repeated full scans
with materialized views, and use PREWHERE to read fewer columns. Queries in
references/implementation.md.
Step 7: Monitor going forward
Track per-query read bytes/rows and duration in your application so cost regressions
surface early. TypeScript cost-tracking wrapper in
references/implementation.md.
Output
Applying this skill produces:
- A ranked storage breakdown (by table and column) identifying the top cost drivers.
- Concrete
ALTER TABLE ... MODIFY COLUMN ... CODEC(...) statements for bloated columns.
- A TTL / partition-drop plan for cold data.
- Cloud compute settings (auto-scale bounds, idle timeout, per-query limits).
- A shortlist of the most expensive queries from
system.query_log with materialized-view or PREWHERE fixes.
- The completed cost-optimization checklist in references/implementation.md.
Error Handling
| Issue | Cause | Solution |
|---|
| Storage growing fast | No TTL, no drops | Add TTL or schedule partition drops |
| High compute bill | Full-scan queries | Add materialized views, fix ORDER BY |
| Egress charges | Large result sets | Add LIMIT, use aggregations |
| Idle compute cost | No auto-suspend | Enable idle timeout in Cloud console |
Examples
Three worked scenarios take a real symptom through diagnosis → fix → verification —
see references/examples.md:
- Storage bill doubled — trace it to a poorly-compressed JSON column and cut the
table ~60% with a
ZSTD(3) codec.
- Compute is the real driver — replace a 30-second full-
count() dashboard query
with a materialized view and enable idle suspension.
- Old data you never query — add tiered hot/cold/delete TTL and drop stale
partitions for an immediate reclaim.
Resources
Next Steps
For broader design patterns — schema layout, ingestion pipelines, and replica
topology that keep costs low by construction — see the
clickhouse-reference-architecture skill in this pack.
