When must you shard a database, and how do you choose the shard key?

Three sharding schemes have three operational profiles. Range sharding is easy to reason about and supports range scans, but creates hot spots — shard by date and today's shard takes all the writes. Hash sharding distributes evenly, in exchange for losing range scans and needing to redistribute nearly everything when you add a shard. Consistent hashing cuts the movement to roughly 1/n, which is why it's the default when you know more shards are coming.

The real pain isn't picking a scheme but everything you give up: no cross-shard foreign keys, cross-shard transactions need coordination, a JOIN across shards moves into the application, and AUTO_INCREMENT stops working so you need UUIDs or Snowflake ids. Each of those is complexity added to EVERY feature you build afterwards, not a one-time setup cost.

For choosing the key, the best test is: list your ten most important queries and count how many already filter on the candidate. Fewer than eight of ten means the wrong key. For multi-tenant systems tenant_id is nearly always right, unless one enormous tenant holds most of the data — then you need a composite key or that tenant on its own, and saying so shows you've met the real problem.