Our product has a "table view" feature that allows customers to display their data in tabular form.
That data is made up of form fields the customer creates, and thus there can be an arbitrary number of fields that aren't known ahead of time.
Right now, this is implemented in Mongo using an array of "column" objects. This works fine right now because we don't allow sorting by arbitrary fields.
What we would like to do is allow our users to sort by any columns, in any order.
For example, if a customer had fields f1, f2, f3, then we would like to allow them to sort by (f3 desc, f1 asc, f2 desc).
So far, the possible solutions I've thought of are:
index every field: I feel like that will have enormous storage overhead
create a collection for each customer: this keeps their collection small, making indexes not required until they have tons of data
combo: make a collection for each customer, and only index them when they pass a certain size
We are not 100% tied to using Mongo, so if there's a database that is capable of doing this naturally, we're open to switching.
Are these the only options in Mongo, or are there better suited databases for this task?
Also, if it's helpful, we are also using PostgreSQL as our canonical data store.