5Time-Series Databases and the Alternatives

Five engines answered the same five questions on the same month of quotes, and each answer was checked against the other four before anything was timed. The row store with an index found one symbol’s last quote before noon in less than a tenth of a millisecond, nearly eighty times faster than the columnar engine, and took over three hundred milliseconds to average the spread of every symbol over the month, over forty times slower than the columnar engine. Neither result is a property of the product; both are properties of the question. This chapter looks at what the databases sold for time series promise, sorts market-data queries into a few shapes, explains why an engine is fast on some shapes and slow on others, and builds the benchmark (firm.tsbench) that a firm should run on its own data before it chooses.

5.1 What a time-series database promises

The chapter-4 tick store has every one of those properties and is not a database product: it is files, an embedded engine and a few conventions. That is the first thing to understand about the category. What a time-series database adds is packaging — a server that ingests, compacts, retains and answers queries without the firm writing the pieces — and, in the better ones, engineering of the same ideas: columnar storage, time partitions, zone maps, vectorised execution. The operations it names are the ones every market-data store needs.

One-minute bars are the standard example: One Quant Book 7’s time bars (chapter 2) are downsampled ticks. A store that keeps tick history for months and minute bars for years downsamples as part of its retention policy (chapter 2): the raw ticks age out, the buckets stay.

5.2 Query shapes of market data

Almost every query a research or trading platform sends to its market data is one of a handful of shapes, and each shape asks something different of the storage (Figure 5.1).

5.3 In-process engines against servers

A database was once a server: a separate process, often on a separate machine, that clients sent queries to over a network. The analytical engines most research platforms now use are not.

SQLite, the most deployed database there is, is embedded and row-oriented; DuckDB is embedded and columnar; Polars is a dataframe library with a query optimiser, which makes it an embedded engine too. An embedded engine gives up what a server gives — concurrent writers, access control, one copy of the data for many users — and gains the round trips and the conversions it does not make (Figure 5.2). For a researcher’s queries on files in a tick store, that is the right trade; for a service many teams write to, it is not (chapter 25).

The other difference between engines is how they execute a plan. A classic engine passes one row at a time from operator to operator, calling a function per row per operator. A columnar engine passes batches of values of one column.

Both ideas come from the research line of the MonetDB/X100 engine (2005) and the column stores that followed; they are why a columnar engine scans a million rows in milliseconds. They are also why it is slower than an index on a point lookup: a vectorised scan of a sorted file still opens a file, reads a footer and decodes at least a block, where a B-tree walks three pages to one row.

The model is crude — a real engine’s cost depends on layout, selectivity and cache — but its prediction is what the benchmark finds: point lookups and short ranges on one side, cross-sections on the other.

5.4 A benchmark that means something

firm.tsbench implements the method for five engines: DuckDB over the month’s Parquet files, partitioned by date and sorted by symbol and time; Polars scanning the same files lazily; SQLite, embedded and row-oriented, with an index on symbol and time; pandas, eager and in memory; and a hand-written numpy engine on the arrays sorted by symbol and time with a per-symbol offset index. Listing 5.1 is one engine’s five queries.

class DuckEngine:
    name = "duckdb"

    def __init__(self, root):
        self.con = duckdb.connect()
        for t in ("quotes", "trades"):
            files = f"{root}/{t}/date=*/part.parquet"
            self.con.execute(f"CREATE VIEW {t} AS SELECT * FROM "
                             f"read_parquet('{files}', hive_partitioning = true)")

    def run(self, q: str, p: Params):
        one = f"symbol = '{p.symbol}' AND date = {p.date}"
        sql = {
            "point": f"SELECT bid, ask FROM quotes WHERE {one} "
                     f"AND ts <= {p.point_t} ORDER BY ts DESC LIMIT 1",
            "range": f"SELECT ts, bid, ask FROM quotes WHERE {one} "
                     f"AND ts >= {p.t0} AND ts < {p.t1}",
            "bars": f"SELECT ts // {MIN_NS} AS m, arg_max((bid + ask) / 2.0, ts), count(*) "
                    f"FROM quotes WHERE {one} GROUP BY m",
            "asof": f"SELECT t.symbol, t.ts, t.price, q.bid, q.ask "
                    f"FROM (SELECT * FROM trades WHERE date = {p.date}) t "
                    f"ASOF LEFT JOIN (SELECT * FROM quotes WHERE date = {p.date}) q "
                    f"ON t.symbol = q.symbol AND t.ts >= q.ts",
            "xsection": "SELECT symbol, avg(ask - bid) FROM quotes GROUP BY symbol",
        }[q]
        return _rows(self.con.execute(sql).fetchall())

The data are the chapter-3 month of quotes (a million rows, fifty symbols, twenty days) and 40 000 trades drawn from it, each one nanosecond after a quote. All five engines agree on all five answers: 1 row for the point lookup, 153 for the hour of S007, 359 one-minute bars for its day, 2 000 trades joined, 50 average spreads.

Figure 5.4 is the result, and the proposition’s prediction holds. SQLite’s index answers the point lookup in 0.05 ms and the hour in 0.3 ms, DuckDB takes 3.8 and 2.2 ms for the same two, dominated by opening and planning; on the cross-section the order reverses, SQLite needing 313 ms to read a million rows one at a time and DuckDB 7 ms. Polars sits near DuckDB, slower on the grouped shapes; pandas pays for scanning the whole in-memory table with a boolean mask on every query and is the slowest on four shapes of five. The hand-written numpy engine is the fastest on the range, the bars and the cross-section, and close to the fastest on the other two: knowing the layout (sorted, with an offset per symbol) beats any general engine, at the cost of writing and maintaining it for every query.

def bench(factory, p: Params, rounds=5, engines=ENGINES, queries=QUERIES) -> list[dict]:
    """factory(name) builds a fresh engine; its first run of each query is the cold one.
    Warm runs are interleaved: in each round every engine runs every query once."""
    live, cold = {}, {}
    for name in engines:
        live[name] = factory(name)
        for q in queries:
            t = time.perf_counter()
            live[name].run(q, p)
            cold[(name, q)] = time.perf_counter() - t
    warm = {k: [] for k in cold}
    for _ in range(rounds):
        for q in queries:
            for name in engines:
                t = time.perf_counter()
                live[name].run(q, p)
                warm[(name, q)].append(time.perf_counter() - t)
    return [{"engine": n, "query": q, "cold": cold[(n, q)],
             "median": statistics.median(warm[(n, q)]),
             "spread": max(warm[(n, q)]) - min(warm[(n, q)])} for n, q in cold]

5.5 A survey of the field

The choice follows from the query mix and from who writes. A firm whose market-data queries are research scans over history, written once a day by a compaction job, is served by files and an embedded engine; a firm that needs many concurrent writers and readers of live data with access control buys or builds a server; a firm whose researchers live in dataframes and want versioned datasets looks at a dataframe store. Whatever the choice, the benchmark of this chapter, on the firm’s own shapes, decides between candidates.

5.6 Tutorial: five engines, one answer

5.7 Build: the engine benchmark

5.8 Exercises

5.9 Problem: The Right Tool for Each Question

5.10 Interview questions