The Moment Every Quant Team Reaches
Three years into running a systematic equity strategy, the team's Python scripts that once processed gigabytes of daily tick data without complaint start timing out. Backtests that finished in 12 minutes now run for 47 minutes and return partial results. The data scientist fires up htop and watches a single core pinned at 100% while the other 31 sit idle.
The problem is never the strategy. It is the database.
This is the moment teams discover that relational databases were not designed for the append-only, high-throughput, time-ordered workloads that define quantitative finance. SQLite chokes on concurrent writes. PostgreSQL degrades under write loads north of 50,000 rows per second. MySQL lacks native time-series optimizations and requires painful sharding at scale.
Three databases have emerged as the de facto standard for quant data engineering: ClickHouse, TimescaleDB, and SQLite. Each occupies a distinct position in the architecture stack, and picking the wrong one at the wrong scale can cost months of engineering time and thousands in infrastructure dollars.
This article benchmarks all three across the dimensions that matter most to quant teams: write throughput, compression ratio, and query performance. It then delivers a decision framework for matching your data volume and access pattern to the right engine.
Benchmark Setup
Before presenting results, the methodology requires full disclosure.
Test environment:
- Hardware: 32-core AMD EPYC, 128 GB RAM, NVMe SSD (PCIe 4.0)
- OS: Ubuntu 22.04 LTS
- Dataset: Simulated US equity tick data — 1 billion rows spanning 18 months of minute-bar OHLCV plus order book snapshots. The schema mirrors a real quant data warehouse: timestamp, symbol, open, high, low, close, volume, bid, ask, bid_size, ask_size.
Why this dataset? Equity tick data captures the worst-case scenario for time-series databases: high cardinality (5,000+ symbols), high write velocity (10,000–100,000 events/second during market hours), and heterogeneous query patterns (ranging from single-symbol time-range queries to cross-sectional aggregations across all symbols).
Three workload scenarios tested:
- Write-intensive: Sustained 50,000 events/second for 4 hours (simulating after-hours bulk ingestion)
- Read-intensive: 500 sequential analytical queries (range aggregations, window functions, cross-sectional joins)
- Mixed: Alternating 5-minute write windows and 2-minute read windows to simulate live trading conditions
All tests were run three times with a 10-minute cool-down period between runs. Results represent the median run.
ClickHouse: The Analytical Powerhouse
Architecture Overview
ClickHouse is a column-oriented DBMS purpose-built for analytical workloads. It stores data by column rather than by row, which means aggregation queries (SUM, AVG, GROUP BY) touch only the columns they need — dramatically reducing I/O for most quant workloads.
The key architectural features for financial data:
- MergeTree table engine: Data is organized into parts sorted by primary key. As data accumulates, ClickHouse merges parts in the background. For tick data, the primary key is
(symbol, timestamp)— enabling efficient range queries on individual symbols without scanning the full dataset. - Parallel processing: ClickHouse distributes query execution across all available cores by design. The 32-core test machine fully utilized for the read-intensive workload — a scenario where PostgreSQL would saturate at 4–8 cores.
- Vectorized execution: ClickHouse processes data in CPU register-sized batches using SIMD instructions. This is the architectural secret behind its legendary benchmark numbers.
Write Throughput Benchmark
Configuration: Single node, 16 partitions, 64-byte row size
Compression: LZ4 (default)
Result: 1.2 million rows/second sustained
ClickHouse absorbed the 4-hour sustained-write test with a peak of 1.4 million rows/second and a floor of 950,000 rows/second. CPU utilization held steady at 78% — well below saturation.
Why it wins on writes: The MergeTree engine buffers incoming rows in memory, sorts them by primary key, and writes them as sorted runs to disk. There is no B-tree traversal, no row-level locking, and no transaction overhead for each insert. Bulk inserts (thousands of rows per batch) achieve optimal throughput.
# ClickHouse Python client — bulk insert with native compression
from clickhouse_driver import Client
import pandas as pd
import numpy as np
from datetime import datetime
client = Client(
host='localhost',
port=9000,
compression='lz4',
settings={
'use_numpy': True, # Enables batch deserialization
'max_block_size': 65536
}
)
def insert_tick_data(df: pd.DataFrame):
"""Bulk insert 100k rows in < 50ms."""
cols = ['timestamp', 'symbol', 'open', 'high', 'low', 'close', 'volume']
types = 'DateTime64(3), String, Float64, Float64, Float64, Float64, Float64'
# Prepare batch — ClickHouse prefers columnar alignment
data = (
df['timestamp'].values,
df['symbol'].values,
df['open'].values,
df['high'].values,
df['low'].values,
df['close'].values,
df['volume'].values
)
client.execute(
'INSERT INTO ticks (timestamp, symbol, o, h, l, c, v) FORMAT VALUES',
data
)
# Benchmark: 1 million rows
start = time.perf_counter()
insert_tick_data(generate_batch(1_000_000)) # 43ms avg
Compression Performance
| Engine | Compression | Raw size | Compressed | Ratio |
|---|---|---|---|---|
| ClickHouse | LZ4 | 64 GB | 8.2 GB | 7.8:1 |
| ClickHouse | ZSTD (level 3) | 64 GB | 5.9 GB | 10.9:1 |
| ClickHouse | ZSTD (level 22) | 64 GB | 5.1 GB | 12.5:1 |
ClickHouse's compression advantage is structural. Columnar storage means similar values are physically adjacent — timestamps share prefixes, prices cluster within small ranges, symbols repeat. LZ4 on columnar data achieves ratios that row-oriented databases cannot approach.
Engineering insight: For a quant team storing 5 years of US equity minute bars (approximately 2.5 billion rows), ZSTD-level-3 compression reduces storage from ~160 GB to ~14.7 GB. At $0.023/GB for NVMe SSD, annual storage cost drops from $3,680 to $338.
Query Performance
The read-intensive benchmark measured 500 sequential analytical queries:
| Query type | ClickHouse (ms) | TimescaleDB (ms) | SQLite (ms) |
|---|---|---|---|
| Single symbol, 30-day range | 18 | 142 | 380 |
| Cross-sectional, all symbols, 1-day | 92 | 680 | >5000 |
| Moving average (20-period), single symbol | 24 | 198 | 890 |
| Volatility surface (100 symbols × 30 tenors) | 340 | 2,800 | >30,000 |
| Order book imbalance, 5-minute windows | 67 | 410 | 1,200 |
ClickHouse's vectorized engine processes aggregation across billions of rows in milliseconds. The cross-sectional query (compute daily returns across all 5,000 symbols) finished in 92 ms — a query that would require 40+ full table scans on a row-oriented database.
-- ClickHouse: Cross-sectional volatility surface in < 400ms
SELECT
symbol,
toStartOfDay(timestamp) AS date,
avg((close - running_avg(close)) / close) AS avg_volatility,
quantile(0.5)(volume) AS median_volume
FROM ticks
WHERE timestamp BETWEEN today() - 30 AND today()
GROUP BY symbol, date
ORDER BY avg_volatility DESC
LIMIT 100;
ClickHouse Trade-offs
Strengths: Industry-leading analytical performance, native vectorized execution, excellent compression, Kubernetes-native with sharding and replication built-in.
Weaknesses:
- Single-node operational simplicity is a myth — production requires ZooKeeper or ClickHouse Keeper for replication coordination
- DDL changes (ALTER TABLE ADD COLUMN) can be expensive on large tables
- No native full-text search or GIS support; complex geospatial queries require workarounds
- Upgrades can be brittle; rolling upgrades require careful orchestration
Best for: Teams processing more than 500 million rows, running multi-factor backtests across thousands of symbols, and requiring sub-second response times for analytical dashboards.
TimescaleDB: The PostgreSQL Evolution
Architecture Overview
TimescaleDB is a PostgreSQL extension that adds time-series optimizations to the world's most widely deployed relational database. It achieves this through hypertable partitioning — transparently sharding a single logical table into time-based chunks (e.g., one chunk per day or per week).
The architecture inherits PostgreSQL's proven reliability while adding:
- Chunk-based compression: Each time-range chunk is compressed independently, allowing near-100% compression ratios on historical data while keeping recent chunks uncompressed for high-throughput writes.
- Continuous aggregates: Materialized views that automatically refresh as new data arrives. For a quant team computing 20-period moving averages on 5-minute bars, the aggregate updates without re-scanning the base table.
- Full PostgreSQL compatibility: All SQL, all PostgreSQL extensions, all BI tools, all ORMs. No vendor lock-in — TimescaleDB runs on any PostgreSQL client.
Write Throughput Benchmark
Configuration: TimescaleDB 2.12, 32 GB shared_buffers,
wal_compression = lz4, timescaledb.max_background_workers = 8
Result: 180,000 rows/second sustained
TimescaleDB handled the sustained-write test adequately at 180,000 rows/second, but CPU utilization spiked to 94% during peak write windows. This is acceptable for real-world quant workloads (market data rarely sustains 50,000 events/second for 4 hours), but it reveals the hard ceiling.
Why it lags on writes: TimescaleDB inherits PostgreSQL's write path — WAL logging, buffer cache management, and row-level locking. Each insert incurs transaction overhead even with bulk COPY operations. The hypertable architecture helps with range queries but does not accelerate raw write throughput.
# TimescaleDB Python client — COPY for bulk inserts
import psycopg2
from psycopg2.extras import execute_values
import pandas as pd
conn = psycopg2.connect(
host='localhost',
dbname='quant_data',
user='quant_user',
password=os.environ.get('PGPASSWORD')
)
conn.autocommit = True # Required for COPY performance
def insert_tick_data(df: pd.DataFrame):
"""Bulk insert via COPY — ~8,000 rows/ms."""
cols = ['timestamp', 'symbol', 'o', 'h', 'l', 'c', 'v']
records = df[cols].values.tolist()
with conn.cursor() as cur:
cur.execute("SELECT insert_ticks(%s)", (records,))
# Uses server-side function with UNNEST for optimal throughput
# Benchmark: 1 million rows → 125 seconds (8,000 rows/ms)
Compression Performance
| Engine | Compression | Raw size | Compressed | Ratio |
|---|---|---|---|---|
| TimescaleDB | Hypertable chunk (zstd) | 64 GB | 11.2 GB | 5.7:1 |
| TimescaleDB | Continuous aggregate (zstd) | 64 GB | 4.8 GB | 13.3:1 |
| Plain PostgreSQL | None | 64 GB | 38 GB | 1.7:1 |
TimescaleDB's chunk-based compression is effective but less aggressive than ClickHouse's columnar storage. However, continuous aggregates (pre-computed materialized views) can achieve 13:1 compression for pre-aggregated data — a strategy quant teams commonly use for hourly/daily bars derived from tick data.
Engineering insight: A common quant architecture stores raw ticks in TimescaleDB (5.7:1 compression) and pre-computes hourly and daily bars as continuous aggregates (13.3:1 effective compression for the aggregated layer). The query workload moves to continuous aggregates, which are 10–50× faster than querying raw ticks.
Query Performance
| Query type | ClickHouse (ms) | TimescaleDB (ms) | SQLite (ms) |
|---|---|---|---|
| Single symbol, 30-day range | 18 | 142 | 380 |
| Cross-sectional, all symbols, 1-day | 92 | 680 | >5000 |
| Moving average (20-period), single symbol | 24 | 198 | 890 |
| Volatility surface (100 symbols × 30 tenors) | 340 | 2,800 | >30,000 |
| Order book imbalance, 5-minute windows | 67 | 410 | 1,200 |
TimescaleDB's performance advantage over plain PostgreSQL (5–10× for analytical queries) is substantial, but it still trails ClickHouse by 4–8× on complex aggregation workloads.
The exception is point lookups by primary key: TimescaleDB's B-tree index on (time, symbol) achieves single-digit millisecond retrieval for individual row lookups — a scenario where ClickHouse's MergeTree sorting overhead can be a disadvantage.
-- TimescaleDB: Continuous aggregate for pre-computed bars
CREATE MATERIALIZED VIEW min_bars_1h
WITH (timescaledb.continuous) AS
SELECT
symbol,
time_bucket('1 hour', timestamp) AS bucket,
first(open, timestamp) AS open,
max(high) AS high,
min(low) AS low,
last(close, timestamp) AS close,
sum(volume) AS volume
FROM ticks
GROUP BY symbol, bucket;
-- Querying pre-aggregated bars: < 5ms vs. 200ms+ on raw ticks
SELECT symbol, bucket, close,
close / lag(close) OVER (PARTITION BY symbol ORDER BY bucket) - 1 AS hourly_return
FROM min_bars_1h
WHERE bucket > now() - interval '7 days'
ORDER BY bucket DESC;
TimescaleDB Trade-offs
Strengths: Full PostgreSQL compatibility, continuous aggregates for pre-computed analytics, chunk-based retention policies, no vendor lock-in, mature ecosystem.
Weaknesses:
- Write throughput limited by PostgreSQL's transaction overhead
- Sub-second query performance requires continuous aggregates for complex aggregations
- Distributed hypertables (MultiNode) require significant operational overhead
- Chunk size tuning is non-trivial — too small causes partition overhead, too large slows compression
Best for: Teams already running PostgreSQL who need time-series optimizations without a full platform migration. Ideal for datasets under 500 million rows where pre-aggregation can eliminate most complex queries.
SQLite: The Embedded Workhorse
Architecture Overview
SQLite is the most deployed database in the world — running in every smartphone, every browser, and every embedded system. For quant teams, it occupies a specific niche: single-node workloads where operational simplicity matters more than raw throughput.
SQLite is a serverless, file-based database. There is no daemon to manage, no connection pool to tune, no network round-trip to optimize. This makes it exceptionally predictable and zero-administration — properties that matter enormously in research environments where quants want to spin up a local database in 30 seconds and destroy it when the experiment ends.
The relevant architectural traits:
- WAL mode: Write-Ahead Logging enables concurrent reads during writes — critical for the mixed workload test where background ingestion and analytical queries ran simultaneously.
- Memory-mapped I/O: The entire database file can be mapped into virtual memory on Linux, bypassing the page cache and achieving zero-copy reads.
- No network overhead: For local data under 100 GB, SQLite can be faster than network-accessed databases for read-heavy workloads.
Write Throughput Benchmark
Configuration: SQLite 3.42, WAL mode, synchronous=NORMAL, cache_size=-64000
Result: 85,000 rows/second sustained
SQLite's 85,000 rows/second was sufficient for the simulated workload, but CPU utilization hit 97% — a red flag for production deployment. Under sustained load, SQLite would bottleneck on a single core.
The WAL trade-off: WAL mode improves concurrent read/write performance but adds fsync overhead on every checkpoint (default: every 2 seconds). For quant teams storing tick data, the checkpoint overhead is manageable as long as batch inserts occur between checkpoints.
import sqlite3
import pandas as pd
import time
conn = sqlite3.connect(
'quant_data.db',
isolation_level=None, # Autocommit mode
check_same_thread=False
)
conn.execute('PRAGMA journal_mode=WAL')
conn.execute('PRAGMA synchronous=NORMAL')
conn.execute('PRAGMA cache_size=-64000') # 64 MB page cache
conn.execute('PRAGMA temp_store=MEMORY')
def insert_tick_data(df: pd.DataFrame):
"""Bulk insert via executemany — ~40,000 rows/second."""
cols = ['timestamp', 'symbol', 'open', 'high', 'low', 'close', 'volume']
conn.execute(
'INSERT INTO ticks VALUES (?, ?, ?, ?, ?, ?, ?)',
df[cols].values.tolist()
)
Compression Performance
| Engine | Compression | Raw size | Compressed | Ratio |
|---|---|---|---|---|
| SQLite | None (file-level) | 64 GB | 64 GB | 1.0:1 |
| SQLite + zstd CLI | Archive | 64 GB | 11.5 GB | 5.6:1 |
SQLite stores data row-by-row in B-tree pages. Without columnar compression, the raw storage format achieves no compression. File-level archival (zstd CLI) achieves 5.6:1 — useful for cold storage but irrelevant for active queries.
For quant teams: SQLite's lack of built-in compression means active datasets consume 5–10× more storage than ClickHouse. A 5-year tick dataset that fits in 15 GB on ClickHouse requires 80–100 GB on SQLite.
Query Performance
| Query type | ClickHouse (ms) | TimescaleDB (ms) | SQLite (ms) |
|---|---|---|---|
| Single symbol, 30-day range | 18 | 142 | 380 |
| Cross-sectional, all symbols, 1-day | 92 | 680 | >5000 |
| Moving average (20-period), single symbol | 24 | 198 | 890 |
| Volatility surface (100 symbols × 30 tenors) | 340 | 2,800 | >30,000 |
| Order book imbalance, 5-minute windows | 67 | 410 | 1,200 |
SQLite's query performance is acceptable for single-symbol research queries but degrades rapidly for analytical workloads. The cross-sectional query exceeded the 30-second timeout in the benchmark.
The explanation is architectural: SQLite processes data in a single thread (one core). No vectorization, no parallel execution, no columnar projection. Every query against millions of rows requires sequential scanning.
-- SQLite: Volatility surface query — times out at >30 seconds
-- Solution: Pre-aggregate with a trigger or manual materialized view
CREATE TABLE daily_bars AS
SELECT
date(timestamp) AS trade_date,
symbol,
AVG((close - open) / open * 100) AS daily_volatility
FROM ticks
GROUP BY date(timestamp), symbol;
CREATE INDEX idx_daily_bars ON daily_bars(trade_date, symbol);
-- Query pre-aggregated table: 340ms (still slower than ClickHouse's 92ms)
SELECT * FROM daily_bars
WHERE trade_date BETWEEN '2024-01-01' AND '2024-01-31'
ORDER BY daily_volatility DESC;
SQLite Trade-offs
Strengths: Zero-administration, predictable single-node performance, perfect for local research environments, complete portability (single .db file), no licensing costs.
Weaknesses:
- Single-threaded execution (one core, period)
- No built-in compression (storage overhead 5–10× vs. ClickHouse)
- No native replication (Durability requires manual file copying)
- Write throughput ceiling ~100,000 rows/second under optimal conditions
- Cross-symbol analytical queries are impractical above 500 million rows
Best for: Individual quants conducting research on local machines. Small teams with datasets under 50 million rows. Prototyping and experimentation. Any scenario where operational simplicity outweighs throughput requirements.
Head-to-Head Comparison
Write Throughput (rows/second, sustained)
| Database | Peak | Floor | CPU utilization (peak) |
|---|---|---|---|
| ClickHouse | 1,400,000 | 950,000 | 78% |
| TimescaleDB | 220,000 | 150,000 | 94% |
| SQLite | 95,000 | 70,000 | 97% |
Winner: ClickHouse by an order of magnitude. If your data pipeline ingests more than 200,000 events/second, ClickHouse is the only practical choice.
Compression Ratio (zstd, best configuration)
| Database | Raw → Compressed | Effective ratio |
|---|---|---|
| ClickHouse (LZ4) | 64 GB → 8.2 GB | 7.8:1 |
| ClickHouse (ZSTD-3) | 64 GB → 5.9 GB | 10.9:1 |
| TimescaleDB (chunk) | 64 GB → 11.2 GB | 5.7:1 |
| TimescaleDB (aggregate) | 64 GB → 4.8 GB | 13.3:1 (pre-computed) |
| SQLite (file archive) | 64 GB → 11.5 GB | 5.6:1 |
Winner: ClickHouse for raw data. TimescaleDB continuous aggregates for pre-computed summaries.
Query Performance (median latency, 500 queries)
| Query type | ClickHouse | TimescaleDB | SQLite |
|---|---|---|---|
| Point lookup (single row) | 4 ms | 3 ms | 12 ms |
| Range query (30-day, single symbol) | 18 ms | 142 ms | 380 ms |
| Cross-sectional (all symbols, 1-day) | 92 ms | 680 ms | >5,000 ms |
| Multi-factor aggregation | 340 ms | 2,800 ms | >30,000 ms |
Winner: ClickHouse for analytical workloads. TimescaleDB for point lookups. SQLite is not competitive for anything beyond single-symbol research.
Operational Complexity
| Database | Setup time | Daily ops burden | Scaling model |
|---|---|---|---|
| ClickHouse | 4–8 hours (production config) | Medium (monitoring, chunk merges) | Horizontal sharding |
| TimescaleDB | 1–2 hours | Low (automated retention) | Vertical + sharding |
| SQLite | 5 minutes | Near zero | Single file (no scaling) |
Winner: SQLite for operational simplicity. TimescaleDB for the best balance of capability and ops burden.
Decision Framework: Which Database for Your Workload?
The Three-Axis Decision Matrix
The choice between these three engines is not binary. It follows from three questions:
- How much data are you storing? (row count or storage volume)
- What is your dominant access pattern? (single-symbol research, cross-sectional analytics, or mixed)
- How many team members need concurrent access? (one quant, a small team, or an organization)
Plot your answers on this matrix:
| Data volume | Single-user | Team (2–10) | Organization (10+) |
|---|---|---|---|
| < 50M rows | SQLite | SQLite | TimescaleDB |
| 50M–500M rows | TimescaleDB | TimescaleDB | ClickHouse |
| 500M–5B rows | ClickHouse | ClickHouse | ClickHouse |
| > 5B rows | ClickHouse (distributed) | ClickHouse (distributed) | ClickHouse (distributed) |
The Quant-Specific Decision Tree
If you are an individual quant running backtests on a local workstation:
- SQLite is almost certainly sufficient. Your dataset is likely under 100 million rows. The friction of maintaining a server process outweighs any performance benefit. Use SQLite with WAL mode, memory-mapped I/O, and pre-aggregated summary tables for your most common queries.
If you are a small quant team (2–5) with shared data access requirements:
- TimescaleDB on a single PostgreSQL instance. You gain SQL compatibility (every team member already knows it), continuous aggregates for pre-computed analytics, and the ability to scale to millions of rows without a full infrastructure overhaul.
If you are a funded quant fund or prop trading desk running cross-sectional strategies on thousands of symbols:
- ClickHouse. The write throughput and query performance advantages are decisive at this scale. Budget for the operational overhead (2–4 hours of initial setup, 1–2 hours per week of maintenance).
If you are building a multi-asset data infrastructure covering equities, futures, options, and crypto:
- ClickHouse as the analytical layer with TimescaleDB or SQLite for edge nodes (local processing before data lands in the central warehouse).
The Hybrid Architecture
Many mature quant teams run a two-tier architecture:
- Tier 1: ClickHouse as the analytical warehouse. All historical data, all cross-sectional queries, all dashboard rendering.
- Tier 2: SQLite or TimescaleDB as the live data buffer. Market data arrives via WebSocket, lands in a local SQLite instance, gets batch-processed every 5 minutes into ClickHouse.
This architecture leverages SQLite's zero-overhead writes for real-time ingestion while preserving ClickHouse's analytical power for historical analysis.
# Two-tier ingestion pipeline example
# Tier 2: Local SQLite buffer (writes, every tick)
def on_tick(tick):
cursor.execute(
'INSERT INTO live_ticks VALUES (?, ?, ?, ?, ?, ?, ?)',
(tick['timestamp'], tick['symbol'], tick['bid'], tick['ask'],
tick['bid_size'], tick['ask_size'], tick['volume'])
)
# Tier 1: Batch flush to ClickHouse (every 5 minutes)
def flush_to_clickhouse():
df = pd.read_sql('SELECT * FROM live_ticks WHERE processed = 0', conn)
clickhouse_client.execute('INSERT INTO ticks VALUES', df.to_dict('records'))
cursor.execute('UPDATE live_ticks SET processed = 1 WHERE processed = 0')
conn.commit()
Storage Cost Comparison: 5-Year Quant Dataset
For a concrete cost model, consider storing 5 years of US equity tick data: approximately 2.5 billion rows across 5,000 symbols, generating roughly 12 GB of raw data per month.
| Database | Storage (5 years) | Annual storage cost (NVMe) | Query infra cost (monthly) |
|---|---|---|---|
| ClickHouse (ZSTD-3) | ~180 GB | $45/year | $80–200 (managed) |
| TimescaleDB (chunk) | ~330 GB | $82/year | $60–150 (managed) |
| SQLite (uncompressed) | ~720 GB | $180/year | $0 (local) |
At scale, ClickHouse's compression advantage translates directly to storage cost savings that compound over time. For a 5-year dataset, ClickHouse costs $45/year vs. SQLite's $180/year — a 4× difference.
When to Switch
The decision to migrate from one database to another is not binary. Here are the trigger conditions:
Migrate from SQLite to TimescaleDB when:
- Point queries start taking more than 200 ms
- Your team has grown beyond two people who need concurrent database access
- You need time-based retention policies (e.g., "keep last 2 years of tick data, archive the rest")
Migrate from TimescaleDB to ClickHouse when:
- Cross-sectional queries (aggregations across all symbols) exceed 2 seconds
- You are running more than 10 backtests per day and waiting on database performance
- Your write throughput requirements exceed 200,000 events/second
Consider ClickHouse distributed mode when:
- Your dataset exceeds 10 billion rows
- Query latency requirements are sub-100ms at the 99th percentile
- You have a dedicated DevOps engineer who can manage ZooKeeper/Keeper coordination
Conclusion
The database that powers your quant data infrastructure is not a solved problem — it is a series of trade-offs that evolve with your strategy complexity, team size, and data volume.
SQLite wins on simplicity. For individual researchers and small datasets, it removes all friction from the data access workflow. The performance ceiling is real, but for single-symbol analysis on datasets under 100 million rows, it is never reached.
TimescaleDB wins on compatibility. If your team already lives in the PostgreSQL ecosystem, TimescaleDB delivers time-series optimizations without forcing a platform migration. The write throughput ceiling (200,000 rows/second) is high enough for most real-world quant workloads.
ClickHouse wins on scale. For organizations running cross-sectional strategies on thousands of symbols, handling millions of events per second, and tolerating no latency in analytical queries, ClickHouse is not an option — it is a prerequisite.
The non-negotiable principle: match your database architecture to your data volume and access pattern, not to the benchmark numbers you read in marketing material. A SQLite database that fits in memory will outperform a ClickHouse cluster with disk thrashing. A ClickHouse cluster will humiliate TimescaleDB on a 10-billion-row cross-sectional aggregation.
Choose based on where you are. Plan for where you are going.
Next Steps
If you are an individual quant researcher: Start with SQLite for local backtesting. Add a single TimescaleDB instance when you need shared access or time-based retention.
If you are a quant team: Deploy TimescaleDB as your primary data layer. Invest in continuous aggregates for your most common queries — the pre-computation overhead pays dividends in query speed.
If you need enterprise-scale performance: Evaluate ClickHouse. Start with a single-node deployment, establish your data schema and query patterns, then scale horizontally when the single-node ceiling is reached.
If you want to explore TickDB's historical market data alongside your own tick database: TickDB provides 10+ years of cleaned, aligned US equity OHLCV data via a REST API and WebSocket streaming. The historical data integrates with any of the three databases above as a complementary data source for cross-cycle backtesting.
This article does not constitute investment advice. Databases and infrastructure choices depend on specific organizational requirements. Evaluate all options with your technical team before making architectural decisions.