Benchmarking read performance of PostgreSQL and MongoDB on same data sets
TL;DR:
In first article I’ve provided overview how was made decision to use certain database for one particular case (perhaps a way rare case). But after received messages and comments and feedbacks it feels like that case was considered as general. So I’ve improved and extended my benchmarks and I’m going to share updated information with you.
Not changed
Data schema remains the same (2 entities: storage and file, with relationship 1 to many).
postgres data example:
mongo data example:
Changed
Imported into databases 2500 entries as storage data and 24705 entries as file data (was 500 and 4924**).**
Added more queries, in this benchmarking.Considered not only query1 from previous benchmarking:
query1 postgres:
query1 mongo:
but also added bunch of else queries.
Updated databases versions: _PostgreSQL_10.0 → 10.5, MongoDB3.4.9 → 4.0.1.
Added benchmarks which measure spent time for next steps:
v1— connect into db, get data from db, print data.v2— get data from db, print data (don’t measure connection time).
Benchmarking:
All technical information about Docker, PostgreSQL, MongoDB etc. you can find in github repo.
Result:
For me, on my computer result looks like:
+-------------+--------------------+--------------------+| Benchmark # | PostgreSQL 10.5 | MongoDB 4.0.1 |+-------------+--------------------+--------------------+| v1.query1 | 34137 microseconds | 52020 microseconds || v1.query2 | 20691 microseconds | 14950 microseconds || v1.query3 | 15952 microseconds | 14410 microseconds || v1.query4 | 18025 microseconds | 15389 microseconds |+-------------+--------------------+--------------------+| v2.query1 | 33596 microseconds | 43352 microseconds || v2.query2 | 20072 microseconds | 1711 microseconds || v2.query3 | 14750 microseconds | 573 microseconds || v2.query4 | 17727 microseconds | 1678 microseconds |+-------------+--------------------+--------------------+
I’ve omitted many benchmarks from this table because in simple queries mongo just faster than postgres.
Conclusion:
Only in my query1 postgres works faster than mongo (because of $unwind) for all other queries mongo works a way faster than postgres.
The question: which database is faster for your particular case?The answer: you have to benchmark!)
PS: If you have interesting queries for benchmarking — please, let me know.