Imagine finding a DBMS that aligns with tech goals of your organization. Pretty exciting, right?
Relational databases held the lead for quite a ti...
For further actions, you may consider blocking this person and/or reporting abuse
Can we have your queries, not just a invalid bench without data ?
Also note, not tuning a SQL engine and comparing with MongoDB which doesn't require real tuning is not valid, you need to tune the MySQL/PostgreSQL buffer for reading & writing performance, especially on a such AWS instance which has many RAM.
PostgreSQL isn't benched and is far far more better than MySQL & PostgreSQL in terms of features, robustness and security (yes PostgreSQL has IPv6, enabled SSL by default without being crazy & CRAM authentication).
PostgreSQL json backend is faster than MongoDB for unrelational data, see enterprisedb.com/node/3441
Another point, there is no transactions with NoSQL, especially mongodb, your data can be totally broken, and the upgrade scheme after using a NoSQL during years, which moving data scheme can make your apps crazy, because you have to handle all cases, or suffer from crazy bugs.
We also compared PostgreSQL 9.6 with MongoDB 3.2 to bench new huge application we developped for production (~15 SQL hard relational tables with big constraints) and the performance/CPU cost is 1.5 lower using PostgreSQL mixed relational & JSON backend than MongoDB.
Last, a reality we see in our production, MongoDB is not safe, it doesn't ensure data is correctly written, and some data can be lost on huge workload whereas relational data really ensure data is here. The mongodb replication is not really safe, it's the major risk factor.
That is an interesting comparison of NoSQL databases.
I don't understand though, both of them have quite flaky but existing nonetheless JOIN implementations. MySQL has SQL-inspired
JOIN
clauses while MongoDB has$lookup
, right?Yes, Mongodb has a join since 3.2, but do not take it lightly, it's not a 1:1 replacement. The feature is part of the aggregation framework, you will not want to aggregate data for each user request, it's not meant to be used like this because it needs more resources to return the result.
To avoid these you usually make subdocuments and keep the data in a single big object so you do not require a join.
If your data can be easily put in an excel like table then you shortly use a relationship DB.
My motto is: if you can put it in a table then put it in PostgreSQL. If you can't, then put it in PostgreSQL anyways, it has indexed JSON fields after all.
I guess my line would be to 'if you can put it in NoSQL then do that' and I come from SQL and Oracle.
Interesting to know that
$lookup
is heavy though, I did use it without noticing performance issues but that's still something to consider.The lookup alone I don't think adds roo much, but usually you need other ops.
And try the test on a large db, eventually with sharding then you'll see the out of memory issue 😀
large
remains to be defined but yup I see the idea"To avoid these you usually make subdocuments and keep the data in a single big object so you do not require a join. "
That's a sure-fire way to make your database unusable down the road.
A nice article as an intro but ...
Readers, keep in mind you should ignore the VS compares,they are not 2 excluding or replacing technologies, they are usually used in the same project for different data types. Other storages are keyvalue like redis, object storage like Amazon S3, file storage and Graph databases. Other times you'll need the entire history and use an event sourcing DB.
Do not try to squeeze all your requirement into a single bucket, use them all if needed,based on each system module needs.
As for large databases I suggest to read about the new cloud dbs like
They're very different kinds of storage system -- I think the bottom line on these things is that if you're relying on simple X vs Y guidance to choose your storage layer then you're honestly not competent to make the choice.
Nice post, I recently hosted a meetup on SpringBoot and MongoDB where I discussed similar arguments.
In it I touch topics like Comparison with SQL, Schema Design, Performance improvements & GeoSpatial indexes/queries. It is based on M101J from MongoDB University.
Give it a look here: slides.com/tonnoz/bloggo
It includes exercises on Github too. It may be a good start point for who used Mongo superficially or only with ODM.
Almost 47 seconds in query sounds pretty much for me in comparation with 4 secs from MySql. In a operational system maybe it worths a try Mongo but for informational systems, when you have much more than 1MM of registers it really is a bad idea to choose MongoDB.
I'm concerned by the misleading chart used in the header. The rankings are given in logarithmic scale, for some reason, making Mongo's adoption seem closer to MySQL's than it really is.
When choosing from MongoDB vs MySQL, it ultimately depends on your project's needs:
MySQL is ideal for structured data, where schema consistency and relationships are critical. It's reliable, with robust performance, and is great for complex queries and transactions.
MongoDB excels with unstructured or semi-structured data, offering flexibility in schema design. It's better suited for handling large volumes of data and allows for faster development when the data structure isn't rigid.
Both have their strengths, so your choice should align with your data's structure and your scalability requirements.
Thx !
MySql query is still much faster than MongoDB
Not even close. Lots of benchmarks on google show Mongo hugely faster than MySql in both queries and inserts, unless you're using simplistic myisam. Here's one:
github.com/webcaetano/mongo-mysql