Problem
I have a use case where I need to build a Faceted search Navigation in a webApp. As an Example lets take a fictional e-commerce site which sells the Shoes. For Simplicity let's consider the shoe can be filtered at Brand, Color and Category. So, the Faceted navigation looks this ways :-
Brand(10):
===========
Nike(6)
Puma(2)
Adidas(2)
Color(10):
===========
Red(4)
Blue(2)
White(4)
Category(10):
=============
Sneakers(3)
Running(3)
Climbing(3)
If any filter is applied on the Brand & Color, then it should not remove all the Brand & Color options in the filter options. It should remove the options following way :- For Brand, remove filter options which do not fall in the categories selected in Color. For Color, remove filter options which do not fall in the categories selected in Brand. For Category, remove filter options which do not fall in the categories selected in Brand & Color.
My Solution
option1 - Elastic Search: I tried building the DataModels & Queries to solve this using Elastic Search. My index Schema & Queries exactly match with the solution discussed in this Blog.
But the main limitations I feel using OpenSearch(AWS flavour of using ES) is, my current data size is just around 1MB. It may evolve to 10GB in very long future. So, min storage requirement is 10GB x 2 x 1.45 = 29 GB (as per the formula in https://docs.aws.amazon.com/elasticsearch-service/latest/developerguide/sizing-domains.html). If split into 3 shards now, would amount to 300KB per shard. The ES documentation mentions that tiny shards consume large amount of CPU and memory.
Is this really a Con? Do any faced CPU issues due to small Shard Size?
Option2 - MongoDB I tried building the document same way as I did in the ES. This blog recommends the same incase if we wanted to create generic Indexes. The problem with MongoDb is if multiple filters are applied on data, it may cause an index-intersection problem & may impact the performance. This blog (pretty old blog, but I think the problem is still not completely optimised according to docs) explains the Index intersection problem. Do MongoDB facets solve this problem? If yes can you share how is the performance in case of getting the data from a collection which satisfies filter A & filter B & filter C & so on.
Can anyone comment Which database is best option for the use case considering the amount of data size. Also, what are the pros & cons in using ES vs MongoDB vs anyOther.
Thanks In Advance!