In this lesson, you will learn how to determine the optimal number of indexes to balance rapid read performance against the overhead costs of write operations in document databases.
The Indexing Trade-off
In document-based systems, every index you create acts like an entry in a library card catalog. When you perform a read, an index allows the database to jump straight to the relevant document without scanning the entire collection. However, the "price" of this speed is paid during every insert, update, or delete operation.
Every time you modify a document, the database must also update every index that contains fields from that document. If you have ten indexes on a single collection, a single write operation triggers eleven distinct actions (the document update plus ten index maintenance tasks). If your system is write-heavy, an excessive number of indexes will create a performance bottleneck, leading to increased latency and potential locking issues.
Finding the Balance
To determine your optimal index count, follow these two steps:
- Identify Query Patterns: Use your monitoring tools to track which queries actually hit the disk. If an index is never used, it is purely a liability—delete it.
- Measure Write Latency: During your load testing phase, observe how write performance degrades as you add indexes. If your write-intensive operations (like logs or transaction streams) exceed your latency budget, start by consolidating your indexes.
For example, if you frequently query by userId and timestamp, you should prefer a single compound index over two separate indexes:
// Instead of two individual indexes:
db.orders.createIndex({ "userId": 1 });
db.orders.createIndex({ "timestamp": -1 });
// Use one compound index for better efficiency:
db.orders.createIndex({ "userId": 1, "timestamp": -1 });
This compound index satisfies queries for userId alone and the combined userId + timestamp filter, while reducing the number of index structures the database must maintain during a write. By favoring compound indexes that align with your most common access patterns, you keep your read speed high without overwhelming your write throughput.
Key Takeaway: Aim for the fewest number of indexes possible that still satisfy your query requirements; every index added is a direct tax on your write throughput.