How to support arbitrary column sorting in a database? 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.
You don't need index to sort data. Because this customers data must have customer_id included to every document, just create index for that customer_id. You make query with that customer_id and set sort parameters according what customer wants data to be sorted. Sorting is done in memory at mongodb node (or nodes, if you have cluster). Of course, if customer have more data than 32MB, sorting without index is impossible. With aggregate and allowDiskUse you can do sort of very large results.