Home PostgreSQL Comparison of MongoDBvs PostgreSQLperformance.Part II: Index

Comparison of MongoDBvs PostgreSQLperformance.Part II: Index

by admin

Continued, beginning here

Experiment II: Index

For this experiment, we created indexes on the fields id and floatvalue (we omitted the text fields, we will not touch the subject of full-text indexes, because this is material for a separate article).Samples from the ranges :

  • 10 000 < id < 100 000
  • 200 000 < floatvalue < 300 000

But first, we need to estimate how much the insertion speed dropped after adding the indexes. To do this, let’s add another 250, 000 records each to MongoDBand POstgreSQL.
MongoDB

Insert 250000 records complete! Total time: 69.453 sec

PostgreSQL

psql -d prefTest -f 250k.p5.sql (Total time: 466.153 sec)

After some simple calculations we can see that MongoDB is still the undisputed leader in insertion speed: after adding the indexes its insertion speed dropped by only ~10% and was 3600 objects per second Whereas PostgreSQL’s insertion rate dropped by ~30% and was about 536 records per second
I would like the sampling situation to be similar. We execute the following queries :
MongoDB

  1. db.tmp.find({$and:[{id:{$gt:10000}}, {id:{$lt:100000}}]})
  2. db.tmp.find({$and:[{floatvalue: {$lt:300000}}, {floatvalue: {$gt:200000}}]})

PostgreSQL

  1. select * from tmp where id> 10000 and id<100000
  2. select * from tmp where floatvalue<300000 and floatvalue> 200000

However, after comparing the speed of operations, the situation changed in favor of PostgreSQL:
Comparison of MongoDBvs PostgreSQLperformance.Part II: Index
It is also worth noting that when sampling not from a range, but with specific numbers (n.p. floatvalue=1234567.76545 ) both DBMS showed a result of 0 milliseconds. Therefore, such operations are not even considered here. This is to the question of reasonable use of indexes according to the planned sampling conditions. Here, however, indexes and queries are used only for the purpose of load testing.
Another revelation was that when using indexes, MongoDB reduced CPU time consumption so dramatically (to 1-2 % vs. 30-40 % searches without indexing) that it even overtakes PostgreSQL in this (down to 4-14% vs. ).

Results

Before I summarize, I am sharing, as promised, the resulting table and charts of resource consumption by request:
Comparison of MongoDBvs PostgreSQLperformance.Part II: Index
Comparison of MongoDBvs PostgreSQLperformance.Part II: Index
Comparison of MongoDBvs PostgreSQLperformance.Part II: Index
And now for the results.
One advantage of MongoDB over PostgreSQL can be seen at a glance: insertion speed It is almost an order of magnitude faster with or without the use of indexes. Moreover, using indices does not reduce it that much (only by ~10% vs. 30% reduction in PostgreSQL). This really is an excellent result! But. how often do you use insertion relative to sampling (by all sorts of conditions)?
When sampling from a collection without indices, MongoDB also leads, though not as much. Not bad! But. How often do you work with tables without indexes?
Don’t think that I’m trying to turn you away from noSQL DBMS with my questions. Tables without indexes (I don’t mean primary) have their place in one solution or another. The priority of insertion speed for some tasks is also very real and, moreover, is sometimes very much in demand. The question is, Do you need it specifically? Specifically for your current task? This (very cursory) testing is not meant to answer the, I’ll admit, rather popular question "Which is better than SQL or noSQL?" It’s meant to get you thinking, to evaluate your needs and capabilities when choosing a solution for your task.
As a final note, I will say that we, for example, use both types of RDBMS, depending on the data structure, purposes and options for working with them. The integrated approach is much better and allows us to work optimally with any data.

You may also like