MERN• 5 MIN READ
Kirito

Kirito

6/10/2026•5 min read

The MongoDB Shard Key That Made 99% of Queries Scan Every Shard

Choosing the wrong shard key turned our 10-shard cluster into a broadcast storm – every query hit all shards, and response times went from 50ms to 5 seconds.

The MongoDB Shard Key That Made 99% of Queries Scan Every Shard

We had outgrown a single MongoDB replica set. 5TB of order data. 2 billion documents. Reads were getting slower every week.

The solution: sharding. But one bad decision on the shard key nearly destroyed our performance.

The Setup

MongoDB Atlas cluster with 10 shards. The collection: orders (2B docs).

I chose order_date as the shard key – seemed natural. Most queries filter by date range.

sh.shardCollection("ecommerce.orders", { "order_date": 1 })

The Disaster

Within hours after sharding, every query became painfully slow.

// Our most common query – get a user's recent orders
db.orders.find({ user_id: "12345", order_date: { $gte: ISODate("2024-01-01") } })
// Took 5.2 seconds instead of 50ms

Why? Because user_id was not part of the shard key.

MongoDB had no idea which shard contained that user's orders. So it broadcast the query to all 10 shards, waited for all of them to respond, then merged results.

99% of our production queries were scatter‑gather.

The Root Cause

A good shard key must have high cardinality (many unique values) and good write distribution. order_date had decent cardinality (one per day) but writes were evenly distributed.

The problem: our read pattern was mostly by user_id, not by date.

We had optimized for writes, not reads.

The Fix

We chose a compound shard key: { user_id: "hashed", order_date: 1 }

// New shard key (requires new collection – can't change shard key online)
sh.shardCollection("ecommerce.orders_new", { "user_id": "hashed", "order_date": 1 })

Now queries with user_id are targeted to a single shard. Queries with only date still scatter, but those are rare.

The Migration

We had to migrate 2B documents to a new collection – online, with no downtime.

// Use $merge to move data incrementally
db.orders.aggregate([
  { $match: { order_date: { $lt: cutoff } } },
  { $merge: { into: "orders_new", on: "_id", whenMatched: "replace" } }
])

We ran this in batches over a weekend.

What I Learned

  • Shard key is forever – choose carefully.
  • Query pattern > write pattern – optimize for your most frequent reads.
  • Hashed shard keys give even distribution for high‑cardinality fields.
  • Compound shard keys can target common query patterns.

Now we use a shard key analysis script before any sharding:

// Analyze query distribution
db.system.profile.find({ "command.find": "orders" }).forEach(log => {
  console.log(log.command.filter);
})

That scatter‑gather disaster cost us 2 days of debugging and one sleepless weekend. Now every new collection gets a shard key designed for our read patterns first.