Choosing NoSQL and Time-Series Databases for Financial Data

# Usage of NoSQL storage in Finance


# Usage of NoSQL storage in Finance












I am wondering if anyone has used NoSQL (mongodb, cassandra, etc.) to store and analyze data. I tried searching the web but was not able to see if the financial firms had gotten in to using nosql storage.

So far even on this site i have only seen talks of using SQL DBs. I would imagine that NoSQL would be much faster.

Would someone shed light on the subject of NoSQL solutions in the finance world. have you seen/heard anyone use it? if so why are they using. if not using then why not.

## Answer by Dirk Eddelbuettel (score 22)

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

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 recent Stonebreaker project, trying 'commercial open source' but more for science apps (at least for now?)

The commercial offerings tend to be rather expensive and I have not heard of use of standard Web 2.0 NoSQL databases in trading environments. Some may of course do rather than talk ...

## Answer by Steve Severance (score 16)

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

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 allows the database to be sharded very effectively since no operation should span multiple machines and in turn this allows them to build scale out architectures. These databases are built for very write heavy workloads and to be able to have fast key lookups to render webpages. While it is possible to use them to store tick data its not really what they are good at.

Financial data tends to be small relative to large web companies data. So scale out in finance is less important. Also financial data tends to partition well (at the day, symbol, or almost any other key) so replica placement tends to be more explicit.

Map reduce is the typical way to build analytical applications for these data stores either using hadoop (Cassandra) or internal map reduce (MongoDB has a java script MR API). This is typically not the ideal paradigm for analyzing time series data.

Traditional column oriented stores with integrated time series analytics tools or custom built platforms will continue to be the preferred way to store and process time series data. I don't think that there will (or should) be much convergence between the databases built for running very high scale websites and the databases built to store and analyze time series information.

All that being said we use mongodb extensively for metadata storage and as a sort of very large cache. But we don't use it to store or analyze financial data.

## Answer by user1041 (score 6)

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

In the finance world, most data (especially given this forum is for Quant monkeys) is transactional and subject to regulatory reporting.

NoSQL isn't generally transactional and given the shape of, say, risk data, there is no overpowering reason to throw away ACID and RDBMS.

There are more mundane reasons: there are 1000s of IT guys who know RDBMS. When your NoSQL folk leave, where is your continuity and support?

I'm biased here: I'm an RDBMS database specialist who manages risk/pricing/trading systems

## Answer by Thomas Browne (score 5)

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

Cassandra is the obvious choice. With MongoDB or any RDBMS, you will hold all ticks in a table (collection in Mongo-speak) and index by ticker. This means that when you want to retrieve data for a ticker, the data will not be contiguously stored, and you will have a massive usage of index and random reads. Even with SSDs this is slow. For 500k ticks into Python from Mongo it takes me well over 200 seconds on a single 16GB SSD-equipped I7. Yes you can cluster, but the starting point is poor. Imagine if you need to bring in 100 tickers?

With Cassandra, everything is stored in column families. Big dicts of dicts, basically. You get entirely sequential storage of each ticker, meaning you can go back to spinning HDs if you want, but with SSDs it positively screams for data retrieval. I'm talking under 15 seconds for the same as the above. I'm not even going to go into the ease with which adding nodes to Cassandra speeds this up. The 15-second average is on a single computer. Lots of the "reasons for Cassandra" on the web talk about its easy scale-out using multiple nodes, and it's true this will make it even faster / safer, but for me this sequential columnar storage is what makes it perfect for finance. It starts out geared for timeseries, wherease Mongo does not. The design gives you at least an order of magnitude out-of-the-box before you even start messing with clusters and/or optimization. If you know Python, the analogy is simple. Cassandra is to Mongo (or an RDBMS) as Numpy is to Python lists. Chained pointers to scattered datapoints as opposed to a big chunk of contiguous data.

The converse of course is also true. If your use case is single (few) record(s) amongst millions (typical in web applications), Mongo makes this much easier than Cassandra. Cassandra is for big data, whereas Mongo/RDBMS map better to website-userID style workloads. Parodoxically, while SQL for example is considered quite rigid compared with "NoSQL", in my opinion, Cassandra is even more rigid than relational databases. But boy do you get performance if you accept its structures.

BTW Hbase is another columnar choice but I have no experience with it. The main message is, forget about RDBMS or "traditional" NoSQL. These are bad choices for multiple time series. What you want is a column database, of which Cassandra is an excellent example.

## Answer by Clebson Derivan (score 4)

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

mongodb seems to be a good opensource solution to store historical tick-by-tick. I am using it here and it seems to be very simple to use.

## Answer by Luciano (score 3)

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

> I am wondering if anyone has used NoSQL ... to store and analyze data.

Yes. Have a look at arctic on github. This is an open-source API built on top of MongoDB, that is in production use by one of the largest hedge funds in the world, for storing time-series data.

> I would imagine that NoSQL would be much faster.

In the github wiki you'll find links to presentations where they show performance metrics vs their existing, commercial DB. This is achieved partly by storing the data in compressed format, and sending over the wire as-such (this is a feature of the API; not MongoDB).

Other cool features include versioning, quotas, acls.

## Answer by kevin (score 0)

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

In regards to the above answers, for tick data or time series , you could probably use a combination of redis (in memory data-store) & mongodb, or use Hbase with bus events.