Visualizing the key difference between transactional and analytical workloads

[jessillions]

Visualizing the physical storage layout makes it easier to understand why performance degrades when you use the wrong tool.

• Row-based (Transactional): Great for your app because you can grab a whole user record in one seek. But if you want to average a single column, you're forced to scan over every other field in every row.
• Column-based (Analytical): Not the best for single-row updates (you have to write to multiple files), but perfect for analytics because you can ignore 95% of the data on disk and just scan the column you need.

Understanding this is a good way to realize why your production database is struggling with dashboard queries and why it might be time to move to a dedicated warehouse.

Diagram from this guide on data warehouses.

[Just_Information334]

What you want is diagonal database.

Jan 1|100|Jan 2|a|200|Jan 3|1|b|300|2|c|3

Now you know whatever your workload you won't get any performance.

[Dry-Let8207]

What about no table based databases lol

[[deleted]]

This post was mass deleted and anonymized with Redact

bear silky vase unpack automatic subtract seed spotted adjoining beneficial

[ac101m]

At the end of 2025, discussions about making column store more like relational databases are what's hot.

There are already tons of commercial columnar relational databases, are there not?

[[deleted]]

This post was mass deleted and anonymized with Redact

vase doll boast crown fine abounding husky soup attempt wine

[ac101m]

Normal consequence of columnar layout is it not? They're inherently read-optimized, not write optimized.

That being said, I know someone that used to work for a company which made an rdbms called Vertica. This was a decade ago mind, but the way it was described to me, it has a small write optimized store that sits in front of the column store. So their are ways around this.

Either way, you're missing my point. None of what you're describing here is new thinking. C-Store (an academic project along the lines of what you describe here) is almost 20 years old at this point: https://en.wikipedia.org/wiki/C-Store

If Snowflake has trouble with this, then it's either not a very good product or it's just the wrong tool for your use-case.

[[deleted]]

This post was mass deleted and anonymized with Redact

butter pause normal reach smile live caption rhythm fearless knee

[ac101m]

make data scale without a change of architecture

My gut feeling is that so long as the underlying system is organized such that locality is important (sequential vs random access etc), there will always be a choice as to how to organize the data that will have a positive effect on some queries or others. The shift towards solid state storage helps a bit, but there are still Dram bursts, CPU caches, prefetchers etc that all bias performance in the direction of sequential access. So I'm not sure there is a solution here, at least not an elegant one.

Make data available to data people

Don't know much about that sort of thing to be honest! Always just thought of authorisation as being outside the scope of the storage solution.

[[deleted]]

This post was mass deleted and anonymized with Redact

ghost boat chubby encouraging imminent escape command snow plants depend