Row-Oriented vs Columnar Storage
People think row storage and column storage hold different data. They hold the very same table, just laid out two ways on disk. Row storage keeps each record together, so writing one order is fast. Column storage keeps each column together, so adding up one column is fast. Apps use rows. Warehouses use columns.
Row-Oriented Storage
Each row is kept together on disk. It is quick for one record at a time.
Row-oriented storage keeps all the values of one row next to each other on disk. Reading or writing one whole record is quick, because it sits in one place. Reading one column from many rows is slow, because every full row must be read. Most databases that run apps store data this way. Warehouses tend to use columnar storage instead.
Columnar Storage
A table kept one column at a time. A report reads just the columns it needs.
Columnar storage keeps each column of a table together on disk. A row store keeps each whole row together instead. A report that needs two columns out of fifty reads only those two. Values in one column look alike, so they also squeeze down well. This makes big scans fast, but writing one row at a time is slow.
In one line
- Row-Oriented Storage
- Each row is kept together on disk. It is quick for one record at a time.
- Columnar Storage
- A table kept one column at a time. A report reads just the columns it needs.