Replacing the HDB: ClickHouse for historical ticker data

Summary

Historical market-data systems separate a durable archive from the real-time trading path, but the archive still must support research, backtesting, transaction-cost analysis, and regulatory reconstruction. The worked example uses Binance’s public archive to load trades and quotes for five USD-M perpetual-futures symbols into ClickHouse, with a MergeTree table ordered by (symbol, ts) and codecs including DoubleDelta and ZSTD. SQL queries calculate VWAP, hourly OHLC candles, and as-of joins, while ClickHouse’s sparse primary index skips most granules for symbol-and-time filters. Across ten months, 16.2 billion rows compress to 76 GiB on disk; the post reports a ClickHouse Cloud cost of $1.88 per month and argues that typical symbol-and-time queries retain low latency at that scale, while broader scans read more data.

Takeaways

1. 1

   ClickHouse stores ten months of trades and quotes for five busy futures pairs—16.2 billion rows—in 76 GiB, with a reported ClickHouse Cloud cost of $1.88 per month.

2. 2

   The demonstrated trading-desk operations—VWAP, hourly OHLC candles, and as-of joins—are expressed as standard SQL queries.

3. 3

   For symbol-and-time filters, the (symbol, ts) primary key lets ClickHouse read only matching granules; queries without those dimensions scan more data.