Which NoSQL DB is best fitted for OLTP financial systems? [closed]

Question

We're designing an OLTP financial system. it should be able to support 10.000 transactions per second and have reporting features.

So we have come to the idea of using:

• a NoSQL DB as our main storage
• a MySQL DB (Percona server actually) making some ETLs from the NoSQL DB for store reporting data

We're considering MongoDB and Riak for the NoSQL job. we have read that Riak scales more smoothly than MongoDB. And we would like to listen your opinion.

• Which NoSQL DB would you use for a OLTP financial system?
• How has been your experience scaling MongoDB/Riak?

Gilroy

Answer 1

There is no conceivable circumstance where I would use a NOSQl database for anything to do with finance. You simply don't have the data integrity needed or the internal controls. Dow Jones uses SQL Server to do its transactions and if they can properly design a high performance, high transaction Relational datbase so can you. You will have to invest in some people who know what they are doing though.

Ejaculation

+1 Anyone who seeks to use an "eventually consistent" database for financials is not qualified to implement financial software, full stop. – Muscovado

Not all NoSQL databases are eventually consistent and many have the necessary integrity and internal controls. Finally, not everything that has to do with finance requires absolute integrity and control. – Selfrestraint

@elad, you clearly have never worked with auditors if you think that. I did work for an audit agency and it is foolish to even consider a noSQL database for financial apps. – Ejaculation

it's a matter of defining "financial". If I build my home-grown algotrading app I don't care about auditors. That doesn't make it "not financial"... – Selfrestraint

At the end we considered VoltDB. ACID compliant, In-Memory storage and SQL. – Gilroy

+1 We have a mature, functioning, SQL Server-based financial application. i was trying to find real-world NoSQL examples, and how it can be used to solve real-world problems. i thought maybe we could convert the system to NoSQL because we have a lot of data (details of transactions, audit logging, etc) and NoSQL can magically make queries run instantly, and it would eliminate the need to archive data after 90 days, and again after 7 years, because with NoSQL you can attach another server to the network and it will just grow. i guess i was mistaken. – Bichromate

What is this answer and "eventually consistent" comment is all about - it's like believing that when you withdraw money from an ATM which belongs say to some french bank, operation is performend inside a single db transaction on the US bank server. If you have atomic modification and compare and set you can build whatever locking mechanism you like. – Wardrobe

Financial can mean many different things, we all understand that if you have to manage account money you need good security, however if you just want to store tickdata at home for personal use this is a different matter. – Behlau

@BlueTrin, whatever you want to do at home is fine, I was referring to business programming. – Ejaculation

@HLGEM: the point is that Finance is not only accounting and slow data, there are usages in high-frequency data for example: See this thread quant.stackexchange.com/questions/1392/…. In high-frequency Finance, KDB is almost standard for the big players. – Behlau

Answer 2

One has to think about the problem differently. The notion of transaction consistency stems from the UD (update) in CRUD (Create, Read, Update, Delete). noSQL DBs are CRAP (Create, Replicate, Append, Process) oriented, working by accretion of time-stamped data. With the right domain model, there is no reason that auditability and the equivalent of referential integrity can't be achieved.

Hypaesthesia

when you said CRAP, I laughed; now I feel like an idiot. great explanation, thank you!. – Gilroy

Answer 3

The global-storage based NoSQL databases - Cache from InterSystems and GT.M from FIS - are used extensively in financial services and have been for many years. Cache in particular is used for both the core database and for OLTP.

Francklin

Answer 4

I can answer regarding my experience with scaling Riak.

Riak scales smoothly to the extreme. Scaling is as easy as adding nodes to the cluster, which is a very simple operation in itself. You can achieve near linear scalability by simply adding nodes. Our experience with Riak as far as scaling is concerned has been amazing.

The flip side is that it is lacking in many respects. Some examples:

• You can't do something like count(*) or list keys on a production cluster. That would require a work around if you want to do ETL from Riak into MySQL - or how would you know what to (E)xtract? (One possible work around would be to maintain a bucket with a known key sequence that map to values that contain the keys you inserted into your other buckets).
• The free version of Riak comes with no management console that lets you know what's going on, and the one that's included in the Enterprise version isn't much of an improvement.
• You'll need the Enterprise version of you're looking to replicate your data over WAN (e.g. for DR / high availability). That's alright if you don't mind paying, but keep in mind that Basho pricing is very high.

Selfrestraint

Thank you for sharing your experience with Riak, Elad. I was thinking, it's not possible doing things like 'count' and 'list keys' using mapReduce? +1 – Gilroy

Answer 5

I work with the Starcounter (so I’m biased), but I think I can safely say that for a system processing financial transactions you have to worry about transaction consistency. Unfortunately, this is what the engines used for Facebook and Twitter had to give up allow their scale-out strategy to offer performance. This is not because engines such as MongoDb or Cassandra are poorly designed; rather, it follows naturally from the CAP theorem (http://en.wikipedia.org/wiki/CAP_theorem). Simply put, changes you make in your database will overwrite other changes if they occur close in time. Ok for status updates and new tweets, but disastrous if you deal with money or other quantities. The amounts will simply end up wrong when many reads and writes are being done in parallel. So for the throughput you need, a memory centric NoSQL database with ACID support is probably the way to go.

Meant

What are some examples of "memory centric NoSQL database with ACID"'s? – Li

@GiscardBiamby - I believe that VoltDB (voltdb.com) would be a good example of a memory centric NewSQL database with ACID. – Oahu

Answer 6

You can use some NoSQL databases (Cassandra, EventStore) as a storage for financial service if you implement your app using event sourcing and concepts from DDD. I recommend you to read this minibook http://www.oreilly.com/programming/free/reactive-microservices-architecture.html

Hart

Answer 7

OLTP can be achieved using NoSQL with a custom implementation,

there are two things, 1. How are you going to achieve ACID properties that an RDBMS gives. 2. Provide a custom blocking or non blocking concurrency and transaction handling mechanism.

To take you closer to solution, Apache Phoenix,apache trafodion or Splice machine.

Mousseline

Answer 8

Trafodion has full ACID support over HBase, you should take a look.

Audiovisual

Answer 9

TigerBeetle: https://tigerbeetle.com/

TigerBeetle redesigns the distributed database storage engine and consensus protocol for the OLTP workload. This solves the problem of OLTP write contention to unlock three orders of magnitude more performance than a general purpose (OLGP) database.

Sherwin

Answer 10

Cassandra can be used for both OLTP and OLAP. Good replication and eventual data consistency gives you the choice in your hand. Need to design the system properly. And after all it's free of cost but not free of developer, give it a try

Budweis