The Core Question
Row and column layouts are not better or worse in general. The right choice depends on the access pattern of the workload.
When Rows Win
A row store keeps all of a row's fields together, which suits transactional workloads.
- Reading or updating a whole record touches one place on disk.
- Inserting a row is a single write.
- Point lookups by primary key are fast.
This is the natural fit for OLTP, the online transaction processing behind apps that read and write individual records constantly.
When Columns Win
A column store groups each column together, which suits analytic workloads.
- Scans over a few columns across many rows read far less data.
- Heavy compression and vectorized execution speed aggregations.
This fits OLAP, the online analytical processing behind dashboards and reports.
The Spectrum and Hybrids
- OLTP leans row, OLAP leans column, but many systems blend the two.
- Hybrid transactional analytical engines keep a row store for writes and a column store for analytics, sometimes converting recent rows in the background.
- The deciding factors are write frequency, how many columns a query reads, and how many rows it scans.
Key idea
Row stores favor transactional point access while column stores favor analytic scans, and the choice follows the workload, with hybrids serving both.