I am making an application here users will be able to post and like/dislike. In the backend, I am using Node and Mongoose. Now, suppose I have some 500k posts (or may be, millions, just for the sake of understanding). So, how do I fetch top trending posts? Sorting will require huge amount of time, right? What is the best way to do this?
Let me explain a bit further. Suppose, a user has made a post under category 'A'. Now, in order to fetch top posts in that category, I have to find the posts listed under that category first. Then I need to sort. This will take ages.
What are your suggestions for this? Any advice on whether Mongo is the right candidate for this use case?
I have two things to take care of.
I looked into Cassandra and Elasticsearch as well. For the given context, do you think these would provide better solution?
This is more like a system design question to be honest. Even with elastic search you will have to mark the analyzer on the basis of certain elements right so it's not like it is supposed to work on everything, you'll have to define it that way.
With respect to database, MongoDB, the best you can do is have indexes to aid the sorting, because if not that then the system will have to fetch those values in the WiredTiger Cache (WiredTiger = Storage Engine) and then sort them in memory, imagine the travesty that will cause :D
Most of the companies maintain a more granular control over things like this, based on expectations most of the things are pre-compiled, on the basis of tags for example in Twitter. And after it has run once you don't need to sort the whole thing again.
I have sorted a dataset on field A for example, do I need to sort all of it again for a new request? No : Just adjust the new entries. This adjustment will depend on what you want to show to the user.
All in all, an interesting problem to solve but heavily will depend on use case. Exact access pattern. Having said that ElasticSearch sounds like a good candidate but... it also will have its limitation. Focus on exact access patterns, like I mentioned already.
Edit as requested by OP.
So, how do I fetch top trending posts?
This doesn't depend on entirely just sorting your results, this is more dependent on explosiveness of the topic where rate has more importance.
Check this article here by Gilad.
Think of it where you check the rate of tags and words, you maintain a count on rate basis for that.
Similarly for your category, based on algorithm keep this piece isolated from just querying all the posts.
Amazon is not ranking products on the fly for a category for all it's dataset, is it? Think of it.
Pre-rank stuff and based on new addition, keep that part dynamic and merge them.
For example for category x => I have top 500 ready based on my algorithm, now for new data which has come in today, I use to algorithm to get relative rank and then merge top 500 with ranked content today and display the results.