Sharding

Sharding is the database version of splitting one giant restaurant line into several smaller counters: faster, but only if the host knows who goes where.

Question: What is sharding in SQL databases?

Answer: Sharding means splitting one large table or dataset across multiple database nodes so each node stores only a slice of the rows. The shard key decides which rows go where, and a router or the application uses that key to find the right shard. This is a form of horizontal scaling, because you add more machines instead of making one machine bigger.

Interview-Ready Answer: I’d say sharding is horizontal scaling for data: instead of one database holding every row, I split rows across multiple shards using a shard key such as customer_id or tenant_id. A router, or the app itself, computes the target shard and sends reads and writes there. The big win is that each shard has less data and less load, but the trade-off is that joins, transactions, and resharding get more complex.

What sharding really is

Sharding is horizontal partitioning across independent databases. Horizontal means you split by rows, not by columns. Each shard is a smaller database that can have its own indexes, memory cache, connection pool, backups, and failure mode. The key idea is simple: every row belongs to exactly one shard by rule, usually based on a shard key (also called a partition key), such as tenant_id, user_id, or order_id.

How it works under the hood

1. Pick a shard key. You choose a column that appears in most important queries and spreads data reasonably evenly. Good keys are stable, frequently used in WHERE clauses, and not extremely skewed.
2. Choose a placement rule. The system maps the key to a shard using a strategy such as hash-based, range-based, or a directory lookup. This mapping is the heart of sharding.
3. Route the request. A router, proxy, or application code computes the shard from the key and sends the SQL statement to that one database. If the key is missing, the query may have to fan out to many shards.
4. Run normal SQL on the shard. Inside one shard, everything looks familiar: indexes, joins within that shard, transactions, constraints, and query plans all still work like a normal database.
5. Merge results when needed. If the query must touch several shards, the app or middleware performs a scatter-gather operation: send the query to many shards, collect results, then sort, aggregate, or deduplicate them.
6. Rebalance when growth changes the map. If one shard gets too big or too hot, rows are moved to new shards. This process is called resharding, and it is expensive because rows, indexes, and sometimes cache warm-up all have to move too.

Common sharding strategies

• Range sharding: keys are stored in ranges, like customer IDs 1–1,000,000 on shard A and 1,000,001–2,000,000 on shard B. This is great for range queries, but it can create hot spots if new IDs are always increasing.
• Hash sharding: a hash function spreads keys more evenly, which helps avoid hot shards. The downside is that range queries become harder because nearby values are no longer stored together.
• Directory sharding: a lookup table says exactly which shard each key belongs to. This is flexible, but the directory becomes another piece of state you must keep consistent and highly available.

Why interviewers care: sharding is where performance stops being just about indexes and starts being about data placement. A perfect index on the wrong shard still won’t save you if every query has to fan out.

Performance notes and practical numbers

A single-shard lookup is usually fast because the router does O(1) work to pick the shard, then the shard uses its own local index, often an O(log n) lookup in a B-tree. The real cost is network and coordination: a same-region database round trip can easily be around 0.5–3 ms, while a scatter-gather query across 16 or 32 shards multiplies that cost because you wait for the slowest shard plus the merge step. In practice, a query that was 5 ms on one node can become 20–100 ms when it fans out and has to aggregate results.

Sharding is best when one node cannot handle the data volume, write throughput, or working set size. It is less attractive for small systems, ad hoc analytics, or workloads that constantly join across unrelated entities, because those queries become expensive distributed operations.

Important edge cases

• Hot shard: one shard gets most traffic because the key is skewed, such as one celebrity user or one large tenant. This defeats the purpose of sharding unless you split that tenant separately.
• Cross-shard joins: joining tables on different shards usually means moving data around or issuing multiple queries. That is slower and harder to optimize than a local join.
• Global uniqueness: auto-increment IDs can collide across shards unless you centralize ID generation or use globally unique IDs such as UUIDs or Snowflake-style IDs.
• Resharding pain: changing the number of shards can move a large percentage of the data, especially with modulo-based hashing. Plan for migrations, dual writes, or routing layers from day one.

Memory hint: a shard is not a faster index; it is a smaller universe. You are shrinking the problem before the index ever runs.

Real-world story

Imagine a checkout service for an e-commerce platform with millions of customers and a fast-growing orders table. The team shards orders by customer_id so that every customer's history stays on one shard, making common reads like WHERE customer_id = ? fast and local. This also keeps the customer's order timeline easy to fetch without scanning every database.

At first, everything looks great: p95 checkout latency drops, and each shard stays under a comfortable CPU level. Then one large enterprise customer starts generating a huge number of small orders, and their traffic lands on a single shard. That shard's CPU climbs to 95%, the connection pool starts queuing, and logs begin showing timeout retries to one specific database host.

What goes wrong: the team assumed sharding automatically balances load. In reality, a bad key choice or a skewed tenant can create a hot shard, which shows up as one shard with slow queries, growing lock waits, and user-visible checkout delays. Users see spinning payment pages, the app logs timeouts like read timeout on shard-07, and support hears complaints that some customers can place orders while others cannot.

Follow-up & Tricky Questions:

• How do you choose a shard key? Pick a column that is used in most queries, spreads values evenly, and changes rarely. If the key is skewed or not present in your common WHERE clauses, sharding will make routing and performance worse, not better.
• How do you handle cross-shard joins? Usually by avoiding them, denormalizing some data, or doing scatter-gather in the application layer. If you do need them, expect higher latency and more complex error handling because the work is now distributed.
• What happens when you add a new shard? You often have to reshard, which means moving data and updating routing rules. The hard part is keeping the system available while some requests still go to the old layout and others go to the new one.
• How do you keep IDs unique across shards? Use globally unique IDs such as UUIDs, Snowflake-style IDs, or a central ID service. Local auto-increment works only if you guarantee the ranges never overlap.
• When would you choose partitioning instead of sharding? Use partitioning when one database can still handle the workload but you want manageability, pruning, or better maintenance. Sharding is for when one node is not enough and you need multiple independent databases.
• Is sharding the same as partitioning? No; partitioning usually stays inside one database system, while sharding spreads data across multiple servers. People sometimes use the words loosely, but in interviews you should draw that boundary clearly.
• Does sharding automatically fix slow queries? No; a bad query can still be slow on every shard. Sharding reduces per-node load, but you still need good indexes, good schema design, and good query patterns.
• Can every table use the same shard key? Not always. A good shard key for orders may be bad for payments or support tickets, so many systems design shard-aware schemas around the main access pattern.
• Can I use modulo-based sharding forever? Usually not, because changing the number of shards can reshuffle most rows. Many teams start with a routing layer or consistent-hash approach so growth is easier to manage.

Tricky / gotcha questions:

• If each shard has an index, why do cross-shard queries still hurt? Because the expensive part becomes coordination, not just lookup. You pay network round trips, merge work, and the latency of the slowest shard.
• Why can a shard be hot even if the hash is uniform? Uniform hashing only spreads keys evenly on average; a single tenant or celebrity account can still generate far more traffic than everyone else. Key distribution and traffic distribution are not the same thing.
• Does adding more shards always improve performance? Not necessarily. More shards can increase routing complexity, operational overhead, and fan-out cost, so there is a point where the extra coordination outweighs the benefit.

Common Mistakes

• Choosing a random shard key: candidates often pick a key that is convenient for the schema, not the workload. Fix: choose the key that matches the most common lookup path and keeps the data evenly spread.
• Forgetting about hot tenants: even a good hash cannot save a single customer who produces most of the traffic. Fix: detect skew early and support tenant-level special handling.
• Ignoring cross-shard operations: joins, transactions, and reports are harder once data is split. Fix: redesign those flows before sharding, not after the outage.
• Assuming resharding is cheap: moving rows means moving data, indexes, cache state, and sometimes application routing rules. Fix: plan migration steps and test them like a production change.

Memory Hook

Memory Hook: Sharding is like dividing one giant library into several branch libraries by zip code. Finding one book is faster because each branch has fewer books, but asking for every book on a topic now means visiting multiple branches.

Cheat Sheet

• Sharding = split rows across multiple databases.
• Use a shard key to decide placement.
• Single-shard queries are fast; scatter-gather queries are expensive.
• Hash sharding balances load; range sharding helps range queries.
• Sharding is not the same as replication.
• Resharding and hot shards are the big operational risks.

Practice Tasks

• Design a shard key for an orders table and explain why you chose it.
• Write a query that detects rows stored on the wrong shard, like the example above.
• Compare how you would shard a chat app versus an e-commerce checkout system.