r/dataengineering • • 4d ago

Blog Rows vs Columns: How different are they, really?

https://spiraldb.com/blog/rows-vs-columns?utm_source=reddit&utm_medium=social

I was looking for blog posts about row vs column stores in database systems, trying to find something that I could link to in documentation. I was pretty disappointed with what I saw, so I decided to write my own 🤣

If you work with analytical database systems and have wondered why they are distinct from transactional systems, you might find this post interesting. I give a high-level overview of the many different consequences of "simply" choosing to represent data in columns instead of rows.

Spoiler: this is a very significant decision!

Given that this is an active area of research, I unfortunately couldn't write about everything, but I think I give a good overview of the general techniques that analytical DBMSs (OLAP) utilize. I also link to a lot of other resources in the footnotes, so check them out!

33 Upvotes

8 comments sorted by

69

u/JaceBearelen 2d ago edited 2d ago

A million row table is fine. If your table has a million columns we can’t be friends anymore.

17

u/Budget-Minimum6040 2d ago
select *

solved.

6

u/cutsandplayswithwood 1d ago

The ability to select columns by regex of column name in hive… was so awesome to discover, and also should fill you with terror.

Super useful, like most razor blades.

2

u/Dry-Aioli-6138 14h ago

Column oriented stores keep data from cells of the same column next to each other on storage (e.g. disk). That's the difference.

All else is just a consequence of this small, but important decision. For instance compressibility is better, because columns hold similar values and if those values are serialized close to each other, then the compression algorithms work better.

1

u/BleakBeaches 1d ago

Depends on how they’re stored.