Shard Key Design and Hotspot Prevention

Eight shards. Seven idle. One on fire.

It's a peak-traffic evening. The dashboard shows a "horizontally scaled" system: eight database shards, load spread across the fleet. Except it isn't. Shard 3 is at 95% CPU. The other seven are napping.

The system was scaled. The traffic didn't read the plan.

Part 8 ended with the warning that leads here: keys decide where data lives. Blog 5 introduced sharding. Part 7 routed events by key. Part 8 named objects by key. Today we face the consequence: keys don't just place data. They place pressure.

The Simple Mental Model: Choosing the Lanes on a Highway

A shard key is the rule that assigns vehicles to lanes on a highway. Shards are lanes. The key is the lane-assignment rule.

If the rule spreads traffic evenly, all lanes move and the highway's full capacity is real. If the rule sends half the city into one lane — "all trucks use lane 3" — that lane jams while the others sit empty. The highway has capacity. The rule wasted it.

And here's the subtlety that fools smart teams: an even split of vehicles is not an even split of traffic. One lane can hold exactly its fair share of cars, and still jam, because those particular cars keep stopping, turning, and honking.

A hotspot is exactly that: one shard, partition, or key receiving a disproportionate share of load while its siblings idle.

Shard, Partition, Replica: Three Words Engineers Confuse

Before going further, three terms that get blended into mush. Shard / partition — a piece of the data; split the dataset, each piece lives somewhere (the two words are near-synonyms — "partition" often refers to the logical piece, "shard" to the piece-plus-its-machine, but practically it's the same idea). Replica — a copy of the data, Bonus I's photocopies.

            The full dataset
           /       |        \
      Shard 1   Shard 2   Shard 3      ← pieces (scale)
       /   \     /   \     /   \
      P     R   P     R   P     R      ← each piece: primary + replica (safety)

Sharding scales data and traffic horizontally — more lanes. Replication buys availability, durability, and read scaling — spare copies of each lane. Real systems use both: the data is split into shards, and each shard is replicated.

Different tools, different failures. A hot shard is not fixed by adding replicas — copies of a jammed lane are still jammed. Replicas can absorb read pressure; they do nothing for writes. Hold that asymmetry — it returns.

The Bad Shard Key Problem

Every hotspot war story starts with a key that looked reasonable in the design review.

Shard by country — and 80% of users are from one country. One shard is the product; the rest are decoration. This is skew meeting low cardinality: only ~200 possible values, and wildly uneven ones.

Shard by plan type — free / pro / enterprise. Three shards maximum, and 95% of users are on free. You've built a one-lane highway with two ceremonial lanes.

Shard by current date — every write today lands on today's shard. Yesterday's shard rests, tomorrow's waits, today's melts. Sequential and timestamp-first keys aim the entire write firehose at one moving point.

Shard by user ID, meet a celebrity — hashing spreads users evenly, but all of one user's data still lives on one shard. When that user receives massive traffic, their shard does too.

Object keys with a shared timestamp prefix — Part 8's version: 2026-07-07-... keys crowd one range of the keyspace. Same disease, warehouse edition.

A stream partition key that funnels everything — Part 7 taught per-key ordering: same key → same partition. Choose event_type as the key when 90% of events are page_view, and one partition carries the company while its consumer lag (Part 7's vital sign) climbs alone.

Vocabulary check, because these nest: a hot key (one identifier dominating) lives on a hot partition, which makes a hot shard. When the key is a person, it's the celebrity problem. Same disease, four magnifications.

Data Distribution Is Not Traffic Distribution

The most important sentence in this article: a shard key can spread rows perfectly and still fail under traffic.

One million users, hashed evenly across ten shards. 100,000 users each. The storage chart is a flat, beautiful line. The design review applauds. Then one celebrity on shard 6 starts receiving 40% of all reads and writes. Shard 6 holds 10% of the data and 40% of the load.

Because "balanced" is not one number. It's five: storage balance (rows/bytes per shard — the one everyone measures), read balance (queries per shard), write balance (writes per shard — the one caches can't rescue), compute balance (CPU cost per shard; one tenant's gnarly queries can outweigh ten simple ones), and operational balance (backups, migrations, resharding effort per shard).

Design reviews stare at the first. Outages come from the middle three.

Range-Based vs Hash-Based Sharding

Two classic strategies for turning a key into a lane assignment. Range-based: keys are split into ordered ranges — Blog 5's A–F / G–M / N–Z user split was exactly this. Hash-based: run the key through a hash, assign by the result — hash(user_id) % N is the textbook version.

Note that last row: hashing fixes sequential hotspots, not popularity hotspots. No hash function can split one key across shards. The unit of distribution is the key itself.

One practical wrinkle: naive % N means adding a shard reshuffles almost every key's home. Techniques like consistent hashing exist to shrink that movement — we'll meet them properly in Bonus E: Load Balancing Algorithms. For today, just know the wrinkle exists; it feeds the resharding pain later in this article.

Composite, Salted, and Synthetic Keys

When one dimension can't distribute well, engineer the key.

Composite keys combine meaningful dimensions: tenant_id + user_id, region + order_id. The extra dimension adds cardinality and keeps related data findable.

Salted / bucketed keys add controlled randomness to spread a known-hot write path. Without salt: orders:2026-07-07:* is one hot range. With bucket/salt:

orders:2026-07-07:bucket-01:*
orders:2026-07-07:bucket-02:*
...
orders:2026-07-07:bucket-16:*

Today's write firehose now sprays across sixteen lanes instead of one.

Synthetic keys go further: generate an identifier purely for its distribution properties, keeping the meaningful attributes as ordinary columns. Time bucketing is the calendar cousin: keys carry a coarse bucket (orders:2026-07:...) so no single moment owns a range.

Now the bill, because Part 2 says there's always a bill: every distribution fix is a query-pattern cost. Those sixteen salt buckets spread writes beautifully, and now "show me today's orders" must query all sixteen buckets and merge. Write relief purchased with read fan-out.

Salt is not seasoning. It's a loan against your read path. Take it only for measured hot writes.

Hotspots Across Systems

Here's the widening move this series keeps making: this is not a database topic. Anywhere a key decides placement, a key can decide a hotspot.

Database shard hotspot — this article's opening scene. Cache hot key — Part 6's cricket score was a hot read key; a 1-second TTL absorbed millions of requests. Hot reads have that savior. Hot writes don't; you can't cache a write. Stream partition hotspot — Part 7's per-key ordering concentrating one key's events, visible as one partition's consumer lag climbing alone. Object storage prefix hotspot — Part 8's timestamp-first keys crowding one keyspace range. CDN hot object — one viral video hammering specific edges (the CDN's job, but capacity planning still cares). API endpoint hotspot — one route eating the gateway while others idle. Tenant hotspot in SaaS — one whale customer who is 60% of your traffic, sharing a lane with minnows.

One disease. Seven costumes. The diagnosis is always the same question: what decides placement here, and what share of traffic lands on the biggest value of it?

How to Detect Hotspots

Hotspots announce themselves, if the metrics are per-shard, per-partition, per-key. Fleet-wide averages hide them by design.

The signals: one shard's CPU far above its siblings; one partition's latency worse than the rest; one key dominating the top-K read/write charts; one stream partition's consumer lag climbing while others stay flat (Part 7); one cache key with extreme hit volume (Part 6); one tenant dominating traffic reports; p95/p99 latency ugly while the average looks fine, since the average is seven idle lanes politely covering for the jammed one; uneven storage growth across shards; uneven queue depth across workers; and throttling firing on one partition while the rest idle.

The meta-signal: if you can only see averages, you have hotspots you can't see.

When a Hotspot Is Fine — and When It Isn't

Not every hotspot is an emergency.

Acceptable: the skew is known, bounded, and covered. A hot read key behind a healthy cache (Part 6's savior). A whale tenant deliberately placed on a dedicated shard — that's not an accident, that's a policy. A predictable spike with measured headroom.

Dangerous: the hotspot is write-heavy (no cache will save you), it throttles innocent neighbors sharing the shard, or it grows with your success — celebrity traffic, viral content, the whale that keeps eating. A hotspot indexed to growth is an outage on layaway.

The judgment question is Bonus F's, again: is this a convenience problem or a money/trust problem, and which direction is it trending?

How to Design Better Shard Keys

The method, condensed:

Start from access patterns. The queries you must serve cheaply come first; the key serves them, not the other way around. Know your read/write ratio: read-hot tolerates more (caches help), write-hot needs distribution discipline.

Avoid the known traps. Low-cardinality keys (country, plan, status), purely sequential keys on high-write paths, and any key you haven't checked for celebrity or tenant skew. Pull the actual top-K distribution before committing.

Choose the shape deliberately. Hash-based when locality doesn't matter; range-based when range queries do; composite when one dimension can't carry it; salting/bucketing only for measured hot write paths, priced against the read fan-out.

The common key families, with their known failure: user-based is natural for user-centric apps, but fails on celebrities. Tenant-based is natural for SaaS isolation, but fails on whales (have a dedicated-shard policy before the whale signs). Region-based is natural for locality and compliance, but fails on population skew (the 80%-one-country problem).

Plan for time. Model growth: will this key distribute in two years, at 10×? Design for the top 1% of traffic, not the average — the average never took down a shard. Keep a resharding strategy on paper. And measure before and after — a key change without before/after per-shard charts is a superstition.

In C.R.E.D terms: Clarify the access patterns, Requirements per the five balances, Estimate the skew (top keys, top tenants, growth), and only then Design the key.

A good architect does not ask only, "How many shards do we need?" A good architect asks, "What key will distribute data and traffic evenly today, and still support tomorrow's access patterns?"

Resharding: The Pain You Avoid by Thinking Early

Why does everyone repeat "choose the key carefully"? Because changing it later is one of the most expensive migrations a team can run: data must physically move, terabytes, live, without dropping writes; routing logic changes, since every reader and writer must learn the new map; caches may need invalidation, since Part 6's keys were built on the old placement; read/write paths get complicated mid-flight with dual writes, backfills, and verification passes; consistency and downtime risks appear, Bonus I's windows, now self-inflicted; and testing is brutal, since production traffic shapes are hard to rehearse.

The full migration playbook — dual writes, backfill, cutover — belongs to later advanced articles. Today's point is simpler: every hour spent on key design is a week not spent on resharding.

Common Mistakes Engineers Make with Shard Keys

Choosing user_id blindly — right instinct, unchecked for celebrities. Choosing tenant_id without checking tenant size skew — the whale eats the shard. Timestamp-first keys on high-write workloads — the moving hotspot, guaranteed. Country, status, or plan type as the key — low cardinality plus skew, two diseases, one decision. Optimizing only for storage balance — flat storage charts, flaming write charts. Ignoring query patterns — a perfectly distributed key that makes every real query scatter-gather. Ignoring the celebrity problem — "our users are all similar" is true until the day it isn't. Ignoring the read/write difference — caches rescue hot reads, never hot writes. Salting without understanding read fan-out — sixteen-bucket writes, sixteen-bucket every-single-read. Assuming resharding will be easy — it never is. Not monitoring per-shard metrics — averages are where hotspots hide.

From My Journey: An Architectural Lesson

In many systems, partitioning starts as a simple scaling idea: split the data across multiple machines so no single machine carries everything.

The first design usually looks mathematically correct. Equal rows per shard. Equal objects per prefix. A tidy diagram, evenly divided — the kind that sails through review.

Then production traffic arrives, and production traffic has never once agreed to be evenly distributed.

The realisation, when it lands, reframes the whole exercise: the issue was never only where the data sits. The issue is where the traffic goes. One tenant, one user, one date range, one object prefix, one stream key — any of them can quietly attract a disproportionate share of the load, and the beautiful even split becomes seven idle lanes watching one lane burn.

And every fix turned out to be a trade. A key that spread writes evenly made reads fan out. A key that kept related data together created the very locality that overheated. A key that served range queries perfectly aimed the write firehose at a single moving point.

The lesson that stuck: shard key design is traffic design, not just data design. A shard key does not just divide data. It decides where pressure will accumulate.

Key Takeaways

• Shards are lanes; the key is the lane-assignment rule — and the rule, not the lane count, decides whether the highway moves.
• Shards split data; replicas copy it. Copies absorb hot reads; nothing copies away hot writes.
• Even data distribution is not even traffic distribution — balance is storage and reads and writes and compute and operations.
• Low-cardinality, skewed, and sequential keys are the three classic hotspot machines.
• Hashing fixes sequential hotspots, never popularity hotspots — no function splits one key across shards.
• Composite, salted, and synthetic keys buy write distribution at a read fan-out price; salt only measured hot paths.
• Hotspots wear seven costumes — database, cache, stream, object prefix, CDN, endpoint, tenant — one diagnosis fits all.
• Averages hide hotspots; per-shard, per-partition, per-key metrics reveal them. Watch the p99.
• Design for the top 1% of traffic and model growth — a hotspot indexed to your success is an outage on layaway.

Interview Lens

Shard key questions are how interviewers separate "has read about sharding" from "has been paged by it."

The setup: "How would you shard this?" — and the trap is always the follow-up: "what if one user is huge?"

The weak answer: "hash(user_id) % N." The strong answer names the distribution and the skew plan: "Hash-based on user_id for even baseline distribution; range queries go through a read model instead of scatter-gather; we monitor top-K keys, absorb hot reads with caching, and salt-bucket any measured hot write path; shard placement uses consistent-hashing-style assignment so adding shards moves minimal data."

Expect the probes: "Range or hash?" — answer with query patterns, not preference. "What breaks with timestamp keys?" — the moving write hotspot. "How do you detect a hot partition?" — per-partition metrics, top-K keys, p99-vs-average, consumer lag. "Even data but uneven load — how?" — the celebrity answer; explicitly saying "data balance is not traffic balance" is the differentiator that lands.

And the question strong candidates ask unprompted: "What does the top 1% of keys look like in this workload?" That one sentence signals you've operated this, not just diagrammed it.

Real-World Engineering Lens

Chart top-K keys per store from day one — database, cache, stream. The hotspot you can name is the one you can fix.

Put per-shard p99 next to the fleet average. The gap between them is your hotspot budget.

Write the whale policy before the whale signs. "Tenants above X traffic get a dedicated shard" is a sentence in a doc today or a war room in a year.

Run the paper reshard drill: "If we doubled shards tomorrow, what fraction of keys move?" If the answer is "nearly all," fix the placement scheme now, while it's cheap.

Treat salt as a loan. Every bucket added to a write path is a fan-out added to a read path — record the balance owed in the design doc.

What's Next

The data has homes. The keys spread the pressure. The messages carry the work. Now — what about the machines actually running the code?

Blog 9 — Scaling Compute: Microservices, Containers, and Serverless. The main series continues: monoliths and microservices, containers and serverless, and how the compute layer multiplies without falling over.

Question for Readers

Right now, in your system: what is the single hottest key, in your database, your cache, or your stream?

If you can name it, you're ahead of most teams. If you can't, that's not a comfort. That's the finding.