Mongodb – Can’t NoSQL DB ensure consistency if it is residing in a single server

Option 1

There are several reasons for this, which I'll explain below. First, here's how to do it.

• Use your choice of standard RDBMS platform.

• Set up your schema with several user-configurable fields, and make your application facilitate the configuration on a per-tenant basis.

• From the per-tenant metadata, you can create a per-tenant view of their data, which has the filters built in, and the columns named from your metadata. Any reports provided can also inherit the metadata. If they want to do M.I. off the data then provide them an extract of the transactional data, or maybe some additional MIS application on a different server if they will pay for that.

• Don't try to provide more customisation than this (i.e. no radical changes to the schema) unless the client is prepared to pay for their own private instance and maintaing a custom build.

The reasons behind this are:

• These database systems will handle the sort of volumes you describe on fairly ordinary hardware. You don't really have the sort of transaction volume that merits a NoSQL database. Unless you have some other architectural reason to want one, there's not much point in going bleeding-edge.

• They're mature, well-understood technologies.

• System management, backup/restore, replication, reporting and disaster recovery are all well sorted on RDBMS platforms.

• You can get client libraries including JDBC for all major RDBMS platforms.

• Views can be used for the per-user customisation and generatedfrom your application metadata.

• It's substantially more efficient than XML fields or EAV structures.

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.