Hybrid Storage: One Table, Two Layouts

Concept. Hybrid storage runs row-oriented and column-oriented layouts side by side: fresh writes land in row format for speed, then a background process converts aged partitions to columnar for cheap analytics. One system, two layouts, picked automatically by data age.

Intuition. Spotify's last hour of listens needs blazing inserts (1M/sec); the past year of listens needs blazing aggregates (100ms dashboards). Pure row gives you the inserts but slow analytics; pure columnar gives you the analytics but slow inserts. ClickHouse's MergeTree keeps recent data as row-shaped "parts" and merges them into columnar segments in the background. Same SQL, both worlds.

The Challenge: Two Workloads, One Table

-- OLTP: high-frequency real-time inserts
INSERT INTO Listens (listen_id, user_id, song_id, rating, listen_time)
VALUES (75382914, 12345, 456, 4.5, NOW());
-- happening 1M times per second

-- OLAP: complex analytics on the same data
SELECT song_id, COUNT(*) AS plays, AVG(rating)
FROM Listens
WHERE listen_time > NOW() - INTERVAL '1 hour'
GROUP BY song_id
ORDER BY plays DESC;
-- must complete in <100 ms for dashboards

Pick row, you get the inserts but the analytics take 30 s. Pick columnar, you get 50 ms analytics but inserts crash to 10 K/sec. Hybrid says: don't pick.

The Trade-off

Layout	Inserts	Analytics
Pure row	1M/sec ✓	30 s ✗
Pure columnar	10K/sec ✗	50 ms ✓
Hybrid (row + columnar)	1M/sec ✓	100 ms ✓

How It Works

A time axis from newest hot data on the left to oldest cold data on the right: fresh listens land as grey row-shaped parts taking 1M inserts/sec; a background-merge arrow converts aged parts into large green columnar segments answering scans in ~100 ms at ~10:1 compression; a green unified-query bar spans the bottom, one SQL surface reading both.

Figure 1. listen_time is the natural partitioning dimension: recent data needs fast writes, old data needs fast scans, so age chooses the layout, not you. Fresh listens land as small row-shaped parts (grey) that accept inserts at about 1M/sec because each insert writes a whole row in one place. A background merge converts aged parts into columnar segments (green) once activity slows, so analytics read one column and skip the rest at roughly 10:1 compression. A single query engine spans both: one SQL surface, no second system, no ETL lag, and the storage tunes itself to the workload instead of you choosing row or columnar up front.

Real-World Implementation: ClickHouse MergeTree

ClickHouse's MergeTree engine is the canonical example, in production at Cloudflare, Uber, and Bloomberg.

Incoming listens land in small row-oriented "parts."
Background merging combines parts into larger columnar-compressed segments.
Query execution reads from both formats simultaneously.
Automatic optimization based on listen_time age and size thresholds.

Performance numbers:

Insert throughput: 1M+ listens/sec sustained
Query latency: sub-second analytics on billions of listening events
Storage: 10:1 compression on real-world listening data
Operational simplicity: one system, not separate OLTP + OLAP

Storage engine connection. ClickHouse's "parts + background merging" pattern is a variation of LSM trees. The twist: traditional LSM trees merge row-to-row; ClickHouse merges row-to-columnar. We cover LSM tree fundamentals in LSM Trees.

Adaptive Storage Research

Active research areas:

Workload-adaptive storage. Systems that learn access patterns and re-tune layout.
Online reorganization. Converting between formats without stopping queries.
Hybrid indexing. Row-based indexes over columnar-compressed data.
Multi-format query processing. Execution across heterogeneous storage layouts.