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
Level 2 tick data is exactly what columnar time-series databases were built for: billions of rows, always sliced by time and symbol, with vectorized math on numeric columns. You need storage that keeps symbol data together, compresses time-ordered ticks, and runs queries near the data. Row-store can technically do it, but it'll work briefly, and then you'll be very sad.
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).
The columnar time-series database proposal addresses 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 other architectures fail: The relational OLTP database proposal is a row-oriented engine. Its composite balanced-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. The document-store proposal embeds all ticks as nested arrays in BSON documents, which 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. The distributed key-value store proposal uses in-memory sorted sets 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.