How do indexes affect write performance in a collection that handles 5,000 inserts per second?

Question

Accepted Answer

At a rate of 5,000 inserts per second, indexes have a dramatic and direct impact on write performance. For every insert operation, MongoDB must not only write the document itself but also update **every index** on the collection with the new document's keys . This means that with 5 indexes, a single insert triggers 6 separate write operations (one for the document and five for the indexes). This overhead scales linearly with the number of indexes, making the difference between a smoothly running system and one that buckles under load . Each additional index introduces measurable latency to write operations. Industry benchmarks and production experience indicate that adding an index can increase document save times by approximately **10-15% per index** for simple indexes, with compound or multikey indexes imposing even higher overhead . At 5,000 inserts per second, a single extra index could translate to 500-750 additional operations per second that the storage engine must handle. This overhead comes from multiple sources: B-tree traversal to locate where to insert the new key, potential page splits in the index structure, and additional I/O operations . A collection with 5-10 indexes could see insert latency increase by 50-200% compared to an unindexed collection. ```javascript
// Without indexes (only _id index): 1 write operation per insert
// With 5 additional indexes: 6 write operations per insert

// Impact on 5,000 inserts per second:
// 1 index   → 5,000 writes/sec
// 3 indexes → 15,000 writes/sec 
// 5 indexes → 25,000 writes/sec

// Each index operation requires:
// - B-tree traversal (CPU)
// - Potential page splits (CPU + I/O)
// - Index key insertion (I/O)
// - Write-ahead logging (I/O)

// To measure actual impact:
db.currentOp() // Check for write contention
db.collection.stats().indexSizes // See index sizes
db.setProfilingLevel(2) // Profile slow operations
``` **Index Type Complexity**: Compound indexes require updating multiple fields per insert, increasing overhead proportionally to the number of indexed fields . Unique indexes add extra validation steps to ensure uniqueness before the insert can complete . **Document Size and Structure**: Larger documents with many indexed fields increase the amount of data that must be processed per insert. Nested fields and arrays (multikey indexes) can create multiple index entries per document, dramatically amplifying write work . **Storage Contention**: With high insert rates, indexes and the journal may compete for I/O resources. When indexes and journal files reside on the same device, this contention can create bottlenecks . **Write-Ahead Logging**: Each index update is recorded in the journal before acknowledgment. At 5,000 inserts/second, journal commit intervals become critical—shorter intervals increase I/O load but reduce risk, while longer intervals may batch more operations but create latency spikes . **Index Fragmentation**: Continuous inserts while indexes exist can lead to index fragmentation and bloat. Tests show that inserting data with indexes active can result in index sizes 3-4x larger than building the index after data insertion, which further compounds performance degradation . To understand the practical impact, consider a simplified throughput model. If your storage system can handle 30,000 write operations per second, then with 1 index (plus the mandatory `_id` index), you could theoretically sustain up to 15,000 inserts/second (30,000 / 2 operations per insert). With 5 additional indexes (total 6 indexes), the maximum sustainable insert rate drops to 30,000 / 6 = 5,000 inserts/second—exactly your target rate. This means the system would be operating at its theoretical I/O capacity limit, leaving no headroom for spikes, read traffic, or background tasks. Real-world performance would likely be lower due to contention and overhead. **Minimize indexes ruthlessly**: Only create indexes that are essential for your most critical queries. Every unnecessary index directly degrades insert performance . Use `db.collection.getIndexes()` regularly to audit index usage. **Use partial or sparse indexes**: For fields that exist only in a subset of documents, partial indexes (`partialFilterExpression`) or sparse indexes can significantly reduce the number of index entries created per insert, as they only index documents meeting specific criteria . **Consider covered queries**: Design indexes that can satisfy queries entirely from the index, reducing read load and justifying the write cost, but be mindful that covering more fields expands index size . **Separate journal and data files**: Place the journal on a dedicated, fast storage device (like an SSD) to reduce I/O contention between journal writes and index/data updates . **Tune journal commit intervals**: Adjust `commitIntervalMs` to balance durability against write load. Longer intervals batch more operations but increase risk. Test different values under your specific workload . **Use u

How do indexes affect write performance in a collection that handles 5,000 inserts per second?

At 5,000 inserts per second, each index adds significant overhead by requiring an index entry update per insert, with benchmarks showing 10-20% latency increase per index and total insert time potentially doubling with 5-10 indexes

How do indexes affect write performance in a collection that handles 5,000 inserts per second?

At 5,000 inserts per second, each index adds significant overhead by requiring an index entry update per insert, with benchmarks showing 10-20% latency increase per index and total insert time potentially doubling with 5-10 indexes

Scenario Questions

Follow-up Questions