user_id is ideal because: (1) High cardinality - 50M unique values, (2) Most queries are 'show my orders' which include user_id, (3) Co-locates all orders for a user on one shard. order_id and created_at would create hotspots on the newest shard.

With consistent hashing, adding N shards to a cluster moves approximately N/(total_shards) of the data. Adding 2 shards to 8 (now 10 total) moves about 2/10 = 20% of the data. This is much better than modulo hashing.

Content-based queries don't include the shard key, making them inefficient for scatter-gather. The best solution is a separate search index (Elasticsearch) optimized for full-text search, kept in sync via events.

3x difference indicates a shard key problem. Either the key has low cardinality, or certain key values are much more common (e.g., sharding by country where 60% of users are in one country). This is called a 'hot shard' or data skew.

To guarantee finding the global top 100, you must fetch at least 100 from EACH shard. In the worst case, all top 100 could be on one shard. So: 100 players x 50 shards = 5,000 players to fetch and re-sort globally.

For small, slowly-changing reference tables like products, replicate them to all shards. This makes JOINs local to each shard. Alternative: denormalize product_name into orders, but that's harder to update when products change.

Keep a secondary index mapping user_id to conversation_ids. This lookup table can be small (just IDs) and unsharded. First query the mapping, then fetch conversations from the appropriate shards using the conversation_ids.

This is a hot partition. One shard key (or small set of keys) is receiving massive traffic. Example: a celebrity user, viral content, or a bot attacking one account. Solutions include rate limiting or special handling for hot keys.

Compound key (tenant_id, user_id) is best: (1) Small tenants fit on one shard, (2) Large tenants spread across shards by user_id, (3) Queries with tenant_id go to subset of shards, (4) Queries with both go to single shard.

When doubling shards with modulo hashing, exactly 50% of data moves (each shard splits in half). Going 4->5 moves ~80%, 4->6 moves ~67%. Doubling is optimal for modulo hashing, which is why powers of 2 are common.

CQRS pattern: stream changes to a separate analytics database (data warehouse) optimized for aggregations. This keeps the operational database fast for transactions while enabling complex queries on the analytics side.

Foreign key constraints are enforced by the database engine, which only sees data on its server. If orders and products are on different shards, the database can't verify the product_id exists. Solutions: application-level checks or co-locate related data.