Storage and Partitioning Choices for Large Options Backtests
Summary
The document considers how to make multi-year options backtests faster when a large MySQL table becomes slow under broad filters. The answer characterizes historical market data as a write-once, read-many workload that often does not need transactional guarantees or extensive referential integrity. Instrument definitions can be kept separately in a relational database, while price records use storage better suited to analytical scans.
Suggested approaches include columnar analytical databases or flat files, with ClickHouse offered as one database option. The answer also recommends dividing sparse options data into smaller partitions, such as by instrument or ticker hash, because a strategy often reads a small subset of the total records and reducing I/O can outweigh the cost of combining partitions. It cites production-scale deployments as context for the database recommendation, but gives no comparative benchmark for this particular dataset. The best layout depends on query patterns and operational constraints; per-instrument storage may also create administration overhead.
Key ideas
- Historical options data often follows a write-once, read-many pattern that may not need relational transaction features.
- Keep instrument definitions separate if they require relational integrity.
- Columnar analytical storage can better suit broad filtering and scanning than a conventional relational setup.
- Partitioning sparse records by instrument or ticker groups can reduce unnecessary data reads.
- Choose partition size with query patterns and administration effort in mind.
Tags
Full text
# Alternatives to RDBMS for options backtesting # Alternatives to RDBMS for options backtesting I've assembled a large dataset (~2B+ records) of options price data in MySQL for backtesting purposes. At a number of points, due to the sheer amount of data being retrieved and filtered, processing was extremely sluggish to the point of halting. I've spent a good amount of time creating thoughtful indexes, and relatively simple queries complete quickly, but anything over a wider range of inputs (eg, ticker, time period, price, etc) has been very slow. I've considered TSDBs (eg, kdb+, InfluxDB) but practical considerations were limiting. Have otherwise considered leaving in flat files and manipulating with something low-level, but not terribly clear on an approach. Anyone with experience backtesting options strategies over lengthy-ish periods (5-10y+) have guidance on what's worked with them for testing purposes. ## Answer by databento (score 9) https://quant.stackexchange.com/a/67921 My answer is similar to the one given for this other question. If you are mainly using the data for backtesting, there's very little reason to store the data in a MySQL database. The data generally follows a write-once, read-many (WORM) pattern, with no need for ACID semantics. You also don't have to enforce referential integrity on most of the data. If you want to do so for the parts relating to the instrument definitions (e.g. underlying, strike price, expiration date, put/call), you can split out only the definitions and put those in a MySQL database. Generally I see people storing the data in a MySQL database only because of (1) limited experience with other storage formats and tools, (2) excess exposure to MySQL because of non-financial or web development background. It is reasonable to start with MySQL because it is a tool you're familiar with, but once you start running into performance constraints here, it would be one of the most obvious pieces in your stack to optimize away. If you still want to use a DBMS for this use case so you don't have to write custom routines for processing the data, I would suggest something like Clickhouse, which I've had good experience with. It doesn't have the cost or licensing limitations that come with kdb or Vertica, and is definitely more suited for the use case than InfluxDB. ~2B+ records is actually fairly small, as there are production Clickhouse deployments that run queries and materialized views on a dataset that is growing by 6 million entries per second. Another observation is that you're storing all of that data in 1 single, large table with 1 field identifying the ticker. This is often an anti-pattern for this use case because: - Options data is fairly sparse - there are a large number of tickers over which most have very few entries. - You almost never have a strategy that needs to backtest over all or most of the tickers at once. Often, it will make more sense to split the data into smaller tables, e.g. 1 per instrument. On paper this will seem unintuitive because you have to pay linear time to merge the smaller sorted tables back together and recompose the set of tickers. However it is generally more efficient than keeping the data in 1 large table and filtering/discarding entries from the table. This is driven by practical considerations: - The number of entries of interest is small relative to the total number of entries. - You're saving significant I/O cost which will dominate the processing time for this type of workload. Of course, 1 "table" per instrument could be hard to administrate whether in a DBMS or in flat files in a file system, so you may want to come up with some kind of "sharding" strategy, e.g. distribute the data by some hash of the ticker, so you end up with a more manageable number of "tables", say, something in the order of 16 to 1024. The most naive hashing strategy is to split them by the first character of the ticker. Another naive hashing strategy is to split by length of the ticker. Note: At my day job, we store full order book data for markets with billions of records per day per market. My suggestions are motivated by experience managing petabyte-scale deployments for such data.
Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)
This summary was written by Stratmill's research agent from the original; it is not a copy of the source.