MongoDB E11000 Duplicate Key — Upsert or Drop Index

E11000 duplicate key error collection: shop.users index: email_1 dup key: { email: "a@x.com" }. It's MongoDB's most informative error — collection, index, and colliding value in one line — yet it still burns hours, because the fix depends on the story: genuine double-submit, retried write, racing requests, dirty legacy data, or an index that shouldn't exist at all.

Unique indexes are the only cross-request duplicate protection MongoDB offers. Application checks ('find then insert') race under concurrency exactly like their SQL cousins, and retries after timeouts re-insert rows whose first attempt actually succeeded. The index is the arbiter doing its job; everything around it decides whether the rejection is a bug or a bodyguard.

This guide reads the error precisely, audits indexes for rogues, finds stray duplicates with aggregations, converts retry paths to upserts, drops obsolete indexes safely, and gates future indexes through review so E11000 stays a signal instead of noise forever.

Read keyPattern and keyValue

The message is a complete diagnosis in one line: collection (shop.users), index (email_1 — field plus direction), and colliding value ({ email: "a@x.com" }). keyPattern { email: 1 } describes the index shape (1 ascending, -1 descending, "text"/"2dsphere"/"hashed" for special types); keyValue is the exact document that collided. Copy both into the ticket — the next engineer reproduces with one find instead of re-deriving from logs.

Confirm against getIndexes before acting: options change everything. A sparse index skips null-missing documents (many nulls coexist); a non-sparse unique allows exactly one null (the beta-incident shape); a partial index enforces only documents matching partialFilterExpression. The same keyValue collides or coexists depending on options you can only see in the index definition.

Trend the pair over time: E11000s scattered across many keyValues mean dirty history or broad races; a single repeated keyValue means one stuck retry loop hammering the same document. The distribution picks the runbook — census-and-clean versus fix-the-loop — before you open either. Single-value floods also implicate client retry loops worth checking in the app logs alongside the database.

# Full index definitions (options decide null/collision behavior)
mongosh 'mongodb://app:secret@db-primary:27017/shop' --quiet \
  --eval 'db.users.getIndexes()' 

# The exact colliding document
mongosh 'mongodb://app:secret@db-primary:27017/shop' --quiet \
  --eval 'db.users.findOne({email: "a@x.com"})'

# Index build/existence for the named index
mongosh 'mongodb://app:secret@db-primary:27017/shop' --quiet \
  --eval 'db.users.getIndexKeys()'

Find the Duplicates With Aggregation

The group-by census is the definitive collision map: group documents by the key (or full tuple for compound indexes), count, keep member ids, filter count > 1, sort by severity. It sizes the response (one pair = surgical merge, thousands = systemic dirt or a rogue) and produces the repair manifest (the ids list) in the same pass. Run it against the exact keyPattern fields — grouping by email when the index is {email, tenant} understates compound collisions.

Classify by recency like any orphan census: tight recent clusters mean races or replay storms (fix the write path); wide historical scatter means legacy dirt (clean the data); uniform null-key groups mean a non-sparse index meeting optional fields (fix the index definition). Each class has a different owner, and the timestamps route the ticket correctly on the first pass.

Promote the census to a pre-index-build gate: every createIndex on a populated collection runs the group-by first and fails the migration on any group. Index builds that discover dirt halfway abort expensively on huge collections — the census costs one aggregation while the aborted build wastes hours of I/O and blocks the entire deploy train behind it for hours.

# Collision census: groups, counts, member ids (compound: group full tuple)
mongosh 'mongodb://app:secret@db-primary:27017/shop' --quiet --eval '
db.users.aggregate([
  {$group: {_id: "$email", n: {$sum: 1}, ids: {$push: "$_id"}}},
  {$match: {n: {$gt: 1}}},
  {$sort: {n: -1}},
  {$limit: 20}
])' 

# Null-key groups (non-sparse unique vs optional fields)
mongosh 'mongodb://app:secret@db-primary:27017/shop' --quiet --eval '
db.users.countDocuments({invite_code: null})'

Upsert Instead of Insert

updateOne with upsert:true is the idempotent write primitive: filter on the unique key, $set the refreshable fields, $setOnInsert the first-write-wins fields (createdAt, initial source), and concurrent attempts converge — one inserts, the rest update. Retries after timeouts become harmless replays; double-clicks merge; races serialize on the index instead of erroring. The unique key graduates from guardrail to concurrency control.

Scope the operators deliberately. $set overwrites on every replay (right for status, wrong for createdAt); $setOnInsert writes only on insert (right for provenance, invisible on replays); $inc accumulates across replays (right for counters, disastrous for amounts — never $inc a payment on a retried webhook). Review each field's replay semantics or the upsert converts duplicate errors into duplicate charges.

Keep plain inserts where duplicates must stay loud: first-time registrations that genuinely collide deserve an error surfacing 'account exists', not a silent merge into someone else's row. Upsert the sync/retry paths; insert (and handle E11000 as business logic) on the human-facing create paths. Loud where humans decide, convergent where machines retry.

# Idempotent write: filter on unique key, split replay semantics
mongosh 'mongodb://app:secret@db-primary:27017/shop' --quiet --eval '
db.users.updateOne(
  {email: "a@x.com"},
  {$set: {name: "Ann Lee", updatedAt: new Date()},
   $setOnInsert: {createdAt: new Date(), source: "web"}},
  {upsert: true}
)' 

# Replay it: second run updates, never duplicates
mongosh 'mongodb://app:secret@db-primary:27017/shop' --quiet --eval '
db.users.countDocuments({email: "a@x.com"})' 
# expect: 1

Rogue Indexes: Drop What Nobody Owns

A rogue index is a unique constraint whose feature died but whose enforcement lives on — beta invite codes, retired coupon formats, migrated-away external ids. Symptoms: E11000 on fields current code never sets (null collisions especially), index names referencing dead features, zero hits in code search for the field. getIndexes plus $indexStats (accesses since restart) confirms abandonment quantitatively: zero ops, still armed.

Drop deliberately, not eagerly. Prove no code path depends on the index (search + staged removal: hide the index first with hideIndex() on 4.4+, watch a full cycle, then dropIndex). Archive affected documents before cleanup so merges restore from backup collections, not oplog archaeology. Announce the drop like a schema migration — because it is one, with query-plan consequences for any reader that used the index.

Re-add correctly when the feature returns in new form: partial unique indexes (partialFilterExpression: { invite_code: { $exists: true } }) enforce only documents carrying the field, letting nulls coexist freely. The beta's blanket unique becomes the referral program's surgical one — same protection where it matters, silence where it doesn't. Record the decision and its evidence in the ticket for the next audit.

# Usage evidence: zero ops since restart = abandonment candidate
mongosh 'mongodb://app:secret@db-primary:27017/shop' --quiet --eval '
db.users.aggregate([{$indexStats: {}},
  {$project: {name: 1, accesses: 1}}])' 

# Stage removal: hide first (4.4+), watch a cycle, then drop
mongosh 'mongodb://app:secret@db-primary:27017/shop' --quiet \
  --eval 'db.users.hideIndex("invite_code_1")' 
# ... one clean cycle later ...
mongosh 'mongodb://app:secret@db-primary:27017/shop' --quiet \
  --eval 'db.users.dropIndex("invite_code_1")'

Races and Retries: Idempotent Writes

Find-then-insert races exactly like SQL check-then-insert: two requests both see absence, both insert, the loser's E11000 is the index saving you from a duplicate row. The error rate tracks traffic overlap, which is why the path passes every staging test and fails at launch — staging never runs the two requests in the same millisecond. Retries add the second head: timeouts lose acks, failovers replay, providers redeliver, and each replay re-inserts the committed row.

The single remedy covers both: filter-on-unique-key upserts as the only write shape on hot paths, with idempotency keys (request id, provider reference) as the unique field where natural keys don't exist. Every consumer gets a doubled-delivery test — same payload twice, assert one document — because providers will redeliver eventually and the test makes it a non-event.

Route repeated violations to dead-letter review with an alert, never back into the retry loop. An E11000 on a path you believe idempotent means the filter misses the real key (upserting on _id while uniqueness lives on email) or two business events share a token — both need human eyes promptly, not a 31st blind attempt against the same colliding key value.

#!/bin/bash
# Doubled-delivery test: same payload twice, exactly one document
set -euo pipefail
CONN='mongodb://app:secret@db-staging:27017/shop'
for i in 1 2; do
mongosh "$CONN" --quiet --eval '
db.payments.updateOne({ref: "PAY-881"},
  {$set: {status: "paid"}, $setOnInsert: {createdAt: new Date()}},
  {upsert: true})' > /dev/null
done
mongosh "$CONN" --quiet --eval 'db.payments.countDocuments({ref: "PAY-881"})'
# expect: 1

Prevention: Index Reviews and Partial Uniques

Govern indexes like schema: every createIndex or dropIndex rides the same review as migrations, with the census query attached for populated collections. Feature-sunset checklists include 'drop or convert its unique indexes' beside 'remove the flag and the code' — the beta incident was a checklist gap, not a technical surprise. Quarterly getIndexes audits (name, key, options, $indexStats accesses) catch drift that reviews miss.

Prefer partial uniques for optional fields: partialFilterExpression { field: { $exists: true } } enforces uniqueness only where values exist, so absent fields never collide. Sparse indexes cover legacy null-missing shapes; partial covers arbitrary predicates and reads clearer. Either beats the blanket unique that permits exactly one null and pages the second signup.

Validate builds on staging restores before production: build time, lock behavior, and dirt discovery all rehearse safely on data shaped like prod. Huge collections earn rolling builds via the documented procedure — an index build that stalls primaries is an availability incident wearing a DDL costume. Always rehearse every big build on a staging restore holding production-shaped data first, without exception.

# Surgical unique: enforced only where the field exists
mongosh 'mongodb://app:secret@db-primary:27017/shop' --quiet --eval '
db.users.createIndex({invite_code: 1}, {unique: true,
  partialFilterExpression: {invite_code: {$exists: true}}})' 

# Quarterly audit: every index with options + usage
mongosh 'mongodb://app:secret@db-primary:27017/shop' --quiet --eval '
db.users.aggregate([{$indexStats: {}},
  {$project: {name: 1, key: 1, accesses: 1}}])'