How do you choose a datastore, and then how do you choose its shard key?

Start from the queries, not the categories

The useful way to pick a store is to write down the three or four operations that dominate the workload and ask which engine makes those cheap. "Fetch a user's last fifty orders by recency" is a clustered range read; "look up a session by token" is a point read with a TTL; "count orders by region and month over two years" is a scan-and-aggregate that no row store will love. If you lead with the query shapes, the choice usually makes itself, and you get to defend it in terms the interviewer can test.

The second question is which invariants the store has to enforce rather than the application. If money must not be double-spent, if a booking must not be sold twice, if two rows must change together or not at all, you want multi-row transactions and real constraints, and a relational engine is the boring correct answer. Document stores are excellent when the unit of consistency is one aggregate that you read and write whole, because then a single-document atomic update is genuinely sufficient. Wide-column stores buy enormous write throughput and predictable latency, but they buy it by making you commit to your access paths in advance, so they punish an application whose query patterns are still moving. A search index and an analytics warehouse are both derived stores fed from a source of truth, never the source of truth itself, and saying that unprompted is worth credit because treating a search cluster as primary storage is a real and common production mistake.

Only then consider scale. Data volume alone rarely decides anything, because a single well-indexed relational node handles far more than candidates expect. What decides it is write throughput that exceeds one primary, or a working set that no longer fits in memory, or a latency target that a shared node cannot hold at the ninety-ninth percentile.

Shard last, not first

Sharding buys write scalability and nothing else, and it charges for it in cross-shard queries, distributed transactions, rebalancing operations and a permanently more complicated data layer. So exhaust the cheaper options and be seen to do so: read replicas if reads dominate, a cache if the same rows are read repeatedly, better indexes if the problem is really a sequential scan, moving cold data to a separate store if the working set is the issue, and single-node partitioning if the problem is unbounded table size rather than throughput. Declarative table partitioning within one engine is often mistaken for sharding; it helps pruning and retention, and it does nothing for a saturated primary.

When writes genuinely exceed one node, shard. And shard on a plan, because the shard key is close to irreversible: it is embedded in every routing decision, every query, and every client that has cached where things live.

What a shard key must satisfy

Three properties, and they pull against each other.

It must let the dominant query hit one shard. This is the property people get wrong. If your hottest read is "everything for this customer" and you shard on order id, every read becomes a scatter-gather across all shards, so your tail latency is now the slowest of N shards on every request and you have made the system worse at the thing it does most. Shard on customer id and that read is a single-shard operation.

It must spread load, which is not the same as spreading data. A key with high cardinality can still be catastrophically skewed if traffic concentrates on a few values, and it usually does, because real workloads are power-law distributed. Timestamp-derived keys are the classic failure: every write goes to the newest range, so one shard absorbs all ingest while the rest sit idle, and adding shards does not help.

It must be present on every request that needs routing. A key the client does not know cannot route a request, which is why systems sometimes embed the shard hint in an identifier — a customer-scoped id that carries its own shard prefix means routing needs no lookup table.

-- Wrong for a workload dominated by "this customer's recent orders":
-- routes by order_id, so the common read fans out to every shard.
shard = hash(order_id) % N

-- Right: co-locates a customer's orders on one shard, keeps the
-- ordering column inside the shard so recency scans stay local.
shard      = hash(customer_id) % N
within     = (customer_id, created_at DESC, order_id)

The composite idea generalises. Where a single key is either too skewed or too fragmented, shard on a coarse key and order within the shard on a finer one. That gives you locality for the queries that need it without concentrating writes on one range.

Hot shards

Assume you will get one, because a uniform hash guarantees uniform key distribution and says nothing about how often each key is touched. The pathological version is a single value so hot that no partitioning scheme helps: one celebrity account, one enormous tenant, one product on the front page. A hash cannot split a single key across shards, and this is the point candidates miss when they answer "hot shard" with "use a hash".

The remedies are all about manufacturing more keys or removing the traffic. You can salt the key by appending a small bounded suffix, so one logical entity becomes ten physical partitions and reads fan out over exactly those ten — cheap for writes, and it costs you a ten-way read, which is only acceptable for the small number of entities that need it. You can pull the hot entity out into its own dedicated shard, which is what "shard splitting by hand" looks like and is often the pragmatic answer for a whale tenant. Or you can serve it from a cache and stop the traffic reaching storage at all, which is usually right for read hotspots and useless for write hotspots. Distinguishing read hotspots from write hotspots before prescribing is itself a signal, because they have almost no remedies in common.

Designing for the reshard you have not done yet

The version of this question that separates senior from mid is what happens when eight shards become twelve. If you routed with hash(key) % 8, changing to twelve remaps roughly every key, which means rewriting the entire dataset while serving traffic. That is why production systems do not shard onto physical nodes directly. They hash into a large fixed number of logical buckets — hundreds or thousands, chosen once and never changed — and then map buckets to nodes. Adding a node moves whole buckets, each of which is a bulk copy of a known key range, and no key's bucket assignment ever changes. Consistent hashing with virtual nodes solves the same problem from the other direction.

If you did not build that in and must reshard anyway, the procedure is a migration, not a config change: stand up the new topology, dual-write to old and new while backfilling historically, verify by comparison rather than by faith, cut reads over per-shard or per-tenant so a mistake is partial, then stop the old writes after a period during which rollback is still possible. Reads during the window need a rule for which copy is authoritative, and getting that rule wrong is how migrations silently lose writes.

The other thing to state before the interviewer asks: after sharding, a transaction spanning two shards needs either two-phase commit, with its coordinator failure modes and held locks, or a redesign that removes the need. The redesign is usually right. Choose the shard key so the transactional boundary sits inside a shard, and where a workflow genuinely spans shards, make it an asynchronous saga with compensations and an idempotent retry path rather than a distributed transaction.

A shard key is chosen by the query you run most and the reshard you will run later; distribution uniformity is the easy constraint and almost never the one that breaks you.