Time-Series Databases

A single liquid stock can generate millions of quote and trade updates in one trading day. Multiply that across thousands of instruments and years of history, and you get a workload a general-purpose relational database was never designed for: near-constant append-only writes, queries that almost always filter by a time range first, and data that's read far more often in bulk time-ordered chunks than by looking up one row by its primary key. A time-series database is a storage engine built specifically around that access pattern, and using one instead of a generic row store is often the difference between a query returning in milliseconds versus minutes.

The core design choice is storing data by column, ordered by time, rather than by row. In a normal row store, a table of trades has each row — timestamp, price, size, side — stored together as one contiguous chunk, which is efficient if you want an entire trade record but wasteful if you only want the price column across a million trades, because the database still has to read past every irrelevant field along the way. A time-series database stores each column separately and keeps it sorted by time, so a query for "average price over this hour" reads only the price and timestamp columns, skipping everything else, and can additionally exploit the fact that time-sorted numeric data compresses extremely well — consecutive prices and timestamps tend to be close to their neighbors.

Retention and downsampling are treated as first-class features rather than something the application has to build manually. Raw tick data is enormous and its value fades fast — most queries against data from three years ago only need daily bars, not every tick. Time-series databases typically support automatic rollups (pre-aggregating raw ticks into minute or daily bars as they're written) and retention policies (dropping or archiving raw data past a certain age while keeping the aggregates), which keeps storage cost and query latency bounded even as the raw stream keeps growing.

Worked example: one query, two storage engines

A researcher asks: "what was the average trade price for stock X between 10:00 and 10:05 yesterday?" In a row-oriented general-purpose database with a billion-row trades table, this query has to locate the relevant rows (an index on timestamp helps, but the database still reads full rows including irrelevant columns like exchange ID and trade condition flags) and can take several seconds on an unoptimized table. In a time-series database with data stored column-wise and pre-sorted by time, the same query touches only the price and timestamp columns within that five-minute window — a much smaller, more sequential read — and typically returns in a few milliseconds, even without a pre-built rollup, because the physical layout already matches the query pattern.

What this means in practice

Time-series databases are worth the operational overhead of a specialized system specifically when the workload is time-range queries over append-mostly numeric data at high volume — tick and quote data, sensor-like telemetry, backtest analytics. For lower-volume reference data like corporate actions or index membership, a general-purpose database is usually simpler and perfectly adequate, since the specialized query pattern that justifies a time-series engine isn't present.