A quantitative research team needs to store and query 20 years of tick-by-tick price data across 5,000 equity instruments, ingesting approximately 50 million records per trading day. Their primary access patterns are: (1) range scans over a specific symbol's price history for backtesting, (2) cross-sectional queries at a specific timestamp across all symbols, and (3) downsampling aggregations (OHLCV) at arbitrary frequencies. Which database technology is best suited for this workload?
Select an answer to reveal the explanation.
Short Explanation and Infographic
Todd Lammle: You wouldn't use a minivan to win Le Mans — and you shouldn't use PostgreSQL to run backtests on 20 years of tick data. Purpose-built time-series databases are the race cars of the financial data world: columnar layout, native downsampling, vectorized scans. Option B is correct because kdb+/q (the industry standard in HFT and quant funds), InfluxDB, and TimescaleDB were all designed from the ground up for exactly this workload, delivering query performance orders of magnitude faster than general-purpose databases.
Full explanation below image
Full Explanation
Time-series databases (TSDBs) are architected specifically for the storage and retrieval patterns that dominate quantitative finance: high-frequency append-only writes, temporal range scans on individual time series, cross-sectional point-in-time queries, and aggregation (downsampling) at arbitrary intervals. Their core design advantages over general-purpose databases are columnar storage layout (contiguous timestamp and value arrays enable vectorized CPU operations), compression codecs tuned for time-correlated data (delta-of-delta encoding, Gorilla compression), and query engines with native temporal operators.
kdb+ is the dominant choice at bulge-bracket banks and quantitative hedge funds for tick data precisely because its in-memory columnar architecture with an on-disk time-partitioned structure can scan 20 years of multi-instrument tick data in seconds. InfluxDB and TimescaleDB (a PostgreSQL extension) offer more accessible tooling while preserving meaningful time-series optimizations.
Option A (PostgreSQL) can technically store time-series data and will perform adequately at modest scale, but at 50 million records per day across a 20-year history (approximately 260 billion records), a row-oriented relational database with composite indexes will exhibit order-of-magnitude worse scan and aggregation performance compared to a TSDB. Partitioning helps but does not close the architectural gap for cross-sectional queries.
Option C (MongoDB time-series collections) improves on standard document storage for temporal data but was designed for IoT and operational metrics use cases, not for tick-level financial data with the three specific access patterns described. Cross-sectional queries at a specific timestamp across 5,000 symbols are poorly served by a document model where each document represents a single symbol's data.
Option D (Redis sorted sets) provides fast range queries within a single instrument but is a poor fit for cross-sectional point-in-time queries and does not natively support aggregation (OHLCV). Redis is an in-memory store; persisting 20 years of tick data at this scale would require enormous memory budgets and complex partitioning logic that time-series databases provide natively.