Why use both NoSQL and SQL

2016-08-06

There are undeniable perks that come with using SQL or NoSQL. At first glance, the NoSQL approach seems very appealing due to its schema less architecture. It is the perfect database type choice if the properties of an object, such as a product, will vary along the development of the product and will stay open. This is the think-as-you-go method and it can be pretty useful if you are unsure of your data model or just want to keep it open.

In the case of our Blog plugin, and due to the far superior natural text search functions of MySQL 5.7, widely inspire by Sphinx Search Engine, it just made more sense to index the body of the document in this database, whereas the rest of the properties of the article document are stored in a MongoDB database. More than keeping open the article object without changing SQL tables, the MongoDB approach was also facilitating other aspects of the article queyring, used in the navigation by tag.

For our client, Rekuta, most of the storage goes into a MySQL cluster. We selected this database type to use MySQL geometric functions to easily calculate distance and radius between the physical location of the search and the results that would be returned, without traversing the entire tree with a map reduce like NoSQL would have us do. On the other hand, we keept some objects open like the user object, which has its core fundation in a SQL table and an open object for all its non disruptive properties in MongoDB.

Due to the hybrid architecture, a point of failure could exists if an entry is created in SQL but its extended properties could not be recorded in the NoSQL database. Another point of failure could simply be a Mongo crash with write concerns turned off. Because of this somewhat extermely rare occurances, all vital data are stored in SQL only and non impacting properties are stored in NoSQL.

MySQL 5.7 now offers navite JSON storage ala NoSQL and it is definitely something to consider for future iterations of our products.