“I’d shard by user ID.”
This sentence appears in roughly 60% of system design interview answers involving a database. Sometimes it’s correct. Often it isn’t. Almost always it’s stated without the reasoning that determines whether it’s right or wrong.
The reasoning is everything. The partition key isn’t a preference — it’s a consequence of your read pattern.
The rule
Partition on the dimension you query by most frequently.
If the most common query is “give me all messages for user X” — partition by user_id. Every message for user X is on the same shard. One network hop.
If the most common query is “give me all messages in conversation C” — partition by conversation_id. Every message in conversation C is on the same shard.
Partition by sender_id in a messaging system where reads are by recipient? Every delivery requires querying every shard. You’ve distributed your data in a way that makes your hot read path maximally expensive.
How to get it right
Before naming a partition key, write down the two or three most frequent queries the system needs to serve. Then choose the partition key that makes those queries hit one shard.
For a payment system: the hot query is “give me all transactions for account X.” Partition by account_id.
For a ride-sharing system: the hot query is “give me all active drivers within 5km of this point.” Geohashing is the partition key — drivers in the same geohash cell land on the same shard.
For a social feed: the hot query is “give me all posts for user X’s followers, sorted by time.” Partition by user_id — each user’s feed is co-located.
The celebrity problem
Even correct partition key choices have a failure mode: hot partitions.
If you partition a social graph by user_id and one of your users is a celebrity with 100 million followers, every follow/unfollow event writes to one shard. Every timeline read for those 100 million followers potentially reads from one shard. That shard is getting 100× the load of any other.
The fix depends on what’s hot. For write-heavy celebrities: scatter their data across multiple shards using a compound key (user_id + bucket_id), then merge at read time. For read-heavy content: replicate it across shards so reads can be served locally.
The L5 answer names the hot partition problem. The L6 answer names the specific fix and the conditions under which you’d apply it.
The mechanical keys worth memorizing
Some partition key decisions are standard enough that deviating from them signals a mistake:
Messages: by recipient_id (the hot read is “messages for user X”)
Notifications: by user_id (same reason)
Ride locations: by geohash (the hot read is proximity query)
Payment transactions: by account_id (the hot read is account history)
Web sessions: by session_id (the hot read is session lookup by ID)
Analytics events: by event_time or by (user_id, date) if per-user queries dominate
When you name a partition key in an interview, the follow-up is almost always: “what’s the failure mode?” The correct answer is almost always: “hot partitions when the distribution is uneven — here’s how I’d detect and address them.”
What “sharding” actually is
One last thing worth naming explicitly: sharding and partitioning are often used interchangeably in interviews, but they mean slightly different things. Partitioning is dividing data logically. Sharding is distributing those partitions across separate physical nodes. When interviewers ask “how would you shard the users table?” they mean both.
The answer format that works: state the partition key, explain why it fits the read pattern, name the hot partition risk, and describe the mitigation. Four sentences. Every sharding answer.
CTA: The distributed cache walkthrough (paid post #10) goes deep on consistent hashing and the hot partition problem specifically — including the virtual nodes fix and the thundering herd mitigation when a shard fails.
Interested in mastering this topic?
Unlock premium content with deeper explanations and real implementation patterns.
Subscription link
https://systemdr.systemdrd.com/subscribe
—Sumedh
The Question Vault has all 52 walkthroughs organized by archetype — so you can see the pattern across questions, not just the surface answer.
Access all 52 Questions here