Catalog My learning Blog
Scalability Patterns: Load Balancing, Caching, Sharding · Phase 3 · Lesson 13 · 11 min read

How to Choose a Shard Key

On this page
  1. Learning Objective
  2. Concept Explanation
  3. Worked Example: Evaluating Four Candidate Keys for a Ticketing System
  4. Real-World Examples
  5. Review Questions & Answers

Learning Objective

Evaluate a candidate shard key against the four properties that determine whether a sharded system works, cardinality, distribution, query alignment, and immutability, and describe the realistic remediation paths available once a bad shard key is already in production.

Concept Explanation

The shard key is the single most consequential decision in a sharded system, and it is close to irreversible. It determines which queries are cheap and which are fleet-wide, which operations can be transactional and which cannot, and whether write load spreads or concentrates. It is chosen early, when the least is known about the eventual access patterns, and changing it later means physically moving every row in the dataset while the system stays online. Treating it as a schema detail rather than an architectural commitment is how teams end up rewriting their data layer two years later.

Four properties decide whether a key will work. The first is cardinality: the key must have far more distinct values than you will ever have shards, ideally by orders of magnitude, because the number of distinct values is a hard ceiling on how finely the data can ever be divided. Sharding on country code caps you at a couple hundred partitions and guarantees that the largest country is a permanent hot shard. The second is distribution: values must be spread evenly across the key space in both storage and access frequency. High cardinality alone is not enough, because a key can have millions of distinct values while ninety percent of traffic touches a handful of them, which is exactly the celebrity and hot key problem covered in the next phase.

The third property is query alignment, and it is the one most often overlooked. Every query that includes the shard key can be routed to a single shard, and every query that does not must be broadcast to all of them. So the shard key should be the value present in the largest share of the high-volume queries. If an application overwhelmingly reads by user, sharding by user is right even if some other key distributes more evenly, because the alternative is turning the most common query in the system into a scatter-gather. This is also where a compound key earns its keep: choosing a key whose prefix matches the query filter and whose remainder provides ordering makes the common query a single-shard ordered scan. The fourth property is immutability. The shard key determines physical location, so changing a row shard key means deleting it from one shard and inserting it into another, which is not an update, is not atomic across shards, and can produce a row that exists twice or not at all if it fails partway. Never shard on anything a user or a business process can edit: not email, not username, not team or organization membership, not status.

When a bad key is already in production, there are four realistic paths and no painless one. The first is a full migration to a new shard key, executed as a dual-write and backfill: write to both the old and the new topology, backfill historical data in batches, verify the two agree, shift reads gradually, then retire the old. It is the only path that truly fixes the problem, and it typically takes months for a large dataset. The second is a secondary index or lookup table that maps the frequently-queried value to the shard holding it, which converts a scatter-gather into two point lookups. This is a genuinely good fix for the specific case of queries that need a different key, and it is what many managed databases call a global secondary index, though it comes with its own consistency lag. The third is to add a hashed or random suffix to the key for the specific hot values only, which spreads a hot tenant across several shards at the cost of making reads for that tenant a fan-out over a small known set, and it is covered in detail in the hot key lesson later in this course. The fourth, which is underrated, is to move the offending access pattern to a different system entirely: a search index, an analytics warehouse, or a denormalized read model built from a change stream, so that the sharded transactional store only has to serve the queries its key is actually aligned with.

One more observation is worth internalizing: the hardest shard key problems are usually not technical but organizational, because the correct key depends on access patterns that will exist in two years, and nobody knows those yet. The defensive move is to shard on the most stable, highest-cardinality entity in your domain model, typically the thing the business is fundamentally about, an account, a tenant, a user, a customer, rather than on whatever key made today most convenient. Domain entities of that kind tend to remain the center of gravity of the access patterns even as features change around them, and they are immutable by nature because they are identities rather than attributes.

PropertyQuestion to AskConsequence of Failing It
CardinalityAre there orders of magnitude more distinct values than shards, forever?A hard ceiling on how far the system can ever be split
DistributionIs both data volume and request volume spread evenly across values?Hot shards that saturate while the rest idle
Query alignmentDoes the majority of high-volume query traffic filter on this key?The most common query becomes a fleet-wide scatter-gather
ImmutabilityCan this value ever change for an existing row?Updates become cross-shard move operations that are not atomic

Worked Example: Evaluating Four Candidate Keys for a Ticketing System

Scoring candidates against the four properties turns an argument into an analysis. The example below is a support ticketing product, and the candidate that looks most natural is the one that fails hardest.

Code
# Domain: support tickets. Orgs have users; users file tickets; agents
# work queues. Top queries by volume:
#   Q1 (68%)  list tickets for one org, filtered and paginated
#   Q2 (19%)  fetch one ticket plus its comments
#   Q3  (9%)  list tickets assigned to one agent
#   Q4  (4%)  global search across all orgs (admin tooling)

# CANDIDATE A: ticket_id
#   cardinality   EXCELLENT   billions
#   distribution  EXCELLENT   uniform if hashed
#   alignment     TERRIBLE    Q1 is 68% of traffic and has no ticket_id,
#                             so the dominant query hits every shard
#   immutability  EXCELLENT
#   -> Verdict: the obvious choice, and wrong. It optimizes Q2 (19%)
#      and destroys Q1 (68%).

# CANDIDATE B: assigned_agent_id
#   cardinality   OK          thousands
#   distribution  POOR        queues are wildly uneven; agents churn
#   alignment     POOR        only Q3, at 9%
#   immutability  FATAL       reassigning a ticket changes its shard,
#                             which is a cross-shard move, not an UPDATE
#   -> Verdict: disqualified on immutability alone.

# CANDIDATE C: org_id
#   cardinality   GOOD        hundreds of thousands, growing
#   distribution  UNEVEN      a few enterprise orgs are 1000x the median
#   alignment     EXCELLENT   Q1 (68%) and Q2 (19%) both carry org_id
#   immutability  EXCELLENT   an org never changes its own identity
#   -> Verdict: correct choice. The distribution weakness is real but is
#      a hot-shard problem with known mitigations, whereas alignment and
#      immutability failures have none.

# CANDIDATE D: (org_id, ticket_id) compound, hashing org_id only
#   Same as C, plus all of one org's tickets are co-located and locally
#   ordered by ticket_id, so Q1's pagination is a single-shard ordered
#   scan rather than a merge.
#   -> Verdict: best. This is C with the ordering made explicit.

# Handling what the chosen key does NOT serve:
#   Q3 (agent queues): a lookup table agent_id -> [(shard, ticket_id)],
#                      maintained from the change stream. Two point reads
#                      instead of a 4096-way fan-out.
#   Q4 (global search): not the shard store's job at all. Feed a search
#                      index from the change stream and query that.
#   Enterprise hot orgs: give the largest orgs dedicated logical shards
#                      via the physical map, which the directory layer
#                      from the previous lesson already supports.
Four candidate shard keys scored against cardinality, distribution, query alignment, and immutability, showing how the most obvious key fails query alignment for the dominant read, how a mutable key turns an update into a cross-shard move, and how a compound key co-locates and orders related rows.
Four candidate shard keys scored against cardinality, distribution, query alignment, and immutability, showing how the most obvious key fails query alignment for the dominant read, how a mutable key turns an update into a cross-shard move, and how a compound key co-locates and orders related rows.

Real-World Examples

  • Notion published a detailed account of sharding its PostgreSQL workload in 2021 and described choosing the workspace as the partition key rather than the block, on the reasoning that nearly all of its queries are scoped to a workspace and that partitioning by block would have turned the most common access pattern into a cross-shard operation. The post also describes the dual-write, backfill, verify, and gradual read-shift migration sequence that this lesson names as the only true remediation for a key that turns out to be wrong.
  • Figma described in 2024 how it sharded PostgreSQL by choosing per-table shard keys that route related objects to the same shard, using the file identifier as the key for the tables that make up a document so that the overwhelming majority of queries remain single-shard, and explicitly noting that tables which could not be assigned a compatible key were handled separately rather than forcing them into the same scheme. The published design is a clear statement that query alignment, not distribution alone, drove the key selection.
WarningNever shard on a value that can be edited. A shard key determines physical location, so changing it is a delete on one shard plus an insert on another, with no transaction spanning the two, which means a crash at the wrong moment leaves the row duplicated or gone. Reassignable owners, editable emails, and status fields all look like reasonable keys and all become data-integrity incidents the first time somebody changes one.

Review Questions & Answers

Cardinality only guarantees that the key can be divided finely; it says nothing about whether the data or the traffic divides evenly across those divisions. A key can have hundreds of millions of distinct values while a small number of them account for most of the rows and most of the requests, which is the normal shape of real-world popularity distributions. Sharding by user id in a social product is high-cardinality and still produces shards holding a celebrity account that receives more traffic than entire other shards combined. Cardinality is a necessary precondition because a low-cardinality key caps partitioning outright, but distribution of both storage and access must be assessed separately, using the actual observed frequency distribution rather than an assumption of uniformity.

Because a distribution problem is bounded and has mitigations, while a query alignment problem is unbounded and does not. If a key distributes unevenly, the result is a hot shard, which can be addressed by splitting it, by giving the largest tenants dedicated shards through the directory layer, or by key salting for the specific hot values, all of which are local interventions that leave the rest of the system unchanged. If a key does not align with the dominant query, then the most frequent operation in the system must be broadcast to every shard, every time, forever. Its cost grows with fleet size rather than result size, its latency is bounded by the slowest shard, and it gets worse with every shard you add, meaning the sharding scheme actively works against the thing it was introduced to do. The only fix is changing the key, which is a months-long migration.

The first option is a lookup table or global secondary index mapping organization to the set of users or shards containing its rows, turning a fleet-wide broadcast into a lookup plus a targeted fan-out to a known small set of shards. It costs an additional structure to maintain and introduces consistency lag between the index and the data. The second is a denormalized read model built from a change stream and organized by organization, which serves that query pattern natively at the cost of running a pipeline and accepting eventual consistency. The third is to accept the scatter-gather but bound its cost with aggressive caching and strict pagination, which works while the query stays a small share of traffic and stops working as it grows. The fourth is to move the query to a different system entirely, such as a search index or analytical store. Which one is right depends on how central the query is becoming: if it is on track to be the dominant access pattern, these are all delaying tactics and the honest answer is to plan the migration.

Because the shard key must remain correct across years of product change, and the thing that predicts future access patterns best is the entity the business is fundamentally organized around. Accounts, tenants, users, and customers tend to remain the center of gravity of queries even as features are added and removed, because new features are almost always scoped to one of those entities. They are also immutable by construction, since they are identities rather than attributes, which satisfies the property that disqualifies most convenient alternatives. A key chosen purely for even distribution optimizes a property that has local mitigations, while sacrificing alignment and stability, which do not. The recommendation is essentially to optimize for the thing you cannot fix later rather than the thing you can.