Choosing a Shard Key

Partitioning vs Sharding ended by naming the shard key "a decision you can't easily reverse." Here is what that sentence costs in the real world. A team ran a five-shard cluster holding more than 50 terabytes. They'd picked a shard key that seemed reasonable at the time, and as the data grew, one truth emerged: a single shard held over 75% of the data and the workload while the other four idled. There is no ALTER TABLE for this. The fix was to stand up an entirely new cluster with better keys and migrate every row across with custom scripts — a project that took weeks and cost new machines, engineering time, and a great deal of nerve. In older MongoDB you couldn't even do that: before version 4.2, the shard key could not be changed, full stop.

That's why this gets its own lesson. Most decisions in a system are reversible — you tune a query, swap a cache, add a replica. The shard key is different: you pour it into wet concrete, and once it sets, changing it means re-cutting every row you own. So it's worth understanding exactly what you're deciding.

A shard key is a bet on two things at once: how your writes will spread across shards, and how your reads will travel to find their data. Win both and your shards share the load evenly while the common query touches just one of them. Lose either and one shard melts while the rest sit idle, or every read scatters to all of them. And you place this bet — both halves — before you write the application code.

In this lesson: the four properties that make a key good, the two keys that look safest and are the classic traps, why hashing rescues one trap but not the other, the composite key that solves both, and the discipline the whole thing demands.

Scope: the hashing scheme that maps a key to a physical shard resiliently is Consistent Hashing; what to do once a shard is already hot (split, salt, isolate) is Resharding & Hot Shards; the hands-on hash-versus-range exercise is the section's codelab. Here we choose the key.

Four properties, and a key needs all four. Miss one and you've bet wrong.

1. High cardinality — enough distinct values to spread. You can't distribute a million rows across a hundred shards on a key that has five values. Cardinality sets the ceiling on how finely you can ever split. A boolean gives you two shards; a status enum, a handful; a country, a few hundred at most. user_id or order_id, millions.

2. Even distribution — no single value carrying a disproportionate share. This is the property everyone conflates with the first, and they are not the same thing. A key can have millions of distinct values and still be brutally skewed if one value is hot — which is the celebrity problem, two sections down. High cardinality is necessary; it is not sufficient. What you actually need is that no single value shows up far more than the others.

3. Query alignment — the common query filters on the key. Data Modeling for Scale taught you to model the queries, not the world; the shard key is where that pays off hardest. If your most common read filters on the shard key, the router sends it to exactly one shard. If it doesn't, every read fans out to all of them — the scatter-gather tax from the last lesson, paid on every single request. The key must match how you actually read.

4. Stability — the value doesn't change, and isn't monotonic. A shard key that can change is a nightmare: update the value and the row has to physically move to a different shard. And a key that increases monotonically (a timestamp, an auto-increment id) has a subtler defect that's worth its own section, because it's the trap that catches the most people.

Now the fun part: the two keys almost everyone reaches for first each fail one of these — and they fail it in a way that's invisible until production. Let's watch them melt.

Reach for the most obvious key on any table — the primary key, an auto-incrementing id, or a created_at timestamp — and you've walked straight into the classic write hotspot.

Here's the cruel part: a timestamp has perfect cardinality. A million rows, a million distinct values. It sails through property one. And it still concentrates 100% of your write load on a single shard, because every new row is "now" — and "now" always lands in the newest range. We measured it on a real million-row table sharded eight ways by id range: the historical storage looked beautifully even (each shard about 12.5% of the rows), which is exactly what hides the bug. But look at the writes: every one of the newest rows landed on the single newest shard. The other seven were read-only museums; one shard took every insert.

The lesson is subtle and worth saying plainly: cardinality was never the problem. Arrival order was. A monotonic key sorts every new write into the same bucket not because it lacks distinct values, but because its values arrive in order, and sharding by order sends today's traffic to today's shard.

The fix, at key-choice time, is to hash the key — a good hash function scatters sequential values across all shards, so writes spread evenly. But hashing has a price you'll meet in the codelab: it destroys range scans. Once hash(timestamp) decides the shard, adjacent timestamps land on different shards, so "give me the last hour of events" becomes a scatter-gather across all of them. Range keeps the scan and hotspots the writes; hash spreads the writes and kills the scan. You can't have both — which is the first hint that a single column is often the wrong shape for a shard key.

So you avoid the monotonic trap and pick the natural entity — user_id. Millions of distinct values, and it's query-aligned: everything about a user lives on their shard, so a user's timeline is a single-shard read. For the vast majority of your traffic, it's a genuinely great key. We measured it: with normal users only, hash(user_id) across eight shards gave 1.01× imbalance — a flat 12.6% per shard, about as close to the ideal 12.5% as physics allows.

Then a celebrity signs up.

In the same measured run, we made one user draw 15% of all traffic — a Taylor Swift, a Justin Bieber; Instagram literally named this the "Justin Bieber Problem." Watch what it does: every one of that user's 150,415 rows hashes to the same shard, because hash(one id) is one number, and one number is one shard. That shard jumps to 256,130 rows against the others' ~106,000 — 25.6% of all load, 2.05× the average, while the key that was 1.01× a moment ago is now melting one box. The other seven shards never noticed.

This is the deep version of "cardinality isn't distribution." Your key has millions of values and one of them is hot, and hashing — the thing that saved you from the monotonic trap — is powerless here. Hashing spreads many values across shards; it cannot split a single value, because a hot key hashes to exactly one place no matter how good the hash. As DDIA puts it, in the extreme where all the reads and writes are for one key, they all go to one partition, full stop.

And the database won't rescue you. DynamoDB's adaptive capacity shifts throughput toward a hot partition and buys you slack for spiky load — but it throttles a sustained single-partition hotspot past about 1,000 writes/second. Most systems can't auto-compensate for skew at all; it's the application's job to design the key so no single value melts a shard. Relieving a known-hot key — splitting Taylor Swift's writes across sub-keys, the way Instagram split one like-counter into many — is a real technique, and it gets its full treatment in Resharding & Hot Shards. What matters here is the diagnosis: a single hot value is a property of your data, not your hash, and no amount of cardinality hides it.

Here's a million-event table and eight shards. Pick a key and watch the writes route in real time. Start with created_at and watch one shard take everything. Switch to user_id — beautiful, even — then flip the celebrity on and watch a single shard glow red at 26% while the rest sit at 11%. Run the common query on each key and see whether it lands on one shard or scatters to all eight. Then find the one key that passes all four properties. The scorecard grades every choice live; the keys that look safest are the ones that fail.

The way out of "one column can't satisfy all four properties" is to stop using one column. Composite keys — two or more attributes combined — are how real systems thread the needle, and the canonical example is Discord storing trillions of messages.

Discord shards messages on (channel_id, bucket), where bucket is a static ten-day time window. Look at how that one choice satisfies every property at once. Query alignment: you read one channel at a time, so a channel's messages are co-located — single-shard reads. Even distribution: millions of channels spread the load, and the time bucket splits a busy channel across windows so no single partition holds a whole popular channel forever. Bounded growth: the ten-day window caps each shard around 100 MB, which is what keeps their storage engine fast. And because their ids are time-sortable, range scans by time still work within a bucket. One key, tuned to the reads and the writes and the size simultaneously. That's the master class.

The general pattern is the same every time: combine an attribute that gives you alignment (the entity your common query filters on — a tenant_id, a channel_id, a user_id) with an attribute that gives you spread (a bucket, a sub-key, a hash) so no single value dominates. The sim's fourth option, (user_id, month), does exactly this: it keeps a user's recent data on one shard and spreads even a celebrity's traffic across month-buckets, dropping the peak from 2.05× toward even.

Honesty check, though — and Discord learned this the hard way in 2022: even a beautifully chosen composite key has a residual hot-value problem. One channel-and-bucket that gets truly enormous traffic still became a hot partition that hurt latency across their whole cluster. A good key makes hotspots rare, not impossible; when one appears anyway, that's what the next lessons are for. The composite key doesn't eliminate the celebrity — it stops the celebrity from being your default state.

The sim runs on a model. Here's the same thing on real Postgres in about two minutes — the celebrity skew and the monotonic hotspot are both a couple of GROUP BYs away. You need docker.

1. A million events, with one celebrity drawing 15% of traffic:

docker run -d --name sk -e POSTGRES_PASSWORD=pw postgres:16 && sleep 6
docker exec -i sk psql -U postgres <<'SQL'
CREATE TABLE events(id bigserial, user_id int, country text);
INSERT INTO events(user_id, country)
SELECT CASE WHEN random() < 0.15 THEN 1                 -- the celebrity: 15% of all rows
            ELSE 2 + (random()*199998)::int END,        -- 200k normal users
       (ARRAY['US','US','US','IN','BR','DE','UK'])[1+floor(random()*7)::int]
FROM generate_series(1, 1000000);
SQL

2. Shard by user_id (hash into 8) and look at the per-shard counts. Predict first: user_id has 200,000 distinct values — surely it spreads evenly?

SELECT (abs(hashint4(user_id)) % 8) AS shard, count(*)
FROM events GROUP BY shard ORDER BY shard;

 shard | count
-------+--------
     0 | 105,867
     1 | 105,742
     2 | 256,130   ← the celebrity lives here
     3 | 107,167
     …    ~106,000 each

Seven shards at ~106k, one at 256,130 — 2.05× the average. Confirm it's one user: SELECT count(*) FROM events WHERE user_id = 1; returns 150,415 rows, and SELECT abs(hashint4(1)) % 8; is 2 — every one of them on the same shard. High cardinality, one hot value, one molten shard. Remove the celebrity (WHERE user_id <> 1) and re-run: the spread drops to 1.01×. The one user was the whole problem.

3. Now the monotonic trap — where do the newest writes go?

SELECT width_bucket(id, 1, 1000001, 8) AS shard, count(*)
FROM events WHERE id > 875000        -- the newest 1/8 of rows
GROUP BY shard;

Every one of the newest rows is in the last bucket — 100% of new writes on one shard. Range storage looked even; the write load is a single point. Clean up: docker rm -f sk.

Put it together into how you'd actually make the call.

First, don't shard too early. The best shard key is often no shard key yet. Partitioning vs Sharding made the case: prove with capacity math that one tuned box genuinely can't hold your write rate before you take on any of this. Sharding for reads, or for storage, or because it's what serious engineers do, buys you the cross-shard tax with none of the benefit.

Then, once you've decided to shard, spend real design time on the key — because it's the decision that dwarfs every other one in the section, and the one you can least take back. Walk the four properties against your actual workload: Does the key have enough distinct values? Is any single value hot — do you have a celebrity, a whale tenant, a default that half your rows share? Does your most common query filter on it, or will every read scatter? Can the value change? When a single column can't answer all four — and it usually can't — reach for a composite that pairs alignment with spread.

And design for the write distribution and the read path at the same time. This is the mistake even careful engineers make: they optimize one and forget the other. A key that spreads writes perfectly but scatters every read has traded a write hotspot for a read tax. A key that aligns every read but funnels writes onto one shard has done the reverse. Good means both — evenly spread and single-shard for the common query — because you only get to pour the concrete once.

What you'd say under follow-up: "I'd shard only after proving the write rate exceeds one box. For the key, I'd check four things — cardinality, skew, query alignment, stability — against the real access pattern. I'd be suspicious of the obvious keys: an auto-increment id or timestamp is a write hotspot even though its cardinality is perfect, and the natural entity id melts on a celebrity even though it's query-aligned. I'd usually land on a composite that pairs the entity my common query filters on with something that spreads a hot value — Discord's (channel_id, bucket). And I'd treat it as near-irreversible: getting it wrong is a multi-week, whole-cluster migration, not a config change."

• "High cardinality means even distribution." No — the deepest trap in the topic. user_id has millions of values and still melts on one celebrity (measured: 1.01× without them, 2.05× with). Cardinality is necessary, not sufficient; you also need low frequency per value.
• "A unique key like an auto-increment id is the safest shard key." It's the classic write hotspot: perfect cardinality, but every new write lands on the newest shard (measured: 100% of new writes on one of eight). The safest-looking key is the trap.
• "Hashing the key fixes hotspots." It fixes monotonic and mildly-skewed keys by scattering many values — but a single hot value still hashes to one shard, and hashing also kills range scans. Hashing spreads values; it can't split one value.
• "The database will rebalance a hot shard for me." Mostly not. DynamoDB's adaptive capacity helps spiky load but throttles a sustained single-key hotspot past ~1,000 writes/second; most systems can't auto-compensate at all. Skew is the application's job.
• "Pick the shard key later, once we see the traffic." It's near-irreversible — unchangeable in older MongoDB, a multi-week 50 TB migration in the real case. It's a before-you-code decision.
• "Shard by the entity I think of first." Maybe — but the real questions are which key your common query filters on and whether any value is hot. Often the answer is a composite ((tenant, id)) or a time-bucketed key (Discord's (channel_id, bucket)), not the obvious column.
• "A good key spreads writes evenly." Half the job. It must also keep the common query on one shard — even distribution with query-misalignment just trades a write hotspot for a scatter-gather tax on every read.

• A shard key is an almost-irreversible bet on how your writes spread and how your reads travel — getting it wrong is a whole-cluster, weeks-long migration (a real case: one shard holding 75% of 50 TB), not a tunable. Choose it before you write the code.
• Four properties, all required: high cardinality (enough values to spread), even distribution (no single hot value), query alignment (the common query filters on the key → single-shard), stability (non-monotonic and unchanging).
• The two safest-looking keys are the classic traps. A monotonic id/timestamp has perfect cardinality yet puts 100% of writes on the newest shard (fix: hash it, and lose range scans). The natural entity id is near-perfect (1.01×) until one celebrity melts a shard (2.05×) — and hashing can't split a single hot value.
• Composite keys are how real systems win — pair an attribute for alignment with one for spread. Discord's (channel_id, bucket): single-shard reads, spread writes, ~100 MB shards. A good key makes hotspots rare, not impossible.
• Design for writes and reads at once, and shard last — only after the write rate beats one box. Next — Consistent Hashing: now that you've chosen what to shard on, how do you map keys to shards so that adding a shard doesn't re-home every row?