If you create a normal index on the field meta that only exists in one document in a collection that contains 1.1B docs, you will have 1.1B entries in your index because indexes in MongoDB create one entry for each document. So one entry contains the value for that ONE meta field and everything else contains “NULL”.
That’s why we have sparse indexes and partial indexes.
Add the sparse option on your index and you won’t index all the documents that do NOT contain that field. You will notice that your index size will be a LOT smaller. You will only be able to use this index if you filter using this field (see doc). It won’t work if you try to get the docs that DON’T have this field, as they are not even in the index by definition.
Regarding the RAM issue, I see that you have really large indexes. Indexes need to be entirely in RAM to be efficient. As a reminder, MongoDB needs RAM for:
- the OS
- indexes
- working set
- queries (including aggregation, sort in memory, etc).
As a rule of thumbs total DB sizes * 15 to 20 % = RAM
So for example. For a 200 GB database, 30 to 40GB of RAM is about right. You can take exemple on the Cluster Tiers available in Atlas to get an idea of the ideal sizes.
Your indexes look very large compared to your data size for this collection. Which version of MongoDB are you using?
Cheers,
Maxime.