---
title: "Replacing the HDB: ClickHouse for historical ticker data"
description: "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."
---

# Replacing the HDB: ClickHouse for historical ticker data

[Clickhouse](https://yomu.fyi/company/clickhouse) · Jul 15, 2026

**Type:** Benchmark

## 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.

## Context

Historical market data is typically divided between a real-time tier for live and current-day queries and a durable historical tier for research, backtesting, transaction-cost analysis, and regulatory reconstruction. The historical tier is presented as a lower-risk place to evaluate a database because it does not affect the live trading path, does not require microsecond latency, and has a well-understood workload.

## Approach / What changed

The example loads Binance’s public market-data archive into ClickHouse. Trade data is stored in a MergeTree table ordered by (symbol, ts), with LowCardinality, Decimal, Boolean, DoubleDelta, and ZSTD encodings. Data can be ingested directly from ZIP archives, and SQL queries demonstrate VWAP, OHLC candles, as-of joins, and symbol-and-time filtering that uses the sparse primary index to skip most granules.

## Takeaways

- 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.
- The demonstrated trading-desk operations—VWAP, hourly OHLC candles, and as-of joins—are expressed as standard SQL queries.
- For symbol-and-time filters, the (symbol, ts) primary key lets ClickHouse read only matching granules; queries without those dimensions scan more data.

**Tags:** [Data Pipelines](https://yomu.fyi/topic/data-pipelines), [Performance](https://yomu.fyi/topic/performance), [Scalability](https://yomu.fyi/topic/scalability), [Search](https://yomu.fyi/topic/search)

- Source: [Clickhouse](https://clickhouse.com/blog/historical-ticker-data)
- Source URL: https://clickhouse.com/blog/historical-ticker-data
- Ingested by Yomu: 2026-08-29T01:38:40.674Z

[Read original post](https://clickhouse.com/blog/historical-ticker-data)