We conducted a performance test on MongoDB for one of our scenarios. We set up a test collection containing 100,000 records with fields like _id, userId, offerId, and createdAt. Specifically, for each unique offerId, we added 20,000 documents and created an index on the offerId field.
Mongo DB instance config : 2 core CPU and 16 GB ram AWS instance.
Our goal was to execute an aggregation pipeline with the following stages:
- Filtering by offerId ("QRSTWVTZ").
- Grouping the results, calculating counts based on specific conditions involving userId.
- Further filtering to include only records meeting specific count criteria.
- Replacing the root document with new data.
- Merging the results.
During testing, we observed that when a particular offerId had 20,000 records and was subjected to a load test at approximately 90 requests per second (RPS), the average response time for the aggregation pipeline ranged from 200 to 300 milliseconds. This was despite having indexes on both offerId and userId, which were utilized in the aggregation pipeline.
However, when we tested an offerId with only 1,000 records out of the total 100,000, the response time improved significantly to 20-30 milliseconds. Even when performing a simple find query, like find({ offerId: "123" }), for an offerId with 20,000 documents, the response time was around 40-50 milliseconds. The response time decreased when the offerId had only 1-2 thousand documents, despite having an index on offerId.
Questions:
- should we consider SQL db like (postgres/mysql) for such scenarios as it will be more performant