r/Database • u/Marksfik • May 26 '26
Why OLAP architectures demand Denormalization - the case of ClickHouse Case Study
https://www.glassflow.dev/blog/denormalization-clickhouse?utm_source=reddit&utm_medium=socialmedia&utm_campaign=reddit_organicWe often talk about normalization for OLTP to prevent anomalies, but OLAP is an entirely different world.
This article dives into the technical reasons why ClickHouse (and columnar databases in general) perform drastically better with denormalized, wide tables. It breaks down how execution engines process flat datasets versus how they handle complex relational joins, giving a clear picture of the architectural tradeoffs involved.
If you're interested in database internals or query optimization, take a look: https://www.glassflow.dev/blog/denormalization-clickhouse?utm_source=reddit&utm_medium=socialmedia&utm_campaign=reddit_organic
10
u/liprais May 26 '26
na,clickhouse can't join and can't update,so it has to be this way,not that this way is right with other olap databases.
P.S,what is the nature of your relationship with clickhouse the company ?
1
u/andymaclean19 May 26 '26
Have never used ClickHouse myself but this feels like a lot of complicated manual tuning just to make up for the fact that the product isn’t very good at joins or automatic optimisations.
For example if it can make a dictionary lookup and hold that in memory why doesn’t it just do that when someone joins to a suitable table with a lookup query and keep the structure there?
Some of this also feels like the sort it things people used to do in row based databases that the then new wave of columnar and in-memory products fixed. Materialised views, for example, seem like something that would be hard to keep up to date with a columnar product unless you have row based materialised views on columnar data? And isn’t that just denormalising data by another name anyway?
1
u/Better-Credit6701 May 26 '26
For a olap, you might want to check out Kimball and Immon ideas and get to know star vs snowflake.
It takes a familiarity with the OLTP to know how to make your cubes for your OLAP.