50

I am going to recommend something that I have no doubt will get people completely up in arms and probably get people to attack me. It happened in the past and I lost many points on StackOverflow as people downvoted my answer. I certainly hope people are more open minded in the quant forum. Note - It seems that this suggestion has created some strong ...


48

Column-oriented storage is faster for reading because of the cache efficiency. Looking at your sample query: select price, time from data where symbol = `AAPL Here I'm concerned with three columns: price, time, and symbol. If all ticks were stored by row, the database would have to read through all rows just to search for the symbols. It would look like ...


27

I have long hungered for the ultimate, super-fast, super-scaleable data storage solution. I have used relational databases, kdb, flatfiles, and binary files. In the end, I used binary files in my research language of choice. My advice is to KISS. The choice of storage is actually not that critical (unless maybe you're working with options tick data). ...


21

Specialised NoSQL database systems are used a lot for time series storage, particularly for tick data: Kx / Kdb is one prominent solution; from Arthur Whitney et al who did A+ at Morgan Stanley Onetick is another, newer entrant, which has traces back to Goldman Sachs Voltdb is something by serial database inventer Michael Stonebreaker SciDB is another ...


17

As of April 2014, the 32-bit version of kdb+ is now free to try. This free version may not be used in production systems. The only technical limitation vs. the 64-bit version is that you can only address up to 4GB of memory per process.


16

Personally I make a distinction between two conflicting goals: (1) storing data incoming in real-time for immediate processing and (2) storing the gathered data for "offline" purposes. Such approach makes things a lot easier if we're talking about a home-grown solution. (1) must be as fast as possible but not necessarily scalable beyond a few dozen millions ...


16

At discretelogics we just released a file format to store time series in flat files called "TeaFiles". In addition to raw data they can store the binary item layout and a description of the contents. C#, C++, Python APIs are available open source, licensed under the GPL, see discretelogics.com/teafiles/ Using memory mapping, read performance reaches that ...


14

You could look into Pandas, a Python library that integrates with PyTables. It was created by someone at AQR and has some similar features as KDB.


14

There are many specialised products for HF tick data. In addition to KDB which you mentioned, there is OneTick, Vertica, Infobright, and some open-source ones like MonetDB etc. (see http://en.wikipedia.org/wiki/Column-oriented_DBMS). My experience is that Column Oriented Databases are overrated when it comes to tick data, because very often you request the ...


14

All of the answers above (unfortunately highly upvoted at this point) are missing the point. You shouldn't pick a DBMS or storage solution by general performance benchmarks, you should pick it by use case. If someone says they get a "x ms read", "y inserts per second", "k times speedup", "store n TB data" or "have m years of experience" and use that to ...


13

The reason that "traditional" NoSql databases will not get much up take in finance is that they are designed to solve a different problem. Most NoSql databases from the web world are designed with two central design parameters. First key lookups should be very fast. Second is that operations should be atomic at the row level and should not span records. This ...


13

You could try Arctic. Other open source column-oriented databases that you may not have considered include LucidDB and C-Store.


12

I don't like KDB+/q. For KDB+ experts, I am not picking a fight. The following is just my own understanding on KDB+ and TimeSeries Database. You're warmly welcome to correct me if anything wrong in your eyes :). First of all, during my near one year's KDB+/q development experience, I never ever find a paper based benchmark result indicating KDB+/q ...


11

I have been using FastBit for a while now and find it to be quite performant. It's very non-intrusive to your existing binary storage format provided your data is stored in a columnar manner. I have briefly tested Tokyo/KyotoCabinet and didnt find it suitable for my (persistent storage) requirements.


11

KDB is useful for two reasons: - Storage of data; and easy access to the data (i.e. querying ticks..etc) - Rich query language that supports many Quant functions however; what KDB does not do well; is the quant query language. I have evaluated KDB, Matlab, and R. So far R is the winner. I have not found any fast solution for storing and retrieving data; ...


11

The standard answer is going to be that for time series, you want a column store database. These are optimized for range queries (ie: give me everything between two timestamps) because crucially, they store data along one of the dimensions (which you must choose, usually time) contiguously on disk, and thus reads are extremely fast. The alternative, when ...


8

We've created a roundup of the top column-oriented database systems: http://www.timestored.com/time-series-data/column-oriented-databases This includes kdb+ and some open source alternatives. Open: InfluxDB, Java Chronicles, OpenTSDB, KairosDB Closed: oneTick, McObject, Teradata Database, vectorwise, sybase, vertica We have done some initial work at ...


8

OpenTSDB is good for large-scale time series storage. metrilyx/opentsdb-pandas and wiktorski/opentsdb_pandas seems to provide the interface with pandas. OpenTSDB and HBase rough performance test | MoreDevs provides a benchmark, may not exactly match your requirements but you can try.


7

Have a look at Kona which is a FOSS project trying to be compatible. Also Tom Szczesny has done some work on its predecessors namely A. I hope this helps. Also if you are not looking for a perfect substitute you can have a look at other Time Series Databases like InfluxDB, Java Chronicles, OpenTSDB, KairosDB which are all Open Source. There are commercial ...


6

Using MySQL for financial data is not unreasonable. But for tick data are you ever going to do anything except a query on a date range? For analyzing tick data in R I generally keep it in a disk file, one tick file per day, and load the files in as I need them. Using .RData files instead of csv files is quicker. I've also used custom C++ classes before, to ...


6

While noble, unfortunately, this type of effort is not very practical. Mostly because market data is a major source of revenue for the market centers and is never simply given away, at least not in intraday form. A few things to consider: Becoming a market data distributor is both costly and entails entering into agreements with each market center. If we ...


6

Few points from my experience: 1 Another filters that you that you should consider is for price = 999 or 999.99 that appears in some data providers. 2 Another set of checks is to look at cross-section of e.g. range = (high-low)/close over all names. Check for the smallest range and largest range to see if the values make sense. You can also check daily % ...


6

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 ...


6

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, ...


5

I have become a fan of SQLite. It's a very lightweight SQL database, which you can use as an intermediate solution. I agree with Rich C that the best thing to do is probably come up with a custom solution that is optimal for your needs. Using SQLite as persistent storage, and loading the data in memory when you want to do intensive computations on it seems ...


5

As background, Floating point precision is a way of storing numbers such that the precision is relative to the largest digit. For instance, the number $0.00123$ stored in fixed precision needs 6 digits of precision (3 zeros and the 3 non-zero numbers). However, this same number stored as floating point precision $1.23 \cdot 10^{-3}$ needs only 3 ...


5

Interesting debate and Not to wake sleeping dogs, the world has moved quite a bit in the 1.5 years, and the data space has exploded. I would like to recommend some new technologies and at the same time share a few of my experiences in this space. As @madilyn is trying to explain: It all depends on your use case. In my experience it's easy to know what you ...


4

I know this is probably a naive answer, but when I started doing data analysis for personal trading I looked for something much faster than SQL. I program in C++ and I found that HDF5 was the answer to all my problems http://www.hdfgroup.org/HDF5/ It's not accounting oriented, but the nice thing about it is that you can do almost anything with it and it is ...


4

Have you considered the HDF5 data model? Edit for Louis : Why using HDF5 ? As stated in the HFDF short description page : HDF5 is a unique technology suite that makes possible the management of extremely large and complex data collections. HDF5 is a suitable solution when dealing with very large datasets and you need performance. Again, as stated ...


Only top voted, non community-wiki answers of a minimum length are eligible