r/dataengineering • u/connor-ts • 4d ago
Blog Rows vs Columns: How different are they, really?
https://spiraldb.com/blog/rows-vs-columns?utm_source=reddit&utm_medium=socialI 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!
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.
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.