Column-Oriented Storage: Analytics Optimization
Your PostgreSQL database handles transactions beautifully. Inserts are fast, updates are atomic, and point lookups return in milliseconds. Then someone asks for the average order value by customer...
Key Insights
- Column-oriented storage reduces I/O by 10-100x for analytical queries by reading only the columns needed, not entire rows
- Homogeneous column data compresses 5-10x better than row data, with techniques like dictionary encoding and run-length encoding
- Modern columnar engines use vectorized execution to process thousands of values per CPU instruction, eliminating row-at-a-time overhead
The Analytics Data Problem
Your PostgreSQL database handles transactions beautifully. Inserts are fast, updates are atomic, and point lookups return in milliseconds. Then someone asks for the average order value by customer segment over the last three years, and the query runs for 47 minutes.
This isn’t a tuning problem—it’s a fundamental mismatch between storage format and access pattern.
OLTP workloads access entire rows: fetch customer 12345, update their address, insert a new order. Row-oriented storage excels here because all fields for a record sit contiguously on disk. One read retrieves everything.
OLAP workloads access entire columns: compute the sum of all order amounts, filter by date range, group by region. Row storage forces you to read every field of every row just to extract the three columns you actually need. For a table with 50 columns, you’re reading 47 columns of garbage.
Column-oriented storage flips the physical layout to match analytical access patterns. The performance difference isn’t incremental—it’s often 10-100x.
Row vs Column Storage Fundamentals
Consider a simple orders table with four columns: order_id, customer_id, amount, and status. Here’s how the same data looks in each storage model:
ROW-ORIENTED LAYOUT (how PostgreSQL/MySQL store data):
┌─────────────────────────────────────────────────────────┐
│ Row 1: [1001, 42, 150.00, "shipped"] │ Bytes 0-32
│ Row 2: [1002, 17, 89.50, "pending"] │ Bytes 33-65
│ Row 3: [1003, 42, 220.00, "shipped"] │ Bytes 66-98
│ Row 4: [1004, 88, 45.00, "delivered"] │ Bytes 99-131
└─────────────────────────────────────────────────────────┘
COLUMN-ORIENTED LAYOUT (how Parquet/ClickHouse store data):
┌─────────────────────────────────────────────────────────┐
│ order_id: [1001, 1002, 1003, 1004] │ Bytes 0-16
│ customer_id: [42, 17, 42, 88] │ Bytes 17-33
│ amount: [150.00, 89.50, 220.00, 45.00] │ Bytes 34-66
│ status: ["shipped", "pending", "shipped", ...] │ Bytes 67-99
└─────────────────────────────────────────────────────────┘
The I/O implications are significant. When you query SELECT AVG(amount) FROM orders, row storage reads all 131 bytes. Column storage reads only bytes 34-66—the amount column. With real tables containing 50+ columns and billions of rows, this difference determines whether your query takes seconds or hours.
Why Columns Win for Analytics
Three factors compound to give columnar storage its advantage:
Reduced I/O: Analytical queries typically touch 3-5 columns from tables with 20-100 columns. Column storage reads only what’s needed.
# Calculate I/O savings for a typical analytical query
table_stats = {
"total_columns": 50,
"columns_queried": 3,
"rows": 100_000_000,
"avg_column_size_bytes": 8
}
row_storage_bytes = (
table_stats["total_columns"]
* table_stats["rows"]
* table_stats["avg_column_size_bytes"]
)
column_storage_bytes = (
table_stats["columns_queried"]
* table_stats["rows"]
* table_stats["avg_column_size_bytes"]
)
savings_ratio = row_storage_bytes / column_storage_bytes
print(f"Row storage reads: {row_storage_bytes / 1e9:.1f} GB")
print(f"Column storage reads: {column_storage_bytes / 1e9:.1f} GB")
print(f"I/O reduction: {savings_ratio:.0f}x")
# Output:
# Row storage reads: 40.0 GB
# Column storage reads: 2.4 GB
# I/O reduction: 17x
Better Compression: Columns contain homogeneous data types. A column of integers compresses far better than a row mixing integers, strings, timestamps, and floats. Similar values cluster together, enabling specialized encoding schemes.
Vectorized Processing: Modern CPUs can process multiple values simultaneously using SIMD instructions. Column storage naturally aligns data for vectorized operations—comparing 256 integers in a single instruction rather than processing rows one at a time.
Compression Techniques in Columnar Stores
Columnar databases exploit data characteristics with specialized encoding schemes:
from collections import Counter
def run_length_encode(column: list) -> list[tuple]:
"""RLE: Replace consecutive duplicates with (value, count) pairs."""
if not column:
return []
encoded = []
current_value = column[0]
count = 1
for value in column[1:]:
if value == current_value:
count += 1
else:
encoded.append((current_value, count))
current_value = value
count = 1
encoded.append((current_value, count))
return encoded
def dictionary_encode(column: list) -> tuple[list[int], dict]:
"""Dictionary encoding: Replace values with integer codes."""
unique_values = list(dict.fromkeys(column)) # Preserve order
value_to_code = {v: i for i, v in enumerate(unique_values)}
encoded = [value_to_code[v] for v in column]
return encoded, {i: v for v, i in value_to_code.items()}
# Sample status column with low cardinality (perfect for dictionary encoding)
status_column = ["shipped"] * 40000 + ["pending"] * 35000 + ["delivered"] * 25000
# Original size (assuming 10 bytes average per string)
original_size = len(status_column) * 10
# Dictionary encoding
encoded_values, dictionary = dictionary_encode(status_column)
# Encoded values: 1 byte each, dictionary: ~30 bytes
dict_encoded_size = len(encoded_values) * 1 + 30
# Run-length encoding on the sorted/clustered data
rle_result = run_length_encode(status_column)
# Just 3 tuples: [("shipped", 40000), ("pending", 35000), ("delivered", 25000)]
rle_size = len(rle_result) * 14 # ~14 bytes per tuple
print(f"Original size: {original_size:,} bytes")
print(f"Dictionary encoded: {dict_encoded_size:,} bytes ({original_size/dict_encoded_size:.1f}x)")
print(f"RLE (sorted data): {rle_size:,} bytes ({original_size/rle_size:.0f}x)")
# Output:
# Original size: 1,000,000 bytes
# Dictionary encoded: 100,030 bytes (10.0x)
# RLE (sorted data): 42 bytes (23810x)
Real columnar stores combine these techniques. ClickHouse might dictionary-encode a status column, then apply LZ4 compression on top. Parquet uses a hybrid of RLE, dictionary encoding, and bit-packing automatically based on data characteristics.
Query Execution on Columnar Data
Modern columnar engines don’t just store data differently—they execute queries differently. Three techniques dominate:
Late Materialization: Rather than constructing full rows early, columnar engines pass around column positions (row IDs) as long as possible, only assembling final rows at the end.
Vectorized Execution: Process batches of 1,000+ values at once rather than row-at-a-time. This amortizes function call overhead and enables SIMD instructions.
Predicate Pushdown: Apply filters during the scan phase, before decompression when possible, using column statistics to skip entire chunks.
-- DuckDB: Analyze a columnar query execution
CREATE TABLE orders AS
SELECT
i AS order_id,
(random() * 1000)::INT AS customer_id,
(random() * 500)::DECIMAL(10,2) AS amount,
CASE (random() * 3)::INT
WHEN 0 THEN 'pending'
WHEN 1 THEN 'shipped'
ELSE 'delivered'
END AS status,
DATE '2020-01-01' + INTERVAL (random() * 1000) DAY AS order_date
FROM generate_series(1, 10000000) t(i);
-- Examine the vectorized execution plan
EXPLAIN ANALYZE
SELECT
status,
AVG(amount) as avg_amount,
COUNT(*) as order_count
FROM orders
WHERE order_date >= '2023-01-01'
GROUP BY status;
-- Key output (simplified):
-- ┌─────────────────────────────────────────────────────────┐
-- │ HASH_GROUP_BY │
-- │ Groups: 3 │
-- │ Aggregates: avg(amount), count_star() │
-- ├─────────────────────────────────────────────────────────┤
-- │ FILTER (order_date >= 2023-01-01) │
-- │ Rows Passed: 3,421,892 │
-- ├─────────────────────────────────────────────────────────┤
-- │ SEQ_SCAN orders │
-- │ Columns: status, amount, order_date │ <- Only 3 columns read
-- │ Vectors Scanned: 9,766 │ <- Vectorized batches
-- └─────────────────────────────────────────────────────────┘
Notice that DuckDB reads only three columns despite the table having five. The “Vectors Scanned” metric shows it processed data in batches of ~1,024 rows, not one at a time.
Trade-offs and When to Use Row Storage
Column storage isn’t universally better. It optimizes for different trade-offs:
Write Amplification: Inserting one row means writing to N separate column files. Columnar databases batch writes heavily to mitigate this, but real-time inserts suffer compared to row stores.
Point Lookups: Fetching a single complete row requires reading from every column file and reconstructing the row. Row storage does this in one read.
Updates: Modifying a single field in a row touches one column file, but maintaining consistency across column files adds complexity. Most columnar databases treat updates as delete-then-insert.
Hybrid approaches bridge the gap. Delta Lake stores recent writes in row-oriented format, periodically compacting them into columnar Parquet files. ClickHouse uses a MergeTree engine that writes data in sorted, columnar chunks and merges them in the background.
Use row storage when: your workload is primarily OLTP, you need sub-millisecond point lookups, or you have heavy update patterns.
Use column storage when: you run aggregations over large datasets, your queries touch few columns from wide tables, or query performance matters more than write latency.
Practical Implementation Considerations
For file-based analytics, Parquet has become the standard. Here’s a practical comparison:
import pyarrow as pa
import pyarrow.parquet as pq
import pyarrow.csv as csv
import time
import os
# Create sample data (1 million rows, 20 columns)
num_rows = 1_000_000
table = pa.table({
f"col_{i}": pa.array(range(num_rows)) if i % 3 == 0
else pa.array([f"value_{j % 100}" for j in range(num_rows)])
for i in range(20)
})
# Write as CSV
csv_path = "/tmp/sample.csv"
csv.write_csv(table, csv_path)
# Write as Parquet with compression
parquet_path = "/tmp/sample.parquet"
pq.write_table(table, parquet_path, compression='snappy')
# Compare file sizes
csv_size = os.path.getsize(csv_path)
parquet_size = os.path.getsize(parquet_path)
print(f"CSV size: {csv_size / 1e6:.1f} MB")
print(f"Parquet size: {parquet_size / 1e6:.1f} MB")
print(f"Compression: {csv_size / parquet_size:.1f}x smaller")
# Benchmark: Read only 2 columns and compute sum
def benchmark_csv():
t = csv.read_csv(csv_path)
return t.column("col_0").to_pylist()
def benchmark_parquet():
t = pq.read_table(parquet_path, columns=["col_0"]) # Column pruning!
return t.column("col_0").to_pylist()
# Time both approaches
start = time.time()
benchmark_csv()
csv_time = time.time() - start
start = time.time()
benchmark_parquet()
parquet_time = time.time() - start
print(f"\nCSV read time: {csv_time:.2f}s")
print(f"Parquet read time: {parquet_time:.2f}s")
print(f"Speedup: {csv_time / parquet_time:.1f}x")
# Typical output:
# CSV size: 341.2 MB
# Parquet size: 24.8 MB
# Compression: 13.8x smaller
#
# CSV read time: 2.14s
# Parquet read time: 0.08s
# Speedup: 26.8x
For database selection: DuckDB embeds directly in your application and handles Parquet files natively. ClickHouse excels at high-throughput ingestion with real-time queries. BigQuery and Snowflake offer managed columnar storage if you prefer not to operate infrastructure.
Migration strategy: Start by exporting analytical tables to Parquet and querying with DuckDB alongside your existing database. This gives you immediate benefits without changing your OLTP system. As you identify query patterns, consider dedicated columnar databases for your heaviest workloads.
Column-oriented storage isn’t a silver bullet, but for analytical workloads, it’s usually the right default. The physics of disk I/O and CPU cache lines favor reading contiguous, homogeneous data. Build your analytics infrastructure around that reality.