Partial unique indexes for soft delete
Parent: MongoDB Compound Index Design: ESR, Selectivity, Covering and Multikey Bounds · Published reference · snapshot 2026-10-22 · skill ranked-40493c90
↓ Facts as markdownall context files
Source-anchored research for Partial unique indexes for soft delete. 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
- MongoDB's planner uses a partial index for a read only when the query predicate implies the partial filter or a stricter subset; this affects read-index eligibility, while the unique partial constraint still governs writes. (high confidence) [source]
- A March 2019 Stack Overflow query-plan report found that { soft_deleted_at: null } still produced a FETCH filter because the [null, null] index bounds also included undefined or missing values. Treat this as a version-specific example and inspect explain() when active documents may have both null and missing states. (low confidence) (verified as of 2019-03-04) [source]
Problems, failure modes and limitations
- If a deleted record's key has been reused, restoring it to the active state makes it match the partial index again; MongoDB then rejects the restore with a duplicate-key error while another active record owns that key. The application needs a restore-conflict policy. (medium confidence) [source]
- Before building a partial unique index, remove duplicate keys among documents that match its active-state filter. MongoDB cannot build a unique index when existing indexed documents violate the key constraint. (high confidence) [source]
Facts and statements
- MongoDB enforces a unique constraint only among documents that match the partialFilterExpression. A soft-delete index such as { email: 1 } with unique: true and partialFilterExpression: { deletedAt: null } allows an active record to reuse an email after the old record receives a deletion timestamp, provided null represents the active state. (high confidence) [source]
- For tenant-scoped uniqueness, use the tenant and normalized identifier as the compound unique key, such as { tenantId: 1, emailNormalized: 1 }, and use the soft-delete state in the partial filter. MongoDB applies compound uniqueness to the combination of indexed key values. (high confidence) [source]
Children
- No children recorded.