Anatomy of a ClickHouse bill: finding $14,800/month of waste in one read-only scan

ClickHouse is famously cheap — fractions of a cent per GB scanned, brilliant compression, queries that finish before you've let go of Enter. Which is exactly why the bill creeps up without anyone noticing. Nobody files a ticket because a query reads 60 GB instead of 6, or because a staging table was left behind after last quarter's migration. Multiply those small leaks across a real workload and a fifth to two-fifths of the bill turns out to be reclaimable.

To make that concrete, here's a teardown of a representative mid-size analytics cluster — roughly 2.4 PB on disk, a few hundred tables, a steady stream of dashboard and API queries. One read-only scan of its system.* tables surfaced about $14,800/month of reclaimable spend. Here is where it was hiding, in order of impact, with the SQL to find and fix each one.

The rules of the audit

1. **Read-only, system.* only.** Every query here is a SELECT against ClickHouse's own system tables — query_log, parts, columns, replicas. No data rows read, no writes, nothing touches your application tables.
2. Money, not just findings. A list of "issues" is noise. What matters is ranking each by the dollars it costs per month, so you fix the $4k problem before the $40 one.

Finding 1 — FINAL on every read · ~$4,200/mo

The single biggest line. A ReplacingMergeTree table was queried with FINAL on every dashboard load to guarantee deduplicated rows. FINAL forces a merge-on-read across all parts at query time — enormous, repeated work for something that should happen once at write time.

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

The fix is to replace read-time FINAL with an explicit deduplication the engine can do cheaply — argMax() over a version column, or an AggregatingMergeTree design — so the hot path stops merging on every read.

Finding 2 — Missing PREWHERE · ~$3,100/mo

A high-frequency API query filtered with a plain WHERE on a column not in the sorting key. ClickHouse read the full set of columns for every candidate row and then applied the filter. Pushing the filter into PREWHERE lets the engine evaluate it first and skip reading most columns for rows that don't match. On a query scanning ~60 GB per run thousands of times a day, that's the difference between a rounding error and a four-figure monthly line.

Finding 3 — Weak column codec · ~$1,800/mo

A large String payload column sat under the default codec, compressing far below its potential. Monotonic and low-entropy columns often shrink 2–5× with the right codec — Delta, ZSTD for monotonic numerics, LowCardinality(String) for categorical strings. Spot the candidates by compression ratio:

SELECT database, table, name AS column,
       formatReadableSize(sum(data_compressed_bytes))   AS compressed,
       round(sum(data_uncompressed_bytes)
           / sum(data_compressed_bytes), 1)             AS ratio
FROM system.columns
WHERE database NOT IN ('system')
GROUP BY database, table, column
ORDER BY sum(data_compressed_bytes) DESC
LIMIT 20;

Finding 4 — Zombie materialized view · ~$1,150/mo

A materialized view kept populating a rollup table that no dashboard had queried in months — paying compute on every insert to maintain data nobody reads. Cross-referencing the MV's target table against query_log reads over 90 days made it obvious. Dropping it reclaimed both the storage and the per-insert compute.

Finding 5 — Cold data on hot storage · the long tail

Below the top four sat the usual long tail: ~300 tables untouched in 90+ days, old partitions still on hot disk with no TTL, and "too many parts" from tiny frequent inserts. Individually small; together another few thousand dollars a year. A TTL rule moves cold partitions to cheap object storage automatically while keeping them queryable:

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

The tally

None of these required a single change to application code — they're database-level fixes, each backed by a query you can audit before you run it.

Run the audit yourself

You don't need us to do this — it's standard system.* work. The open CLI ships a demo so you can see the shape of it with no cluster at all, and optihouse queries prints every statement it would run so you can audit it first. Source is MIT-licensed: github.com/mmadov/optihouse-cli.

pip install optihouse
optihouse scan --demo     # bundled synthetic cluster, no DB
optihouse queries         # print every statement it would run

If you'd rather see the figures measured on your own data, ranked, with the exact ALTER/SELECT to apply each fix, that's OptiHouse — read-only connection, free to try. To sanity-check a single query, paste it into the free optimizer, no signup. Most clusters carry 20–40% reclaimable spend — estimate yours with the cost calculator. The only way to know your number is to look.