11 ms·
Comparison of Joins: MongoDB vs. PostgreSQL
- throw_m239339 7y agoCan someone who is using MongoDB and a RDBMS at the same time tell me the exact use cases where MongoDB is fitter? Structured logs? Caching? Sessions? And why MongoDB does it better?
- mixermachine 7y agoRelational DBs for everything that needs joins. MongoDB is horrible with that and should not be used. One would not put an off-road vehicle on a race track and expect it to win. I use MongoDB for everything where I don't know the data structure before hand. MongoDB is very good in indexing unknown data structures and makes them searchable. Also, when I build prototypes I often use MongoDB.
- anthony_doan 7y agoCluster is very easy in MongoDB and for unstructured data. I've looked into clustering postgresql a few time and it seems like a pain in the butt. TBH, storing data without thinking about relation is just kicking the can down the road. My previous company store coupon code on it (referrals). I prefer a hash nosql for that though (cassandra) but for a one man shop it's overkill. --- edit: IIRC postgresql have their own document storage like MongoDB. I just never had to use such thing because I guess I'm too OCD about how my data suppose to look like.
- airocker 7y agoThe biggest one is searching jsons. You essentially have a database that stores jsons and you can search it. Just dump a json and search it goes a long way. My problems are more the atomicity guarantees which are not very consistent.
- tasubotadas 7y agoIf you picked mongodb and you are using joins, you've picked a wrong tool for a job.
- Beefin 7y agoright, this is an apples to oranges comparison. you're comparing the feature that RDBs do best to the feature that NoSQL DBs do worst...
- ldng 7y agoLike NoSQL proponent, never do that. Worst offender often beeing Mongo (although they calmed down a bit since "Web-scale" days ...)
- AmericanChopper 7y agoThe most common use case for Mongo is storing relational data for web apps, and the most common outcome for those apps is complete dysfunction. How do I know that’s the most common use case? Aside from my own anecdotal experience, you just need look at the features those DBs implement. They’re constantly implementing new RDBMS-like and ACID-like features. They wouldn’t be doing that if that wasn’t their market. Why does this happen? Because people who don’t know better keep thinking that they can somehow work around the constraints, and just make a mongo (or dynamo) RDBMS work somehow. It is an apples to oranges comparison. But it’s worth making, because of the large number of people who think it’s ok to substitute oranges into apple pie recipes.
- Beefin 7y agoNone of that matters because your data needs to be modeled around your application usage patterns. If you maintain inter-collection relationships via Joins in Mongo just as if you were in Postgres, you're not doing it right.
- AmericanChopper 7y agoYour schema needs to be modelled around your domain, because that will dictate what your application usage patterns actually are. That will determine wether using a document database is feasible or not. If not, then there isn’t any design cleverness that can save you.
- daneel_w 7y agoThis was quite the lopsided comparison - a document store just doesn't get fair competition in a relational setting. Additionally, both the SQL and the MongoDB queries appear unnecessarily complicated (or inefficient) for the examples they illustrate.
- throwanem 7y agoThis does feel like an article that would have made more sense in 2013 or so, back when the habit of mistaking a document store for a database was much more widespread.
- d_watt 7y agoIs this actually an objective truth? "relational" just refers to the relation within the table, it's less about having a database system with good inter-table relationship handling. It happens to be that a lot of the things we think about as relational are OLTP and have good fkeys, etc. Is there any real reason why a document store can't have good joins between collections? If you index the lookup keys, I don't understand what about the underlying document model would make joining impossible to optimize as well as a relational model.
- alexpetralia 7y agoI don't think most document stores enforce foreign key relationships (and hence consistency) like RDBMS do, so in practice that would make joins difficult.
- d_watt 7y agoIs joining on an fkey any more performant than without it assuming the same indexing is applied? When you say difficult do you mean from a developer ergonomics standpoint (knowing there's data integrity), or from a database optimizability standpoint?
- alexpetralia 7y agoThe former. You have a guarantee of consistency, whereas otherwise you must adjust for this in your application code. Database performance wise I agree they are equivalent.
- stepbeek 7y agoWhile I think it's easy to poke fun at this article - relational database better at handling relational data, more at 10 - it does make me want to understand why one would choose mongo over postgres?
- mixermachine 7y ago- Faster development speed - Unknown content of the object that should be stored (and run indexes on this content)
- nicoburns 7y agoYou can do that by using a JSONB column in postgres (including indexes), and that way you can upgrade to typed columns at yout leisure (and on a per-column basis). Personally, having inherited a database that wqs absolutely chock full of bad data due to the lack of schema, I'd be very reluctant to go that route unless I really needed to.
- mixermachine 7y agoThis is a side feature for Postgresql and other relational dbs. MongoDB focuses on especially this. I would not mix the two concepts. Split of the relational data and make it fast with relational DBs. Everything else goes in MongoDB and if fields always occure, they move into the relational DB.
- Ididntdothis 7y agoI would mix the two. JSON columns allow you to deal with unstructured data in Postgres so you don't have to manage two databases. I much prefer that.
- alexbanks 7y agoI agree with you on the original premise of "Why would you use Mongo over Postgres?", managing two DBs is terrible. But if you replace Mongo with something like DynamoDB, I think it gets more complicated. You don't really "manage" DynamoDB as much as just define what you're trying to do, as it's a managed service. I quite enjoyed using DynamoDB for some specific tasks at work, much more than I've enjoyed PostgreSQL's JSON columns.
- psychometry 7y agoWe're still talking about MongoDB? Why?
- strbean 7y agoMongoDB is web scale. [1] [1] https://www.youtube.com/watch?v=b2F-DItXtZs https://www.youtube.com/watch?v=b2F-DItXtZs
- alexbanks 7y agoBecause lots of people are still using it. In Q1 2020 they 89m in revenue, up from 50m in Q1 2019. Just because something is bad technically doesn't mean people will immediately abandon it. See: php.
- liquidify 7y agoOk everyone, calm down. I think it is obvious that the author knows that he is comparing apples to oranges here... but that isn't the point. The point of this article was to quantify the difference. His work accomplishes that for these specific tests very well. Yes, we know relational systems are better in these cases, but it is useful for those considering no-sql solutions to see this kind of hard data.
- diddid 7y agoThis should really be titled “A comparison of aggregates”.
- ahachete 7y agoCo-author here. Thank you all for your comments. While I understand is tempting to easily conclude that this is apples-to-oranges or that "obviously a relational database is better at data relationships", I recommend to dive deeper into the topic. First of all, almost all data models you may think of are in some or another way relational (call it "related data", if you prefer). So any database, RDBMS or NoSQL or whatever, will need to deal with data that is related to other bits of data. So this is, from this perspective, very apples-to-apples IMHO. Second, and regardless of the above statement, the exercise here is to model a data model that is, necessarily, two (or three) pieces of related data. And actually we considered the two models that MongoDB themselves consider for data modelling: embedded and reference. This is explained in the post and references to official MongoDB documentation are provided. Third: benchmark is open and public. The repository also contains all the query examples used in the post. If anyone considers that there's a better way to represent this data model (problem statement), that would still answer all the "business" questions (queries) considered in the post, please send a Merge Request to the repository. We will be very happy to analyze it. All in all, this is an exercise of data modeling, for a given dataset. MongoDB requires data modeling, is not "schema-less", and this is pervasive through their official documentation. The reference model, and their $lookup operator, are not a corner case nor a hidden downplayed option. It is a first-class operation, well advertised. But its complexity, performance and rewriting needs to adapt to changes are what are not, apparently, advertised, after our observations.
- verst 7y agoI would really love to see a direct comparison for scaling MongoDB and PostgreSQL. When do I usually need to scale up / out in either database? How do I assess that? And how do I actually go about scaling? Specifically I would love to know how to scale up and scale out (what are the different multi-node options and what are the consistency / replication delay guarantees)? The last time I used Mongo in production (2013) most startups eventually had to switch databases or hire a consultant to scale. This has always stuck with me.
- gshulegaard 7y agoI have spent most of my (limited) career scaling PostgreSQL with (IMO) surprisingly good results. Most recently I have been using Citus and it has been ridiculously easy to shard data. To be fair I have only been exposed to workloads of a few Terabytes of data ingest a day so YMMV. Unfortunately, I can't compare to Mongo as I have had somewhat limited experience with it.
- matchy 7y ago> can’t compare to Mongo as I have had somewhat limited experience with it Doesn’t seem to stop most people here.
- BenoitP 7y agoA basis for the apples to oranges argument is that you don't use MongoDB when you need JOINs. To me, this almost never happens. You almost always need JOINs, otherwise your data is sterile and useless. What point is there to talk about entities devoid of relationships to other entities? Data is always relational; we don't live in a 1-dimensional world. The only way I see it being useful is for scaling purposes. But there are way better, more stable, more resistant to failure kv stores out there.
- kthejoker2 7y agoOnly one person in this comment thread mentioned CAP theorem, which is the (highly informal*) rationale for NoSql's existence and also its deliberate aversion to join dependencies (which more or less violate the core principles of eventual consistency modeled distributed data stores.) Useful links for erudition: https://arxiv.org/pdf/1302.0309.pdf https://arxiv.org/pdf/1302.0309.pdf https://codahale.com/you-cant-sacrifice-partition-tolerance/ https://codahale.com/you-cant-sacrifice-partition-tolerance/ https://martin.kleppmann.com/2015/05/11/please-stop-calling-databases-cp-or-ap.html https://martin.kleppmann.com/2015/05/11/please-stop-calling-... https://www.somethingsimilar.com/2013/01/14/notes-on-distributed-systems-for-young-bloods/ https://www.somethingsimilar.com/2013/01/14/notes-on-distrib... https://www.microsoft.com/en-us/research/publication/replicated-data-consistency-explained-through-baseball/?from=http%3A%2F%2Fresearch.microsoft.com%2Fpubs%2F157411%2Fconsistencyandbaseballreport.pdf https://www.microsoft.com/en-us/research/publication/replica...