A quantitative research team must store and efficiently query five years of Level 2 order book data for 3,000 equity symbols. Each record captures the bid and ask queues at 10 price levels with nanosecond-precision timestamps, generating approximately 4 TB of raw data per year. The dominant query pattern involves three operations: (1) time-range slices by symbol and date, (2) point-in-time order book reconstruction at arbitrary timestamps, and (3) aggregation of volume-weighted metrics across trading sessions. The infrastructure team proposes four candidate architectures. Which is MOST appropriate?
Select an answer to reveal the explanation.
Short Explanation and Infographic
Level 2 tick data is the poster child for what columnar time-series databases were built for: billions of rows, always queried by time + symbol slice, requiring vectorized operations on numeric columns. kdb+ was literally invented for this in the 1990s by financial engineers. Postgres can technically do it, but asking a row-store to scan nanosecond tick data is like asking a spreadsheet to run a nuclear reactor — it'll work, briefly, and then you'll be very sad.
Full explanation below image
Full Explanation
Level 2 order book data has three structural properties that dictate database selection: extreme write throughput (millions of records per second during market hours), time-ordered access patterns where the vast majority of queries involve slicing by symbol and timestamp range, and heavy numerical aggregation over columnar values (bid/ask sizes, price levels, derived metrics).
Columnar time-series databases address all three. kdb+/Q stores data in column-major format, meaning that scanning a single attribute (e.g., bid price at level 1) across millions of rows does not require touching adjacent columns. Symbol-partitioned storage on disk means a query for AAPL data never reads MSFT pages. Time-ordered compression (run-length encoding, delta encoding for timestamps) delivers compression ratios of 10:1 or better on tick data. Crucially, in-database query execution in Q allows aggregations (VWAP, order book imbalance) to run as vectorized operations near the data without serializing rows to an application tier.
Why the distractors fail: (A) PostgreSQL is a row-oriented OLTP engine. Its B-tree indexes help for primary key lookups but are inefficient for range scans across billions of rows; MVCC overhead and page-level I/O make it orders of magnitude slower than columnar engines for analytical tick data workloads. (C) Embedding all ticks as nested arrays in MongoDB documents creates documents that exceed practical BSON size limits (16 MB) for active trading sessions, prevents efficient time-range queries without loading entire day-symbol documents, and offers no compression advantage on numeric time-series. (D) Redis sorted sets are an in-memory data structure appropriate for real-time quote caching, not multi-year historical storage; 20 TB of data cannot be practically maintained in DRAM, and sorted sets do not support columnar aggregations.
For firms that do not license kdb+, alternatives include Arctic (a MongoDB/TickStore wrapper optimized for pandas DataFrames), InfluxDB, QuestDB, or ClickHouse with time-series partitioning — all share the columnar, time-partitioned design principle.