0

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:

  1. index every field: I feel like that will have enormous storage overhead

  2. create a collection for each customer: this keeps their collection small, making indexes not required until they have tons of data

  3. 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.

  • I'm not entirely sure how you organize your data without using a seperate collection for each user anyway (or maybe even per form, but I assume several forms display the same (user specific?) base data). Indexes have to be specified in the exact field order and sort order you are using later (so field1 asc, field2 asc is different from field2 asc, field1 asc and field1 asc, field2 desc). You probably don't want to create every possible combination, so you might want to allow the user to add (maybe a limited number of) specific indexes himself. – Solarflare Mar 26 '17 at 12:16
  • @Solarflare Right now we organize it in one collection with an index on the customer id. Giving users the option of adding indexes is not really a great user experience. Most users have no idea what an index is. – cdmckay Mar 26 '17 at 18:56
  • true about asking customers for indexes, but instead you can ask them which of the fields they want to be able to sort by (and only index those). Note that you can index one field and then if the sort is by that field plus another field, the first part of the sort will be supported by the index but second part (second field sort) will be done in memory - that might be a compromise to having way too many indexes. Depending on the size of the data for each customer, the answer may vary significantly. – Asya Kamsky Mar 27 '17 at 13:21
1

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.

  • Yeah, that's what I figure the best solution will be. Thanks! – cdmckay Apr 5 '17 at 17:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.