Database Sharding in System Design
Database sharding is a way to scale a database by splitting one large dataset into smaller independent databases called shards. Each shard stores only part of the data, but together they still appear as one database to the application. This helps the system scale reads, writes, and storage across multiple machines.
Data is placed into shards using a shard key, such as user_id, tenant_id, or account_id. This key helps decide which shard should handle a request. A routing layer, inside the application, a gateway, or a distributed database system, uses this key to send each request to the correct shard. As systems grow, this routing logic becomes an important part of the overall architecture.
Sharding is not just a performance tweak. It changes how data is owned and accessed. After sharding, many things that are easy in a single database, like global joins, cross-table transactions, and centralized indexes, are no longer available by default. Queries, schemas, operations, and failure handling must all be designed around data being spread across shards.
Sharding is often confused with partitioning, but they are different. Partitioning splits data inside one database engine, and the database decides which partition to read. Sharding splits data across multiple separate databases, which means the system or application must manage where data lives.
From a system design point of view, sharding allows a database to grow beyond the limits of a single machine. Instead of scaling up one server, the system scales out by adding more shards. This increases both storage capacity and write throughput, and it usually appears later in the broader database scaling journey.
The Importance of Sharding
As applications grow, a single database eventually becomes a bottleneck. At first, this shows up as slow queries or higher latency. Over time, the database itself limits how much the system can grow.
The main benefit of sharding is horizontal scalability. Instead of putting all data and traffic on one database, sharding spreads them across multiple databases. This allows the system to grow by adding shards instead of constantly upgrading hardware.
Sharding is especially useful for systems with heavy write traffic. In large applications like social platforms, financial systems, or SaaS products, writes often become the main performance problem. By distributing writes across shards, the system reduces load on each database and increases overall throughput.
Another important benefit is workload isolation. In multi-tenant systems, one heavy customer can slow down the system for everyone else. Sharding by tenant or account helps isolate these workloads so one noisy user does not affect others.
Sharding also helps manage long-term data growth. Very large databases are hard to maintain, indexes grow large, backups take longer, and recovery becomes slower. Sharding breaks large datasets into smaller pieces, making operations, maintenance, and recovery more manageable.
In mature systems, sharding is not used to fix a single slow query. It is used to remove a hard scaling limit and support steady, long-term growth of the system.
How Sharding Works in Practice
At a basic level, sharding follows a simple flow:
Choose a shard key
Decide how that key maps to a shard
Make sure all reads and writes for that key always go to the same shard
The logic that decides which shard to use is called routing. This routing can live in the application code, in a middleware or gateway layer, or be handled by a distributed database system.
The hard part is not the first routing rule. The real challenge is keeping the system healthy as it grows. Over time, traffic changes, some shards become larger or busier than others, and new shards need to be added. A good sharding design plans for this from the start.
Most systems begin with a simple, deterministic routing rule. Common approaches use hashing or modulo logic so that the same key always maps to the same shard. This keeps behavior predictable and easy to reason about.
TypeScript
const shards = ['shard_a', 'shard_b', 'shard_c', 'shard_d'];
function pickShard(userId: number) {
return shards[userId % shards.length];
}This example illustrates the core principle, not a production-ready solution. While deterministic routing is necessary, it is rarely sufficient on its own.
In practice, mature sharded systems also need safe rebalancing, ways to handle hot tenants or hot keys, gradual data movement when capacity grows, and clear visibility into shard size and load. These concerns often matter more than the first routing rule.
Sharding succeeds not because the first routing rule is clever, but because the system is built to adapt safely as it grows.
How to Choose the Shard Key
Choosing the shard key is the most important decision in any sharding strategy. It decides where data lives, how requests are routed, and how well the system scales over time. A good key spreads load reasonably evenly, appears in most reads and writes, and keeps related data together so most operations stay on one shard.
In practice, this decision starts with a few simple questions. Which identifier appears in most requests? Which field matches natural ownership in the product? Which choice reduces cross-shard queries and distributed transactions? And which key is least likely to create hotspots as the system grows?
Strong shard keys are typically business identifiers such as user_id, tenant_id, account_id, or merchant_id. These keys align with how data is accessed and owned, and they allow most queries to be satisfied by a single shard. Weak shard keys are values that grow unevenly, are missing from common request paths, or force routine operations to span multiple shards.
Balance on paper is not enough. A shard key must match real production behavior. A celebrity account, a large enterprise tenant, or a flash-sale merchant can overwhelm one shard even if the data looked evenly distributed at first. Good shard-key design plans for this reality.
In mature systems, the goal is not a shard key that looks perfect on paper, but one that fails in predictable ways and can still be managed as the business changes.
Sharding Techniques
There is no single best sharding strategy. The right approach depends on how the system is used, what the queries look like, and how much flexibility the team needs over time. Most real systems choose a strategy not because it is perfect in theory, but because it fails in ways the team can manage.
The most common sharding techniques fall into three broad categories: hash-based sharding, range-based sharding, and directory-based sharding.
Hash-Based Sharding
Hash-based sharding applies a hash function to the shard key and uses the result to select a shard. Instead of routing directly on the raw key, the system first transforms it, ensuring that keys are spread evenly across shards.
For example, a system might hash user_id and then take the result modulo the number of shards. This ensures that users are evenly distributed, even if user IDs themselves are sequential.
Example
User profiles distributed by user ID
Imagine a platform where most requests are simple lookups such as loading one user profile, one session, or one settings record. The system hashes user_id and uses that result to pick a shard.
This works well because requests are independent and evenly spread. The trade-off is that broad scans or range-based queries can no longer stay local to one shard.
The main advantage of hash-based sharding is balance. Because the hash function spreads keys evenly, no single shard is likely to receive too much traffic just because of the key pattern. This makes it a strong default choice for write-heavy systems where even load matters more than query locality.
The downside is that related data is intentionally scattered. Range queries, ordered scans, and time-based queries become expensive because the data is spread across many shards. A query like "fetch users with IDs between 1M and 2M" or "load all events from yesterday" must touch every shard.
Hash-based sharding works best for systems dominated by point lookups and independent entities, such as user profiles, session data, or per-user metadata.
Range-Based Sharding
Range-based sharding assigns specific ranges of shard key values to specific shards. Each shard owns a continuous slice of the key space, making the data easy to reason about and easy to query in order.
Example
Orders split by ordered ID ranges
Why teams use it
This design is easy to understand and works well when queries naturally follow the same ordering, such as recent orders, recent events, or a known ID interval.
Main risk
The weakness appears when new traffic mostly lands in the newest range, because one shard can become much hotter than the rest.
This approach works well when the business naturally queries ranges. Time-based data is a common example. Logs, orders, or events can be sharded by date ranges so that queries like "last 7 days" or "this month's orders" touch only a small number of shards.
The main risk with range-based sharding is hotspots. Newer ranges almost always receive more traffic than older ones. If all new users or recent orders land on the same shard, that shard becomes overloaded while older shards sit mostly idle.
Range-based sharding is often chosen when query efficiency and simplicity matter more than perfect balance. It can work well when combined with techniques like pre-splitting ranges or periodically creating new shards to absorb new traffic.
Directory-Based Sharding
Directory-based sharding introduces an explicit lookup layer that maps a shard key, such as a tenant or account, to a specific shard. Instead of calculating shard placement, the system consults a directory to find where the data lives.
Example
Tenant mapping controlled by a lookup directory
Why teams use it
This is useful when tenant growth is uneven and the platform needs the freedom to move one tenant without changing the overall shard-key scheme.
Main risk
The cost is that the directory becomes critical infrastructure. Every request depends on it being correct and highly available.
This directory can be stored in a database table, a cache, or a dedicated configuration service. When a request arrives, the system first looks up the tenant in the directory and then routes the request to the correct shard.
The key advantage of this approach is flexibility. Data can be moved between shards by updating the directory entry, without changing the shard key or rewriting application logic. This makes directory-based sharding particularly useful for multi-tenant systems, where some tenants grow much faster than others.
The trade-off is extra operational complexity. The directory becomes a critical part of the system. It has to be fast, correct, and highly available because every request depends on it. If the directory is wrong or unavailable, the whole system can be affected.
Directory-based sharding is commonly used in SaaS platforms where tenant isolation, controlled rebalancing, and predictable operations matter more than simplicity.
In practice, many large systems combine these techniques. A directory may map tenants to shards, while data inside each shard is also organized by range or hash. The goal is not to find a perfect strategy, but to choose one that matches access patterns, handles growth, and can be changed safely later.
Sharding does not remove hard problems. It helps teams contain them, understand them, and manage them as the system grows.
The Problem with Adding a New Shard
One of the biggest hidden costs in sharding appears when the system needs to grow. A simple modulo-based rule such as hash(key) % numberOfShards works well at the beginning, but it behaves badly when a new shard is added.
The reason is simple: the divisor changes. If the system moves from 4 shards to 5 shards, the routing result for a very large percentage of keys changes immediately. That means a large amount of data must be moved to different shards, caches become less useful, and the migration itself becomes operationally risky.
In other words, adding one shard can force a near-global reshuffle of ownership. For small systems this may be tolerable. For large systems, it can become one of the most expensive parts of the architecture.
How Ring-Based Hashing Helps
A common way to solve this problem is ring-based hashing, more commonly known as consistent hashing. Instead of mapping keys with a simple modulo rule, both shards and keys are placed on a logical hash ring.
Each key is routed to the next shard clockwise on the ring. When a new shard is added, only the keys in a limited portion of the ring move to that shard. Most keys continue to map to the same place, so the system avoids large-scale reshuffling.
Why it matters
With simple modulo hashing, adding one shard can move a large fraction of the dataset.
With consistent hashing, only a smaller and predictable slice of keys usually moves.
This makes growth, rebalancing, and operational rollouts much safer.
Mature systems often take this further by using virtual nodes. Instead of placing each shard on the ring only once, each shard is represented multiple times. This helps smooth out uneven distribution and makes balancing more predictable when shards are added or removed.
Ring-based hashing does not remove the need for migrations, monitoring, or careful rollout controls. It simply changes the problem from "move almost everything" to "move only the part that must move." That is why it is such an important technique in long-lived sharded systems.
Examples
Good sharding examples are not just about where data goes. They show whether the shard key matches real product usage, keeps common requests local, and places trade-offs in parts of the system that can handle them safely. In practice, a shard key succeeds when it fits the dominant access pattern, not when it looks perfectly balanced on paper.
Example
Sharding a SaaS Platform by Tenant ID
Context
Consider a multi-tenant SaaS product where most reads and writes are scoped to a single customer account. User management, billing, configuration, projects, and activity logs are typically accessed within the context of one tenant at a time.
Shard key choice
Sharding by tenant_id is a natural choice. When a request arrives for tenant 2041, the routing layer reads the tenant ID, maps it to a shard, and forwards the request there. All data related to that tenant, users, invoices, projects, and usage, lives on the same shard.
Why it works
The shard key mirrors the business model. The product already thinks in terms of tenants, and so does the data layer. Most requests are handled by a single shard, cross-shard queries are rare, and operational ownership stays clear.
Trade-off
The trade-off appears when tenants grow unevenly. A large enterprise customer can generate far more traffic than hundreds of small tenants combined. When that happens, the system must support moving a tenant to a larger shard or isolating it entirely. Sharding by tenant works best when the architecture anticipates that some tenants will eventually outgrow others.
Example
Sharding a Social Platform by User ID
Context
Consider a social platform where a large share of reads and writes are user-scoped. Profile updates, settings, drafts, notifications, and many write-heavy actions are usually handled in the context of one user's data at a time, even though the platform serves many users concurrently.
Shard key choice
Sharding by user_id is a common and effective choice. A request for user 981 always routes to the same shard. Profile changes, preference updates, and user-scoped reads stay local, which keeps write paths simple and predictable. This approach scales well as the user base grows because load naturally spreads across shards.
Why it works
The primary database is optimized for user-scoped operations, which are the dominant access pattern in many social systems. This keeps most high-frequency reads and writes on a single shard.
Trade-off
The limitations show up in shared features. Timelines, global search, trending content, and ranking systems combine data from many users. These flows usually require feed services, aggregation layers, or precomputed views. This is an intentional trade-off: user-scoped data stays simple, while cross-user features move into specialized systems.
Example
Sharding a Payments Platform by Merchant ID
Context
In a payments system, transactions, refunds, settlements, and reporting are usually scoped to one merchant. A high-volume business should not degrade performance for unrelated merchants.
Shard key choice
Sharding by merchant_id is often a strong design choice. When a payment request arrives, the routing layer uses the merchant ID to send it to the correct shard. All transaction history and operational data for that merchant stays together.
Why it works
This simplifies reconciliation, customer support, and merchant-specific reporting. It also prioritizes isolation and operational clarity, allowing each merchant to grow independently.
Trade-off
Platform-wide workflows such as fraud detection, settlement across merchants, and regulatory reporting often require aggregation across many shards. These workloads are usually handled asynchronously or through separate analytical systems rather than the primary transactional database.
Across all these examples, the same rule appears again: a good shard key matches how the product is used most of the time, keeps common operations on one shard, and moves cross-shard complexity into separate systems with clear responsibilities. The goal is not to remove trade-offs, but to choose trade-offs the system can handle as it grows.
Sharding vs Partitioning vs Replication
Partitioning, replication, and sharding are often discussed together, but they solve different problems. Strong system design starts by identifying the real bottleneck and choosing the simplest technique that addresses it. Many systems fail not because they lack sharding, but because they adopted it before exhausting simpler options such as partitioning or replication.
Partitioning
Partitioning splits a large table into smaller parts inside the same database engine. To the application, it still looks like one table, and the database decides which partition to read or write.
Partitioning is most useful when tables grow very large and queries naturally filter on a specific column, such as time or category. It helps with query pruning, retention policies, and maintenance tasks like archiving or deleting old data.
The key limitation is that partitioning does not change the physical ownership of data. All writes still go through the same database instance. If write throughput or storage capacity of one machine is the bottleneck, partitioning alone will not solve it.
Replication
Replication copies the same data to multiple database nodes. One node usually accepts writes, while others serve reads or act as failover replicas.
Replication is primarily about availability and read scalability. It allows systems to survive node failures and handle more read traffic by spreading reads across replicas.
However, replication usually does not remove the main write bottleneck. Writes still need to be processed by a primary node and then propagated to replicas. If write volume is the problem, replication helps reliability but not capacity.
Sharding
Sharding splits data ownership across multiple independent databases. Each shard owns only a subset of the data and handles reads and writes for that subset.
Sharding is the technique that enables true horizontal scaling. It increases total write throughput and storage capacity by adding more nodes. It also allows workload isolation, which is critical for multi-tenant systems or platforms with uneven usage patterns.
The cost is complexity. Sharding introduces routing logic, rebalancing challenges, and cross-shard coordination. These are architectural concerns that must be designed and operated carefully.
Summary Comparison
| Technique | What it does | Best for | Main limit |
|---|---|---|---|
| Partitioning | Splits one large table into smaller parts inside the same database engine. | Large tables, pruning, retention, easier maintenance. | Does not scale writes across machines. |
| Replication | Copies the same data to multiple nodes. | Availability, failover, read scaling. | Usually keeps a single write bottleneck. |
| Sharding | Splits ownership of data across independent databases. | Write scaling, storage scaling, workload isolation. | Adds routing, rebalancing, and query complexity. |
Common Challenges in Production
Sharding solves fundamental scaling limits, but it introduces a new class of production challenges. These issues rarely appear in early testing. They emerge gradually as real traffic patterns, real customers, and real failures interact with the system. Mature designs plan for these challenges rather than reacting to them.
Hot Shards
Even a design that looks perfectly balanced on paper can fail in production. Traffic is rarely uniform. A single large tenant, a popular user, or a recent time window can generate far more load than the rest of the system combined.
Hot shards are not just about data size. They are often driven by request rate, write amplification, lock contention, or bursty traffic. A shard with fewer rows can still be the busiest shard in the system.
To manage this, teams need strong observability. It is not enough to track row counts per shard. Systems must monitor query rate, write pressure, latency, and error rates at the shard level. Without this visibility, hotspots are detected only after users are already impacted.
Cross-Shard Queries
Queries that span multiple shards are inherently more expensive than single-shard operations. They require fan-out, coordination, and result aggregation, all of which increase latency and failure risk.
Well-designed systems avoid making cross-shard queries part of the critical request path. Instead, they rely on denormalization, search indexes, precomputed views, or background aggregation jobs. These techniques move complexity away from synchronous user-facing flows and into controlled, asynchronous pipelines.
Cross-shard queries are sometimes unavoidable, but they should be treated as exceptional cases, not the default access pattern.
Rebalancing
Adding a new shard is not just a capacity exercise. Data must often be moved, routing rules must be updated safely, and traffic must be shifted without violating correctness or availability guarantees.
Rebalancing is one of the highest operational costs of sharding. It touches data consistency, deployment processes, monitoring, and rollback strategies. Systems that succeed at scale treat rebalancing as a routine operation, not an emergency procedure.
This usually requires tooling for gradual data movement, dual writes or reads during transitions, and clear visibility into progress and impact.
Application Design Constraints
Sharding places real constraints on application design. Assumptions that are natural in a single-database system, such as free joins, multi-row transactions, and implicit foreign-key relationships, become harder once related data may live on different machines.
As a result, applications must become explicit about data ownership boundaries. Entities are designed to live within a shard, and interactions across shards are handled through well-defined APIs, events, or asynchronous workflows.
This shift often improves system clarity, but it requires discipline. Sharding does not just change the database layer; it reshapes how the application itself is structured.
References
Interview prep
Scenario-Based Interview Questions
Why do teams usually shard only after trying other scaling options first?
Sample answer
Sharding permanently changes how data is owned, routed, and queried. It increases operational complexity and limits certain database features. Teams usually exhaust simpler options such as indexing, caching, replication, and partitioning because they are easier to implement, cheaper to operate, and easier to reverse. Sharding is introduced only when a single database becomes a hard scaling limit.
What makes a shard key good in practice?
Sample answer
A good shard key appears in most read and write paths, distributes traffic reasonably evenly, keeps related data on the same shard, and minimizes cross-shard queries. Just as important, it should allow the system to rebalance or isolate workloads as usage patterns evolve.
What is the biggest risk of choosing a bad shard key?
Sample answer
Hot shards. One shard ends up handling a disproportionate amount of traffic, data growth, or write contention. This removes the main benefit of sharding and often creates a single point of instability in an otherwise distributed system.
Does sharding automatically solve read and write performance problems?
Sample answer
No. Sharding increases total write capacity and storage by distributing data ownership, but it does not fix inefficient queries, excessive fan-out, or poor access patterns. Bad routing or frequent cross-shard operations can still make a sharded system slow and expensive.
How do teams handle queries that need data from many shards?
Sample answer
They redesign the system to avoid those queries in the critical path. Common approaches include denormalization, precomputed views, search or analytics systems, and dedicated aggregation services. In well-designed sharded systems, multi-shard queries are treated as exceptional cases, not normal request flows.
Next topic
Back to Database Scaling
Go back to the main database scaling guide to connect sharding with partitioning, replication, and modern production architecture.
Back to Main Guide