We are building a real time banking platform and app.
Our prime concern is safety and consistency of our data across multiple db servers. We need transactions and we can't afford to risk power outages and lose our data (I'm talking about MongoDB).
Our secondary concern is schema – like functionality for our app tenant users.
We also would like to escape schema updating hell on large databases. Custom schemas for our tenants is optional but very desirable.
We are very heavy AJAX frond end and our backend is full nodejs. We do a lot of JSON data processing.
Should we go with MySQL? Is there any noSQL solution (maybe CouchDB?) for our case?
Thank you for taking your time with this question!
Best Answer
As far as I know there are no "nosql" databases that promise ACID transactions, so for banking purposes they are a non starter. Referential consistency support is not usually in their key feature sets either.
mySQL claims ACID transactions when using innodb tables, but I believe there are some caveats around that which may be show stoppers (any mix of other table types, including in-memory tables which are sometimes used for intermediate results in complex logic, will break ACID compliance for instance).
If you are looking at these options because they are free, then consider postgres which does offer ACID transactions, even including transactions that make schema changes. Please consider your support SLAs though: if this is a system that needs high availability (and the words "realtime" and "banking" suggest it is) then I recommend having a support contract in case something beyond your understanding or access to fix goes wrong. This will be rather expensive and once you are talking about that sort of money you might as well at least consider commercial database servers too.
One final point not relating to any particular database: make sure you have the right options selected for full ACID behaviour. A lot of systems default to settings that bend the isolation part, because guaranteeing complete isolation often requires serialisation which can significantly impact the performance of concurrent operations. Whatever DBMS you chose make sure that you understand its options for isolation/serialisation (like "SET TRANSACTION ISOLATION LEVEL" and related directives in MSSQL) and how they interact with each other.