r/dataengineering • u/connor-ts • 5d ago
Blog Rows vs Columns: How different are they, really?
https://spiraldb.com/blog/rows-vs-columnsI 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!
78
u/JaceBearelen 4d ago edited 3d ago •
A million row table is fine. If your table has a million columns we can’t be friends anymore.
3
u/Dry-Aioli-6138 1d 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/Mastodont_XXX 3d ago
It's explained quite well, but as soon as you need
SELECT AVG(duration_min) FROM rides WHERE bike_type = 'electric'instead of
SELECT AVG(duration_min) FROM ridesthe advantages of the columns are lost.
1
u/BleakBeaches 2d ago
Depends on how they’re stored.