Sharding
Sharding splits a database into smaller pieces (shards) distributed across multiple machines. Each shard holds a subset of the data. This enables horizontal scaling of both reads and writes — but adds complexity in routing, cross-shard queries, and rebalancing.
How it works
Sharding (also called horizontal partitioning) splits a large table into smaller pieces, each on a different machine. Example: users table split by user_id — shard 1 holds users 1-1M, shard 2 holds 1M-2M, etc. Each shard is an independent database.
This enables horizontal scaling of writes (each shard handles its own writes) and reads (each shard handles its own reads). The trade-off: cross-shard queries (e.g., 'count all users') require querying every shard and aggregating.
The shard key (partition key) determines which shard holds a row. Choosing the right shard key is the most important sharding decision:
- Good:
user_id— distributes evenly, enables per-user queries on a single shard. - Bad:
timestamp— all writes go to the latest shard (hot spot). - Bad:
country— uneven distribution (US has more users than NZ). - Bad:
last_name— uneven distribution (more Smiths than Zywickis).
The shard key must distribute data evenly AND enable common queries without cross-shard fan-out.
Adding or removing a shard is expensive if you use fixed ranges (you have to move data). Consistent hashing minimizes data movement: each shard owns a range on a hash ring, and adding a shard only moves the keys in the new shard's range. See the Consistent Hashing lesson.
Sharding introduces hard problems:
- Cross-shard queries: 'count all users' queries every shard and sums. Slow and expensive.
- Joins: joining data across shards is hard. Usually denormalize to avoid it.
- Transactions: a transaction spanning two shards requires distributed transactions (2PC) — slow and complex. Avoid by keeping transactions within a shard.
- Rebalancing: when a shard gets too big, you need to split it — moving data and updating the router. Online rebalancing is hard.
- Hot shards: if the shard key is uneven, one shard gets most traffic. Reshard.
These challenges are why sharding is a last resort — try replication, caching, and read replicas first.
You're sharding a users table. Which shard key is the BEST choice?
Pick one answer.
Your sharded database has 5 shards. A user wants to see their order history. How many shards do you query?
Pick one answer.
Real example: Uber's sharded Schemaless.
By 2014, Uber had outgrown a single PostgreSQL database. Their trips table alone was growing ~1M rows/minute at peak, and writes were the bottleneck (every ride request, driver location update, fare calculation is a write). They built Schemaless, a custom data store layered on top of MySQL, designed around three principles (documented in their engineering blog):
- Sharding by UUID: each trip, user, and driver record is keyed by a 128-bit UUID. The UUID is hashed to determine which shard holds it. Tens of thousands of shards, each a MySQL primary + replicas.
- Schema flexibility: like a wide-column store, Schemaless stores rows as versioned JSON-like blobs (no enforced schema), so they could evolve the data model without painful migrations across thousands of shards.
- Append-only writes: each cell is a list of versions; updates create new versions rather than overwriting. This makes conflict resolution tractable for their multi-region deployment — last-write-wins by timestamp is acceptable for most fields, and CRDTs (commutative data types) for counters.
The migration was incremental: Schemaless ran alongside the existing PostgreSQL for months; new features wrote to Schemaless while old features still read from PostgreSQL. Eventually PostgreSQL was retired. They later documented migrating some Schemaless clusters from MySQL to RocksDB (MyRocks) for ~70% storage savings, demonstrating that even the underlying store of a sharded system can be swapped if the shard router interface is stable.
Key lessons from Uber's experience:
- Sharding is a multi-year investment, not a sprint. Plan for it or pay for it later under duress.
- The shard router is the contract — as long as it routes correctly, the underlying stores can evolve.
- Pick a shard key that you'll never need to change — re-sharding is the most expensive operation in distributed databases.
- Cross-shard queries are inevitable — design your schema so they're rare and read-only (no cross-shard transactions).
When a shard grows too big or a shard key turns out wrong, resharding means moving a large fraction of your data across machines — typically with dual-writes during the migration (write to old AND new shard, read from old, verify, switch reads, stop writes to old, delete old). Slack's 2017 reshard took 2 months of engineering work. Instagram's 2014 move from a single PostgreSQL to 12 shards took 6 months. Uber's Schemaless-to-RocksDB migration took years. The unambiguous lesson: get your shard key right the first time. Pick something stable, high-cardinality, and aligned with your dominant query pattern. If you can't, defer sharding as long as possible — replication and caching are cheaper bandaids.
You're sharding an orders table for an e-commerce platform. The dominant query is 'show me this user's recent orders' (95% of traffic). The remaining 5% are operational: 'count all shipped orders in the last 24 hours' and 'find the order with this tracking_id'. Which shard key best balances these needs?
Pick one answer.
Cross-shard transactions — the thing you must never need.
Sharding distributes data across multiple primaries. If a single business operation must atomically update rows on two different shards (e.g., 'transfer money from user A on shard 1 to user B on shard 3'), you need a distributed transaction.
The two main options:
- Two-phase commit (2PC) — a coordinator asks all participating shards to 'prepare' (lock the rows, promise to commit). If all agree, the coordinator says 'commit' and they all commit. If any shard times out or disagrees, the coordinator says 'abort'. The problem: the coordinator holds locks across all shards during the protocol — often 100ms-1s of latency — which kills throughput. If the coordinator itself dies, the system blocks indefinitely (the coordinator is a new single point of failure).
- Saga pattern — break the multi-shard transaction into a sequence of local transactions, each with a compensating action. Transfer money from A: debit A's shard, then credit B's shard; if the credit fails, run a compensating 'refund A' action. This avoids distributed locking but gives up atomicity — the system is eventually consistent across shards.
The standard advice: design your shard key so that transactions are always single-shard. This is the single most important sharding principle. For an e-commerce platform, shard orders by user_id so that 'create order + update user's order count' happens on one shard. For a chat app, shard conversations by conversation_id so all messages in a conversation live on one shard.
Real-world example: Instagram chose to shard by user_id (hashed) so all of a user's media lives on one shard. This is why their architecture can do 'get user's recent photos' as a single-shard query (fast) and avoids cross-shard transactions for atomic user-level operations. The price they pay: cross-user queries (e.g., 'find users who liked photo X') must fan out across shards. They mitigate this with a separate 'likes' service backed by Redis, not the main sharded store.
Engineering mental model
Mental model. Think of Sharding as a deliberate boundary in a system. The boundary exists because something becomes harder to manage when everything is done in one place: latency, scale, failure isolation, consistency, cost, or team ownership. The useful question is not “what does Sharding mean?” but “what pressure makes this boundary worth introducing, and what new failure mode does it create?”
Before choosing Sharding, name the workload, the critical user path, the dominant bottleneck, the failure you are trying to absorb, and the trade-off you are willing to accept. If you cannot name those five things, the design is probably premature.
// Pseudocode
request = receive()
result = sharding(request)
return result
// Production questions:
// 1. What happens on timeout?
// 2. Can this operation be retried safely?
// 3. What is the bottleneck?Back-of-the-envelope reasoning
Numerical lens: write down traffic, payload size, read/write ratio, peak multiplier and durability target before choosing a component. The numbers should justify the architecture.
Interactive thought experiment: Sharding
Change the variables below and predict what breaks first in Sharding. The production lab can later reuse these same inputs.
Change one variable at a time. Predict the failure mode first, then move the slider and see whether your mental model matches the simplified system response.
If you are stuck on Sharding, start by drawing the request path and marking every network hop, stateful component, queue, cache and failure boundary. Then estimate where the system will saturate.
You increase traffic by 10× in a system using Sharding. What should you inspect first?
Pick one answer.
Which statement is the safest engineering habit when using Sharding?
Pick one answer.
You have dashboards for traffic, latency, errors and saturation. You can change the architecture, but every change has operational cost.
Production scenario: your system uses Sharding, traffic suddenly spikes, and p99 latency doubles. What is your first move?
Interview drill
Answer this without notes: When would you choose Sharding, and when would you intentionally avoid it? Mention at least one bottleneck it addresses, one failure mode it introduces, and one alternative. Then quantify the workload you are designing for.
For Sharding, start with access patterns rather than brand names. Identify the dominant reads/writes, data relationships, consistency requirements, partition key, hot keys and failure behavior before choosing a storage strategy.
Numerical sanity check
A rough capacity check: required write throughput ≈ peak writes/s × average record size. At 5,000 writes/s and 2 KB average payloads, the raw incoming data stream is about 10 MB/s before indexes, replication and overhead.
Do not optimize for a memorized definition. Reason from the workload and failure mode.
A team proposes Sharding because it 'scales'. What workload characteristic would make that choice a poor fit?
Pick one answer.
What you gain, what you pay
- +Scales both reads AND writes (unlike replication, which only scales reads).
- +Each shard is independent — no distributed writes for per-shard operations.
- +Enables databases beyond a single machine's capacity.
- −Cross-shard queries are expensive (fan-out + aggregate).
- −Cross-shard transactions require 2PC (slow, complex).
- −Rebalancing is hard (adding/removing shards moves data).
- −Choosing the wrong shard key is catastrophic (hot spots, uneven distribution).
- −Operational complexity — backup, monitor, and migrate N databases.
How this breaks in production
- Hot shard — bad shard key causes uneven distribution.
- Cross-shard transactions — slow and can deadlock.
- Rebalancing — moving data between shards is expensive and risky.
- Shard key change — if you need to re-shard, it's a massive migration.
Don't fall into these traps
- •Sharding too early — try replication, caching, and read replicas first.
- •Choosing a shard key that creates hot spots (timestamp, auto-increment).
- •Forgetting that cross-shard queries are expensive — design queries to be shard-local.
- •Not planning for rebalancing — what happens when a shard grows too big?
Real systems using this
How real systems implement this
- Uber — Sharded Schemaless (their custom DB on top of MySQL) by UUID. Each shard is a MySQL primary + replicas. Tens of thousands of shards.
- Cassandra — Sharding is built-in. Uses consistent hashing to distribute data across nodes. Each node owns a range of the hash ring.
Practice saying it out loud
- Q1What is sharding? When would you shard a database?
- Q2How do you choose a shard key?
- Q3What are the challenges of sharding?
- Q4Sharding vs replication — how do they differ?
Further reading & references
Core explanations are original NO CAP material. External references are provided for deeper study and standards.
What next?
Mark as understood once the mental model clicks.
Next recommended
Federation