Choosing Storage for Historical Options Data and Research

# Best way to store hourly/daily options data for research purposes


# Best way to store hourly/daily options data for research purposes












There are quite a few discussions here about storage, but I can't find quite what I'm looking for.

I'm in need to design a database to store (mostly) option data (strikes, premiums bid / ask, etc.). The problem I see with RDBMS is that given big number of strikes tables will be enormously long and, hence, result in slow processing. While I'm reluctant to use MongoDB or similar NoSQL solution, for now it seems a very good alternative (quick, flexible, scalable).

- There is no need in tick data, it will be hourly and daily closing prices & whatever other parameters I'd want to add. So, no need for it to be updated frequently and writing speed is not that important.

- The main performance requirement is in using it for data mining, stats and research, so it should be as quick as possible (and preferably easy) to pull and aggregate data from it. I.e., think of 10-year backtest which performs ~100 transactions weekly over various types of options or calculating volatility swap over some extended period of time. So the quicker is better.

- There is lots of existent historical data which will be transferred into the database, and it will be updated on a daily basis. I'm not sure how much memory exactly it will take, but AFAIK memory should not be a constraint at all.

- Support by popular programming languages & packages (C++, Java, Python, R) is very preferable, but would not be a deal breaker.

Any suggestions?

## Answer by Matt Wolf (score 6, accepted)

https://quant.stackexchange.com/a/7930

A columnar database or No-SQL solution may be your best choice.

It depends on which OS you target, what your throughput and latency requirements are and whether you look to persist all data or not and finally how big the size of your data is expected to be. Obviously if you only look to store hourly/daily data then even a database that comprises a year of all options data of the SPX500 underlyings may fit into memory and if that is the case you should definitely look at RedisDB. It can persist/snapshot data but generally loads them back into memory.

If the size of your data is a constraint to fitting it into memory then another solution such as RavenDB (well written .Net library), or other non-.net solution, depending on requirements, such as Mongo or Couch db may fit a lot better. Please add more requirements and I am happy to edit my post and add information, given I believe I can add value.

Edit:

According to your updated information I recommend to look more closely at Redis : Not only are there libraries for pretty much any programming language and OS imaginable (I use it in my .Net framework with the BookSleeve API). You also get great support in R. You can dump literally time series with millions of elements into it, have it stay in memory (you can also persist it) but you can incredibly fast access the data out of R. I do not know a faster way to access time series data out of R to be honest plus additional indexing packages will give you a great accessor library. If you look for something fast, efficient and look to profile ideas/data, which hints at R usage then RedisDB Is what you want. Of course this is not supposed to be a solution for huge data requirements but you specifically mentioned you deal with hourly/daily data and for that purpose even a machine with 16gb of core memory running 64bit code will be plenty enough thus certain suggestions that redis will force you to invest in outrageous hardware are unwarranted. If you look to heavily profile those data in R or python or other languages and want blazing fast access then redis is your solution. If you are more about long term storage and are happy to accept disk access latencies then no need for a cache based db and rather look at document or other key/value or columnar data stores.

Other solutions may also work but if you look to solve all your requirements that do not force you to sit down for days just to think about schemata and table relationships just in order to get an SQL solution going then I recommend you look into the direction of Redis or similar approaches and not SQL. Columnar databases may also work though the open source solutions are rather clumsy to work with and I would say not very well thought out in terms of usability and extensibility. (Example: HDM5, though its more of a file format than a database, but until 2013 the guys have still not managed to enable dynamic sizing of data in hdf5 files. You can delete content but the allocated space is not returned. Whatever people tell me about NASA or other organizations using it, I settled the story in the way that those are big organizations with heavy government involvement where funding and resources in many areas that are not priority can get extremely scarce. I do not believe any large organization would still operate on 1990s type of databases if they had plenty enough funding to work on developing better ones. That is the reason certain organizations still use them but I do not find it a point that speaks in favor of using Berkeley DB or HDF5; I used both of them and find them very limited in many ways, plus the performance did not even turn out to be that great.

Summary: pick your weapon of choice, columnar databases could be the solution if you are willing to work around sometimes strange limitations in the api or core data structure but they are made to deal with time series data. Or chose no SQL solutions, either memory based/cache based solutions or purely persisted ones. But I would strongly advise you not to go with SQL. It's generally a giveaway of a beginner or junior person when being asked which database they use for time series storage and the answer comes out they use MySQL, MySQL or embedded SQL solutions. Those are just not designed to handle time series data well.

## Answer by madilyn (score 7)

https://quant.stackexchange.com/a/7934

I recommend you optimize your SQL implementation instead of going for NoSQL, and throwing more expensive hardware at the problem.

- Always benchmark first. The reason I'm saying this is that I've seen MS SQL Server scale perfectly fine for options data of the magnitude you're describing and "big number of strikes tables will be enormously long and, hence, result in slow processing" is not a good way to judge.

- Redis is a very bad idea for what you're trying to accomplish. From what I can see from the other post, all it has going for it is that it has R bindings. But quite frankly, almost everything has multi-language bindings nowadays that it's an inactive selling point. Redis is designed to trade off consistency and durability for speed. Mongo is similar (it's not that there is no durability, you'd look up WALs to recover and this is rather sketchy but that's for another topic). To put this in perspective: This trade-off becomes a necessary evil if you're doing FB social ad metrics, logging 30 million events per second in realtime. But if you're logging 2000 options * 50 records per hour = 28 records per second in batch, you don't need those trade-offs. The risks are asymmetric: If you lose your market data, you would have to seek a vendor, pay, and spend time adjusting the backfill to your own storage format. If FB misses a few clicks for user statistics to deliver latency requirements, everything still moves along smoothly. So you have to work around this, set up persistence servers. The persistence servers should separated (e.g. you'd put them in NY2/NY4) in case of a localized failure. It sums up to be much costlier than mirroring your disks. You need to have a lot of memory. This is less a problem if you're hosting everything on the cloud (but this comes with other issues and chances are you aren't). 16 cores and 244 GB memory, a Redis slave per core and you are down to 15 GB of memory. See: https://moot.it/blog/technology/redis-as-primary-datastore-wtf.html NOTE: The problems stated above are different from the management concerns that see NoSQL being limited in established firms - the latter are usually misguided.

## Answer by Phil H (score 3)

https://quant.stackexchange.com/a/7931

You need to log some data, and later use it for analysis. Dare I suggest you just append the values to a set of files? You can load up the data later for your runs, and cache anything needed frequently. Frankly buying an SSD and copying the files to that before you run analysis should solve your problem, and no-one had to get hurt.

## Answer by unclepaul84 (score 3)

https://quant.stackexchange.com/a/7938

Check out http://discretelogics.com/teafiles/. It solves the large memory footprint by using memory mapped files.

## Answer by Thomas Browne (score 1)

https://quant.stackexchange.com/a/15209

For any data that is not strictly tabular and unchanging in schema, you should rule out SQL solutions. Option pricing fits that description in my experience, because high-liquidity stocks, currencies, or bonds, will have a far bigger set of strikes and maturities than lower liquidity instruments. Thus in a relational database you will have to have columns for strikes and maturities, with clunky joins everywhere, whereas in NoSQL such as Mongo, you can have an atomized "document" that has all the data associated with a timestamp, that is extremely rapid to query, and is also flexible for meta-data. Who hasn't found the need to for meta data, even in financial time series? You'll be able to go back and systematically add new structures and substructures to your data without messing up the whole schema.

My own experience with Mongo is very positive. Once you have familiarised yourself with its JSON-based query syntax, it's easy, and extremely fast on cheap commodity hardware. It's also massively easy to setup. That is definitely not the case for the relational databases on equivalent hardware. Another bonus is that you'll learn JSON - a very useful and widely use format for data that allows you to export "flat" data CSV-style, but can also do hierarchical nesting which can be really useful, without going to the overkill that is xml.

If extremes of consistency are what is necessary, then SQL is your bet. Stuff like: this salary MUST be linked to an EXISTING employee that is NOT on sabbatical. This type of rule can be embedded in the database, which will enforce consistency, unlike the more flexible (and consequently more dangerous) NoSQL products where these types of rule must be performed in the terminal. But when we're talking about atomised data with no dependencies such as a vol surface, this consistency enforcement is probably not even going to be used. The design "fit" of SQL is overkill and comes with heavy burdens. Recall that SQL was designed for business logic in the 70s.

Don't believe those who tell you Mongo or other NoSQL is not reliable when it comes to data safety. That is false. There are all the usual tools to guarantee writes, journaling, redundant servers, everything you might need for multiple layers of security, including incremental backup to cloud. Redis of course could be problematic in that respect. But the established NoSQL databases are safe and reliable, indeed possibly more safe if you do not have a skilled DB administrator who knows how to get a complex SQL system up and running again on failure.

Finally if your use case is only single-machine, then strongly consider HDF5. This will outperform and be better suited to a large financial data mining application than any of the database technologies. It has been designed exactly for what you require (massively fast, flexible queries and schemas) but of course it's not as strong for sharing.