Time-series databases

Data that arrives with a clock

Ticks, quotes, risk numbers and server metrics share a shape: a timestamp, some labels, some measured values, and an endless stream of them. A time-series database (InfluxDB, TimescaleDB, QuestDB, kdb+) is built for exactly this. Writes are almost always appends at the newest time, so they are fast; data is stored in time-ordered chunks, so "the last hour" touches little; old detail can be summarised into coarser data and finally deleted. The data model of InfluxDB is typical: a measurement (like a table), tags (indexed labels such as the symbol or venue), fields (the measured values) and a timestamp.

# One point in InfluxDB line protocol: measurement,tags fields timestamp
point = "trades,symbol=AAPL,venue=XNAS price=184.25,qty=100i 1700000000000000000"
head, fields, ts = point.split(" ")
measurement, *tag_pairs = head.split(",")
tags = dict(pair.split("=") for pair in tag_pairs)
print(measurement, tags)                             # trades {'symbol': 'AAPL', 'venue': 'XNAS'}
print(fields, int(ts))                               # price=184.25,qty=100i 1700000000000000000

Downsampling: from ticks to bars

Keeping every tick forever is expensive and rarely needed. Downsampling groups points into fixed time buckets and keeps a summary of each. For prices the standard summary is OHLCV: open (first price in the bucket), high, low, close (last price) and volume. A tick at time ts belongs to the bucket that starts at ts - ts % bucket. Databases do this continuously and write the result to a separate, much smaller table (a continuous aggregate), which is what charts and most backtests read, while the raw ticks are kept only for a short time.

# Rolling ticks (ts, price, size) into 60-second OHLCV bars
def ohlcv_bars(ticks, bucket):
    bars = []
    for ts, price, size in ticks:
        start = ts - ts % bucket
        if bars and bars[-1]["start"] == start:
            bar = bars[-1]
            bar["high"], bar["low"] = max(bar["high"], price), min(bar["low"], price)
            bar["close"] = price
            bar["volume"] += size
        else:
            bars.append({"start": start, "open": price, "high": price, "low": price, "close": price, "volume": size})
    return bars

ticks = [(0, 100.0, 10), (30, 101.0, 5), (61, 99.5, 20), (65, 100.5, 1)]
for bar in ohlcv_bars(ticks, 60):
    print(bar)
# {'start': 0, 'open': 100.0, 'high': 101.0, 'low': 100.0, 'close': 101.0, 'volume': 15}
# {'start': 60, 'open': 99.5, 'high': 100.5, 'low': 99.5, 'close': 100.5, 'volume': 21}

As-of joins and retention

Series rarely tick at the same instants, so joining them on equal timestamps finds almost nothing. The question you actually ask is "what was the quote when this trade happened?": for each trade, take the latest quote at or before its time. This as-of join (pandas.merge_asof, ASOF JOIN in kdb+ and DuckDB) must look only backwards; using a quote from after the trade leaks the future into your analysis. Because quotes are sorted by time, a binary search (bisect_right) finds the right one in logarithmic time. Finally, a retention policy drops points older than a limit so storage stays bounded: keep the last 30 days of ticks, a year of minute bars, and daily bars forever.

# For each trade, the latest quote at or before it, found by binary search
from bisect import bisect_right

def asof_join(trades, quotes):
    times = [quote[0] for quote in quotes]
    joined = []
    for ts, price in trades:
        position = bisect_right(times, ts)
        joined.append((ts, price, *quotes[position - 1][1:]) if position else (ts, price, None, None))
    return joined

quotes = [(10, 99.9, 100.1), (40, 100.5, 100.7)]
print(asof_join([(5, 100.0), (10, 100.0), (50, 101.0)], quotes))
# [(5, 100.0, None, None), (10, 100.0, 99.9, 100.1), (50, 101.0, 100.5, 100.7)]

# Retention: keep only the points younger than the limit
def apply_retention(points, now, ttl):
    return [(ts, value) for ts, value in points if ts > now - ttl]

points = [(10, 1.0), (40, 2.0), (50, 3.0), (90, 4.0)]
print(apply_retention(points, 100, 60))              # [(50, 3.0), (90, 4.0)]   the point at 40 is exactly 60 s old: dropped
print(apply_retention(points, 100, 1000))            # everything is younger than the limit

• A time-series point is a measurement, indexed tags, measured fields and a timestamp; writes are appends and data is stored in time order.
• Downsample ticks into OHLCV bars (ts - ts % bucket) and read the bars; keep raw ticks only briefly.
• Join series with an as-of join that only looks backwards (binary search), apply a retention policy, and keep tag cardinality and timestamp units under control.

Next up: packaging a Python library, turning your code into something others can install.

Lab · what's graded

• Roll ticks into bars with ohlcv_bars25 pts
• Find the quote at each trade with asof_join25 pts
• Write a point with line_protocol25 pts
• Drop expired data with apply_retention25 pts