Systems Dictionary • Storage Engines • Columnar Storage
Columnar Storage Architecture
A database storage format that organizes data on disk and in memory by column attributes rather than rows, enabling massive compression ratios and vectorized analytical query execution.
Row-Oriented vs Columnar Memory Layout
Row-Oriented (OLTP - PostgreSQL, MySQL):
[ UserID_1 | Timestamp_1 | Amount_1 ] [ UserID_2 | Timestamp_2 | Amount_2 ]
Query
Column-Oriented (OLAP - ClickHouse, Parquet, Snowflake):
[ UserID_1 | UserID_2 | UserID_3 ... ]
[ Timestamp_1 | Timestamp_2 | Timestamp_3 ... ]
[ Amount_1 | Amount_2 | Amount_3 ... ]
Query
[ UserID_1 | Timestamp_1 | Amount_1 ] [ UserID_2 | Timestamp_2 | Amount_2 ]
Query
SELECT SUM(Amount) must read all UserIDs and Timestamps from disk into RAM.Column-Oriented (OLAP - ClickHouse, Parquet, Snowflake):
[ UserID_1 | UserID_2 | UserID_3 ... ]
[ Timestamp_1 | Timestamp_2 | Timestamp_3 ... ]
[ Amount_1 | Amount_2 | Amount_3 ... ]
Query
SELECT SUM(Amount) reads ONLY the Amount stream sequentially with zero waste.
Because all values within a column share identical data types, columnar databases achieve compression ratios exceeding 5:1 to 10:1 using Dictionary Encoding, Delta Compression, and Run-Length Encoding (RLE).