Return to Answer

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 to go with SQL. It's generally a giveaway or 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.

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.

Some may see it as a rant but I felt I wanted to add this piece of advice because it took me many hours and days to compare different solutions in the past before settling with one choice. )

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 to go with SQL. It's generally a giveaway or 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.

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.

Other solutions may also work but if you look to solve all your requirements, and that does 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.

Some may see it as a rant but I felt I wanted to add this piece of advice because it took me many hours and days to compare different solutions in the past before settling with one choice. )

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.

Some may see it as a rant but I felt I wanted to add this piece of advice because it took me many hours and days to compare different solutions in the past before settling with one choice. )