ClickHouse cost optimization checklist

ClickHouse query optimization is about latency. Cost optimization is about the bill: bytes you keep, bytes you scan, CPU you burn on merges, and memory you force the server to hold. Those are the same system.parts and system.query_log rows you already have — you just look at them as money.

This is a checklist, not a second copy of the query optimization pillar. When a row says "fix the schema," follow that link.

What actually drives the bill

If you only watch wall-clock latency, a cheap-looking dashboard query that scans 400 GB every minute still wins the invoice.

Storage checklist

Largest tables, compression, and tiny-part tax:

SELECT
    database,
    table,
    formatReadableSize(sum(bytes_on_disk)) AS on_disk,
    round(sum(data_uncompressed_bytes) / nullIf(sum(data_compressed_bytes), 0), 2) AS uncomp_to_comp,
    count() AS active_parts,
    uniq(partition) AS partitions
FROM system.parts
WHERE active
GROUP BY database, table
ORDER BY sum(bytes_on_disk) DESC
LIMIT 30;

• Huge on-disk + terrible compression — wrong codecs, nested JSON as String, or you never drop old partitions (TTL).
• Hundreds of tiny active parts — merge CPU forever. That is not fixed by a nightly OPTIMIZE TABLE … FINAL. See too many parts.
• Projections, materialized views, skip indices you never query — they still write and store. Inventory them before you add another.

Table size in the UI: Tables.

Query CPU and memory checklist

Last-day cost by normalized query:

SELECT
    normalizedQueryHash(query) AS qh,
    any(query) AS sample,
    count() AS runs,
    formatReadableSize(sum(read_bytes)) AS bytes_read,
    formatReadableQuantity(sum(read_rows)) AS rows_read,
    round(avg(query_duration_ms)) AS avg_ms,
    formatReadableSize(max(memory_usage)) AS peak_mem
FROM system.query_log
WHERE type = 'QueryFinish'
  AND event_time > now() - INTERVAL 1 DAY
  AND query_kind = 'Select'
GROUP BY qh
ORDER BY sum(read_bytes) DESC
LIMIT 20;

Spill is a cost smell, not a feature to celebrate. If ProfileEvents['ExternalAggregationWrittenRows'] is nonzero, you paid disk I/O to survive a hash table that didn't fit. Fix cardinality / GROUP BY first; then see external GROUP BY and MEMORY_LIMIT_EXCEEDED.

OPTIMIZE TABLE: when not to run it

OPTIMIZE TABLE t asks ClickHouse to merge parts it would merge anyway. OPTIMIZE TABLE t FINAL forces a full rewrite of the selected scope.

Do not:

• Cron OPTIMIZE TABLE … FINAL on large MergeTree tables "to keep things tidy."
• Use FINAL as the fix for too many parts or slow inserts — that is partition and insert-batch design.
• Run unscoped FINAL on terabyte tables during business hours.

Sometimes yes:

• One frozen partition after a one-off cleanup on ReplacingMergeTree.
• A small dimension table where a single part is actually the point.

Forced merges steal CPU from queries and inserts. That is cost optimization, even if storage barely moves.

Weekly review order

Related