What we want to do

In our application customers can create their own reports (CSV, Excel, PDF). Within the application they can combine up to 30 fields for filtering and up to 20 fields for individual sorting (asc & desc). A preview is immediately shown.

History and Future

We develop and run the application for several years now. In the past we used the NoSql RavenDB 3.5 and it was very easy to put all data in an index. Querying the fields was no problem and the result was quite fast. E.g. showing the preview of 20 custom sorted and filtered results took less than a second.

For the successor product we started to switch to MongoDB because of some issues with RavenDB in the past. MongoDB was our first choice because we prefer NoSql (easy setup, no ORM, cluster, performance, stability) and a great community and a supported .NET driver was available. We made a simple POC during evaluation and everything seems to work fine. Maybe it was too simple.

How does our data look like?

We have to query over 50 Million documents in one collection and its structure looks like:

public class Customer {
    public Metadata Metadata { get; set; }

    [BsonId]
    [BsonRepresentation(BsonType.ObjectId)]
    public string Id { get; set; }
    [BsonRepresentation(BsonType.ObjectId)]
    public string PersonId { get; set; }
    public int OrderIndex { get; set; }
    public DateTime EventTime { get; set; }
    public string[] Scopes { get; set; }  // hierarchical data e.g.: 12.345.678 or 12.340.000
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public DateTime DateOfBirth { get; set; }
    public string Sex { get; set; }  // male or female or unknown   
    public string[] Tags { get; set; }
    public bool IsLocked { get; set; }
    public Address[] Addresses { get; set; }
    public int? SomeAmount { get; set; }
    public Relation[] Relation { get; set; }
}

This is an extract of the current state (projection) of a customer. We process thousands of change events per day and store the state in a collection. In our real data structure we have over 40 properties. The data structure looks very similar to the old application. In fact, that should make a migration more easily.

Use Cases and issues

We offer a UI with up to 30 fields which can be combined, sorted and scored.

Possible Use Cases:

1. Find all persons older 30 and male sorted by LastName ASC, FirstName ASC, DateOfBirth ASC
   • Query: DateOfBirth + Sex; Sort: LastName, FirstName, DateOfBirth
2. Find all persons living in New York tagged with 'funny' sorted by LastName DESC, FirstName ASC, DateOfBirth DESC
   • Query: Tags + Address; Sort: DateOfBirth
   • Attention: Sort order changed. Address is in an array.
3. Find all locked persons sorted by SomeAmount DESC
   • Query: IsLocked; Sort: SomeAmount

As you can see, there are several combinations. Therefore we cannot use one index only because of the following limitations:

• strict order of fields in a compound index (see)
• max. 32 fields in a compound index
• mixed sort orders of fields in index (see)
• single collection can have no more than 64 indexes (Currently no issue)

Summary

• Total documents: 50 Million
• Average Document Size: 3KB
• Properties: >40 (incl. nested types and multiple arrays)
• Queryable fields: ca. 30 (maybe more)
• Freely sortable fields: up to 20 in any combination and direction (ASC/DESC)

Technical Requirements

• .NET Core
• MongoDB .NET Driver

Questions

1. What can we do to query 1-n fields in arbitrary sort order?
   • Multiple indexes in every kind of field order? Hmm, seems to be very expensive (disk, performance) and don't forget: max. 64 indexes per collection!
2. What if our customers perform 40 reports at the same time?
   • Sort is performed entirely in RAM that can cause a massive performance impact. What is a good strategy to handle that kind of load?
3. What should I do if we have to query more than 32 fields?
   • Add multiple indexes and use index intersection? Does that work for all kind of combinations?
   • Split our document in multiple parts and use references stored in another collection? Than we have to work with $lookup. Hmm, this will probably not perform very well.
4. Is MongoDB the right database for such complex queries/reports?
   • As you can see, we have been thinking about for a while but nothing seems to fit our needs.