MonoDB vs PostgreSQL performance comparison. Part II: Index

Continued, start here .

Experiment II: Index

For this experiment, we created indices in the id and floatvalue fields (we omitted the text fields, we will not touch on the topic of the full-text index, since this is material for a separate article). Samples from ranges were used as queries:
')

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

But first, you need to assess how much the insertion speed has dropped after adding indexes. To do this, add another 250,000 entries to MongoDB and 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 simple calculations, it can be understood that MongoDB remained the undisputed leader in the speed of insertion: after adding indexes, its insertion speed dropped by only ~ 10% and amounted to 3600 objects per second . Whereas, PostgreSQL insertion speed dropped by ~ 30% and amounted to about 536 records per second .

I would like the sampling situation to be similar. Perform the following requests:

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 the operations, the situation with the samples changed in favor of PostgreSQL:



It is also worth noting that when sampling not from a range, but with indication of specific digits (for example, floatvalue=1234567.76545 ), both DBMS showed a result in 0 milliseconds. Therefore, such operations are not even considered here. This is the question of the reasonable use of indexes in accordance with the planned conditions of the sample. Here, indexes and queries are used only for the purpose of load testing.

Another revelation was that when using MongoDB indexes, the consumption of processor time was so drastically reduced (up to 1-2% against 30-40% when searching without indexing), which even outperforms PostgreSQL (down to 4-14% against 5-25). % ).

Results

Before summarizing something, I share it, as promised by the resulting table and resource consumption diagrams for queries:







And now about the results.

With the naked eye, you can immediately notice one advantage of MongoDB over PostgreSQL: insert speed . It is almost an order of magnitude higher, both with and without the use of indices. Moreover, the use of indexes does not significantly reduce it (only ~ 10% compared to a 30% reduction for PostgreSQL). This is really an excellent result! But ... how often do you use the insert relative to the sample (for various conditions)?

When sampling from a collection without indices, MongoDB is also in the lead, although not so significantly. Not bad! But ... how often do you work with tables without indexes?

Do not think that with my questions I am trying to turn you away from noSQL DBMS. Tables without indices (I do not mean primary) have a place to be in certain solutions. The priority of insertion speed for some tasks is also very real and, moreover, is sometimes very much in demand. The question is, does this specifically need you? Right for your current task? This (very superficial) testing is not intended to answer the, I will not hide, quite popular question “Which is better than SQL or noSQL?”. It is intended to bring you to thinking, to assess the needs and possibilities when choosing one or another solution for a particular task.

Finally, I will say that we, for example, use both types of DBMS, depending on the data structure, goals, and options for working with them. An integrated approach is much better and allows you to work as optimally as possible with any data.