Part 3 of 5 · 1 min read
Column-oriented storage
Each column's values stored together, efficient for reading one column across many rows.
Think of a spreadsheet turned on its side. Every value from one column is grouped together, rather than every value from one row.
In plain words: column-oriented storage keeps all values of one column physically adjacent, across every row. Reading one column across millions of rows, such as computing a total or an average, becomes efficient, since only that one column's data needs to be touched at all. Reading one whole row becomes relatively less efficient, since its values are now scattered across separate column groupings.
A worked example. Computing the total revenue across a year's worth of orders only needs the price and quantity columns, which column-oriented storage reads without touching any other column at all, such as customer names or addresses.
The key lines. No SQL syntax changes; the same SELECT runs either way, but performs very differently depending on the underlying storage layout.
SELECT SUM(price * quantity) FROM order_items JOIN books USING (book_id);
-- efficient in column-oriented storage: only price and quantity columns are read
Column-oriented storage
Column-oriented storage keeps each column's values physically adjacent across all rows, efficient for reading or aggregating one or a few columns across many rows.
Example: summing revenue across millions of orders, needing only two columns, is a natural fit for column-oriented storage.