Rootconf 2026 Annual Conference

Session title
One Dataset, Three Physical Representations

One-line summary
We tested three physically different ClickHouse schemas for the same analytical dataset and workload — and the representation that read 168× more rows from disk won. This talk is the investigation into why, and the framework it produced for choosing physical data representation by query workload instead of intuition.

What problem are you addressing?
We were building an OLAP system to answer analytical questions over a large advertising dataset. The expensive part — precomputing an index per frame, audience, and hourly daypart — was already decided. What remained was a genuinely open engineering question: how should those precomputed results be physically stored? We tried three representations of the same logical data — one column per daypart (16,800 columns), one array per row, and one row per daypart (pre-exploded) — while holding the data and query workload fixed. A quick benchmark made arrays look like the clear winner. Switching on the real workload — computing an index independently per selected hour, then averaging — reversed the result entirely: the pre-exploded row table, storing ~110× more rows, ran ~1.9× faster and used less memory. That contradiction is what made it worth investigating: it isn’t a ClickHouse quirk, it’s a general lesson about how physical layout determines the work a columnar engine must do at query time.

Who is the intended audience?
Developers/Architects (primarily data/backend engineers who design analytical schemas), with strong relevance to Platform Engineering / Infrastructure teams that own data platforms.

Level
Intermediate
(Could argue Advanced — it assumes comfort with columnar databases and execution models, but deliberately avoids ClickHouse-internals trivia, so intermediate engineers should follow it fine)

Practical takeaways

1. A four-question framework for choosing a physical data representation based on what the query actually needs to do at runtime — not intuition, not a simplified benchmark.
2. “Fewer bytes read” is not a proxy for “less work” — a columnar engine can read far less data and still do far more computation.

What will you share?

• Architecture/design decisions (three schema representations for one logical dataset)
• Benchmarks or measurements (exact ClickHouse 24.1 numbers: query time, memory, bytes read, storage rows for each representation)
• Trade-offs and alternatives (schema width vs. runtime reshaping vs. storage volume)
• Before-and-after experience (a simplified test that pointed the wrong way, and what the real workload revealed)

What is your experience with this problem?
We encoutered this while building a tech solution for one of our clients. We spiked this and then the version which we are talking about is deployed to production.

What approaches failed, disappointed, or created unexpected problems?
The wide/column representation looked ideal on paper — column pruning could skip ~99.9% of a 16,800-column table for a typical query window — but maintaining that width created its own overhead (thousands of column files, marks and metadata per part, merge coordination) and was ruled out before the real benchmark even ran. Separately, the array representation’s early benchmark (137ms) was misleading because it never exercised the real per-hour computation — a classic case of a simplified test producing a confidently wrong answer.

What will you do differently today?
Benchmark against the actual query shape — exact filter grain, exact aggregation — before trusting any representation’s numbers. Treat physical representation as an execution-cost decision made up front, driven by what the query needs to do, rather than an afterthought optimized purely for storage compactness.

What trade-offs did you consider?
Wide/columns traded schema simplicity for enormous, hard-to-operate width. Arrays traded compact storage for CPU spent reshaping data (ARRAY JOIN) on every single query. Pre-exploded rows traded storage volume — paid once, at write time — for a query that always operates at its natural grain, with no runtime reshaping. We optimized for real query latency under the actual hourly-grain workload, and knowingly gave up raw storage compactness to get it.

How can this help other practitioners?
A reusable framework for evaluating physical schema choices in any columnar/analytical database (not ClickHouse-specific): what does the query need, what representation makes that work natural, where are you paying the complexity, and did you measure the real workload. Also a debugging habit worth stealing — don’t trust a benchmark that ran against a simplified stand-in for your actual query.

Current state
Lessons from a previous system

Tags
#databases #olap #clickhouse #schemadesign #benchmarking #performanceengineering #datamodeling #casestudy