Trong lĩnh vực tài chính định lượng và hệ thống giao dịch tần suất cao (HFT), chi phí lưu trữ và phân tích dữ liệu order book lịch sử là một trong những yếu tố quyết định ROI của toàn bộ hệ thống. Bài viết này tôi sẽ chia sẻ kinh nghiệm thực chiến khi đánh giá hai phương án: Tardis (dịch vụ SaaS chuyên biệt) và Self-Hosted ClickHouse (triển khai on-premise). Tôi đã vận hành cả hai giải pháp trong môi trường production với data volume thực tế, và đây là phân tích chi tiết từ góc nhìn kỹ sư.
Tardis Là Gì? ClickHouse Là Gì?
Tardis là dịch vụ market data API cung cấp dữ liệu order book, trade, ticker từ hơn 50 sàn giao dịch tiền mã hóa và traditional finance. Tardis hỗ trợ tính năng data replay, cho phép developer backtest chiến lược với dữ liệu lịch sử chất lượng cao.
ClickHouse là columnar database mã nguồn mở được thiết kế cho OLAP workloads, đặc biệt phù hợp với việc query dữ liệu chuỗi thời gian (time-series) với hiệu suất cực cao. ClickHouse là lựa chọn phổ biến khi teams cần full control và tiết kiệm chi phí ở scale lớn.
Kiến Trúc So Sánh
Kiến Trúc Tardis
┌─────────────────────────────────────────────────────────────────┐
│ TARDIS ARCHITECTURE │
├─────────────────────────────────────────────────────────────────┤
│ │
│ Exchange APIs ──► Tardis Collectors ──► Managed Storage │
│ │ │
│ ▼ │
│ ┌─────────────┐ │
│ │ ClickHouse│ │
│ │ (Managed) │ │
│ └─────────────┘ │
│ │ │
│ ▼ │
│ Client ──► REST/WebSocket API ◄── Query Engine ──► Replay │
│ │
│ Features: │
│ ✓ Automatic exchange connection management │
│ ✓ Normalized data format across exchanges │
│ ✓ Built-in replay functionality │
│ ✓ SLA guaranteed │
└─────────────────────────────────────────────────────────────────┘
Kiến Trúc Self-Hosted ClickHouse
┌─────────────────────────────────────────────────────────────────┐
│ SELF-HOSTED CLICKHOUSE │
├─────────────────────────────────────────────────────────────────┤
│ │
│ Exchange APIs ──► Data Collectors ──► Kafka ──► ClickHouse │
│ (Python/Go) (Custom) Cluster Replicas │
│ │ │
│ ▼ │
│ ┌─────────────┐ │
│ │ Zookeeper │ │
│ │ /Keeper │ │
│ └─────────────┘ │
│ │ │
│ ▼ │
│ Client ──► Application Server ◄── Query Cache ──► Backup │
│ │
│ Infrastructure Required: │
│ • 3x Compute Instances (recommended for HA) │
│ • Block Storage (NVMe SSD recommended) │
│ • Network configuration & security groups │
│ • Backup strategy & disaster recovery │
└─────────────────────────────────────────────────────────────────┘
So Sánh Chi Phí TCO Chi Tiết
| Thành Phần Chi Phí | Tardis (SaaS) | Self-Hosted ClickHouse | Chênh Lệch |
|---|---|---|---|
| Chi phí subscription | $299-999/tháng (tùy gói) | $0 (license miễn phí) | Tardis cao hơn |
| Compute (3 nút) | Đã bao gồm | $180-450/tháng (AWS c5.xlarge) | Self-hosted tiết kiệm |
| Storage (2TB/tháng) | Đã bao gồm | $100-200/tháng (IO1 NVMe) | Self-hosted tiết kiệm |
| Network egress | Miễn phí (API calls) | $20-80/tháng | Tardis thắng |
| Engineering (setup) | ~8 giờ | ~80-120 giờ | Tardis thắng lớn |
| Engineering (monthly ops) | ~2 giờ | ~16-40 giờ | Tardis thắng |
| Monitoring & Alerting | Đã bao gồm | $30-100/tháng (Datadog/Prometheus) | Tardis thắng |
| Backup & DR | Đã bao gồm | $50-150/tháng | Tardis thắng |
| Tổng TCO tháng (1 năm) | $299-999 | $380-980 | Tùy scale |
| Tổng TCO năm đầu (bao gồm setup) | $3,588-11,988 | $5,160-12,960 | ~Tương đương ở scale nhỏ |
Benchmark Hiệu Suất Thực Tế
Tôi đã chạy benchmark trên cả hai hệ thống với cùng dataset: 30 ngày order book data từ Binance Futures (khoảng 2.1TB raw data sau khi nén). Kết quả:
-- Query Test: Count trades within time range
-- Dataset: 30 days Binance Futures ETHUSDT orderbook
TARDIS API:
===========
Endpoint: https://api.tardis.dev/v1/replay
Method: POST with exchange, from, to filters
Response Time: ~2,400ms (P95)
Throughput: ~15,000 rows/second
Rate Limit: 10 requests/minute (Basic tier)
SELF-HOSTED CLICKHOUSE:
=======================
Query:
SELECT count() FROM orderbook_trades
WHERE exchange = 'binance_futures'
AND symbol = 'ETHUSDT'
AND timestamp BETWEEN '2026-04-03' AND '2026-05-03';
Response Time: ~180ms (P95) ⚡ 13x faster
Throughput: ~2,000,000 rows/second
Query Concurrency: Up to 64 parallel queries
--- Complex Aggregation Query ---
SELECT
symbol,
toStartOfMinute(timestamp) as minute,
avg(price) as avg_price,
stddevPop(price) as volatility,
count() as trade_count,
sum(volume) as total_volume
FROM orderbook_trades
WHERE timestamp BETWEEN '2026-04-03 00:00:00' AND '2026-04-03 23:59:59'
GROUP BY symbol, minute
ORDER BY minute;
ClickHouse: 1,240ms (P95)
Tardis: ~45,000ms (requires multiple API calls, pagination)
Code Mẫu: Integration Với Hai Hệ Thống
Tardis Integration
#!/usr/bin/env python3
"""
Tardis Market Data API Integration
https://api.tardis.dev/v1
"""
import httpx
import asyncio
from datetime import datetime, timedelta
from typing import List, Dict, Any
TARDIS_API_KEY = "your_tardis_api_key"
BASE_URL = "https://api.tardis.dev/v1"
class TardisClient:
def __init__(self, api_key: str):
self.api_key = api_key
self.client = httpx.AsyncClient(
base_url=BASE_URL,
timeout=60.0,
headers={"Authorization": f"Bearer {api_key}"}
)
async def get_replay(
self,
exchange: str,
symbols: List[str],
from_ts: datetime,
to_ts: datetime,
data_types: List[str] = ["trade", "orderbook"]
) -> List[Dict[str, Any]]:
"""
Fetch historical market data via replay API
"""
params = {
"exchange": exchange,
"symbols": ",".join(symbols),
"from": int(from_ts.timestamp() * 1000),
"to": int(to_ts.timestamp() * 1000),
"dataTypes": ",".join(data_types),
"limit": 10000 # Max records per page
}
all_records = []
cursor = None
while True:
if cursor:
params["cursor"] = cursor
response = await self.client.get("/replay", params=params)
response.raise_for_status()
data = response.json()
all_records.extend(data.get("data", []))
cursor = data.get("nextCursor")
if not cursor:
break
# Respect rate limits (10 req/min on Basic tier)
await asyncio.sleep(6)
return all_records
async def get_symbols(self, exchange: str) -> List[str]:
"""Get available symbols for an exchange"""
response = await self.client.get(f"/exchanges/{exchange}/symbols")
response.raise_for_status()
return [s["symbol"] for s in response.json()["data"]]
async def close(self):
await self.client.aclose()
Example usage
async def main():
client = TardisClient(TARDIS_API_KEY)
try:
# Fetch 1 hour of ETHUSDT trades
end_time = datetime.now()
start_time = end_time - timedelta(hours=1)
trades = await client.get_replay(
exchange="binance",
symbols=["ETHUSDT"],
from_ts=start_time,
to_ts=end_time,
data_types=["trade"]
)
print(f"Fetched {len(trades)} trades")
# Analyze trade distribution
if trades:
volumes = [t["volume"] for t in trades]
prices = [t["price"] for t in trades]
print(f"Average price: ${sum(prices)/len(prices):.2f}")
print(f"Total volume: {sum(volumes):.2f}")
finally:
await client.close()
if __name__ == "__main__":
asyncio.run(main())
ClickHouse Integration (Self-Hosted)
#!/usr/bin/env python3
"""
Self-Hosted ClickHouse Integration for Order Book Storage
"""
import clickhouse_connect
from datetime import datetime, timedelta
from typing import List, Dict, Generator
import pandas as pd
ClickHouse connection settings
CLICKHOUSE_HOST = "clickhouse.example.com"
CLICKHOUSE_PORT = 8443
CLICKHOUSE_USER = "quant_user"
CLICKHOUSE_PASSWORD = "secure_password"
CLICKHOUSE_DATABASE = "market_data"
class ClickHouseMarketDataClient:
def __init__(self):
self.client = clickhouse_connect.get_client(
host=CLICKHOUSE_HOST,
port=CLICKHOUSE_PORT,
username=CLICKHOUSE_USER,
password=CLICKHOUSE_PASSWORD,
database=CLICKHOUSE_DATABASE
)
def create_tables(self):
"""Create optimized tables for order book data"""
# Order book level 2 table
create_orderbook_sql = """
CREATE TABLE IF NOT EXISTS orderbook_levels (
exchange String,
symbol String,
timestamp DateTime64(3),
side Enum8('bid' = 1, 'ask' = 2),
price Decimal(20, 8),
quantity Decimal(20, 8),
level UInt8,
received_at DateTime64(3) DEFAULT now64(3)
) ENGINE = ReplacingMergeTree(received_at)
PARTITION BY (exchange, toYYYYMM(timestamp))
ORDER BY (exchange, symbol, timestamp, side, level)
TTL timestamp + INTERVAL 90 DAY
"""
# Trades table
create_trades_sql = """
CREATE TABLE IF NOT EXISTS trades (
exchange String,
symbol String,
timestamp DateTime64(3),
trade_id String,
price Decimal(20, 8),
quantity Decimal(20, 8),
side Enum8('buy' = 1, 'sell' = 2),
is_maker UInt8,
received_at DateTime64(3) DEFAULT now64(3)
) ENGINE = ReplacingMergeTree(trade_id)
PARTITION BY (exchange, toYYYYMM(timestamp))
ORDER BY (exchange, symbol, timestamp)
TTL timestamp + INTERVAL 365 DAY
"""
for sql in [create_orderbook_sql, create_trades_sql]:
self.client.command(sql)
print("Tables created successfully")
def insert_trades_batch(self, trades: List[Dict]) -> int:
"""Insert trades in batch mode for high throughput"""
if not trades:
return 0
insert_sql = """
INSERT INTO trades (exchange, symbol, timestamp, trade_id,
price, quantity, side, is_maker)
VALUES
"""
values = []
for t in trades:
values.append(f"('{t['exchange']}', '{t['symbol']}', "
f"{t['timestamp_ms']}, '{t['id']}', "
f"{t['price']}, {t['quantity']}, "
f"'{t['side']}', {int(t.get('is_maker', False))})")
full_sql = insert_sql + ", ".join(values)
self.client.command(full_sql)
return len(trades)
def query_vwap(
self,
exchange: str,
symbol: str,
start: datetime,
end: datetime,
interval_seconds: int = 60
) -> pd.DataFrame:
"""
Calculate Volume Weighted Average Price with materialized views
"""
query = f"""
SELECT
toStartOfInterval(timestamp, INTERVAL {interval_seconds} second) as ts,
sum(price * quantity) / sum(quantity) as vwap,
sum(quantity) as volume,
count() as trade_count,
avg(price) as avg_price,
stddevPop(price) as price_std
FROM trades
WHERE exchange = '{exchange}'
AND symbol = '{symbol}'
AND timestamp BETWEEN '{start.isoformat()}' AND '{end.isoformat()}'
GROUP BY ts
ORDER BY ts
"""
result = self.client.query(query)
df = result.result_set.to_pandas()
df['ts'] = pd.to_datetime(df['ts'])
return df
def get_orderbook_snapshot(
self,
exchange: str,
symbol: str,
ts: datetime,
levels: int = 20
) -> Dict[str, List[Dict]]:
"""Get orderbook snapshot at specific timestamp"""
query = f"""
SELECT side, price, quantity, level
FROM orderbook_levels
WHERE exchange = '{exchange}'
AND symbol = '{symbol}'
AND timestamp <= toDateTime64('{ts.isoformat()}', 3)
AND level <= {levels}
ORDER BY timestamp DESC
LIMIT {levels * 2} BY side
"""
result = self.client.query(query)
bids = []
asks = []
for row in result.result_rows:
entry = {"price": float(row[1]), "quantity": float(row[2])}
if row[0] == 'bid':
bids.append(entry)
else:
asks.append(entry)
# Get latest levels only
bids = sorted(bids, key=lambda x: -x['price'])[:levels]
asks = sorted(asks, key=lambda x: x['price'])[:levels]
return {"bids": bids, "asks": asks}
def stream_trades(
self,
exchange: str,
symbol: str,
start: datetime
) -> Generator[Dict, None, None]:
"""Stream trades using clickhouse-driver for memory efficiency"""
query = f"""
SELECT exchange, symbol, timestamp, trade_id,
price, quantity, side, is_maker
FROM trades
WHERE exchange = '{exchange}'
AND symbol = '{symbol}'
AND timestamp >= toDateTime64('{start.isoformat()}', 3)
ORDER BY timestamp
"""
result = self.client.query(query)
columns = ['exchange', 'symbol', 'timestamp', 'trade_id',
'price', 'quantity', 'side', 'is_maker']
for row in result.result_rows:
yield dict(zip(columns, row))
def get_storage_size(self) -> Dict[str, int]:
"""Get storage statistics"""
query = """
SELECT
database,
table,
formatReadableSize(sum(bytes_on_disk)) as size,
sum(rows) as row_count
FROM system.parts
WHERE database = 'market_data'
AND active = 1
GROUP BY database, table
"""
result = self.client.query(query)
return {row[1]: {"size": row[2], "rows": row[3]}
for row in result.result_rows}
Usage Example
if __name__ == "__main__":
client = ClickHouseMarketDataClient()
# Create tables on first run
# client.create_tables()
# Query VWAP for analysis
end = datetime.now()
start = end - timedelta(days=7)
vwap_df = client.query_vwap("binance", "BTCUSDT", start, end, 300)
print(f"VWAP Analysis:\n{vwap_df.head()}")
# Get storage stats
storage = client.get_storage_size()
print(f"\nStorage Usage: {storage}")
Lỗi Thường Gặp Và Cách Khắc Phục
1. Tardis: Rate Limit Exceeded
# ❌ LỖI: Request thất bại do rate limit
Error: 429 Too Many Requests
{"error": "Rate limit exceeded. 10 requests per minute allowed."}
✅ KHẮC PHỤC: Implement exponential backoff và caching
import time
import asyncio
from functools import wraps
from cachetools import TTLCache
class TardisRateLimitedClient:
def __init__(self, api_key: str, max_retries: int = 5):
self.api_key = api_key
self.max_retries = max_retries
self.base_delay = 6 # 60 seconds / 10 requests = 6s between calls
self.cache = TTLCache(maxsize=1000, ttl=300) # 5 minute cache
def with_rate_limit(self, func):
"""Decorator để handle rate limiting tự động"""
@wraps(func)
async def wrapper(*args, **kwargs):
cache_key = f"{func.__name__}:{args}:{kwargs}"
# Check cache first
if cache_key in self.cache:
return self.cache[cache_key]
last_exception = None
for attempt in range(self.max_retries):
try:
result = await func(*args, **kwargs)
# Cache successful result
if result:
self.cache[cache_key] = result
return result
except httpx.HTTPStatusError as e:
if e.response.status_code == 429:
# Exponential backoff: 6s, 12s, 24s, 48s, 96s
delay = self.base_delay * (2 ** attempt)
print(f"Rate limited. Waiting {delay}s before retry...")
await asyncio.sleep(delay)
last_exception = e
else:
raise
except httpx.ConnectError as e:
# Retry on connection errors
delay = self.base_delay * (2 ** attempt)
print(f"Connection error. Retrying in {delay}s...")
await asyncio.sleep(delay)
last_exception = e
raise Exception(f"Max retries exceeded: {last_exception}")
return wrapper
Alternative: Batch requests để giảm API calls
async def fetch_data_in_batches(client: TardisClient,
exchange: str,
symbol: str,
start: datetime,
end: datetime,
batch_days: int = 7):
"""
Fetch data in weekly batches để optimize rate limit usage
"""
current = start
all_data = []
while current < end:
batch_end = min(current + timedelta(days=batch_days), end)
try:
batch_data = await client.get_replay(
exchange=exchange,
symbols=[symbol],
from_ts=current,
to_ts=batch_end
)
all_data.extend(batch_data)
print(f"Fetched {len(batch_data)} records: {current} to {batch_end}")
# Always respect rate limit between batches
await asyncio.sleep(6)
except Exception as e:
print(f"Error fetching {current} to {batch_end}: {e}")
# Smaller batch on error
batch_end = current + timedelta(days=1)
current = batch_end
return all_data
2. ClickHouse: Merge Thread Stuck / Too Many Parts
# ❌ LỖI: Query chậm bất thường do quá nhiều parts chưa merge
Error in logs: "DB::Exception: Too many parts..."
✅ KHẮC PHỤC: Tối ưu cấu hình merge tree và cleaning
1. Kiểm tra số lượng parts hiện tại
SELECT
table,
count() as parts_count,
sum(rows) as total_rows,
formatReadableSize(sum(bytes)) as total_size
FROM system.parts
WHERE database = 'market_data'
AND active = 1
GROUP BY table
ORDER BY parts_count DESC;
2. Tối ưu bảng với cấu hình merge thích hợp
ALTER TABLE trades MODIFY SETTING:
parts_to_throw_insert = 300, -- Throw error if parts > 300
parts_to_delay_insert = 150, -- Start delaying if parts > 150
max_delay_to_insert = 5, -- Max delay seconds
number_of_free_entries_in_buffer_to_execute = 0; -- Prioritize merge
3. Force merge for specific time range
OPTIMIZE TABLE trades FINAL;
4. Python script để monitor và tự động merge
import clickhouse_connect
from datetime import datetime
import schedule
import time
def check_and_optimize_parts():
client = clickhouse_connect.get_client(
host=CLICKHOUSE_HOST,
port=CLICKHOUSE_PORT,
username=CLICKHOUSE_USER,
password=CLICKHOUSE_PASSWORD
)
# Get parts count
result = client.query("""
SELECT table, count() as parts
FROM system.parts
WHERE database = 'market_data' AND active = 1
GROUP BY table
HAVING parts > 100
""")
for row in result.result_rows:
table_name = row[0]
parts_count = row[1]
if parts_count > 100:
print(f"Optimizing {table_name} with {parts_count} parts...")
# Schedule merge for off-peak hours
client.command(f"OPTIMIZE TABLE market_data.{table_name}")
# Check for old partitions (> 2 months) - potential archive
result = client.query("""
SELECT table, min(partition) as oldest_partition
FROM system.parts
WHERE database = 'market_data'
AND active = 1
AND modification_time < now() - INTERVAL 7 DAY
GROUP BY table
""")
for row in result.result_rows:
print(f"Consider archiving {row[0]} partition {row[1]}")
Run every 30 minutes
schedule.every(30).minutes.do(check_and_optimize_parts)
if __name__ == "__main__":
print("Starting parts optimizer...")
while True:
schedule.run_pending()
time.sleep(60)
3. ClickHouse: Out of Memory Khi Query Order Book
# ❌ LỖI: Query order book full snapshot gây OOM
Error: "Memory limit (for query) exceeded"
✅ KHẮC PHỤC: Sử dụng sampling và streaming
1. Query với LIMIT và WHERE tối ưu
-- Thay vì query toàn bộ:
-- SELECT * FROM orderbook_levels WHERE symbol = 'BTCUSDT';
-- ✅ Dùng precise WHERE với timestamp range:
SELECT side, price, quantity, level
FROM orderbook_levels
WHERE exchange = 'binance'
AND symbol = 'BTCUSDT'
AND timestamp BETWEEN
toDateTime64('2026-04-03 12:00:00.000', 3) AND
toDateTime64('2026-04-03 12:00:05.000', 3)
AND level <= 20
ORDER BY side, level
LIMIT 40; -- 20 bids + 20 asks
2. Python: Stream processing thay vì load all vào memory
def stream_orderbook_with_processing(
client,
exchange: str,
symbol: str,
start: datetime,
end: datetime,
batch_size: int = 10000,
process_func=None
):
"""
Stream orderbook data với batch processing
Memory efficient: chỉ load batch_size records tại một thời điểm
"""
query = f"""
SELECT side, price, quantity, level, timestamp
FROM orderbook_levels
WHERE exchange = '{exchange}'
AND symbol = '{symbol}'
AND timestamp BETWEEN
toDateTime64('{start.isoformat()}', 3) AND
toDateTime64('{end.isoformat()}', 3)
ORDER BY timestamp
LIMIT 1000000000 -- No limit, but use chunked reading
"""
result = client.query(query)
batch = []
total_processed = 0
for row in result.results:
batch.append({
'side': row[0],
'price': float(row[1]),
'quantity': float(row[2]),
'level': row[3],
'timestamp': row[4]
})
if len(batch) >= batch_size:
if process_func:
process_func(batch)
total_processed += len(batch)
batch = [] # Release memory
print(f"Processed {total_processed} records...")
# Process remaining
if batch and process_func:
process_func(batch)
total_processed += len(batch)
return total_processed
3. Tăng memory limit cho specific queries khi cần
Chỉ dùng cho ad-hoc analysis, không production
ALTER TABLE trades MODIFY SETTING
max_memory_usage_for_user = 10000000000; -- 10GB limit
Hoặc set per-query:
-- SELECT * FROM trades SETTINGS max_memory_usage = 5000000000;
Phù Hợp / Không Phù Hợp Với Ai
| Tiêu Chí | Nên Chọn Tardis | Nên Chọn Self-Hosted ClickHouse |
|---|---|---|
| Team size | 1-5 developers, không có DBA | 5+ engineers, có DevOps/DBA |
| Budget | Có budget $300-1000/tháng cố định | Muốn linh hoạt chi phí, scale được |
| Data volume | < 500GB/tháng | > 500GB, có thể lên petabyte |
| Customization | Cần support multi-exchange ngay | Cần customize compression, schema |
| Latency requirement | Chấp nhận 2-3s cho replay queries | Cần < 200ms cho production queries |
| Compliance | Cần data residency không cố định | Cần data ở region cụ thể (GDPR) |
Giá Và ROI
| Phương Án | Tardis Basic | Tardis Pro | ClickHouse Self-Hosted |
|---|---|---|---|
| Monthly cost | $299 | $999 | $380-980* |
| Annual cost | $3,588 | $11,988 | $4,560-11,760* |
| Setup time | 1 ngày | 1 ngày | 2-4 tuần |
| Ongoing ops/month | 2 giờ | 2 giờ | 16-40 giờ |
| Opportunity cost | Low | Low | High (dev time) |
| Break-even point | N/A (managed) | N/A | 6-12 tháng** |
* Chi phí infrastructure AWS/GCP cho 3-node ClickHouse cluster
** So với Tardis Basic khi tính engineering time