ClickHouse performance & cost re-architecture with projections

01 The problem

Interactive dashboards over a very large event table were failing with memory-limit errors, and storage kept growing faster than the value it delivered.

02 What I built

1. 1Profiled query logs to find which dimension and measure combinations dashboards actually used.
2. 2Designed tiered projections that pre-aggregate each dashboard tier, including every primitive aggregate the metrics layer needs, so queries hit small projections instead of raw data.
3. 3Capped the projections’ storage footprint and set deduplicate-merge-projection behavior so merges stay correct.
4. 4Moved to directory-based partitions, set TTLs on non-lookup tables, dropped unused high-cardinality columns such as session IDs, and tuned ZSTD compression levels.
5. 5Set query memory and concurrency limits and disabled unnecessary refreshes during the backfill.

03 Impact

Dashboard memory failures stopped and queries moved onto small pre-aggregated projections. This work is part of the ~30–40% storage and CPU reduction delivered for enterprise customers.