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.

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 (maxthreads, maxmemory_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

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

• ClickHouse Cloud Pricing
• Data Compression
• TTL for Data Management

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.