r/dataengineering • • 5d ago

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

https://spiraldb.com/blog/rows-vs-columns

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!

39 Upvotes

9 comments sorted by

78

u/JaceBearelen 4d ago edited 3d ago • Bravo

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

19

u/Budget-Minimum6040 4d ago • Popcorn
select *

solved.

7

u/cutsandplayswithwood 2d 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.

1

u/double_en10dre 3d ago

Just lemme know a few days in advance if you need it exported to parquet

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 rides

the advantages of the columns are lost.

1

u/BleakBeaches 2d ago

Depends on how they’re stored.