Community

Artur Costa

Discover the power of MongoDB schema optimization in the second part of The Cost of Not Knowing MongoDB series. This deep dive explores the impact of different data modeling approaches, focusing on the Bucket Pattern and Computed Pattern to enhance query performance and storage efficiency. Learn how grouping data by month or quarter can significantly reduce index size and improve read operations, while pre-computing aggregations can optimize reporting. With real-world benchmarks, load test results, and practical TypeScript code, this guide demonstrates how strategic schema design can maximize MongoDB’s potential.

The cost of not knowing MongoDB (V5RX) - Part 2

The focus of this series is to show how much performance you can gain when using MongoDB properly, following the best practices, studying your application needs, and using it to model your data.

The Cost of Not Knowing MongoDB

When developers or administrators who had previously only been "followers of the word of relational data modeling" start to use MongoDB, it is common to see documents with flat schemas. This behavior happens because relational data modeling makes you think about data and schemas in a flat, two-dimensional structure called tables.

In MongoDB, data is stored as BSON documents, almost a binary representation of JSON documents, with slight differences. Because of this, we can create schemas with more dimensions/levels. More details about BSON implementation can be found in its [specification](https://bsonspec.org/spec.html). You can also learn more about its [differences from JSON](https://www.mongodb.com/json-and-bson). 

MongoDB documents are composed of one or more key/value pairs, where the value of a field can be any of the BSON data types, including other documents, arrays, or arrays of documents.

Using documents, arrays, or arrays of documents as values for fields enables the creation of a structured schema, where one field can represent a group of related information. This structured schema is an alternative to a flat schema. 

Let's see an example of how to write the same `user` document using the two schemas:

![Comparing Flat vs Structured Schemas in a Document Database][1]

The two documents above contain the same data. The one on the left, `flatUser`, uses a flat schema where all the field-and-value pairs are on the same level. The one on the right, `structuredUser`, employs a structured schema where the field and values have nested levels according to related information inside the document.

So, what are the advantages of using a structured rather than a flat one? The quick answer for those in a hurry is that a structured schema may require **less storage** and be **faster to traverse** than a flat schema. For those who want to know why, we need a better understanding of BSON.

For the purpose of this article, a BSON document can be seen as a list of items, where each item represents a field-and-value pair of the document. An item is composed of the field’s `type`, `name`, `length`, and `data` in a serialized form. The field `type` is one byte long and indicates the data type in the `data` field. The field `name` is the field's name in a string form. The field `length` is four bytes long and indicates the length of the `data` field for those `types` where the size is not fixed. The `data` field is the actual data of the field-and-value pair. Putting this definition in a graphical representation, we have:

![BSON Document Structure][2]

Let's see how a structured schema uses less storage than a flat schema by analyzing the field-and-value pair related to the user's name.

In the `flatUser`, we have the following table from a storage perspective:

| **field-and-value**   | **Type** | **Field Name** | **Field Length** | **Field Data** | **Total**    |
| --------------------- | -------- | -------------- | ---------------- | -------------- | ------------ |
| name_first: "john"    | 1 byte   | 10 bytes       | 4 bytes          | 4 bytes        | **19 bytes** |
| name_last: "smith"    | 1 byte   | 9 bytes        | 4 bytes          | 5 bytes        | **19 bytes** |
| name_middle: "oliver" | 1 byte   | 11 bytes       | 4 bytes          | 6 bytes        | **22 bytes** |

Adding up the table's total sizes, the flat document uses 60 bytes to store the field and value related to the user's name.

To analyze the storage of the `structuredUser`, let's divide it into two tables. In the first table, we'll have the storage used by the document of the field `name`, and in the second table, we'll have the storage utilized by the field-and-value `name`. 

Let’s build the first table for the value/content of the field `name`:

| **field-and-value** | **Type** | **Field Name** | **Field Length** | **Field Data** | **Total Size** |
| ------------------- | -------- | -------------- | ---------------- | -------------- | -------------- |
| first: "john"       | 1 byte   | 5 bytes        | 4 bytes          | 4 bytes        | **14 bytes**   |
| last: "smith"       | 1 byte   | 4 bytes        | 4 bytes          | 5 bytes        | **14 bytes**   |
| middle: "oliver"    | 1 byte   | 6 bytes        | 4 bytes          | 6 bytes        | **17 bytes**   |

Adding up the previous table's total sizes, the value/Field Data of the field `name` uses 45 bytes. Building the second table for the field-and-value `name`, we get:

| **field-and-value** | **Type** | **Field Name** | **Field Length** | **Field Data** | **Total Size** |
| ------------------- | -------- | -------------- | ---------------- | -------------- | -------------- |
| name: { … }         | 1 byte   | 4 bytes        | 4 bytes          | 45 bytes       | **54 bytes**   |

The structured document uses 54 bytes to store the values related to the user's name.

Comparing the tables, we see the main difference is the "Field Name" storage size. The flat schema uses 30 bytes to store the names of its fields, while the structured schema uses 19 bytes to store the names of its fields. This is due to the repetition of the sub-string "name_" in the "Field Name" of the flat schema.

Storing the two documents in a MongoDB instance, we will get a size of 403 bytes for the flat schema and 307 bytes for the structured schema. Not bad getting a 24% improvement in storage just by changing the schema, and a structured document is easier to read and more pleasant to look at.

Now, let's see how a structured schema is faster to traverse than a flat schema by getting the zip code of the work address.

In the `flatUser` document, to get to the field `address_work_zip` starting at the beginning of the document, a cursor would need to perform a **12 field names comparison** until it reaches the desired field.

In the `structuredUser` document, to get to the field `address.work.zip` starting at the beginning of the document, a cursor would need to perform an **8 field names comparison**. The smaller number of comparisons here is due to some values of a field-and-value pair being a document. When the cursor checks the field `name`, it can jump three fields/comparison — `first`, `middle`, and `last`— because it knows that `address.work.zip` won't be inside of `name.<field>`. When the cursor checks the field `address.home`, it can also jump five fields/comparison — `street`, `number`, `zip`, `state`, and `country`.

To quantify the performance gain on traversing a structured schema instead of a flat schema in MongoDB, a test with the following methodology was used:

- To isolate the result to be influenced just by the traversing of the documents, the MongoDB instance used was configured with [in-memory storage](https://www.mongodb.com/docs/manual/core/inmemory/).
- Documents with 10, 25, 50, and 100 fields were utilized for the flat schema.
- Documents with 2x5, 5x5, 10x5, and 20x5 fields were used for the structured schema, where 2x5 means two fields of type document with five fields for each document.
- Each collection had 10.000 documents generated using [faker/npm](https://www.npmjs.com/package/@faker-js/faker).
- To force the MongoDB engine to loop through all documents and all fields inside each document, all queries were made searching for a field and value that wasn't present in the documents.
- Each query was executed 100 times in a row for each document size and schema.
- No concurrent operation was executed during each test.

Now, to the test results:

| **Documents** | **Flat** | **Structured** | **Difference** | **Improvement** |
| ------------- | -------- | -------------- | -------------- | --------------- |
| 10 / 2x5      | 487 ms   | 376 ms         | 111 ms         | 29,5%           |
| 25 / 5x5      | 624 ms   | 434 ms         | 190 ms         | 43,8%           |
| 50 / 10x5     | 915 ms   | 617 ms         | 298 ms         | 48,3%           |
| 100 / 20x5    | 1384 ms  | 891 ms         | 493 ms         | 55,4%           |

As our theory predicted, traversing a structured document is faster than traversing a flat one. The gains presented in this test shouldn't be considered for all cases when comparing structured and flat schemas, the improvements in traversing will depend on how the nested fields and documents are organized.

This article showed how to better use your MongoDB deployment by changing the schema of your document for the same data/information. Another option to extract more performance from your MongoDB deployment is to apply the common schema patterns of MongoDB. In this case, you will analyze which data you should put in your document/schema. The article [Building with Patterns](https://www.mongodb.com/blog/post/building-with-patterns-a-summary) has the most common patterns and will significantly help.

The code used to get the above results is available in the [GitHub repository](https://github.com/ArturGC/flat-vs-structured).


  [1]: https://images.contentstack.io/v3/assets/blt39790b633ee0d5a7/blt0d2f0e3700c6e2ac/65b3f5ce655e30caf6eb9dba/schema-comparison.jpg
  [2]: https://images.contentstack.io/v3/assets/blt39790b633ee0d5a7/blte533958fd8753347/65b3f611655e30a264eb9dc4/image1.png

Learn how to optimize the size of your documents within MongoDB by changing how you structure your schema.

Improving Storage and Read Performance for Free: Flat vs Structured Schemas

We are developing an application to summarize a user's financial situation. The main page of this application shows us the user's identification and the balances on all banking accounts synced with our application.

As we've seen in blog posts and recommendations of how to get the most out of MongoDB, ["Data that is accessed together should be stored together."](https://www.mongodb.com/developer/products/mongodb/schema-design-anti-pattern-separating-data/) We thought of the following document/structure to store the data used on the main page of the application:

```javascript
const user = {
    _id: 1,
    name: { first: "john", last: "smith" },
    accounts: [
        { balance: 500, bank: "abc", number: "123" },
        { balance: 2500, bank: "universal bank", number: "9029481" },
    ],
};
```

Based on the functionality of our application, we determined the following rules:

- A user can register in the application and not sync a bank account.
- An account is identified by its `bank` and `number` fields.
- The same account shouldn't be registered for two different users.
- The same account shouldn't be registered multiple times for the same user.

To enforce what was presented above, we decided to create an index with the following characteristics:

- Given that the fields `bank` and `number` must not repeat, this index must be set as [Unique](https://www.mongodb.com/docs/manual/core/index-unique/).
- Since we are indexing more than one field, it'll be of type [Compound](https://www.mongodb.com/docs/manual/core/index-compound/).
- Since we are indexing documents inside of an array, it'll also be of type [Multikey](https://www.mongodb.com/docs/manual/core/index-multikey/).

As a result of that, we have a `Compound Multikey Unique Index` with the following specification and options:

```javascript
const specification = { "accounts.bank": 1, "accounts.number": 1 };
const options = { name: "Unique Account", unique: true };
```

To validate that our index works as we intended, we'll use the following data on our tests:

```javascript
const user1 = { _id: 1, name: { first: "john", last: "smith" } };
const user2 = { _id: 2, name: { first: "john", last: "appleseed" } };
const account1 = { balance: 500, bank: "abc", number: "123" };
```

First, let's add the users to the collection:

```javascript
db.users.createIndex(specification, options); // Unique Account

db.users.insertOne(user1); // { acknowledged: true, insertedId: 1)}
db.users.insertOne(user2); // MongoServerError: E11000 duplicate key error collection: test.users index: Unique Account dup key: { accounts.bank: null, accounts.number: null }
```

Pretty good. We haven't even started working with the accounts, and we already have an error. Let's see what is going on.

Analyzing the error message, it says we have a duplicate key for the index `Unique Account` with the value of `null` for the fields `accounts.bank` and `accounts.number`. This is due to how indexing works in MongoDB. When we insert a document in an indexed collection, and this document doesn't have one or more of the fields specified in the index, the value of the missing fields will be considered `null`, and an entry will be added to the index.

Using this logic to analyze our previous test, when we inserted `user1`, it didn't have the fields `accounts.bank` and `accounts.number` and generated an entry in the index `Unique Account` with the value of `null` for both. When we tried to insert the `user2` in the collection, we had the same behavior, and another entry in the index `Unique Account` would have been created if we hadn't specified this index as `unique`. More info about missing fields and unique indexes can be found in our [docs](https://www.mongodb.com/docs/v5.0/core/index-unique/#unique-index-and-missing-field).

The solution for this issue is to only index documents with the fields `accounts.bank` and `accounts.number`. To accomplish that, we can specify a [partial filter expression](https://www.mongodb.com/docs/manual/core/index-partial/#std-label-partial-index-with-unique-constraints) on our index options to accomplish that. Now we have a `Compound Multikey Unique Partial Index` (fancy name, hum, who are we trying to impress here?) with the following specification and options:

```javascript
const specification = { "accounts.bank": 1, "accounts.number": 1 };
const optionsV2 = {
 name: "Unique Account V2",
 partialFilterExpression: {
   "accounts.bank": { $exists: true },
   "accounts.number": { $exists: true },
 },
 unique: true,
};
```

Back to our tests:

```javascript
// Cleaning our environment
db.users.drop({}); // Delete documents and indexes definitions

/* Tests */
db.users.createIndex(specification, optionsV2); // Unique Account V2
db.users.insertOne(user1); // { acknowledged: true, insertedId: 1)}
db.users.insertOne(user2); // { acknowledged: true, insertedId: 2)}
```

Our new index implementation worked, and now we can insert those two users without accounts. Let's test account duplication, starting with the same account for two different users:

```javascript
// Cleaning the collection
db.users.deleteMany({}); // Delete only documents, keep indexes definitions
db.users.insertMany([user1, user2]);

/* Test */
db.users.updateOne({ _id: user1._id }, { $push: { accounts: account1 } }); // { ... matchedCount: 1, modifiedCount: 1 ...}

db.users.updateOne({ _id: user2._id }, { $push: { accounts: account1 } }); // MongoServerError: E11000 duplicate key error collection: test.users index: Unique Account V2 dup key: { accounts.bank: "abc", accounts.number: "123" }
```

We couldn't insert the same account into different users as we expected. Now, we'll try the same account for the same user.

```javascript
// Cleaning the collection
db.users.deleteMany({}); // Delete only documents, keep indexes definitions
db.users.insertMany([user1, user2]);

/* Test */
db.users.updateOne({ _id: user1._id }, { $push: { accounts: account1 } }); // { ... matchedCount: 1, modifiedCount: 1 ...}

db.users.updateOne({ _id: user1._id }, { $push: { accounts: account1 } }); // { ... matchedCount: 1, modifiedCount: 1 ...}

db.users.findOne({ _id: user1._id }); /*{
 _id: 1,
 name: { first: 'john', last: 'smith' },
 accounts: [
   { balance: 500, bank: 'abc', number: '123' },
   { balance: 500, bank: 'abc', number: '123' }
 ]
}*/
```

When we don't expect things to work, they do. Again, another error was caused by not knowing or considering how indexes work on MongoDB. Reading about [unique constraints](https://www.mongodb.com/docs/manual/core/index-unique/#unique-constraint-across-separate-documents) in the MongoDB documentation, we learn that MongoDB indexes don't duplicate strictly equal entries with the same key values pointing to the same document. Considering this, when we inserted `account1` for the second time on our user, an index entry wasn't created. With that, we don't have duplicate values on it.

Some of you more knowledgeable on MongoDB may think that using [$addToSet](https://www.mongodb.com/docs/v6.0/reference/operator/update/addToSet/) instead of [$push](https://www.mongodb.com/docs/v6.0/reference/operator/update/push/) would resolve our problem. Not this time, young padawan. The `$addToSet` function would consider all the fields in the account's document, but as we specified at the beginning of our journey, an account must be unique and identifiable by the fields `bank` and `number`.

Okay, what can we do now? Our index has a ton of options and compound names, and our application doesn't behave as we hoped.

A simple way out of this situation is to change how our update function is structured, changing its filter parameter to match only the user's documents where the account we want to insert isn't in the `accounts` array.

```javascript
// Cleaning the collection
db.users.deleteMany({}); // Delete only documents, keep indexes definitions
db.users.insertMany([user1, user2]);

/* Test */
const bankFilter = { 
    $not: { $elemMatch: { bank: account1.bank, number: account1.number } } 
};

db.users.updateOne(
    { _id: user1._id, accounts: bankFilter },
    { $push: { accounts: account1 } }
); // { ... matchedCount: 1, modifiedCount: 1 ...}

db.users.updateOne(
    { _id: user1._id, accounts: bankFilter },
    { $push: { accounts: account1 } }
); // { ... matchedCount: 0, modifiedCount: 0 ...}

db.users.findOne({ _id: user1._id }); /*{
 _id: 1,
 name: { first: 'john', last: 'smith' },
 accounts: [ { balance: 500, bank: 'abc', number: '123' } ]
}*/
```

Problem solved. We tried to insert the same account for the same user, and it didn't insert, but it also didn't error out.

This behavior doesn't meet our expectations because it doesn't make it clear to the user that this operation is prohibited. Another point of concern is that this solution considers that every time a new account is inserted in the database, it'll use the correct update filter parameters.

We've worked in some companies and know that as people come and go, some knowledge about the implementation is lost, interns will try to reinvent the wheel, and some nasty shortcuts will be taken. We want a solution that will error out in any case and stop even the most unscrupulous developer/administrator who dares to change data directly on the production database 😱.

[MongoDB schema validation](https://www.mongodb.com/docs/manual/core/schema-validation/) for the win.

A quick note before we go down this rabbit role. MongoDB best practices recommend implementing schema validation on the application level and using MongoDB schema validation as a backstop. 

In MongoDB schema validation, it's possible to use the operator `$expr` to write an aggregation expression to validate the data of a document when it has been inserted or updated. With that, we can write an expression to verify if the items inside an array are unique.

After some consideration, we get the following expression:

```javascript
const accountsSet = { 
    $setIntersection: { 
        $map: { 
            input: "$accounts", 
            in: { bank: "$$this.bank", number: "$$this.number" } 
        },
    },
};


const uniqueAccounts = {
    $eq: [{ $size: "$accounts" }, { $size: accountsSet }],
};


const accountsValidator = {
    $expr: {
        $cond: {
            if: { $isArray: "$accounts" },
            then: uniqueAccounts,
            else: true,
        },
    },
};
```

It can look a little scary at first, but we can go through it.

The first operation we have inside of [$expr](https://www.mongodb.com/docs/manual/reference/operator/query/expr/) is a [$cond](https://www.mongodb.com/docs/manual/reference/operator/aggregation/cond/#mongodb-expression-exp.-cond). When the logic specified in the `if` field results in `true`, the logic within the field `then` will be executed. When the result is `false`, the logic within the `else` field will be executed.

Using this knowledge to interpret our code, when the accounts array exists in the document, `{ $isArray: "$accounts" }`, we will execute the logic within`uniqueAccounts`. When the array doesn't exist, we return `true` signaling that the document passed the schema validation. 

Inside the `uniqueAccounts` variable, we verify if the [$size](https://www.mongodb.com/docs/manual/reference/operator/aggregation/size/) of two things is [$eq](https://www.mongodb.com/docs/manual/reference/operator/aggregation/eq/). The first thing is the size of the array field `$accounts`, and the second thing is the size of `accountsSet` that is generated by the [$setIntersection](https://www.mongodb.com/docs/manual/reference/operator/aggregation/setIntersection/) function. If the two arrays have the same size, the logic will return `true`, and the document will pass the validation. Otherwise, the logic will return `false`, the document will fail validation, and the operation will error out.

The [$setIntersenction](https://www.mongodb.com/docs/manual/reference/operator/aggregation/setIntersection/) function will perform a set operation on the array passed to it, removing duplicate entries. The array passed to `$setIntersection` will be generated by a [$map](https://www.mongodb.com/docs/manual/reference/operator/aggregation/map/) function, which maps each account in `$accounts` to only have the fields `bank` and `number`.

Let's see if this is witchcraft or science:

```javascript
// Cleaning the collection
db.users.drop({}); // Delete documents and indexes definitions
db.createCollection("users", { validator: accountsValidator });
db.users.createIndex(specification, optionsV2);
db.users.insertMany([user1, user2]);

/* Test */
db.users.updateOne({ _id: user1._id }, { $push: { accounts: account1 } }); // { ... matchedCount: 1, modifiedCount: 1 ...}

db.users.updateOne(
    { _id: user1._id },
    { $push: { accounts: account1 } }
); /* MongoServerError: Document failed validation
Additional information: {
 failingDocumentId: 1,
 details: {
   operatorName: '$expr',
   specifiedAs: {
     '$expr': {
       '$cond': {
         if: { '$and': '$accounts' },
         then: { '$eq': [ [Object], [Object] ] },
         else: true
       }
     }
   },
   reason: 'expression did not match',
   expressionResult: false
 }
}*/
```

Mission accomplished! Now, our data is protected against those who dare to make changes directly in the database. 

To get to our desired behavior, we reviewed MongoDB indexes with the `unique` option, how to add safety guards to our collection with a combination of parameters in the filter part of an update function, and how to use MongoDB schema validation to add an extra layer of security to our data. 

Learn about how to handle unique documents in an array and some of the surrounding MongoDB unique index quirks.

Artur Costa

Content by Artur Costa

The cost of not knowing MongoDB (V5RX) - Part 2

The Cost of Not Knowing MongoDB

Improving Storage and Read Performance for Free: Flat vs Structured Schemas

Unique Indexes Quirks and Unique Documents in an Array of Documents