Q36 — Why do we shard databases — and what breaks when we do?

What problem does sharding solve that replication doesn't?

Replication (Q35) copies all data to multiple machines. This solves fault tolerance and read scaling — but it doesn't solve write scaling or storage capacity. Every replica still holds the entire dataset. If your dataset is 10TB, every replica is 10TB. If writes are 100,000 per second, every write still goes to one primary server.

When you outgrow a single machine's write capacity or storage capacity, replication isn't the answer. You need to split the data across multiple machines, where each machine holds only a fraction of the total dataset. This is sharding — also called horizontal partitioning.

With 4 shards, each shard holds ~25% of the data. Writes are distributed across 4 primaries (one per shard), quadrupling write throughput. Total storage is distributed — each machine handles 25% of the storage requirement. Adding more shards linearly scales both write capacity and storage capacity.

How does sharding actually work?

Every row in your database is assigned to a shard based on a shard key — a field (or combination of fields) whose value determines which shard owns that row. A shard key must be chosen at schema design time and is generally immutable — changing a row's shard key requires moving it to a different machine, which is expensive.

Common sharding strategies:

Range-based sharding: Divide the key space into ranges. Shard 1 owns user IDs 1–1,000,000. Shard 2 owns 1,000,001–2,000,000. Shard 3 owns 2,000,001–3,000,000.

Good for: range queries stay on one shard (SELECT * FROM orders WHERE user_id BETWEEN 100 AND 500). Sequential access is fast.

Bad for: hot spots. If user IDs are assigned sequentially and new users are most active, all new-user writes go to the last shard (the one holding the highest IDs). Old shards are idle; the newest shard is overwhelmed. The load distribution is skewed by access pattern, not by shard count.

Hash-based sharding: shard = hash(user_id) % num_shards. Keys distribute uniformly regardless of their values. No hot spots from sequential access.

Good for: even load distribution across shards. Bad for: range queries scatter across all shards (SELECT * FROM orders WHERE user_id BETWEEN 100 AND 500 must hit all shards and merge results — called scatter-gather).

Directory-based sharding: A lookup table maps each key to its shard explicitly. The most flexible approach — you can rebalance by updating the lookup table without moving data first. But the directory is a bottleneck: every request must consult the directory, and the directory must be fast and highly available.

What breaks when you shard — the costs you pay

Sharding is a powerful technique that fundamentally changes what your database can and cannot do efficiently. Understanding what breaks is as important as understanding what it enables.

Cross-shard joins are gone. A JOIN between two tables works when both tables are on the same machine — the database engine can access both locally. Once a table is sharded across 10 machines, a join between users and orders might need to compare rows from 10 different machines. The database engine can't do this natively. You must fetch the data from multiple shards in application code and join it there — a scatter-gather operation that's slower, more complex, and loses the query optimizer's ability to push predicates down into the join.

Most sharding strategies avoid cross-shard joins by co-locating related data on the same shard. If orders are always sharded by user_id and users are also sharded by user_id, a user and all their orders live on the same shard — the join is local. But this only works for one relationship; other join patterns still scatter.

Cross-shard transactions are painful. An atomic transaction that modifies rows on different shards requires distributed transaction coordination (two-phase commit — covered in Q40), which is slow and has serious failure modes. Most sharded systems either avoid cross-shard transactions by design (ensuring related data is on the same shard) or accept eventual consistency for cross-shard operations.

Unique constraints are shard-local. A UNIQUE constraint on email in a single-database setup means no two rows anywhere have the same email. In a sharded setup, the UNIQUE constraint is enforced within each shard, not globally. Two users with the same email can exist on different shards. Enforcing global uniqueness requires a separate coordination service (a dedicated "email → user" lookup table, possibly on its own machine) or accepting the possibility of duplicates and catching them at the application level.

Auto-increment IDs are broken. If Shard 1 and Shard 2 both have auto-increment primary keys, they'll both generate ID 1, ID 2, ID 3... Rows from different shards will have colliding IDs. You need globally unique ID generation — either a centralized ID service (Facebook's TAO used this), UUID generation (globally unique by construction), or a distributed ID generator like Snowflake (covered in Q61).

Rebalancing is expensive. When you add a new shard (because existing shards are full or overloaded), some existing data must move to the new shard. Moving data means: read from old shard, write to new shard, update routing, delete from old shard. While data is moving, queries must account for data being in-flight. Rebalancing a large sharded database can take hours or days and requires careful orchestration to avoid inconsistency during the migration.

Schema migrations are painful. Running ALTER TABLE in a single-database setup is one operation. In a 20-shard setup, it's 20 operations, executed in sequence or with coordination, across 20 independent database instances. Schema migrations become a significant operational challenge.

Operational complexity multiplies. Instead of monitoring one database, you're monitoring N. Instead of backing up one database, you're backing up N. Slow query analysis, index tuning, capacity planning — all N times the complexity. The operational burden of a sharded database is dramatically higher than a single database.

How do you choose the right shard key?

The shard key determines the entire character of your sharding strategy. A bad shard key causes problems that are extremely hard to fix without resharding — a painful operation.

Good shard key properties:

• High cardinality: many distinct values → fine-grained distribution
• Even distribution: values spread uniformly → no hot shards
• Query locality: most queries filter on the shard key → queries stay on one shard
• Immutable: once assigned, never needs to change (changing means moving the row)

Classic mistakes:

• Sharding by country: 50 distinct values globally, but the US generates 40% of traffic. The US shard is always overloaded.
• Sharding by created_at (time): new data all goes to the newest shard. Hot shard guaranteed.
• Sharding by a field that users change: now the row must move shards on update.

Classic good choices: user_id (high cardinality, even distribution, most queries filter by user), tenant_id (for multi-tenant SaaS — each tenant's data is co-located), hashed primary key (even distribution, no hot spots).

When is sharding the right answer vs wrong answer?

Sharding is expensive — it should not be the first response to database performance problems.

First, try: better queries (indexes, query optimization), read replicas (for read scaling), vertical scaling (bigger machine), connection pooling, caching. These are all dramatically simpler than sharding.

Shard when: you've exhausted single-machine capacity — either the dataset genuinely can't fit on one machine, or write throughput genuinely exceeds what one primary can handle. These are high bars. Most applications never reach them.

The mental model

Replication: copies all data to N machines → fault tolerance + read scaling
             Doesn't help: write throughput limit, storage capacity limit

Sharding: splits data across N machines → each holds 1/N of data
          Helps: write throughput (N primaries), storage (N × per-machine capacity)

Shard key determines which machine owns each row:
  Range: good for range queries, bad for sequential hot spots
  Hash: good for even distribution, bad for range queries (scatter-gather)
  Directory: most flexible, but directory is a bottleneck

What breaks:
  Cross-shard JOINs → scatter-gather in application code
  Cross-shard transactions → 2PC or saga (both hard)
  Global uniqueness → centralized lookup or UUID-based IDs
  Auto-increment IDs → need distributed ID generator
  Rebalancing → expensive data migration when adding shards
  Schema migrations → N separate ALTER TABLE operations
  Operations → N× monitoring, backup, tuning complexity

Good shard key: high cardinality, even distribution, query locality, immutable
Bad shard keys: time, country, low-cardinality fields, mutable fields

Escalation order:
  Query optimization → indexes → read replicas → vertical scaling → caching → then shard

Sharding is the solution to the problem you have after you've done everything else. It multiplies write capacity and storage capacity but divides operational simplicity, query expressiveness, and transactional guarantees. It's a trade-off you make consciously and deliberately, not a default architecture choice.