3

I am designing for a new application and I don't want to suffer later for the performance that manges discussion threads (posts + replies, very similar to Facebook or StackOverflow posts).

I wonder which kind of data store / data format I shall choose to persist the threads. I looked for an answer to my question, but all I found actually was "How to tune an RDBMS design to handle this requirement"

But is RDBMS really the best fit for this ? Most answers I could find were somehow outdated or wanted to tune some legacy systems and they did not consider No-SQL DBs.

I think that handling big amount of requests using all proposed asnwers (like here and here for example) will hurt performance when data scales because of the need to ORDER BY clauses.

I thought about storing the entire thread as one json for the sake of fast read performance. But also I think it will make a problem for update, maintenance and traffic specially because I need to apply security roles on thread component (some users can see some replies, others not)

Actually I am not much into No-SQL DBs, I just worked slightly with hbase and SOLR and most of my experience is with RDBMS. I think that Document databases are well suited for blog posts but I have no hands-on experience with that.

Any recommendations about which kind of database technology would best fit such needs ?

Important note: I don't ask for recommendation on specific products or resources, but about arguments for the choice of technology (RDMBS vs No-SQL DB).

EDIT: Thanks to the answers below, I revisited the requirements in more details , they are as follows :

1- The data is a nested set of "Issues" and "Actions" and each one can have any number of comments (i.e. issues have actions and actions have issues , and each of actions and issues can have comments)

2- conversation can't handle more than five users (converastion is a set of "issues" and "actions" and their relative comments

3- only one conversation is active at a time per set of users

4- a subset of the conversation can include users other than the rest of the conversation (but not more than five)

5- System will be distributed (in the future)

6- It will good to use a new technology other than RDBMS -unless it hurts-

7- Frontend is mobile app

I think from the above that choosing a document DB will be better, specially for points 5 and 6 and also due to the fact that data -as described- is not relational and that modeling hierarchial data + enforcing joins will not be good when data scales.

again many thanks for all who helped and still open for any recommendations including changing the technology

  • 7
    If you are in doubt use a RDBMS. It is going to be fast enough for your purpose. – JacquesB Jan 13 '18 at 8:35
  • @jacquesb because that's where the op's experience lies? Or for other reasons? – svidgen Jan 14 '18 at 0:52
  • I do agree with the OP's note, by the way. This isn't off-topic. There are perfectly objective ways to compare and contrast RDBMS's and No-SQL DB's in a relevant manner here -- and to offer a reasonable course of action. This question is honestly far less a matter of opinion than the majority of questions we field here... – svidgen Jan 14 '18 at 1:17
6

Most proponents of NoSQL overstate the scaling/performance problem.

This is admittedly an oversimplified point of view, but one of the big reasons that NoSQL is popular is because Google uses it. If Google uses it, then it must be good. But Google has enormous data requirements. Internet search notwithstanding, their source control repository is so large that they had to write their own custom source control system. You will never have this problem.

The size of your data becomes an important factor in this decision, not with millions of records, but with billions or trillions of records. As long as your in the millions of records space, you will never have a problem with relational databases as long as you maintain them properly (i.e. have sensible indexes and normalized table design).

Where I work, we recently did a proof of concept project with Apache Hive. Apache hive is a NoSQL database. We were still in the millions of records space, but if it rolls out completely, it will be in the billions of records. The purpose of the project is to mine telemetry data for information about specific events. The reason NoSQL is a good choice for this is that the data is relatively flat, i.e. there are no relationships or joins to speak of, and the nature of the data resists attempts to index it. The Map/Reduce function that hive embodies is uniquely suited to this situation.

But if your data is in any way related to ordinary business operations (as is your described scenario), relational databases are almost always a more sensible choice. Data size becomes a factor only at Facebook scale or Google scale. If that scaling problem ever happens to you, it will be a good problem, and you'll have the money you need to solve it then.

I think you overstate the performance concerns. Relational databases are more than capable of providing adequate performance in most situations, and unlike our Apache Hive scenario, your situation is not novel.

Further Reading
RDBMS vs. NoSQL: How do you pick?

  • In addition to Robert's answer, These days, where even RDBS can be scaled out/up in a distributed fashion thanks to the DBaaS, choosing RDBS over NoSQL has become easier. The decision, as Robert suggests, lays on the requirement first and on performance later (when you are really suffering a leak of it). Try to foresee the data size first in order to dimension properly the solution. Scale it out/up later if need it. Or migrate it if the data size grows so much that RDBS becomes insufficient. That would be what we call here "Dying of success" – Laiv Jan 13 '18 at 19:33
  • Leaving aside my disagreements and criticisms with the content of your analysis, I think it would be good to include an explicit summary of the article. If that's what the rest of your answer is, it would be good to clarify that. On my read, it seems like I need to go read this article to understand your commentary on it -- which I don't think is your intention. – svidgen Jan 14 '18 at 1:21
  • @svidgen: OK, well the author of that blog discusses several other things that don't really have much to do with scaling and performance (which appears to be the OP's primary concern), so I've removed that reference from my answer, but included it at the bottom as "further reading." – Robert Harvey Jan 14 '18 at 17:13
  • many thanks to the comprehensive answer, but kindly check my edit and tell me what you think – osama yaccoub Jan 21 '18 at 13:38
  • None of your requirements preclude the use of an RDBMS, except of course for number six, which is really just a wish, not a requirement. – Robert Harvey Jan 21 '18 at 18:08
3

I'll give a bit of rationale for JacquesB's comment with which I completely agree.

First, for most domains RDBMSs can very likely scale to the load, and by the time this stops being true (if it ever does), you'll have a lot more understanding of your data and the queries you need to support (and presumably a lot more money). In other words, an RDBMS suffices for most use-cases for, at least, quite a while.

However, the real reason to use RDBMSs is flexibility. Using a typical key-value or document store means encoding a preferred way of accessing the data. For your example, this might mean that replies are child elements in a "post" document. This is great when you want data in just that format. It becomes far less great when you want, say, to get all the replies by a specific user. Now you have to walk over every post in the system and check all replies. In a relational database, this query would be trivial and, perhaps with the addition of a few indexes, would likely perform reasonably well. To get reasonable performance from the document store would require rearchitecting the data model, or, more realistically, storing a copy of the data organized by user. The latter is essentially manually creating an index. Sarah Mei's article outlines exactly how this plays out in detail.

Adding to the flexibility of RDBMSs, most are very featureful. Want to store JSON? Fine. XML? No problem. Need some full-text searches? Well, you should probably use a dedicated solution, but you can at least start meeting the need without changing technologies. Need integration with message queues, ORMs, Excel? No problem. Need more consistency? Use SERIALIZABLE transactions. Don't need that much consistency? Drop to READ COMMITTED, which is often the default! This monolithic, kitchen sink approach is one of the downsides of RDBMSs, but it definitely improves agility when you can use an out-of-the-box, integrated solution to, at least, prototype rather than needing to evaluate and integrate multiple 3rd-party solutions.

One of the things traditional RDBMSs do poorly that NoSQL solution usually do well is distribute. Virtually all of them can be used in a distributed way, but they are much more complicated to set up and maintain than most NoSQL solutions. So-called "NewSQL" systems, such as VoltDB, do give an experience much more like NoSQL solutions with regards to setting up distributed clusters. Of course, if you are using a database-as-a-service from a cloud provider, then you don't have to worry about this.

The place where I think most key-value/document stores fit is as a caching layer/secondary index. So, I actually do think you should "manually create indexes" using a NoSQL data store. I just think they should be "indexes" of data stored in a different system, typically an RDBMS. Another, closely related, way to think about this is the NoSQL data store is a materialized view of data stored elsewhere. This suggests a bit more generality: the "materialized view" may be sourced from multiple primary sources each the source of truth for their respective data, and they don't need to be RDBMSs, e.g. some might come from an LDAP server. RDBMSs do, generally, do very well as systems of record. Most NoSQL solutions usually should not be used as systems of record. This splitting of the work alleviates pressure on the system of record, while not asking NoSQL solutions to do things they aren't designed to do. Most of the problems with NoSQL solutions (weak consistency, redundancy, preferred access paths, limited or no integrity checking) don't matter when they are used this way.

Revisiting the earlier scenario: You're a year in to the project and you have 100,000 users. Your sole RDBMS database combined with standard web page caching is performing adequately until a viral article leads to a huge spike in traffic. Your RDBMS struggles to meet the demand. To avoid this in the future, you consider using a NoSQL solution to store AJAX responses in a ready-to-go manner for posts and replies. You realize this may mean that users see different looking reply threads at different times, but you accept this. By using sticky sessions, you can at least avoid the situation that a user fails to see their own reply after posting it. At this time you get the requirement to add a "Replies by User" feature. In a day, you write the simple SQL query and, after a bit of initial performance testing, add a few indexes. You don't expect this to be the subject of a spike in demand, so you just go directly against the RDBMS. Since you're using a snapshot-based isolation mode, this neither blocks nor is blocked by any other (read or write) request.

To directly answer your question, I'm pretty confident most comment systems are based on RDBMSs. It is very unlikely your use-case is an outlier where an RDBMS would not suffice. Usually the structure of the data is not a significant factor for choosing a database solution (full-text and graph-based data may be an exception). Usually what's more relevant is how the data will be used: issues such as latency requirements, consistency requirements, availability requirements, the cost of losing data, how independent the data is, and generally how you want to query the data. For most OLTP loads, including comment threads, RDBMSs make reasonable trade-offs for these issues.

  • many thanks to the comprehensive answer, but kindly check my edit and tell me what you think – osama yaccoub Jan 21 '18 at 13:38
  • Nothing in your edit changes my answer. 5 I addressed. 6 is not a requirement, but even if it was it is not an either/or choice as I illustrated in the answer. You can get familiar with a NoSQL document store while also using an RDBMS. (Also, if novelty was the main goal, NewSQL systems are even newer technology than NoSQL.) The rest of the requirements, including 1, are no issue for any of the major RDBMSs. – Derek Elkins Jan 21 '18 at 21:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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