SQL and NoSQL in one project

Question

MySQL

I would like to know the opinion of knowledgeable people about crossing SQL solutions (MySQL) and NoSQL (MongoDB) in one project. On the one hand, it’s somehow not according to Feng Shui to use both at once. But on the other hand, separately, each solution does not fully satisfy the needs.

On the one hand, the project must use a very large number of "documents" with a variable structure (a different number of fields (search for which will be required)) and different sizes (from 200 bytes to 200 kilobytes on average) and this requires a large speed with them. Therefore, MongoDB is very well suited for this. In addition, a fairly easy cluster organization to increase the speed of work and fault tolerance (Replica Sets + Sharding)

On the other hand, the project requires a lot of well-structured data that is very closely related to each other. And storing them in non-relational databases is simply not advisable and it is rather inconvenient to extract information (restoring links may require hundreds of subqueries). And everything related to the removal, in general, will cause a lot of inconvenience. At the same time, this data is the minimum number (compared to the number of “documents”). So all work can be easily organized through one MySQL server.

If you use only Mongo, then there will be many problems when working with related data.
If you use only MySQL, then there will be problems with extensibility, speed and data storage

Who thinks whether it is necessary to use both approaches at once or still come up with something else? Has anyone used this in practice?

PS The project is an ordinary web service, but it must be able to withstand heavy loads and have good horizontal scalability.

Answer 1

>> On the one hand, it's somehow not Feng Shui to use both at once
Here, you just need to decide what is more important for you - Feng Shui or timeline.

Answer 2

According to my project, I’ll say that it would be done now, I would do it only on MongoDB. The main problem is search. It is impossible to do a search in one database and another at the same time, but how to search for the intersection of data? That is, we need, for example, to find user profiles (SQL with connections) with red noses and green ears (Mongo). As a result, we need to either make a query in the RDBMS, and then, using the received id, search for the mongo with parameters, or vice versa, search for the parameters found in the mongo in MySQL (or another database).
If there is not much data, then there is no particular problem, but when the amount of data of the same type grows, the number of records that needs to be dragged from one database to another also increases.
Now the search will be moving to a more ideologically kosher sphinx, but the project will be rewriting to the full MongoDB.

Answer 3

Perhaps you are missing another factor - the killer feature of many relational databases (although this is not related to the sql / nosql classification, but in practice it is) is ACID support. By using transactional and non-transactional storage together you end up with a common non-transactional storage… This is the only BUT when using your approach. In general, it is rather difficult to advise, since there are such options:
1) Use only MongoDB. What-no requests he has there. And perhaps in practice everything will not be as scary as you think. For example, “hundreds of subqueries” may be faster than a heavy sql nickname with joins.
2) Use only RDBMS. Storage of documents can be implemented, for example, in the form of XML. Many databases, I don’t know about MySQL, but Postgresql even allows you to make queries using XPath. Scaling should be done by handles on the client with the storage of meta-information “where which set lies” in some Riak.

Answer 4

> somehow not Feng Shui to use both
Why do you think so? If because there are two warring camps of fans, then some like to break spears and troll, others like to work.
By the way, this is also stated on the Mongi website www.mongodb.org/display/DOCS/Use+Cases

Answer 5

They use memcache (also a key-value storage) along with RDBMS - it’s just that each of the storages is responsible for its own, for what it does better.

Answer 6

On the one hand, it’s somehow not according to Feng Shui to use both at once
. And we immediately read what we mean by NOSQL. Then we take our brain, put it in its place and understand that for each task you need to use your own tools.
On the other hand, the project requires a lot of well-structured data that is very closely related to each other. And storing them in non-relational databases is simply not advisable and it is rather inconvenient to extract information (restoring links may require hundreds of subqueries).
Are you by any chance trying to transfer the model of working with RDBMS to Document Oriented Databases?