You need to shard a large orders table across several database nodes. How do you choose a shard key, and what do you give up by sharding?

Choose a shard key that does three things:

• It has high cardinality, which means many different values.
• It spreads data and traffic evenly across the shards.
• It matches the main access pattern, so most queries hit only one shard.

For orders, customer_id often works well. A customer's order history is then one lookup on one shard.

A key that always grows, such as a timestamp, is a poor choice for range sharding (each shard holds a range of key values). All new writes land on the last shard, so that one shard gets all the load.

There are three common ways to map keys to shards:

• Hash-based sharding spreads load evenly. But a range query must ask many shards.
• Range-based sharding keeps range queries on few shards. But growing keys create a hot shard.
• Directory-based sharding uses a lookup service that says where each key lives. It adds that extra service, but lets you move data freely.

What you give up by sharding:

• Cross-shard queries and joins become scatter-gather: you ask every shard and merge the results.
• Cross-shard transactions need sagas or two-phase commit.
• Hot keys can still overload one shard, for example one huge customer.
• Rebalancing data when you add nodes is hard to run in production.

This is why teams usually shard only after vertical scaling, caching and read replicas.