Hybrid Storage: One Table, Two Layouts

Concept. Hybrid storage keeps row-oriented and column-oriented layouts in the same table. Fresh writes land in row format to maximize insert throughput. A background process converts older partitions to a columnar layout to reduce scan cost. Data age selects the layout.

Intuition. Spotify's last hour of listens needs inserts at 1M/sec. The past year of listens needs aggregates for dashboards in 100ms. A pure row layout reaches the insert rate but takes 30 s for analytics. A pure columnar layout reaches 50 ms analytics but drops inserts to 10 K/sec. ClickHouse's MergeTree keeps recent data in row-shaped parts and merges older data into columnar segments in the background. The same SQL reads both layouts.

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 provides a partitioning dimension. The system writes fresh listens into small row-shaped parts (grey) to accept inserts at about 1M/sec by writing each row contiguously. A background merge rewrites older parts into columnar segments (green) after activity slows. Analytics scan one column and skip others at roughly 10:1 compression. One query engine reads both layouts through the same SQL surface, without a second system, without ETL lag, and without manual layout choice.

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.