Columnar Storage: Column-Oriented vs Row-Oriented
Row stores (OLTP) pack entire rows consecutively. Analytical engines (OLAP, DuckDB, Parquet) store records column-by-column. A query like `SELECT AVG(salary)` scans only the contiguous `salary` array from disk, ignoring gigabytes of unused row columns 📊🏎️.
class ColumnTable:
def __init__(self):
self.columns = {}
def insert_columns(self, **data):
for col, vals in data.items():
self.columns.setdefault(col, []).extend(vals)
def aggregate_sum(self, col_name):
# Scans ONLY the target column in memory:
return sum(self.columns[col_name])
table = ColumnTable()
table.insert_columns(
id=[1, 2, 3],
description=["heavy_blob_1", "heavy_blob_2", "heavy_blob_3"],
amount=[100, 250, 450]
)
print(f"Total Amount: {table.aggregate_sum('amount')} 🚀")
# 48. Columnar Storage: Column-Oriented vs Row-Oriented If you run `SELECT AVG(latency) FROM logs` on a traditional row-oriented database (like MySQL or Postgres), the storage engine must load the entire 200-column row (including JSON payloads, headers, user-agents) into memory just to extract one float 🛑. ```python # Row-Oriented Layout (OLTP): # [ID_1, Payload_1, Latency_1][ID_2, Payload_2, Latency_2]... # (CPU must jump over gigabytes of garbage payloads) # Column-Oriented Layout (OLAP): # Latencies: [12.4, 45.1, 10.2, 8.9, 14.3] (Packed contiguously!) # Payloads: [Payload_1, Payload_2, ...] (Never touched by the query!) ``` ### The Analytical Advantage 1. **Contiguous Memory Prefetching**: When column values are packed back-to-back, the CPU hardware prefetcher loads them into L1/L2 data cache in a single burst. 2. **Eliminated I/O Waste**: If your table has 50 columns and the query touches 2, a columnar engine reads only 4% of the bytes from disk! 3. **Hyper-Compressible**: Consecutive numbers or repeated strings compress far better than heterogeneous row structures 📊🏎️. --- ### Interactive Pop-Quiz 🧠 #### Why is a column-oriented storage layout dramatically faster than a row store for aggregation queries? - [ ] It reads only the contiguous memory bytes of queried columns, maximizing disk I/O efficiency and CPU cache hits 📊 - [ ] It bypasses CPU floating point registers completely - [ ] Because columns are stored exclusively in GPU video memory - [ ] Because column stores prevent users from executing SQL WHERE clauses --- ### Practical Challenge 🎯 Implement `ColumnStore` with `load(**cols)` and `filter_indices(col, pred)`. Load `user_id=[1, 2, 3, 4]`, `score=[88, 92, 75, 95]`. Filter for scores `>= 90`. Collect the matching `user_id` values for those filtered indices, and print them. <details> <summary>Solution</summary> ```python class ColumnStore: def __init__(self): self.cols = {} def load(self, **cols): self.cols = cols def filter_indices(self, col, pred): return [i for i, v in enumerate(self.cols[col]) if pred(v)] store = ColumnStore() store.load(user_id=[1, 2, 3, 4], score=[88, 92, 75, 95]) indices = store.filter_indices("score", lambda s: s >= 90) top_users = [store.cols["user_id"][i] for i in indices] print(top_users) ``` </details>