A team maintains an index table that maps order status — one of PENDING, SHIPPED, DELIVERED, or CANCELLED — to order IDs, so they can quickly list all orders in a given status. Under heavy load, writes to this index table start throttling even though the base orders table stays healthy. What's the likely root cause, and how would you redesign the index table's key to fix it?

## Why the key choice bites This is a hot-partition problem, and it's a direct consequence of how index tables are usually keyed: the partition key is the indexed attribute's value, chosen because it's exactly what a query needs to look up by. That choice is correct for query simplicity but can be badly wrong for write distribution when the indexed attribute has low cardinality — few distinct values — because most horizontally-scaled key-value stores (DynamoDB, Azure Table Storage, Cassandra, Cosmos DB): - achieve their aggregate throughput by spreading data and traffic across many physical partitions keyed by hash ranges of the partition key; - but every single partition key value always maps to exactly one physical partition (or a small, fixed replica set), and that one partition has a hard throughput ceiling regardless of how much capacity is provisioned for the table overall. DynamoDB, for example, has historically capped a single partition around 1,000 write capacity units per second (and around 3,000 RCU for reads) no matter how many thousands of WCU you've provisioned in aggregate for the table. ## What four values do to the table In this scenario, the indexed attribute — order status — has exactly four distinct values. That means the index table has, at most, four physical partitions doing any work at all, no matter how the store's autoscaling or partition-splitting logic tries to help; you cannot split a partition further than one distinct key value can go. And the traffic isn't even evenly spread across those four: - a healthy e-commerce system has most of its live orders sitting in `PENDING` (just created, being fulfilled) or transitioning through `SHIPPED`; - while `DELIVERED` and `CANCELLED` are comparatively rare events per unit time — an order becomes `DELIVERED` once and then typically isn't written to again under that key, whereas `PENDING` receives a write for every single new order created. So the write load concentrates overwhelmingly on one or two of the four partitions, and those specific partitions throttle — returning capacity-exceeded errors — while the table's aggregate provisioned capacity, spread in theory across four partitions, looks nowhere near exhausted in the account-level or table-level metrics, which is exactly the confusing symptom described: the base table (keyed by the high-cardinality order ID) is healthy because every order gets its own partition-ish slice, but this index table (keyed by four-valued status) is starving. ## The fix — widen the key space The fix is to break the natural one-partition-per-status structure by artificially widening the key space — a technique generally called write sharding or salting. Instead of using the raw status value as the partition key, compute a composite key that combines the status with some evenly-distributed extra bits, such as a hash of the order ID (or a random number, or a round-robin counter) modulo some shard count N — for example, `PENDING-0`, `PENDING-1`, ... `PENDING-7` for `N=8` shards. Each individual order's index-table write now lands on one of the N sub-partitions for its status, chosen by the hash, spreading what used to be one hot partition's worth of traffic across N physical partitions, each handling roughly 1/N of the load. The trade-off is on the read side: 'list all `PENDING` orders' can no longer be answered by a single partition query — it now requires fanning out N parallel queries (one per shard) and merging the results client-side, which adds complexity and some latency (bounded by the slowest of the N parallel reads) in exchange for removing the write bottleneck. ## Choosing N Choosing N is itself a judgment call: - too small and the hot-partition problem persists in diminished form; - too large and you pay unnecessary read fan-out cost and per-shard overhead for a table whose absolute load might not have needed that much spreading. Teams typically size N based on the peak observed write rate for the hottest status divided by the known single-partition throughput ceiling, with headroom for growth, and may choose to shard only the specific status values that are actually hot (leave `DELIVERED` and `CANCELLED` unsharded, shard `PENDING` and `SHIPPED` into, say, 8 sub-partitions each) rather than applying uniform sharding to a key space that doesn't need it everywhere. This exact class of fix — sharding a low-cardinality partition key with an appended hash or random suffix to avoid a hot partition — is explicitly documented in AWS's own DynamoDB best-practices guidance as the standard remedy for this failure mode.