Write amplification and cache cost of unused indexes
Parent: Unused index detection with $indexStats · Published reference · snapshot 2026-10-31
↓ Facts as markdownall context files
Source-anchored research for Write amplification and cache cost of unused indexes. Run status: COMPLETED.
These notes link each claim to its source. Some citations identify archived research reports that are no longer hosted on this site. A published reference means the content is available; it does not certify independent review or accuracy.Read the editorial policy and follow the sources before relying on a claim.
Definitions
- A zero $indexStats access count is not a measurement of maintenance work or proof that an index has no value: Percona notes that uniqueness enforcement does not increment the access counter and warns about indexes needed by occasional reporting workloads. (medium confidence) [source]
How it works
- Before deciding an index is unnecessary, observe a representative application cycle and consider rare but important query patterns. Percona warns about monthly reports; Alibaba illustrates a rarely used leaderboard index that must remain. (medium confidence) [source]
- Index maintenance can create cache pressure: MongoDB documents that sufficiently scattered random index inserts can cause high WiredTiger eviction and replacement, requiring disk updates. An index's lack of query reads does not eliminate this write-side access. (medium confidence) [source]
- Representative workload coverage matters before removal: Percona warns that an index used only for monthly reporting may appear unused during a shorter observation period, and that retained monitoring history should cover the full application cycle. (low confidence) [source]
Problems, failure modes and limitations
- Hiding an index removes it from query planning but continues its write maintenance and memory/disk consumption. A hide experiment can assess query dependence, but cannot measure the maintenance savings of actually dropping the index. (medium confidence) [source]
Facts and statements
- Maintaining unnecessary indexes adds work to writes and consumes storage; removing indexes that the workload does not need can improve write performance and reduce storage requirements. (high confidence) [source]
- MongoDB inserts or removes corresponding keys on inserts and deletes. Updates may change only a subset of indexes, depending on affected keys; sparse and partial indexes are maintained only for documents included in them. The blanket statement that every update rewrites every index is too broad. (medium confidence) [source]
- Indexes without query benefits can still increase WiredTiger cache pressure through maintenance. Percona describes additional cache evictions and slower operation under over-indexing; Netdata independently identifies unused indexes as a source of cache waste and write amplification. (medium confidence) [source]
- WiredTiger uses both an internal cache and the filesystem cache. Indexes in the internal cache have a different representation from their on-disk form and may retain prefix compression; therefore, on-disk index size alone does not establish how much RAM dropping an index will release. (low confidence) [source]
- A zero $indexStats access count does not quantify index maintenance cost or prove the absence of useful behavior: the counter covers user-driven access, excludes internal operations such as TTL expiration, and uniqueness checks can occur without incrementing it. (medium confidence) [source]
- Alibaba documents another overhead at high object counts: each index adds a disk file and corresponding WiredTiger dhandle structures. Reducing unnecessary indexes reduces those resources; the performance effect depends on workload and access patterns. (low confidence) [source]
- No universal RAM or write-latency saving follows from an unused-index count. Percona explicitly scopes its customer example to that workload, while MongoDB describes per-index overhead without a fixed multiplier. Treat estimated benefits as hypotheses for a measured removal experiment. (medium confidence) [source]
- Unused indexes can add write work despite serving no queries: MongoDB maintains applicable index entries on inserts and deletes, and Percona identifies excess indexes as overhead on writes, disk and memory. (medium confidence) [source]
- The FAST 2022 paper measures physical write amplification as post-compression bytes written to NAND divided by bytes written into the database. Its WiredTiger/B+-tree comparisons vary workload and storage parameters; they do not measure savings from removing unused MongoDB secondary indexes and should not supply a universal index-removal multiplier. (low confidence) [source]
Children
- Physical versus logical write amplification (frontier)
- Index working-set residency and cache eviction (frontier)
- Sparse and partial index maintenance (frontier)
Frontier under this node: Index working-set residency and cache eviction, Physical versus logical write amplification, Sparse and partial index maintenance