Row-Oriented vs. Column-Oriented Storage

The physical layout decision that explains why an analytical query only pays for the columns it touches, and why a system built for single-row updates almost always stores data by row instead.

IntermediateJune 4, 2026

Databases and Analytical Storage introduces OLAP and OLTP as two different workload shapes. This article covers the physical reason a storage engine built for one is a poor fit for the other: where, exactly, each format puts a given value on disk.

Two layouts for the same table#

Text

Table:  order_id | customer_id | amount
Row-oriented layout (record by record):  [1, 101, 50.00] [2, 102, 30.00] [3, 101, 75.00] ...
Column-oriented layout (field by field):  order_id:    [1, 2, 3, ...]  customer_id: [101, 102, 101, ...]  amount:      [50.00, 30.00, 75.00, ...]

Nothing about the data changed between these two layouts — every value is still there. What changed is the serialization choice: which bytes sit next to each other on disk, and that single detail decides how expensive every later read or write is.

Reading one column out of many#

SELECT AVG(amount) FROM orders needs exactly one field from every row. In the row-oriented layout, that value is interleaved with order_id and customer_id for every single record — reading it means touching the entire row, for every row, and discarding two-thirds of what was read. In the column-oriented layout, amount is one contiguous run of bytes; the query reads exactly that, and nothing else in the table is touched at all.

This is the mechanical reason behind OLAP vs. OLTP

Databases and Analytical Storage frames OLAP as "wide scans, few columns" and OLTP as "whole-record reads and writes." This article's diagram is why: a column-oriented layout is cheap for the first pattern and expensive for the second; a row-oriented layout is the reverse. The workload shape and the storage layout are two sides of the same design decision.

Writing one whole record#

Inserting a single new order needs order_id, customer_id, and amount written together. In the row-oriented layout, that is one write to one location. In the column-oriented layout, it is three separate writes, to three different places — cheap for a bulk load of a million rows at once, comparatively expensive for inserting or updating one row at a time, which is exactly the pattern an application's transactional database serves constantly.

Why columnar data compresses better#

Values of the same type, drawn from the same domain, sitting next to each other compress far better than a row's mixed types interleaved together — a status column repeating four distinct strings a million times compresses beautifully; a row mixing that string with a float and an integer does not. Compression and Encoding Trade-offs covers this in depth; it is a direct consequence of the layout covered here, not an independent feature of any one file format.

Common mistakes#

Choosing a columnar format for a single-row-lookup workload#

An application that mostly fetches or updates one full record by its ID — the OLTP pattern — pays the multi-location-write cost of a columnar layout on every single operation, for a read benefit ("skip unneeded columns") that pattern never actually uses.

Assuming columnar storage is unconditionally "the modern choice"#

Column-oriented storage is a specific trade, not a strict upgrade. It wins decisively for wide scans over few columns and loses decisively for whole-record, high-frequency single-row writes — the right answer depends entirely on which pattern a given table actually serves.

Forgetting that a table's access pattern can change#

A table that started as an OLTP record store and grew an analytics workload on top of it — dashboards querying it directly — is a common way teams discover their storage layout no longer matches their actual query pattern. This is usually the point a data mart, covered in Building Staging and Data Marts, splits the analytical workload onto its own column-oriented copy.