I'm building filtering functionality and I have hard times making it scalable when it comes to maintaining compound indexes, so I hope to get some advice here.
The use case:
Assume collection of data with the following fields and the filter operation used for each:
- "date created" (">" start date, "<" end date),
- "first name" ("array-contains"),
- "company name" ("==")
User can filter data by either of those in any combination.
Like 1+2 or 2+3 or 1+2+3 etc
The problem:
Each of those queries require a compound index - 4 indexes in this case (1+2, 2+3, 1+3, 1+2+3)
But when we add 4th or even 5th property to filter by, the number of compound keys explodes exponentially. And then we need to add another collection...
Having the limit of 200 compound keys in firestore, it seems like this is not a viable option.
My thoughts
I was hoping to create a single compound index that includes all of the properties and then to provide some sort of a special value, kind of "any value" for those props the user don't want to filter by, but I can't figure out how to do that and not sure if it's even possible.
Kind of analogous to 'endAt(queryText + "\uf8ff")' in a sense that was used in RTDB when you need to filter data there (reference link).
Any suggestions on how to make filtering scalable? Or is it a bottleneck of firestore?
