8.5 Tick-Level Databases & Time-Series

Basic Prerequisites: High-Performance C++ · Memory & Cache (why layout beats arithmetic). Working knowledge of SQL and Python. (these are the folder-level prerequisites for pages 02–06; page 01 states its own, smaller, entry requirements)

1. Intuition & Practical Objective

A backtest is only as honest as the store it reads from. Tick-level databases are where a strategy’s data becomes a physical artifact - billions of rows on disk, queried millions of times during research. Get the layout wrong and every study is either too slow to run or, worse, silently look-ahead: the query returns the restated value, the future bar, or the quote that arrived after the trade it was supposed to price.

This folder is a hub. It (a) gives the fast lookup tables below (job #1 of this pillar: memorise the storage comparison and the query-cost arithmetic), and (b) routes you to six sub-pages that walk from raw intuition, through formats, compression, engines, failure modes, and point-in-time pipelines.

The one-sentence essence. “A tick store is not a table you SELECT from - it is a columnar, time-sorted, compressed, partitioned artifact, and every design choice is a trade between bytes on disk, bytes scanned per query, and what a query is allowed to know at time $t$.”

The three laws of the tick store (each is a first principle, not a preference):

1. You never read a row to answer a column question. Row stores fetch whole records; column stores fetch one column. The traffic ratio is $\text{row}/\text{col}=s/s_c$ - for the 24-byte schema of §2 this is a constant $6\times$.
2. Time is the sort key and the partition key. Sorted, date-partitioned data makes the as-of join a single linear merge, $O(N+M)$, and makes “one day” a $1/252$ slice of the table.
3. Point-in-time correctness is a storage property, not a query habit. If the store keeps only the latest value of each field, no query can recover what was knowable at $t$ - the leak is baked into the bytes.

2. Mathematical Ground Truth & Lookups

Notation. $N$ rows, record width $s$ bytes, column width $s_c$ bytes, $K$ distinct values in a dictionary column, $D$ day-partitions.

Traffic: row vs column. A single-column scan of $N$ rows touches

For iso-ts/sym/price/size $=8+4+8+4$, $s=24$: querying SUM(size) reads $24\text{B}$ per row from a row store but $4\text{B}$ from a column store - $6\times$ less traffic, before compression.

Compression ratio. With per-column encoded width $w_i$ bits/row,

Partition pruning. With $D$ equal day-partitions, a single-day predicate reads a fraction $1/D$ of the table:

As-of join complexity. A merge/as-of join over sorted keys is

Lookup tables. All figures below are order-of-magnitude or measured by the runnable models in §3 and the sub-pages.

Critical caveat. Compression ratios are data-dependent: the $25.1$ bits/row below assumes a smooth auction stream with a near-constant tick. Illiquid names, irregular arrival times, and wide price grids compress far worse. Always re-measure on the actual feed (see 05 · Failure Modes).

3. Computational Implementation - the storage & query-cost model

This hub ships one self-contained model that ties the folder together: the daily storage bill, the columnar compression ratio, and the query traffic for a real predicate. Stdlib only; the per-column bit costs are those measured by 03 · Compression.

Read it as an engineering budget: the compression ratio decides the storage bill, the column count decides the query bill, and the sort/partition key decides whether either is affordable at research cadence.

4. Failure Modes & First-Principles Breakdowns

Hub signposts - the folder’s fault analysis lives in 05 · Failure Modes & Practice. In one line each:

1. Look-ahead in storage - a store that keeps only the latest value of each field makes every historical query leak the future; the same-bar close turned Sharpe $0.04$ into $20.4$ in the seeded experiment.
2. Schema drift - feeds add and drop fields; a fixed-width record assumption silently mis-decodes after each change (5 breaking changes in 24 months in the model).
3. Storage cost - naive row storage is $\sim 7.6\times$ larger (and $6\times$ more I/O per scan), while date-partition pruning - a separate axis - cuts scanned data $252\times$; at 5 years the raw store reaches $15.12$ TB versus $1.98$ TB compressed.

5. References

• Borror, Jeffry - Q for Mortals (4th ed.), Kx Systems (free at code.kx.com/q4m3/)
• Psaris, Nick - Q Tips: Fast, Scalable and Maintainable kdb+, Vector Sigma
• kdb+ and q - Official Documentation & Tick Architecture (code.kx.com)
• DuckDB - AsOf Join & Time-Series Documentation (duckdb.org) and Needham & Simons, DuckDB in Action (Manning)
• ClickHouse Documentation (clickhouse.com)
• Novotný, Jan et al. - Machine Learning and Big Data with kdb+/q (Wiley Finance, 2017)

6. Connected Graph Bridges

• Sibling topic: High-Performance C++ for Trading (cache-line and column layout - the memory model beneath this one)
• Sibling topic: Event-Driven Backtesting Engines (the consumer of these stores)
• Sibling topic: FIX Protocol & Exchange Connectivity (where the ticks arrive from)
• Data-lineage: Data Sources & Corporate Data (point-in-time hygiene - the same as-of discipline, applied to fundamentals)
• Data quality: Quantitative Research (what honest data enables downstream)
• Sub-pages (in-folder): 01 From Zero · 02 Storage Formats · 03 Compression · 04 Time-Series Databases · 05 Failure Modes · 06 Advanced Extensions

Beginner: start at 01 · Practitioner: start at 05

• Absolute beginner (zero systems background): 01 · From Zero - why one day of ticks breaks a relational database.
• Engineering core (undergrad / job-seeking): 02 · Storage Formats → 03 · Compression → 04 · Time-Series Databases.
• Robustness & tooling (practitioner / graduate): 05 · Failure Modes → 06 · Advanced Extensions.
• Forward links: Backtesting Engines · Alternative Data Pipelines