Tick-Data Storage System Development

Imagine you analyze the crypto market and your strategy needs access to every trade for the last two years. Without proper tick-data storage, this is impossible. Even experienced teams face typical issues: slow writes, high storage costs, and aggregation difficulties. We have designed and deployed dozens of such systems—from prototypes to production installations with billions of rows. Recently, a market maker approached us because their PostgreSQL couldn't handle the load: writes took over 5 seconds, and queries for a single trading day took minutes. After migrating to ClickHouse, write latency dropped to 50 ms, and on-the-fly aggregation now takes seconds.

Data Volumes

To understand the scale: Binance on BTC/USDT generates about 50,000–200,000 trades per day. Across all pairs on all exchanges—hundreds of millions of records daily. A year of data means tens of billions of rows. Ordinary PostgreSQL cannot handle this without special solutions.

Which Tick-Data Storage Technologies to Choose?

ClickHouse is a columnar DBMS from Yandex optimized for analytical queries. It compresses time series better than competitors and runs aggregations on billions of rows in seconds. A table partitioned by exchange and month looks like this:

CREATE TABLE trades ( exchange LowCardinality(String), symbol LowCardinality(String), trade_id String, timestamp DateTime64(3, 'UTC'), price Decimal(24, 8), quantity Decimal(24, 8), side LowCardinality(String), is_maker Bool ) ENGINE = MergeTree() PARTITION BY (exchange, toYYYYMM(timestamp)) ORDER BY (exchange, symbol, timestamp) SETTINGS index_granularity = 8192; 

LowCardinality for strings with few unique values—automatic dictionary encoding saves significant space.

TimescaleDB is a good choice if you already use PostgreSQL and have moderate volumes (<1 billion rows). It supports hypertables and compression policies.

Arctic is a specialized solution for financial time series in Python, with versioning and tick-data support.

How We Design the Ingestion Pipeline

For maximum write performance, we use a buffer table:

-- Buffer: accumulates data in memory, flushes every 10 sec or 1M rows CREATE TABLE trades_buffer AS trades ENGINE = Buffer(currentDatabase(), 'trades', 16, 10, 100, 10000, 1000000, 10000000, 100000000); -- Write to buffer, read from main table INSERT INTO trades_buffer VALUES (...); SELECT * FROM trades WHERE ...; 

A Python pipeline with async buffering:

import asyncio from collections import deque class TickDataIngester: BATCH_SIZE = 10000 FLUSH_INTERVAL = 5.0 # seconds def __init__(self, clickhouse_client): self.buffer = deque() self.client = clickhouse_client async def on_trade(self, trade: NormalizedTrade): self.buffer.append(trade) if len(self.buffer) >= self.BATCH_SIZE: await self.flush() async def flush(self): if not self.buffer: return batch = [self.buffer.popleft() for _ in range(min(self.BATCH_SIZE, len(self.buffer)))] await self.client.insert('trades_buffer', batch) async def flush_loop(self): while True: await asyncio.sleep(self.FLUSH_INTERVAL) await self.flush() 

How to Aggregate Tick Data into OHLCV Candles

On-the-fly aggregation using ClickHouse window functions:

SELECT toStartOfInterval(timestamp, INTERVAL 1 MINUTE) AS candle_time, argMin(price, timestamp) AS open, max(price) AS high, min(price) AS low, argMax(price, timestamp) AS close, sum(quantity) AS volume, count() AS trade_count FROM trades WHERE exchange = 'binance' AND symbol = 'BTC/USDT' AND timestamp BETWEEN '2024-01-01 00:00:00' AND '2024-01-02 00:00:00' GROUP BY candle_time ORDER BY candle_time; 

On ClickHouse, this query on 50 million rows executes in 1–3 seconds. An alternative approach is materialized views that precompute candles on insert. Comparison:

The choice depends on the scenario: for ad-hoc analytics use on-the-fly, for real-time dashboards use materialized views.

Compression and Retention

ClickHouse compresses data automatically. Additionally, we enable cold storage via TTL:

ALTER TABLE trades MODIFY TTL timestamp + INTERVAL 1 YEAR TO DISK 'cold_storage'; 

Data older than one year is automatically moved to cheaper storage (S3-compatible).

Historical Data Backfill

To fill historical data, we use public exchange APIs. Binance provides trade history via /api/v3/aggTrades with pagination by fromId. Parallel backfill over time ranges with rate limiting loads years of data in a few hours.

What's Included in the Work (Deliverables)

• Architectural documentation: DBMS selection, partitioning scheme, retention and compression policies
• Ingestion pipeline development: buffering, deduplication, latency monitoring
• Source integration: exchange WebSocket, REST API, Kafka
• ClickHouse/TimescaleDB configuration tailored to your hardware profile
• Load testing: simulating peak loads up to 1 million records per second
• Operations documentation: backup/restore, upgrade, monitoring
• Guarantee: 30 days post-launch support and incident handling

Request a free consultation — we will analyze your workload and propose the optimal architecture.

Why Choose Us?

We have 5+ years of experience in developing data storage systems for cryptocurrency exchanges. We have completed over 30 projects with a total stored data volume exceeding 100 billion records. Our clients range from startups to large market makers. We guarantee that the system will perform as specified and not degrade under growing volumes.

Timeline and Cost

Development timeline: from 4 to 12 weeks depending on complexity and volume. Cost is calculated individually based on data volume, number of sources, and latency requirements. Contact us to get demo access to a working system under your load—we will conduct a preliminary assessment and propose a solution.