0

We are currently running a Python web app querying a SQL MariaDB (through SQLAlchemy). Unfortunately we have run into some hard limitations of MariaDB (number of joins are limited to 61) and are looking for alternatives. Our database is not very large (generally under 2GB and growing slowly) and the entries could actually very well be broken down to simple json objects, which made us look at a NoSQL solution.

Initially this seemed like a great solution. Queries were super fast (as the DB fit into RAM) and we were happy (for a few days).

I came here to seek advice if the NoSQL DB is a recommended solution for our needs, though. So let's talk about the data:

The nature of our data is that multiple group of entries / rows generally reference back to the same object, pretty much as if you had multiple versions that relate to a single object.

Assume a document entry could look like this:

_id: 5a31...
description: Object
    location: "XYZ"
    name: "ABC"
    status: "A"
    m_nr: null
    k_nr: null
    city: "QWE"
    high_value: 17
    right_value: 71
more_data: Object
    number: 101
    interval: 1
    next_date: "2016-01-16T00:00:00Z"
    last_date: null
    status: null
classification: Object
    priority_value: "?"
    redundancy_value: "?"
    active_value: "0"

Imagine the description.location will often need to be grouped and sorted so that I can only display the $last entry for each of these grouped entries. A common query may look like this (in MongoDB):

db.getCollection('a').aggregate(
    [{ $sort:
        {"description.location": 1}
     },
     { $group:
        {_id: "$description.location"}
     }]
)

I have unfortunately found that this particular query takes very long, when the DB does not fit into memory - even with an index for description.location present in the MongoDB. (For some reason the $group aggregation operation never appears to be using the available index, while $sort actually does use it).

Either way, is this data / layout / query strategy something that resonates well with a NoSQL DB ?

closed as primarily opinion-based by LowlyDBA, Erik Darling, mustaccio, John Eisbrener, RDFozz Dec 15 '17 at 22:47

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    Not saying your data can't fit into a NoSQL solution, but if you're looking to move from RDBMS because of a JOIN limit then I think you're addressing the problem wrong. While 61 isn't impossible, I'd start with looking at if you really need to do that big of a query all at once and possibly evaluate other RDBMS products. NoSQL will be an entirely different beast all-around. It is no trivial thing to jump into a whole different, much younger (MongoDB specifically), database product type. – LowlyDBA Dec 15 '17 at 14:57
  • Thank you. We generally agree and are evaluating PostgreSQL as well as our query / table strategy, but due to the limitations of our cloud provider for out-of-the-box solutions (read: no PostgreSQL, only MariaDB), we are technically limited to what they offer. To be fair, I can of course deploy my own PostgreSQL server, but it won’t be smooth sailing. – Chris Dec 17 '17 at 20:03
2

I see your interest in JSON objects.

Our database is not very large (generally under 2GB and growing slowly) and the entries could actually very well be broken down to simple json objects, which made us look at a NoSQL solution.

MongoDB is better suited for huge databases, in your case, I suggest PostgreSQL.

You can use JSON types in PostgreSQL: https://www.postgresql.org/docs/current/static/datatype-json.html

Should be easy enough to migrate your app to PostgreSQL, because it's SQL all the way, and I doubt that you will find that stupid join limit.

Either way, is this data / layout / query strategy something that resonates well with a NoSQL DB ?

Performance has costs when using NoSQL, many times, you will have to modify your code, your database schema, your server configuration. Are you prepared to pay the price?

Answering your question, I try to avoid MongoDB aggregation, preferring to manually aggregate on the spot (my source code is my SQL), but I do it for small ranges. If doing the entire table/collection, would be better to use Map-Reduce or Aggregation or custom code? It depends, who is doing the job faster?

Most of the time, people will advise to denormalize your data, but doing this can cause another range of issues. Again, pay the price.

1

MongoDB documents are similar to JSON objects.The values of fields may include other documents, arrays, and arrays of documents.Even a record in MongoDB is a document, which is a data structure composed of field and value pairs.

{"field": "value"}

The advantages of using documents in MongoDB are:

  1. Documents (i.e. objects) correspond to native data types in many programming languages.
  2. Embedded documents and arrays reduce need for expensive joins.
  3. Dynamic schema supports fluent polymorphism.

While Aggregation operations group values from multiple documents together, and can perform a variety of operations on the grouped data to return a single result.

As in your case mention as aggregation code like

db.getCollection('a').aggregate(
    [{ $sort:
        {"description.location": 1}
     },
     { $group:
        {_id: "$description.location"}
     }]
)

Even from MongoDB 3.6 some of new aggregation operator Here has introduced like position operator & $expr, through which you can improve your query.

For further your ref Aggregation and Introduction to MongoDB

  • Thanks! I read those and we certainly were looking at a NoSQL solution due to the need to reduce the number of joins we are currently doing. How exactly can those operators improve my query ? – Chris Dec 14 '17 at 12:06
  • @Chris,$positional Operator,$expr Query Operator & Array Update Improvements , change streams,updateLookup and aggregation pipeline etc – Md Haidar Ali Khan Dec 14 '17 at 12:11

Not the answer you're looking for? Browse other questions tagged or ask your own question.