Order Book Snapshots Storage System Development

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.

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.

Process

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.