Anatomy of a ClickHouse audit: what 5 minutes reveals

June 17, 2026·6 min read

#ClickHouse#audit#cost

What does an audit of a large ClickHouse cluster actually turn up? The figures below are illustrative — from a representative ~2 PB cluster — but the categories are what almost every mature deployment carries. A read-only scan reads system.* and the query log; it never touches your data.

312 unused tables

Cross-referencing every table against the queries in query_log over 90 days surfaces the tables nobody reads: abandoned staging tables, old backup copies, deprecated rollups. They keep paying hot-disk prices while feeding nothing — usually the fastest storage win.

847 expensive queries

Ranked by read_bytes, a handful of patterns dominate: FROM ... FINAL on hot tables, filters that break partition pruning, and wide reads with no PREWHERE. The top 10 queries almost always account for most of the compute cost.

23 critical issues

Operational risks that are not about cost but about stability: a growing merge backlog, a disk forecast to fill within days, or a replica falling behind. These are the findings you want before they page you at 3am.

≈ $14,800 / month reclaimable

The dollar figure comes from converting reclaimable bytes and avoidable compute into your own storage and compute pricing — storage tiering plus query fixes. It is an estimate, ranked so you can apply the highest-impact fixes first.

The how behind each number

Every category above has diagnostic SQL in the playbooks: Reducing storage & cost, Diagnosing slow queries and Parts, merges & ingestion.

Run the same audit on your cluster

OptiHouse produces exactly this — read-only, in under five minutes, with the exact SQL for each finding and a dollar estimate using your pricing. Your numbers will differ from the example above. Start a free trial or try the optimizer first.

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.