Consistency/Atomicity (or even ACID) properties in multiple SQL/NoSQL databases architecture

Question

I'm rather used to use one database alone (say PostgreSQL or ElasticSearch). But currently I'm using a mix (PG and ES) in a prototype app and may throw other kind of dbs in the mix (eg: redis).

Say some piece of data need to be persisted to each databases in a different way. How do you keep a system consistent in the event of a failure on one of the components/databases ?

Example scenario that i'm facing: Data update on PostgreSQL, ElasticSearch is unavailable. At this point, the system is inconsistent, as I should have updated both databases. As I'm using an SQL db, I can simply abort the transaction to put the system in its previous consistent state.

But what is the best way to keep the system consistent ?

• Check everytime that the value has been persisted in all databases ?
• In case of failure, restore the previous state ? But in some NoSQL databases there is no transaction/ACID mechanism, so I can't revert as easily the previous state.

Additionnaly, if multiple databases must be kept in sync, is there any good practice to have, like adding some kind of "version" metadata (whether a timestamp or an home made incrementing version number) so you can put your databases back in sync ? (Not talking about CouchDB where it is built-in!)

Moreover, the databases are not all updated atomically so some part are inconsistent for a short period. I think it depends on the business of the app but does anyone have some thought about the problem that my occur or the way to fix that ? I guess it must be tough and depends a lot of the configuration (for maybe very few real benefits).

I guess this may be a common architecture issue but I'm having trouble to find information on the subject.

Answer 1

1. Keep things simple.
2. Search engine can and will lag behind sometimes. You may fight it. You may embrace it. It's fine, and most of the times its acceptable.
3. Don't mix the data. If you use Redis for sessions - good. Don't store stuff from database A in B and vice versa.
4. Select proper database with ACID and strong consistency for your Super Important Business Data™®.
5. Again, do not mix the data.

Answer 2

Using more than one database technology in one product is a decision one shouldn't make light-hearted. The more technologies you use the more complex your project will become in development, deployment, maintenance and administration. Also, every database technology will become an individual point of failure. That means it is often much wiser to stick to one technology, even when it means that you need to make some compromises.

But when you have good(!) reason to use multiple DBMS, you should try to keep them as separated as possible. Avoid placing related data spanning multiple databases. When possible, no feature should require more than one DBMS to work (preferably a failure of the DBMS would only affect those features which use it). Storing redundant data in two different DBMS should also be avoided.

When you can't avoid redundancies and relationships spanning multiple DBMS, you should decide on one system to be the single source of truth (preferably one which you trust most regarding consistency). When there are inconsistencies between systems, they should be resolved by synchronizing the data with the SSOT.