Benchmarking read performance of PostgreSQL and MongoDB on same data sets
TL;DR:
I am busy right now with writing new microservice for web project and the target — is to create as fast microservice as possible. As programming language will be used GOlang (truly believe it’s super fast, and just perfect for such tasks). And in this article I will shed some light on reasons how was made decision to use certain database.
This topic isn’t new and you can find lot of benchmarks related to performance of these databases. But I didn’t manage to find any technical information about benchmarks (what, where, how, how much, what was used etc.) also no information about versions of databases (not every article which I’ve found provides this info) but maybe something has changed, maybe some DB works better now, and maybe my case is bit different from cases considered in other articles, so it’s my turn to benchmark these databases!)
In my microservice I need to store 2 entities (storage and file) with relationship 1 to many (super simple case).It will be 2 tables in postgres:
And in mongo will have document with embedded data:
Nothing special so far, but the interesting thing — is that how data will be received. Suppose we need to get information like this:
And to receive this exact information from both databases we have to run next SQL query in postgres:
and this aggregation query in mongo:
It’s unfair to benchmark postgres join versus simple mongo find, because mongo already have all information (as nested data) but postgres has to work… But with our query it’s gonna be more interesting!!!Each database have to perform almost same actions: to find data by non-indexed field count, to order by id and name, perform offset and limit and postgres must perform join when mongo must perform unwind.I believe it’s pretty rightfully!
Benchmarking:
All technical information about Docker, PostgreSQL, MongoDB, GOlang, data-dumps, data import and so on and so forth you can find in github repo.Please take a look, and check all steps or even perform this benchmark on your computer, or just review GOlang implementation for postgres and mongo.
Result:
For me, on my computer result looks like:
+-------------+-------------------+--------------------+| Benchmark # | PostgreSQL | MongoDB |+-------------+-------------------+--------------------+| query 1 | 8349 microseconds | 21721 microseconds |+-------------+-------------------+--------------------+
Conclusion:
It’s only my particular case, only my query, only read operation and my result on my computer… For your case you have to check your queries, your datasets, etc.
If you find any gap or mistake or your result is different — please provide any feedback. If you have some another interesting test cases— please let me know!
PS: I really like mongo and I’m looking how to speed it up — if you have any clue — please contact me!