Skip to main content
A common question when designing a sharded database is: when a request arrives for a user, how does the system know which shard holds that user’s data? One simple approach is to use the user’s ID and compute a remainder. For example, take the user’s numeric ID, divide by 4, and use the remainder to pick a shard:
  • remainder 0 → shard 0
  • remainder 1 → shard 1
  • remainder 2 → shard 2
  • remainder 3 → shard 3
In this approach the user ID is the column we used to decide which shard a user goes to. That column — whatever column we use to partition the data — is called the shard key. When a request for a user arrives, we apply the same function (for example id % 4) to the user ID and go straight to the corresponding shard to fetch the data. There is no searching and no coordinator to ask. Selecting the shard key is critical: it determines how data and load are distributed across shards, and changing it later is difficult and expensive. Consider this example layout produced by sharding on user ID:
If you later decide to change the shard key to country, every row on every shard must be inspected and many rows moved so that all rows for the same country end up together. For example, Chris (on shard 1) and Leo (on shard 2) are both from India — both rows would need to be relocated to a single shard. This can require a huge rebalancing operation and is error-prone. So be absolutely sure about the shard key before sharding. Below are the five properties to evaluate when choosing a shard key.
  1. High cardinality — many distinct values
    The shard key should have many distinct values so data can be spread across many shards. Sharding by country provides only ~200 values, which is insufficient if you need dozens of shards. User IDs usually have very high cardinality (each user typically has a unique ID), which allows even spreading.
  2. Even distribution (spread)
    Values of the shard key should distribute load and storage evenly across shards. If most users join in the current year, sharding by signup_year will overload the current-year shard while others remain underutilized. If you shard by user ID and generate IDs that map evenly (or hash them), new signups will be balanced across shards.
A dark-themed diagram showing an app/server at the top fan-out to four database shards across the bottom labeled Shard 0 (2023), Shard 1 (2024), Shard 2 (2025), and Shard 3 (2026). The top menu highlights the "2 SPREADS EVENLY" option.
  1. Matches access patterns (minimize scatter-gather)
    Pick a shard key that aligns with your most common queries. If most requests fetch all data for a single user, sharding by user_id ensures the user’s rows are co-located on one shard. If you shard posts by post_id, a query to list all of Chris’s posts requires contacting every shard to find posts authored by Chris and then merging results — a pattern called scatter-gather that defeats the purpose of sharding.
  2. Immutability (the shard key should not change)
    The shard key must remain stable for a row. Changing a shard key value requires moving that row to a different shard; if the operation fails mid-way, data can become inconsistent and the application may not know where to find the row. Immutable keys like internal user_id are therefore safer shard keys than mutable fields like username or email.
  3. Co-location of related data
    Applications often have related tables: users, posts, comments, likes. If users is sharded by user_id but posts is sharded by post_id, a profile view requires fetching user data from one shard and posts from many shards. Sharding related tables by the same key (co-location) keeps related rows on the same shard and lets queries hit a single machine.
A diagram of a sharded database architecture showing an app routing user data (users, chirps, likes) to four database shards, with one shard highlighted. A phone UI labeled "Leo" is shown on the left as the client sending requests.
Because userId commonly satisfies all five properties above, it is often chosen as the shard key.
Choose a shard key carefully. Changing it later requires inspecting and moving data across every shard, which is expensive and error-prone.
A practical problem remains: the simple remainder approach (for example id % 4) works for numeric keys. But shard keys can be non-numeric (for example username or email). How do we map arbitrary values to shard numbers and still spread data evenly? The answer is hashing. Hashing approach
  • Compute a deterministic numeric hash from any shard key value (string or number).
  • Map the numeric hash to a shard by taking hash % num_shards.
  • This supports numeric and non-numeric keys and tends to spread values evenly.
A diagram showing "Shard key = user ID" where an app routes user records to four database shards (0–3) by taking the user ID modulo 4. Example tables under each shard list user IDs and usernames, with ID 41 shown mapping to shard 1.
Example (Python):
Hashing gives a consistent function that works for both numeric and non-numeric keys while helping to spread values across shards. One caveat: if you change the number of shards, the simple hash-then-modulo mapping remaps most keys and requires moving data. To reduce reshuffling when scaling, consider:
  • consistent hashing (reduces the fraction of keys that move when shard count changes), or
  • a mapping/proxy layer that maps keys to shard IDs (allowing you to reassign ranges or virtual nodes without moving all data at once).
Summary table: Key properties to evaluate when choosing a shard key Further reading and references If you need help evaluating candidate shard keys for a specific schema or workload, share your schema and common query patterns and I can help pick and justify a shard key.

Watch Video