-1

Intro

I am stuck with the limitations of the DBs I've tested* and need someone experienced to give me some advice.

Project details

Data sets

The project has 2 sets of data, the sets of items and their correlated prices.

Items are the list of all the supported items with their properties.

Prices are the list of item's price data for many countries where each country may have multiple stores (shops) that offer the item. This one-many-many (item-country-store) relation makes it hard to flatten the document into a single set of data where each item has prices embedded as properties.

Main goal

The user should be able to get a list of items with their lowest price offer after applying preferred filters and sort.

The user should be able to filter by item properties (such as item.title LIKE 'The %', item.rating > 8 or other filters) AND/OR price properties (such as price.value <= 10.00, price.store IN (1,2), price.discount >= 50 or other filters).

The user should be able to sort by either the selected item property (sort by item.title ASC or item.rating DESC) or the price property (sort by price.value ASC, price.discount DESC)

The response needs to be fast so the user can quickly paginate through the list. I suppose the DB caching, pre-sorted collections (such as ArangoDB views), pre-aggregated (filtered) collections (such as MongoDB on-demand materialized views) or some other solution is a must, but correct me if I am wrong. Relying on just indexing proved to be slow in the DBs I've tried since there are around 150 000 items with the total records of prices easily going over a million of records per country.

Limitations

I cannot afford to use enterprise (proprietary) or cloud licenses so I would have to stick to open source (community) DB projects.

Question

Based on your experience, which DB would you recommend for this use case and why?


* I've tested MariaDB, Redis (with RedisJSON module) and ArangoDB.

MariaDB

Since it's a relational DB, it's too slow to JOIN more than 10 different tables to get a single list. I use MariaDB to store the data, but I need another solution for the frontend serving.

Edit

I did not give details about my MariaDB implementation because I thought it was irrelevant since I asked for a NoSQL solution. Looks like this is the part that everyone wants to know about.

I said it was too slow because the items and prices data types in MariaDB are spread out to multiple tables.

In MariaDB, item has 17 related tables with mostly one-to-many relations. Think of properties such as item.genre, item.category, item.rating and others. Price has up to 6 relations with mostly one-to-one relations.

When flattened to JSON documents, everything fits into two data sets, hence I said items and prices in NoSQL.

Because of the complexity of relations, just building one whole item should take longer in SQL (even with the simplest ID index relations) than fetching one document from NoSQL. This is especially because of a lot of one-to-many relations, which would take more than one query to build the arrays.

I am not an expert and I do not claim to be one so correct me if I am wrong.

Redis

Redis with its RedisJSON module looked like a great candidate because it's an in-memory DB, but there were too many limitations to make it a viable option.

Because of the prices complexity, I am unable to flatten the data into a single JSON document so I have multiple roundtrips to get the "JOINed" results. The complexity of multiple roundtrips escalated when I had to introduce filtering by both data sets—I had to filter prices to see which items had an offer within the wished ranges, then filter items to see which of those with an offer were within wished item ranges, then, for example, sort by item.title, and finally get the best corresponding price details.

I lost performance since I had to use FT.AGGREGATE with SORT by the grouped property—this made Redis work on results without the use of index.

Final nail on the coffin was Redis being single-threaded. When I ran multiple queries at the same time, I saw queries go over 10 seconds and time out.

ArangoDB

ArangoDB was a great candidate until I saw missing data in the results.

It has neat caching, inverted indexes with pre-sorted collections in views and even full-text search on the collections.

I was able to separate prices into collections for each country and then create multiple views to include all possible sort options.

The pre-sorted view on price.value was huge because I was able to always get the lowest price offer just by doing a FIRST() in the subquery (AQL syntax similar to LIMIT 1 in SQL).

The queries were very fast once the results got cached.

The main issue with ArangoDB I had was getting the nulls in the results array while I was doing UPDATEs* on the collections.

Examples...

Query without update:

[{"id":"348","t":"Item 348"}, {"id":"112","t":"Item 112"}, {"id":"225","t":"Item 225"}]

Query when update starts:

[null,null,null]

Mid-update query:

[null,null,{"id":"225","t":"Item 225"}]

I thought it was an issue because I would first run a subquery to get the candidate document _keys and then run the main query with those _keys, and if there was an UPDATE, the document might not exist during that short time span. But because an UPDATE should not first DELETE and then INSERT the document (the way the REPLACE usually works), I doubt this is the case. I couldn't get the answers from the support.

This issue made it unreliable because the user would get nulls as the results, which would also break my frontend app.

* ArangoDB has the ImportDocuments function to import multiple documents at once with an option to UPDATE on duplicate.


Note

I have also considered ElasticSearch, but I would not be able to afford it if the project grows and needs any kind of scaling.

5
  • 2
    "MariaDB: Since it's a relational DB, it's too slow to JOIN more than 10 different tables to get a single list." no, you just need to get better at indexing. NoSQL is not faster because of no joins, it's mainly faster because of no locks and "Eventual Consistency". Postgres and MySQL are other options not mentioned, as is Microsoft SQL Server Express, all of which are free. You are railroading yourself by only looking at NoSQL. Commented Aug 26, 2022 at 11:13
  • Either way, this is essentially off-topic for Database Administrators because of "Shopping list question" Commented Aug 26, 2022 at 11:15
  • 1
    Where does the "JOIN more than 10 different tables" come from, if your "project has 2 sets of data"?
    – mustaccio
    Commented Aug 26, 2022 at 11:40
  • @Charlieface I have edited the MariaDB part. Also, I did not mention Postgres and MySQL because I mentioned only the DBs that I tested. For indexing, I guide myself with O'Reilly's High Performance MySQL, 2021 edition. One-to-many relations would still require more roundtrips, is that correct? Commented Aug 26, 2022 at 14:34
  • No, why would it? You can do it all in a single query, or you can do a multi-statement batch. Commented Aug 26, 2022 at 17:28

1 Answer 1

0

Agreed with all the comments on your post so far. And actually you can emulate exactly the same benefits of a NoSQL database in a modern relational database, so NoSQL is not any faster at all than a relational database.

The objects you've described, Items and Prices, for the use cases you have, sound like you should be using a relational database system. You have a very standard set of use cases.

One of the main benefits of a NoSQL database is a flexible schema. If your use cases were that your schema changes at high frequency, or you're not in control of the schema (such as ingesting data from multiple external data sources), then NoSQL might be worth considering.

Your post is very generalized and needs more details. As mustaccio pointed out, you initially only talked about two objects Items and Prices but then later go on to say "JOIN more than 10 different tables", which is confusing. You should instead list out all objects at play, their properties, and how they relate to each other.

You also mention things like "Since it's a relational DB, it's too slow" and other generalized statements about speed without giving an actual example, based on the objects and use cases, of how long a query took and what your goal is in regards to time.

Finally, as Charlieface mentioned in the comments, the way your question is currently worded can be considered a shopping list question which unfortunately is not appropriate for this forum. If you focus on one database system, e.g. MariaDB, and update your post targeted to more concrete details as I mentioned, you can instead ask for help with improving performance. (Some of the techniques and features you've mentioned, such as materialized views, already exist in most modern relational database management systems.)

Here's some other StackOverflow answers to help educate on when to use a NoSQL database or not:

  1. What exactly is an unstructured data and why to use Non-relational DBMS for that?
  2. How to tell if a project needs a NoSQL database solution?
  3. Choosing between NoSQL and Relational databases while having both flexibility and consistency requirements
8
  • Thanks for your answer! I've edited the question and gave more info about the MariaDB implementation (explains the JOINs). For comparison on the speed, MariaDB took around 4 seconds with filters applied, while ArangoDB takes around 120 ms without cache and 15 ms with cache. The goal is under 500 ms. It is not a shopping list, it is item comparison application. Does that change anything with your answer? Commented Aug 26, 2022 at 14:27
  • @DeliciousBacon I'm sure the 4 seconds you saw in MariaDB was without proper tuning that would've met you goal otherwise. So it is a misunderstanding to say MariaDB is slow when you're not using it correctly. Don't feel bad though as this is a common mistake many make when starting to get involved with databases. Also your statement "just building one whole item should take longer in SQL...than fetching one document from NoSQL" is misconceived too. What you're comparing are design choice efficiencies not technological ones. You can make a flattened table in a SQL DB just the same as NoSQL.
    – J.D.
    Commented Aug 26, 2022 at 14:33
  • @DeliciousBacon I'll also re-emphasize, despite the ability to design your database the same way regardless if you use a NoSQL or SQL database system, I don't believe in your particular use case you should need to flatten your entire data structure to see the performance you're looking for. You don't have a lot of data (a million records is nothing), and you don't have a number of JOINs that are outside the realm of reality. So I'd encourage you to pick a database system (MariaDB is a fine one), design it out, test, record the queries you're stuck on, and post a targeted question for them.
    – J.D.
    Commented Aug 26, 2022 at 14:41
  • Well, for indexing, I guide myself with O'Reilly's High Performance MySQL, 2021 edition. If it's worth anything, indexes show up in query EXPLAINs the way I expect them to—covering all columns by order and matching (full/partial matching). The extra layer that slows down the queries are BIGINTs around prices, which I need because the data requires it. Commented Aug 26, 2022 at 14:44
  • 1
    @DeliciousBacon Again I think you might be misunderstanding a few things on how such a design choice is structured. "Flattening one-to-many relations in SQL looks like an overkill for me" - It's no different than doing so in a NoSQL database like MongoDB, which stores a flattened copy of the data with redundant data points. There's literally no difference in structure or how you would programmatically build that structure. But again, I don't see any reason you'd need to design your database that way based on the information you've provided so far. I'd recommend a relational design.
    – J.D.
    Commented Aug 26, 2022 at 16:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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