Making sure our database queries are fast
Tags: database, index, mongodb
Published: 2023-03-22 11:03:33

As Woovi scales, we have more and more charges and transactions. A charge represents a payment request, and a transaction represents a movement in our merchant bank accounts. These two collections in our MongoDB database are very big and still growing very fast.

To make sure, we don't push new code that causes a `COLLSCAN` (reading the whole table), we always need to make sure our queries are using the proper index.

## Understand MongoDB Explain Output

We first need to understand how MongoDB plan their queries.
For this we are going to use the command `db.collection.explain()`, you can read more about it in the [MongoDB docs](https://www.mongodb.com/docs/manual/reference/explain-results/)

It can have 5 different operations:

- `COLLSCAN` for a collection scan
- `IXSCAN` for scanning index keys
- `FETCH` for retrieving documents
- `GROUP` for grouping documents
- `SHARD_MERGE` for merging results from shards
- `SHARDING_FILTER` for filtering out orphan documents from shards

`COLLSCAN` is the worst case, as it needs to scan the whole collection to find an item, aka, O(n), very slow for big collections like ours.
`IXSCAN` is the best operation, as it uses the index a balanced B-tree, in the worst case O(log(n))
`FETCH` is when they need to retrieve more documents, used in pagination
`SHARD_MERGE` is when you have a shared database and need to merge result of some shards
`SHARDING_FILTER` is when you need to filter data in shards.

## Automating IXSCAN index testing 

We use an In-Memory MongoDB in our tests to make sure our code is doing the right queries and aggregations in the database. This avoids many false positives and false negatives that happen when mocking the database.

As we are using a real MongoDB, we can call `.explain()` method when performing a find or aggregate and MongoDB will return the query planner showing the winning plan of how the database will execute the query.

The test to check if our query is using an index, called `IXSCAN` in MongoDB, it is very simple:

```jsx
const getStageFromExplanation = (explanation: MongoExplanation) =>
  explanation.queryPlanner.winningPlan.inputStage.stage;

it('should test the ixscan from myquery', async () => {
  // force model index creation 
  await Model.syncIndexes();

  // create your test query
  const modelCursor = Model.find({
    name: 'Edu',
    age: '18',
  });

  // ask to explain
  modelCursor.explain();

  // wait for explanation
  const modelExplained = await modelCursor;
 
  //check if the query explanation uses index - IXSCAN
expect(getStageFromExplanation(modelExplained)).toEqual('IXSCAN');
});
```

We perform a find, ask for explanation using `.explain` method and check for IXSCAN in the winning plan of the query planner.


## To sum up
As you scale, your database is getting more critical, so you can't push new code to production that will use a COLLSCAN, or do slow queries.
At Woovi we automated this performance check to make sure we are using proper index before pushing new code to production.
We also monitor performance regression using Kibana and Elastic APM both in staging and production environment.


## References

- [Explain Results](https://www.mongodb.com/docs/manual/reference/explain-results/)
- [MongoDB Explain](https://whitestonedev.com.br/mongodb-explain/)


---
Woovi
[Woovi](https://www.woovi.com) is a Startup that enables shoppers to pay as they like. To make this possible, Woovi provides instant payment solutions for merchants to accept orders.

If you want to work with us, we are [hiring](https://woovi.com/jobs/)!

---
Photo by <a href="https://unsplash.com/de/@shiroscope?utm_source=unsplash&utm_medium=referral&utm_content=creditCopyText">Shiro hatori</a> on <a href="https://unsplash.com/photos/WR-ifjFy4CI?utm_source=unsplash&utm_medium=referral&utm_content=creditCopyText">Unsplash</a>