Unique, TTL and shard-key indexes that read as unused
Parent: Unused index detection with $indexStats · Published reference · snapshot 2026-10-31
↓ Facts as markdownall context files
Source-anchored research for Unique, TTL and shard-key indexes that read as unused. 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.
How it works
- A unique index can enforce uniqueness while accesses.ops remains zero: checking uniqueness during INSERT does not increment this counter. Removing an index solely because of zero accesses can remove a required integrity constraint. (high confidence) [source]
Problems, failure modes and limitations
- $indexStats accesses.ops measures index access driven by user requests; it excludes internal TTL deletion and chunk split or migration activity. A zero counter therefore cannot establish that an index has no operational purpose. (high confidence) [source]
- Hiding preserves uniqueness enforcement, TTL expiration, and index maintenance. Inference: a successful hidden-index trial tests query dependence but cannot establish that removing a unique or TTL index preserves application semantics. (high confidence) [source]
- For ordinary shard-key index protection, the supporting index may be the shard-key index itself or a compound index with the shard key as its prefix. MongoDB documents that the only non-hidden supporting index cannot be hidden or dropped; the hashed-key exception requires separate handling. (high confidence) (verified as of 2026-10-30) [source]
- The blanket assertion that every shard-key index must always remain is too broad. MongoDB introduced dropping hashed shard-key indexes in 7.0.3, 6.0.12 and 5.0.22; its documented procedure warns that balancing for the collection stops and orphan cleanup cannot proceed without that index. It recommends a supporting non-hashed index and checking for orphans before removal. (high confidence) [source]
- An audit must preserve index properties alongside usage counts. The My DBA World script projects only name, since, and accesses, while $indexStats supplies a full spec. Inference: that script output alone cannot identify unique or TTL constraints. (medium confidence) [source]
Comparisons and alternatives
- On ranged sharded collections, unique indexes generally require the shard key as a prefix, with the default _id index as an exception. If _id is not the shard key, its index enforces uniqueness per shard rather than across the cluster; index review must preserve the actual constraint scope. (high confidence) [source]
- An automated audit should inspect the index specification and shard-key support before acting on zero accesses. The full spec is included in $indexStats; identify TTL indexes by the presence of expireAfterSeconds, rather than a truthiness check that would miss expireAfterSeconds: 0. Excluding unique and required shard-key indexes is a conservative policy supported by the tool examples. (high confidence) [source]
Facts and statements
- A TTL index may expire documents while its recorded accesses remain zero. Treat expireAfterSeconds as evidence of a retention function that must be reviewed separately from query usage. (high confidence) [source]
- A historical MongoDB production incident on 4.4.18 involved dropping a shard-key index on one shard, followed by queued deletion tasks and degraded performance. The ticket reports rebuilding the index and scaling up to let deletions catch up; it does not establish that an unused-index tool caused the drop. (low confidence) [source]
- The mdbkit author excludes unique, TTL and required shard-key indexes from unused-index candidates. Indexterity describes removing zero-usage advisories for these protected types after a test where a unique index rejected a duplicate while remaining at ops zero. These are independent tool-author implementations of conservative classification, not independent proofs of MongoDB semantics. (medium confidence) (verified as of 2026-10-24) [source]
- The blanket rule that shard-key indexes can never be dropped is too broad: MongoDB permits dropping hashed shard-key indexes starting in 7.0.3, 6.0.12, and 5.0.22. This exception requires a separate operational review. (high confidence) (verified as of 2026-10-30) [source]
- The hashed-index drop procedure warns that removal disables balancing for that collection and prevents remaining orphan cleanup. It recommends a supporting non-hashed index to avoid shard-key-filter collection scans; recreate the hashed index to restore balancing eligibility. (low confidence) (verified as of 2026-10-30) [source]
- Treat zero-access indexes as review candidates: inspect unique and TTL properties in spec, identify shard-key support from collection metadata, and approve any semantic or sharding change explicitly before removal. This is a synthesis of the documented exceptions. (medium confidence) [source]
Children
- No children recorded.