We are a team of Web3 engineers with 10+ years of experience developing crypto infrastructure. We have implemented over 20 projects for exchanges and trading firms, including order book storage systems handling up to 5000 updates per second. In crypto exchanges, the order book is one of the most data-intensive sources. Traders demand low latency, while analysts require full history. Improper storage leads to enormous infrastructure costs.
Order book data is the most informative yet the most challenging to store. A full BTC/USDT book on Binance contains 5000 levels on each side, updates 5–10 times per second, and generates hundreds of megabytes per hour. With a naive approach (storing every snapshot), volume reaches 100 GB per day for a single symbol. A proper system balances data completeness with practical constraints. Our solution combines full snapshots and deltas (diff), achieving 50x compression without loss of resolution.
Which Order Book Storage Format to Choose?
Before designing storage, it's essential to understand what data is actually needed. The table below compares the main formats.
| Type of Data | Size (per update) | Write Frequency | Use Case |
|---|---|---|---|
| Full snapshot | 8–15 KB | Once per minute | State recovery, backups |
| Depth snapshot (20 levels) | 200–500 bytes | 1–5 per second | Trading strategies, visualization |
| Order book diff | 150–300 bytes | Every update | Second-level resolution between snapshots |
| Mid-price + spread | 40 bytes | Every update | Long-term analysis, monitoring |
In practice, systems store a combination: full snapshots for recovery and deltas for historical precision.
Storage Format: Delta Encoding
Delta encoding is critical for reducing volume. Instead of saving the full book, we only store changes relative to the previous state.
Snapshot @ T=0:
bids: [(43250.0, 1.5), (43249.5, 2.0), (43249.0, 0.8)]
asks: [(43251.0, 1.2), (43251.5, 3.0), (43252.0, 0.5)]
Diff @ T=1 (only changes):
bids_updated: [(43250.0, 2.1)] # объём изменился
bids_removed: [(43249.5, 0)] # уровень исчез
bids_added: [(43248.5, 1.0)] # новый уровень
asks_updated: []
asks_removed: []
asks_added: [(43251.75, 0.3)]
Full snapshot: ~8 KB. Diff: ~200 bytes. At 5 updates per second and a snapshot every 60 seconds — 300 diffs + 1 snapshot = ~60 KB/min instead of 3 MB/min. Gain: 50x.
Why ClickHouse Is the Optimal Choice?
We use ClickHouse with custom serialization. Columnar storage and support for tuple arrays are ideal for the order book structure. ZSTD compression further reduces volume. According to ClickHouse documentation, columnar storage and ZSTD can compress numeric data 2-3 times more efficiently than LZ4.
CREATE TABLE orderbook_snapshots (
exchange LowCardinality(String),
symbol LowCardinality(String),
snapshot_time DateTime64(3, 'UTC'),
depth UInt16,
bids Array(Tuple(Decimal(24,8), Decimal(24,8))),
asks Array(Tuple(Decimal(24,8), Decimal(24,8)))
)
ENGINE = MergeTree()
PARTITION BY (exchange, toYYYYMM(snapshot_time))
ORDER BY (exchange, symbol, snapshot_time);
CREATE TABLE orderbook_diffs (
exchange LowCardinality(String),
symbol LowCardinality(String),
diff_time DateTime64(3, 'UTC'),
first_update_id UInt64,
last_update_id UInt64,
bids_changes Array(Tuple(Decimal(24,8), Decimal(24,8))),
asks_changes Array(Tuple(Decimal(24,8), Decimal(24,8)))
)
ENGINE = MergeTree()
PARTITION BY (exchange, toYYYYMM(diff_time))
ORDER BY (exchange, symbol, diff_time);
CREATE TABLE orderbook_metrics (
exchange LowCardinality(String),
symbol LowCardinality(String),
ts DateTime64(3, 'UTC'),
mid_price Decimal(24,8),
spread Decimal(24,8),
spread_bps Decimal(10,4),
bid_1 Decimal(24,8),
ask_1 Decimal(24,8),
bid_vol_10 Decimal(24,8),
ask_vol_10 Decimal(24,8),
imbalance Decimal(10,6)
)
ENGINE = MergeTree()
PARTITION BY (exchange, toYYYYMM(ts))
ORDER BY (exchange, symbol, ts)
SETTINGS default_codec = ZSTD(3);
Reconstructing the Order Book State
The key operation is restoring the book at an arbitrary point in time. This is implemented by sequentially applying deltas from the last snapshot.
class OrderBookReplay:
def __init__(self, storage: OrderBookStorage):
self.storage = storage
async def reconstruct_at(self, exchange: str, symbol: str, target_ts: int) -> OrderBook:
snapshot = await self.storage.get_last_snapshot_before(exchange, symbol, target_ts)
if not snapshot:
raise ValueError("No snapshot available before target timestamp")
diffs = await self.storage.get_diffs(exchange, symbol, from_ts=snapshot.timestamp, to_ts=target_ts)
book = OrderBook.from_snapshot(snapshot)
for diff in diffs:
book.apply_diff(diff)
return book
class OrderBook:
def apply_diff(self, diff: OrderBookDiff):
for price, qty in diff.bids_changes:
if qty == 0:
self.bids.pop(price, None)
else:
self.bids[price] = qty
for price, qty in diff.asks_changes:
if qty == 0:
self.asks.pop(price, None)
else:
self.asks[price] = qty
It's important to apply deltas in order and validate via update_id — at Binance, each diff has lastUpdateId, the next must start with lastUpdateId+1. A gap means missing data.
Compression and Optimization
Before writing to ClickHouse, we apply:
- Delta encoding for prices: store the difference from the best bid/ask in basis points (bps). Integers compress better.
- Binary serialization: Protocol Buffers or MessagePack instead of JSON. Gain 3–5x in size and speed.
- ClickHouse compression: ZSTD(3) algorithm for Decimal and Float data — 20% more efficient than default LZ4.
Stream Ingestion
The ingestion pipeline runs in parallel: snapshots every 60 seconds, deltas buffered and saved in batches of 100.
class OrderBookIngester:
SNAPSHOT_INTERVAL = 60
DIFF_BATCH_SIZE = 100
def __init__(self, storage):
self.storage = storage
self.diff_buffer = []
self.last_snapshot_time = 0
async def on_orderbook_update(self, book: OrderBook, diff: OrderBookDiff):
now = time.time()
if now - self.last_snapshot_time >= self.SNAPSHOT_INTERVAL:
await self.storage.save_snapshot(book.to_snapshot())
self.last_snapshot_time = now
self.diff_buffer.append(diff)
if len(self.diff_buffer) >= self.DIFF_BATCH_SIZE:
await self.storage.save_diffs(self.diff_buffer)
self.diff_buffer.clear()
Analytical Queries
After data accumulates, analysis becomes possible. For example, average spread by hour or correlation of imbalance with price movement.
-- Средний спред BTC/USDT по часам за выбранный месяц
SELECT
toStartOfHour(ts) AS hour,
avg(spread_bps) AS avg_spread_bps,
avg(imbalance) AS avg_imbalance
FROM orderbook_metrics
WHERE exchange = 'binance'
AND symbol = 'BTC/USDT'
AND ts BETWEEN '2024-01-01' AND '2024-02-01'
GROUP BY hour
ORDER BY hour;
-- Корреляция imbalance с последующим движением цены
WITH book AS (
SELECT ts, imbalance, mid_price
FROM orderbook_metrics
WHERE exchange = 'binance' AND symbol = 'BTC/USDT'
),
future AS (
SELECT
b.ts,
b.imbalance,
(f.mid_price - b.mid_price) / b.mid_price * 10000 AS fwd_return_bps
FROM book b
ASOF JOIN book f ON b.symbol = f.symbol
AND f.ts BETWEEN b.ts + INTERVAL 1 MINUTE AND b.ts + INTERVAL 2 MINUTE
)
SELECT
round(imbalance, 1) AS imbalance_bucket,
avg(fwd_return_bps) AS avg_1min_return_bps,
count() AS count
FROM future
GROUP BY imbalance_bucket
ORDER BY imbalance_bucket;
Monitoring and Data Quality
It is critical to track gaps in delta sequences. The validation system compares lastUpdateId of each diff with firstUpdateId of the next and alerts on gaps. A gap between snapshots makes recovery impossible.
Metrics for monitoring: snapshot write frequency per symbol, latency from exchange timestamp to ClickHouse write, delta buffer size, percentage of missed updates.
Data Quality Checklist
- Verify the sequence of update_id in diffs - Ensure snapshot interval does not exceed 60 seconds - Monitor write latency (should be < 1 second) - Periodically reconstruct a test symbol's book and compare with the latest snapshotProcess
| Stage | Duration | Result |
|---|---|---|
| Requirements Analysis | 2-3 days | Technical specification, schema prototype |
| Schema Design | 3-5 days | ER diagram, tool selection |
| Pipeline Implementation | 5-10 days | Working ingestion, tests |
| API Development | 3-5 days | Documentation, query examples |
| Monitoring and Debugging | 2-3 days | Dashboards, alerts |
| Documentation and Training | 1-2 days | README, instructions |
Estimated timeline: 2 to 4 weeks depending on complexity. Cost is calculated individually after reviewing the task.
What's Included
- Storage schema design tailored to your load (update frequency, number of symbols, latency requirements).
- Implementation of an ingestion pipeline in Python with WebSocket or REST API integration.
- API development for historical data access (book reconstruction, delta retrieval, aggregates).
- Documentation for recovery and analytical queries.
- Team training.
- One month of support after launch.
If you are interested in optimizing exchange data storage, contact us for a preliminary assessment. Reach out to evaluate your project. Order a turnkey order book storage system and get a consultation on architecture and timelines.