Sharding and partitioning

One machine has a ceiling: disk, connections, write IOPS, and the time to rebuild a replica. Sharding raises the ceiling by putting different rows on different machines. It also makes some queries, transactions, and schema changes harder. Propose it when you can point at the ceiling, not as a default.

Partition first

Partitioning splits a table inside one database. The common case is time: each month of events lives in its own partition, queries include the month, and dropping a year is a metadata operation. This does not add write throughput beyond one machine. It does make retention and backups sane. Say “partition” when you mean this, and “shard” when you mean separate servers.

The key

The shard key is a column (or columns) present in the hot path. All rows with the same key live together.

• Hash. Spreads keys evenly. Range queries on the key become scatter-gather. Good for user ids.
• Range. Keeps ranges together, which is what you want for time series and for “scan this customer’s timeline.” The newest range is often the hot one.
• Directory. A lookup table maps key to shard. Flexible, and the directory is now a dependency.
• Geo. Pin a user’s rows to a region for latency or residency. Cross-region features become explicit.

If you cannot name the key, you are not ready to shard.

What gets worse

Hot keys. Even a good hash has celebrities. Measure, then isolate them: a bucket suffix (artist_id#0 … #15) with a merge on read, or a dedicated shard.

Cross-shard queries. Fan out, merge, and put a deadline on it. If the product cannot work without a global sorted query, maintain a separate index or accept that this query is approximate.

Cross-shard transactions. Avoid them. Place rows that must commit together on the same shard (the tenant id, the conversation id). If you cannot, you have a saga.

Resharding. Doubling the cluster with consistent hashing moves about half the keys once, not all of them. Moving them is still an online job: copy, catch up a changelog, flip a directory, delete the old copy. “We will reshard over a weekend” is not a plan at this size.

Secondary indexes. A global index is itself a distributed system. A local index on each shard only answers queries that already know the shard. Pick one and say which queries it serves.

flowchart TB
  R[Router] --> S0[Shard user 0-n]
  R --> S1[Shard user n-2n]
  S0 --> I0[Local index]
  S1 --> I1[Local index]