Columnar Storage
18 min
The previous module classified queries as OLTP or OLAP — this one explains why WAREHOUSES physically store data differently to serve OLAP well. Row-based storage keeps a whole row's columns together on disk — great for OLTP's "give me everything about this one row" pattern. Columnar storage instead keeps each COLUMN contiguous — so an aggregate query touching only 1-2 columns never has to read (and discard) all the others, exactly the pattern OLAP queries have.
def bytes_read(storage_type, total_rows, total_columns, bytes_per_cell, columns_needed):
if storage_type == "row":
return total_rows * total_columns * bytes_per_cell
elif storage_type == "column":
return total_rows * len(columns_needed) * bytes_per_cell
A 50x reduction in bytes read for the exact same query result, purely from how the data is physically laid out on disk -- no indexing, caching, or query optimization involved at all. This is why warehouses like Snowflake, BigQuery, and Redshift are all columnar under the hood.
Write `bytes_read(storage_type, total_rows, total_columns, bytes_per_cell, columns_needed)`: for `'row'` storage, a query must read EVERY column of every touched row (`total_rows * total_columns * bytes_per_cell`). For `'column'` storage, it only reads the columns actually needed (`total_rows * len(columns_needed) * bytes_per_cell`).
Why is columnar storage such a good fit for OLAP's typical query pattern (aggregate one or two columns across millions of rows)?
You can quantify how much I/O columnar storage saves over row storage for a column-selective aggregate query, and explain why that layout choice matches OLAP's access pattern.