Does the size of the value affect the size of the index in MongoDB?

user_4960410 0
Viewed 89

I have a set of IDs which are numbers anywhere between 8 and 11 digits long, and there are only 300K of them (so not exactly sequential etc.). These are stored in collection A.

I have a collection B with millions of entries in which every entry has an array of these IDs, and every array could have thousands of these IDs. I need to index this field too (i.e. hundreds of millions, potentially up to a billion+ entries). When I indexed it, the index turned out to be very large, way over the RAM size of the cluster.

Would it be worth trying to compress the value of each ID down from 8-11 numbers into some small alphanumeric encoded string? Or simply re-number them from e.g. 1 - 300,000 sequentially (and maintain a mapping of this)? Would that have a significant impact on the index size, or is it not worth the effort?

mongodb indexing
2 Answers

The size of your indexed field affects the size of the index. You can run a collStats command to check the size of the index and compare the size of your indexed field with the total size that MongoDb needs to create the index.

Mongo already performs some compressions on indexes so trying to encode your field in an alphanumeric encoded string is probably not going to have a benefit or a marginal one.

Using a smaller numeric type is going to save a small amount of size in your index, but if you need to maintain a mapping, it´s probably not worth the effort and it is probably overcomplicating things.

The size of the index for a collection with 300K elements only indexing the 11 digits ID should be small, something around a few Mb. So it´s very likely that you don't have storage or memory issues with that index size.

Regarding your second collection, if you reduce some bytes in each ID, you can get some reduction of the size of the index.

e.g. Reducing the size of each ID from 8bytes to 4bytes and having about 1Billion elements, you are reducing some GB the size of the index.

Reducing the size of the index and the collection B some GB could be an interesting save, so based on your needs, it could worth the effort to modify the IDs to use the smallest type possible. However, you could still have or you could have it in the near future if the collections keep growing, the memory issues due to the index not fitting in memory. So sharding the collection could be a good possibility.

user_25807730

Hashed Column index you can create which will give more or less same performance in case you interested more on saving the size of index.

You can check with some data how much % size of data you saving for index and performance impact and take decision

user_40157490
Related