The moment you try to query a decade of 1-minute OHLCV data for a single stock symbol — roughly 3.5 million rows — against a naive MySQL installation, you will watch a spinner for 90 seconds or longer. That delay is not inevitable. It is an architectural choice.
TickDB stores 10 or more years of cleaned, aligned US equity OHLCV data across hundreds of symbols and serves queries at sub-second latency. Achieving that consistently, under load, with real-time ingestion running in parallel — that is a systems engineering problem with no clean textbook answer. This article walks through how TickDB's storage layer is actually designed: the data model, the hot-cold separation strategy, the indexing approach, and the query execution techniques that make sub-second responses the norm rather than the exception.
1. The Fundamental Tension: Write Volume vs. Query Breadth
Before diving into architecture, it helps to understand the scale of the problem TickDB solves.
TickDB's historical K-line store holds data across six asset classes — US equities, Hong Kong equities, A-shares, crypto, forex, and commodities. For US equities alone, 10 years of 1-minute bars across approximately 5,000 symbols represents on the order of 78 billion data points before aggregation. Row-level storage at this scale, even compressed, introduces latency at two layers:
- Write throughput: Every tick processed in real-time must be incorporated into the historical record without blocking the streaming path.
- Query latency: Analysts and algorithms requesting multi-year OHLCV data for backtesting or visualization expect responses in seconds, not minutes.
The naive solution — a single Postgres or MySQL table with a composite primary key — falls apart at roughly 50 million rows on commodity hardware. Queries that touch broad time ranges span millions of index pages. The storage engine bogs down. This is the problem that drives every architectural decision in TickDB's storage layer.
2. Data Model: K-Line Storage as a Time-Series Problem
TickDB models K-line data as a time-series workload, not a general relational workload. That distinction is architecturally significant.
2.1 Why Time-Series Modeling Matters
Relational databases are optimized for arbitrary joins, updates, and point queries. K-line data — open, high, low, close, volume for a time interval — is append-only and queried almost exclusively by time range and symbol. This access pattern makes column-oriented storage and time-partitioned layouts dramatically more efficient than row-oriented alternatives.
TickDB's internal data model for OHLCV bars follows this structure:
from dataclasses import dataclass
from typing import Optional
import time
@dataclass
class Kline:
symbol: str # e.g., "AAPL.US"
interval: str # e.g., "1m", "1h", "1d"
open_time: int # Unix timestamp (seconds) of bar open
close_time: int # Unix timestamp of bar close
open_price: float
high_price: float
low_price: float
close_price: float
volume: float
quote_volume: float # total notional in quote currency
trades: int # number of individual trades in this bar
is_final: bool # whether this bar is closed (confirmed)
def to_row(self) -> dict:
"""Serialize for bulk insert into the storage layer."""
return {
"symbol": self.symbol,
"interval": self.interval,
"open_time": self.open_time,
"close_time": self.close_time,
"open": self.open_price,
"high": self.high_price,
"low": self.low_price,
"close": self.close_price,
"volume": self.volume,
"quote_volume": self.quote_volume,
"trades": self.trades,
"is_final": self.is_final,
}
This model reflects three design choices:
- Symbol + interval + open_time as a natural composite key — Every bar is uniquely identified without a separate auto-increment ID. The key is information-bearing: it tells you what, when, and at what resolution.
is_finalflag — Critical for streaming integration. A bar can be updated in real-time as new ticks arrive (the current incomplete bar), then sealed asis_final=Truewhen the interval closes. The storage layer must handle both states correctly.quote_volumeinstead of justvolume— Enables notional-weighted queries and cross-symbol normalization without application-layer conversion.
2.2 The Interval Hierarchy
TickDB maintains data at multiple temporal resolutions simultaneously. A single symbol might be queryable at 1-minute, 5-minute, 15-minute, 1-hour, 4-hour, 1-day, 1-week, and 1-month intervals. Each interval is stored as a separate logical time series. This is not materialized views in the SQL sense — it is a deliberate storage topology where higher intervals are derived from lower ones at ingestion time, not at query time.
The practical benefit: query latency for a 5-year daily chart does not require scanning 5 years of minute bars and aggregating on the fly. The daily bars already exist in stored form.
3. Tiered Storage: Hot, Warm, and Cold Layers
The most consequential architectural decision in TickDB's storage layer is tiered storage — separating data by access frequency and organizing physical placement accordingly.
3.1 The Three-Tier Model
| Layer | Data characteristics | Access pattern | Storage medium | Typical retention |
|---|---|---|---|---|
| Hot | Current month; real-time bars still being written | Extremely high-frequency reads and writes; streaming ingestion | NVMe SSD; in-memory write buffer (WAL) | Rolling 30 days |
| Warm | 1–12 months old; historical but actively queried | Moderate read frequency; backtesting, strategy research | SSD (SATA or NVMe) | 12 months |
| Cold | 12+ months old; archival reference | Low-frequency reads; long-horizon backtests, compliance queries | Object storage (S3-compatible) + metadata index | 10+ years |
The tiering is not a feature flag. It is enforced at the partition level. Each partition covers a fixed time window (e.g., one month of 1-minute data) and carries a tier designation in its metadata. The query planner routes requests to the appropriate tier based on the requested time range.
3.2 Hot Layer: NVMe with Write-Ahead Logging
The hot layer must handle two simultaneous workloads:
- Real-time ingestion: New ticks arrive continuously and update the current, unsealed bar.
- Live queries: Dashboard clients and streaming consumers request the latest completed bars.
TickDB uses a write-ahead log (WAL) in memory for the current bar, flushed to NVMe SSD in batched writes every 1–5 seconds. This decouples ingestion throughput from storage write IOPS. The bar remains mutable in the WAL until close_time passes, at which point it is sealed and flushed as an immutable row.
NVMe drives are non-negotiable for the hot layer. At TickDB's ingestion rates, SATA SSDs hit IOPS ceilings that cause write queuing under burst conditions. NVMe provides the headroom needed to absorb 10x burst traffic — such as a market open or an earnings event — without degrading the real-time streaming path.
3.3 Warm Layer: Columnar Compression
The warm layer stores completed bars as columnar files (Parquet format). Columnar storage delivers two advantages for this access pattern:
- Column projection: A query requesting only
close_priceandvolumereads only those columns, skipping high, low, open, and timestamp data entirely. For wide time ranges, this reduces data read by 60–80%. - Dictionary encoding: Symbol names and interval strings are dictionary-encoded, converting string comparisons into integer comparisons at the storage engine level.
Each warm partition is further divided into symbol shards — contiguous blocks of bars for a single symbol within the partition. This prevents a query for AAPL from scanning partitions that contain only TSLA data.
3.4 Cold Layer: Object Storage with Metadata Index
The cold layer stores Parquet files in S3-compatible object storage (e.g., MinIO for on-prem deployments, AWS S3 for cloud). Object storage is dramatically cheaper per GB than block storage — critical when storing 10 years of data at scale.
The challenge with object storage alone is query latency. Scanning cold Parquet files for every historical query would reintroduce the minutes-long response times tiered storage aims to eliminate. TickDB solves this with a metadata index: a lightweight database (implemented internally as a columnar index over partition metadata) that maps time ranges and symbols to specific cold storage objects.
The query flow for a cold-range request:
- Query planner resolves the requested time range against the metadata index.
- Metadata index returns a list of cold storage object keys.
- These objects are fetched in parallel (async I/O) and decompressed.
- Results are merged with any warm-layer data and returned.
Step 3 is the most sensitive. Without optimization, a query spanning 5 years of cold data would trigger hundreds of object GET requests, each with network round-trip latency. TickDB mitigates this through coarse-grained partitioning: cold partitions are sized so that no single query needs to touch more than 20–30 objects. Parallel fetch of 30 objects at 50 ms each adds roughly 200 ms — acceptable for a cold query that would otherwise take minutes.
4. Index Architecture: What Makes Queries Fast
Storage media and tiering strategies matter only insofar as the query engine can navigate them efficiently. TickDB's index architecture is built on three mutually reinforcing structures.
4.1 Primary Index: Symbol + Interval + Open Time
The primary index is a clustered B+ tree (or equivalent ordered structure) keyed on (symbol, interval, open_time). This is the index that answers "give me all AAPL.US 1-minute bars between January 1 and March 31, 2024." Every row in this index has a physical address pointing to its storage location in the appropriate tier.
The ordering is deliberate. (symbol, interval, open_time) groups all bars for the same symbol and interval contiguously. This means range scans on a single symbol visit sequential storage pages — a massive win for hard drive and even SATA SSD throughput, which degrades badly under random access.
4.2 Secondary Index: Time-Based Partitioning
A second index layer partitions data by open_time alone, independent of symbol. This supports cross-symbol queries — "give me the last hour of all US equity 1-minute bars" — without requiring a full table scan.
The time-based partition index is materialized as a directory structure on the storage layer:
warm-storage/
us-equities/
1m/
2024-01/
AAPL.US.parquet
TSLA.US.parquet
...
2024-02/
...
hk-equities/
1m/
...
This directory layout lets the query planner prune entire subtrees when the time range is known. A query for January–February 2024 skips all March directories without reading a single file.
4.3 Bloom Filter: Existence Probing Without I/O
For cold-layer queries, TickDB attaches a Bloom filter to each Parquet file. A Bloom filter is a probabilistic data structure that answers the question "does this file contain any rows matching these filter predicates?" with no I/O — just a memory lookup.
Bloom filters eliminate unnecessary cold storage reads. If a query requests AAPL bars for March 2024 and the March 2024 AAPL parquet file's Bloom filter returns probably_yes, the file is fetched. If it returns definitely_no, the file is skipped entirely. The probability of false positives (filter says yes, but no matching rows exist) is tunable; TickDB targets a false-positive rate below 1%.
import hashlib
import bisect
class BloomFilter:
"""Lightweight Bloom filter for cold storage index probing.
Each Parquet file has one Bloom filter. This class is used at
query planning time to determine which cold objects to fetch
without performing I/O.
"""
def __init__(self, size: int, hash_count: int):
self.size = size
self.hash_count = hash_count
self.bit_array = bytearray(size // 8)
def _hashes(self, item: str) -> list[int]:
"""Generate k hash values for a given item."""
result = []
for i in range(self.hash_count):
h = hashlib.sha256(f"{item}:{i}".encode()).digest()
result.append(int.from_bytes(h[:8], "big") % (self.size * 8))
return result
def add(self, item: str) -> None:
for h in self._hashes(item):
self.bit_array[h // 8] |= 1 << (h % 8)
def might_contain(self, item: str) -> bool:
"""Return True if the item might be in the set, False if definitely not."""
for h in self._hashes(item):
if not (self.bit_array[h // 8] & (1 << (h % 8))):
return False
return True
# Usage in query planner:
# for partition_key, bloom_filter in cold_index.items():
# if bloom_filter.might_contain(f"{symbol}:{interval}:{start_ts}-{end_ts}"):
# fetch_cold_object(partition_key)
5. Query Execution: How the Layers Cooperate
Having described the storage and indexing layers, the next logical question is: how does a query actually execute?
5.1 The Query Planner
Every request passes through a query planner that performs three steps:
- Time range decomposition: The requested time range is split into hot, warm, and cold segments based on the tiering policy.
- Index resolution: The planner identifies the specific partitions, symbol shards, and (for cold) object keys relevant to the query.
- Execution plan construction: A parallel execution plan is built, specifying which tier handles which segment and how results are merged.
5.2 Parallel Tier Execution
The query planner does not wait for the hot layer to finish before starting the warm layer. It launches parallel tasks for each tier:
import asyncio
import concurrent.futures
from dataclasses import dataclass
from typing import List
import time
@dataclass
class QueryRequest:
symbol: str
interval: str
start_time: int # Unix timestamp
end_time: int
columns: List[str] # e.g., ["open", "high", "low", "close", "volume"]
@dataclass
class QueryResult:
rows: List[dict]
tier_source: str
elapsed_ms: float
async def execute_query(req: QueryRequest) -> QueryResult:
"""Execute a TickDB historical query across all storage tiers.
The query planner decomposes the request by time range,
then dispatches parallel tasks to hot, warm, and cold layers.
Results are merged and sorted before return.
⚠️ For production deployments handling >10k QPS, replace this
asyncio model with a dedicated query engine (e.g., DataFusion
or DuckDB) that understands partition pruning natively.
"""
now = int(time.time())
# Step 1: Decompose by tier based on time range
hot_end = _month_boundary(now - 30 * 86400) # 30 days ago
warm_end = _month_boundary(now - 365 * 86400) # 1 year ago
hot_range = (req.start_time, min(req.end_time, hot_end))
warm_range = (max(req.start_time, hot_end), min(req.end_time, warm_end))
cold_range = (max(req.start_time, warm_end), req.end_time)
# Step 2: Launch parallel tasks per tier
tasks = []
executor = concurrent.futures.ThreadPoolExecutor(max_workers=3)
if hot_range[0] < hot_range[1]:
tasks.append(
asyncio.get_event_loop().run_in_executor(
executor, _query_hot_layer, req.symbol, req.interval,
hot_range[0], hot_range[1], req.columns
)
)
if warm_range[0] < warm_range[1]:
tasks.append(
asyncio.get_event_loop().run_in_executor(
executor, _query_warm_layer, req.symbol, req.interval,
warm_range[0], warm_range[1], req.columns
)
)
if cold_range[0] < cold_range[1]:
tasks.append(
asyncio.get_event_loop().run_in_executor(
executor, _query_cold_layer, req.symbol, req.interval,
cold_range[0], cold_range[1], req.columns
)
)
# Step 3: Wait for all tiers, merge results
start = time.perf_counter()
tier_results = await asyncio.gather(*tasks)
elapsed_ms = (time.perf_counter() - start) * 1000
all_rows = []
for rows, tier in tier_results:
all_rows.extend(rows)
# Sort by open_time to return chronologically ordered results
all_rows.sort(key=lambda r: r["open_time"])
return QueryResult(rows=all_rows, tier_source="multi-tier", elapsed_ms=elapsed_ms)
def _month_boundary(ts: int) -> int:
"""Snap a Unix timestamp to the first second of its month."""
import datetime
dt = datetime.datetime.fromtimestamp(ts, tz=datetime.timezone.utc)
return int(dt.replace(day=1, hour=0, minute=0, second=0, microsecond=0).timestamp())
def _query_hot_layer(symbol: str, interval: str, start: int, end: int, cols: List[str]) -> tuple[List[dict], str]:
"""Query the hot storage layer (NVMe + in-memory WAL)."""
# Production: reads from NVMe partition + WAL merge
# Simplified: return empty for illustration
return ([], "hot")
def _query_warm_layer(symbol: str, interval: str, start: int, end: int, cols: List[str]) -> tuple[List[dict], str]:
"""Query the warm storage layer (Parquet on SSD).
Production: pyarrow or pandas read_parquet with row-group
predicate pushdown for early exit on time range.
"""
return ([], "warm")
def _query_cold_layer(symbol: str, interval: str, start: int, end: int, cols: List[str]) -> tuple[List[dict], str]:
"""Query the cold storage layer (S3 + metadata index).
Production: boto3 S3 Select + Bloom filter pre-screening.
"""
return ([], "cold")
The key insight in this execution model is that a query spanning 8 years does not wait for the cold layer to complete before returning. The hot and warm results — typically the most recent data — return first, and the cold results are merged in as they arrive. For dashboard use cases, where the most recent data is what the user cares about most, this provides perceived sub-second latency even when the full query has not yet resolved.
5.3 Predicate Pushdown
The query engine implements predicate pushdown: filter conditions (symbol, interval, time range) are evaluated at the storage layer, not after reading all data into memory. For Parquet files, this means only row groups whose min/max statistics satisfy the predicate are read. For S3 cold storage, the S3 Select API applies the predicate remotely, so only matching rows are transferred over the network.
Predicate pushdown is where the real latency savings accumulate. A 5-year daily query for AAPL that reads 1,825 rows does so by scanning row group statistics — a few KB of metadata — and discarding all non-matching row groups before a single data page is read.
6. Maintenance and Compaction
Tiered storage introduces a maintenance problem that does not exist in single-tier designs: data aging.
A background compaction process runs on a configurable schedule (typically daily during off-peak hours). Its responsibilities:
- Hot → Warm promotion: Completed monthly partitions in the hot layer are compacted into Parquet files and moved to warm storage.
- Warm → Cold promotion: Monthly warm partitions older than 12 months are uploaded to cold storage and removed from warm storage.
- Compaction: Within each tier, small Parquet files are merged into larger ones to maintain optimal file sizes for I/O throughput. Many small files cause excessive open/close overhead; one monolithic file prevents parallel read optimization.
This promotion pipeline is designed to be non-blocking: writes always go to the hot layer. Compaction runs as a background job that does not interrupt query or streaming workloads.
7. Putting It Together: A Query Execution Walkthrough
Consider a query: "Give me all AAPL.US 1-minute bars from January 2022 to December 2023."
Here's how this query navigates TickDB's storage stack:
| Step | Action | Storage layer | Latency estimate |
|---|---|---|---|
| 1 | Query planner decomposes time range | — | <1 ms |
| 2 | Check hot layer: Jan 2022–Dec 2023 is outside 30-day hot window | Skipped | 0 ms |
| 3 | Resolve warm partitions: Jan 2022–Dec 2023 spans 24 warm monthly partitions | Warm (SSD) | 80–150 ms |
| 4 | Parallel fetch of 24 Parquet files for AAPL.US 1m | SSD I/O | Included above |
| 5 | Parquet predicate pushdown: only matching symbol+time rows read | In-filter | Included above |
| 6 | Return sorted results | Memory merge | 5 ms |
Total: well under 200 ms for 2 years of 1-minute data — approximately 730,000 rows — served at sub-second latency.
Now consider the same query for 5 years (2020–2025). The 2020–2024 portion falls into cold storage. The query planner fetches warm partitions for 2024 and cold partition metadata for 2020–2023. Cold objects are fetched in parallel via S3. With 20–30 objects and parallel I/O, total query time remains under 1 second for the full 5-year range.
8. What TickDB Stores — and What It Does Not
The storage architecture described above serves TickDB's actual data capabilities precisely. Understanding the boundaries is as important as understanding the features.
| Data type | Supported | Storage notes |
|---|---|---|
| US equity OHLCV (kline) | Yes | 10+ years; all standard intervals (1m through 1M) |
| US equity tick-level trades | Not supported | The trades endpoint does not cover US equities or A-shares |
| HK equity kline | Yes | Full OHLCV across standard intervals |
| Crypto kline and trades | Yes | Both kline and trades endpoints available |
| A-share kline | Yes | Supported via standard kline endpoint |
depth (order book) |
US: L1 / HK: L1–L10 / Crypto: L1–L10 | Not available for forex, precious metals, or indices |
This boundary exists in part because tick-level trade data for US equities carries regulatory and licensing constraints that make long-term archival storage at scale economically impractical for a unified data service. The K-line storage architecture described in this article is optimized for the data types that do flow through TickDB at scale.
Next Steps
If you want to query TickDB's historical data directly, visit tickdb.ai and sign up for a free API key. The /v1/market/kline endpoint provides access to the historical K-line data described in this article.
If you are integrating TickDB into a backtesting pipeline, the GET /v1/market/kline endpoint is designed for completed-period queries. For the current real-time candle, use /v1/market/kline/latest. Both endpoints share the same authentication header (X-API-Key) and respect the same rate-limit handling pattern.
If you need high-frequency access patterns or multi-symbol stream queries, consider a dedicated query engine layer (DuckDB, Apache Arrow, or DataFusion) that integrates with TickDB's bulk export API. At scale, moving query computation closer to the data reduces round-trip overhead significantly.
If you are evaluating TickDB for institutional use, reach out to enterprise@tickdb.ai for data volume pricing and SLA details.
This article does not constitute investment advice. Markets involve risk; past performance does not guarantee future results. All latency figures are architecture-level estimates; actual query performance depends on network conditions, query complexity, and concurrent load.