If you, a backend developer, had to describe your job, what would you say? We usually talk a lot about servers, clusters, layers, algorithms, software stacks, memory consumption, ...

We put data into databases and we get it back as fast as we can.
Databases are our cornerstone. Why don't we talk more often about them? We are always relying on our ORMs.

My best advice? Simplify your queries. Simplify your data models. Simplify your access patterns.

Performance in MongoDB

Metrics

If you are using MongoDB, these two metrics will be your best friends:

• Scanned Documents/Returned ratio
• IOPS

Scanned docs/returned: it means how many documents you are reading from disk vs the number of documents you are actually returning in your find() or aggregate(). Ideally: this should be 1 (every document read is returned). The only way to get it? All your queries must be covered by indexes. Indexes are in memory (or they are if they fit), so MongoDB doesn´t have to read and filter them from disk.

IOPS: I/O operations per second. That is, operations on disk. It is correlated with scanned docs, as they are read from disk. But there are more sources of IOPS, for example writes.
Your disk will give you a limit to your maximum IOPS. Ours is 3000.

Your goal is to keep IOPS below that threshold, and as low as possible. It is hard to know how many IOPS your query consumes, but it is easy to know the scanned docs/returned ratio.

Tools

How can you analyze why your database is behaving as it is?

MongoDB Profiler
If you can afford to enable it, do it now. It's the best source of info. We use the Atlas MongoDB Profiler and it is worth every penny.

Explain planner
The planner is the key to understanding your access patterns.
There are several ways of calling it, but you can start with explain() after your cursor:

db.our_collection.find(query).explain()

You can use add executionStats to get more data about the query.

db.our_collection.find(query).explain("executionStats")

There are many stages, but these ones are the most important:

• COLLSCAN: the query is scanning the collection in disk. Pretty bad, as no index covered the search, so MongoDB has to read the whole collection.
• IXSCAN: the query is using an index to filter. It doesn´t mean that all the query is covered by the index, but at least some part.
• FETCH: the planner is reading the documents from the collection. If your query is returning documents, you will get a FETCH stage probably (unless your query is covered by the index).

This is an example of one of our queries:

winningPlan: 
      { stage: 'COLLSCAN',
        filter: 
         { '$and': 
            [ { '$or': 
                 [ { 'invitaed_user_id': { '$eq': '1' } },
                   { owner_id: { '$eq': '1' } },
                   { 'player_id': { '$eq': '1' } } ] },
              { is_canceled: { '$eq': false } },
             ] } },

This is not good, as it is scanning the whole collection.

Another one:

db.our_collection.find(query).explain("executionStats")
executionStats: 
   { executionSuccess: true,
     nReturned: 22,
     executionTimeMillis: 0,
     totalKeysExamined: 24,
     totalDocsExamined: 22,
     executionStages: 
      { stage: 'FETCH',
        nReturned: 22,
        docsExamined: 22,
        inputStage: 
         { stage: 'OR',
           nReturned: 22,
           inputStages: 
            [ { stage: 'IXSCAN',
                nReturned: 0,
                indexName: 'example-index-1',
                indexBounds: 
                 { owner_id: [ '["1", "1"]' ],
                   start_date: [ '[MaxKey, MinKey]' ] } },
              { stage: 'IXSCAN',
                nReturned: 22,
                indexName: 'example-index-2'
                indexBounds: 
                 { 'player_id': [ '["1", "1"]' ],
                   start_date: [ '[MaxKey, MinKey]' ] ] },
                keysExamined: 23,
                dupsDropped: 0 }] } } },

Here you can two see IXSCANs, merged by and OR. After that, the query is fetching the documents. I am reading the query inside-out. example-index-1 is used to resolve one part of the query, and example-index-2 for the other part.

Sometimes you will get a FETCH just after an IXSCAN: it means that the index covers only part of the filter. After that, the planner needs to read the documents from disk to finish the filter.

The complete list of stages? You need to check the code.

Simplify your queries

$ors

$ors are the devil. You, as a programmer, are used to thinking in $ors. You add a few $ors, your condition gets much more expressive. But guess what? Your query has gotten exponentially more complex. With every condition you add to the $or, you are adding one more combination of parameters.

How does the planner resolve all those combinations? It needs an index for each of them.

Do you remember the explain() above? The query was:

{
    $or: [{"owner_id: "1"}, {"player_id: "1"}].
}

The indexes used?

example-index-1: {"owner_id: "1", "start_date": 1}
example-index-2: {"player_id: "1", "start_date": 1}

And here is another tip: bigger indexes can cover smaller queries as long as the fields are at the beginning of the index.
We are not using start_date to filter.

What would happen if I add an extra $or?

{
    $or: [{"owner_id: "1"}, {"player_id: "1"}].
    $or: [{"is_canceled": true}, "start_date": {$gt: ISODate("2022-02-02T00:00:00)}]
}

Then there would need 4 combinations:

{owner_id, is_canceled}, 
{owner_id, start_date}, 
{player_id, is_canceled], 
{player_id, start_date}, 

Denormalized fields

If you find yourself filtering by several fields within an $or, or sorting by several fields, consider adding a denormalized field (based on the others).

Yeah, my apologies to the third normal form.

Keep reading, we will give an example pretty soon using the so-called Summary Pattern.

nulls are always the minimum value

This is a minor trick, but still useful. Let's say you have a nullable field, and you have to filter (or sort descending) by that field.

You will probably find yourself using a query similar to this:

{
 $or: [
        field: {$exists: false}, 
        field: {$lte: value}
      ]
}