What Is Database Sharding?

Database sharding is a scaling technique that splits data across multiple independent database servers. Instead of storing every row on one database, each shard owns only a portion of the dataset.

Sharding can increase storage capacity and distribute read and write traffic, but it also changes how queries, transactions, indexes, failures, and data migrations work. The difficult part is rarely creating several databases. The difficult part is deciding where each piece of data belongs and how the application finds it reliably.

Table of Contents

• How Database Sharding Works
• Why Databases Need Sharding
• Sharding vs Partitioning vs Replication
• Choosing a Shard Key
  • Range-Based Sharding
  • Hash-Based Sharding
  • Directory-Based Sharding
  • Geographic Sharding
• Routing Queries to the Correct Shard
• Cross-Shard Queries and Transactions
• Hot Shards and Uneven Distribution
• Resharding and Data Migration
• Designing a Sharded Database
• When Not to Shard
• Conclusion
  • More Articles to Read

How Database Sharding Works

Consider an application with 300 million users. A single database contains every user's profile, settings, orders, and other records.

Without sharding, all requests eventually reach the same database:

Application → Database
              300M users

With sharding, the dataset is divided across multiple databases:

Users 1–100M   → Shard A
Users 100M–200M → Shard B
Users 200M–300M → Shard C

Each shard is an independent database containing the same logical schema but a different subset of rows.

A request for user 42 million goes to Shard A. A request for user 250 million goes to Shard C.

This distributes storage and traffic across several machines instead of requiring one machine to handle the entire dataset.

If three shards receive approximately equal traffic, each database may handle roughly one-third of the workload. In practice, distribution is rarely perfectly uniform, which makes shard-key selection critical.

Why Databases Need Sharding

Databases can often scale surprisingly far without sharding. Better indexes, query optimization, caching, larger machines, read replicas, and table partitioning should usually be considered first.

Eventually, however, a database may encounter limits that cannot be solved efficiently on one primary server.

Common reasons include:

• the dataset no longer fits economically on one machine;
• write throughput exceeds the capacity of one primary database;
• indexes become too large for efficient memory utilization;
• maintenance operations take too long;
• one server cannot provide enough CPU, memory, storage IOPS, or network bandwidth;
• data needs to be placed closer to users in different regions.

Suppose a database receives 60,000 writes per second, while a properly tuned primary can sustainably process only 25,000 writes per second.

Adding read replicas does not solve the problem because writes still reach the primary.

Writes → Primary
Reads  → Primary + Replicas

Sharding can distribute writes:

Writes → Shard A
       → Shard B
       → Shard C

The database layer now has multiple independent write paths.

The broader progression from scaling a single machine to distributing database workloads is covered in Database Scaling Explained: Vertical vs Horizontal Scaling.

Sharding vs Partitioning vs Replication

Sharding, partitioning, and replication all distribute data in some form, but they solve different problems.

For example, a 2 TB orders table could be partitioned by month while still living in one PostgreSQL cluster. Partitioning may improve pruning, maintenance, and lifecycle management without distributing ownership across independent database servers.

Partitioning Large Tables for Production Systems covers table partitioning in more detail.

Replication works differently. A primary database and two replicas may each contain the same 2 TB dataset. Reads can be distributed, but storage has been duplicated rather than divided.

Replication and Read Replicas in Distributed Databases explains the trade-offs of that model.

Sharding instead might divide the 2 TB dataset into four 500 GB subsets, with each shard responsible for different records.

Choosing a Shard Key

The shard key determines which shard owns a record. It is one of the most important decisions in a sharded architecture because it affects data distribution, query routing, transaction boundaries, and future resharding.

For a multi-tenant SaaS platform, a natural shard key might be tenant_id.

def get_shard(tenant_id: int) -> int:
    return tenant_id % SHARD_COUNT

If there are four shards:

tenant_id 100 → shard 0
tenant_id 101 → shard 1
tenant_id 102 → shard 2
tenant_id 103 → shard 3

A good shard key should distribute workload reasonably evenly while allowing common requests to identify the correct shard without searching the entire cluster.

A poor shard key can create hot shards, expensive cross-shard queries, or difficult migrations even when the underlying database servers are powerful.

Range-Based Sharding

Range-based sharding assigns continuous key ranges to shards.

user_id 1–1,000,000         → Shard A
user_id 1,000,001–2,000,000 → Shard B
user_id 2,000,001–3,000,000 → Shard C

The model is simple and makes range queries efficient when the queried range maps to a small number of shards.

But monotonically increasing keys can create hotspots. If all new users receive increasing IDs, nearly all new writes may go to the newest shard.

Range sharding works best when the range itself has useful locality and traffic is not concentrated on one part of the key space.

Hash-Based Sharding

Hash-based sharding transforms the shard key and uses the result to select a shard.

def shard_for_user(user_id: str, shard_count: int) -> int:
    value = stable_hash(user_id)
    return value % shard_count

Hashing generally distributes records more evenly than sequential ranges.

The disadvantage is that related records may no longer be naturally adjacent. A query such as "find every account created this week" may need to query every shard unless another index or analytical system supports it.

Another issue appears when the number of shards changes. With simple modulo routing, moving from four shards to five changes the destination for many keys.

old_shard = stable_hash(user_id) % 4
new_shard = stable_hash(user_id) % 5

This can require moving a large percentage of the dataset. Production systems often use virtual shards, consistent hashing, or explicit shard maps to reduce this problem.

Directory-Based Sharding

Directory-based sharding stores an explicit mapping between a key and its shard.

tenant_1001 → shard_03
tenant_1002 → shard_07
tenant_1003 → shard_03

This adds a lookup layer but provides significant flexibility. A large tenant can be moved from one shard to another without changing the routing algorithm for every other tenant.

The directory itself becomes critical infrastructure. It must be highly available, consistent enough for routing decisions, and usually aggressively cached.

This strategy is useful when tenants vary greatly in size because placement can be controlled independently rather than relying entirely on hashing.

Geographic Sharding

Geographic sharding places data according to region.

US customers → US shard
EU customers → EU shard
APAC customers → APAC shard

This can reduce latency and help satisfy data-residency requirements.

Geography alone may not provide sufficient distribution. A region containing most customers can still become a bottleneck, so large deployments may combine geographic routing with additional sharding inside each region.

Routing Queries to the Correct Shard

Once data is distributed, the application must determine where every query should execute.

If tenant_id is the shard key, requests should carry enough context to calculate or look up the destination shard.

def get_orders(tenant_id: int):
    shard = shard_router.get_shard(tenant_id)

    return shard.execute(
        "SELECT * FROM orders WHERE tenant_id = %s",
        [tenant_id],
    )

This is efficient because the request touches exactly one shard.

Problems appear when a query does not contain the shard key:

SELECT *
FROM users
WHERE email = 'alice@example.com';

If users are sharded by user_id, the email address does not reveal which shard contains the row.

The system now needs another strategy, such as:

• a global lookup index mapping email addresses to user IDs or shards;
• a separate search service;
• a directory service;
• a scatter-gather query across all shards.

Scatter-gather queries are particularly dangerous at scale. A request that once performed one database query can become dozens or hundreds of queries as the shard count grows.

Request → Shard 1
        → Shard 2
        → Shard 3
        → ...
        → Shard 100

Good sharding architecture therefore starts with access patterns, not merely data size.

Cross-Shard Queries and Transactions

Operations are simplest when all related data lives on the same shard.

For an e-commerce system, placing a customer's profile, addresses, orders, and payment metadata according to customer_id may allow most customer operations to remain local.

A transaction can then execute normally inside one database:

BEGIN;

UPDATE accounts
SET balance = balance - 100
WHERE account_id = 123;

INSERT INTO payments (account_id, amount)
VALUES (123, 100);

COMMIT;

But transferring data or value between entities on different shards is harder.

Account A → Shard 1
Account B → Shard 7

A local database transaction can no longer atomically update both independent databases.

Distributed transactions can coordinate multiple participants, but they introduce additional failure states, latency, locking, and operational complexity. Many systems instead redesign operations around asynchronous workflows, idempotency, compensating actions, or saga-style coordination.

Designing Distributed Transactions with Sagas and Two-Phase Commit covers these trade-offs in detail.

This is why shard boundaries should often follow business boundaries. Data frequently updated together should ideally live together.

Hot Shards and Uneven Distribution

Equal data volume does not guarantee equal workload.

Suppose four shards each contain 25% of customers:

Shard A → 25% of data → 20% of traffic
Shard B → 25% of data → 22% of traffic
Shard C → 25% of data → 18% of traffic
Shard D → 25% of data → 40% of traffic

Shard D is a hot shard even though storage is evenly distributed.

This commonly happens when a few tenants, products, celebrities, geographic regions, or other entities generate disproportionate traffic.

A multi-tenant system illustrates the problem clearly. Ten thousand small customers might generate less traffic than one very large enterprise customer.

If the large tenant is assigned entirely to one shard, adding more shards elsewhere does nothing to relieve that hotspot.

Useful production metrics should therefore be tracked per shard:

• queries per second;
• writes per second;
• CPU and memory utilization;
• storage size and growth rate;
• disk IOPS and latency;
• active connections;
• lock contention;
• p95 and p99 query latency.

Monitoring only cluster-wide averages can hide a shard approaching saturation.

Resharding and Data Migration

Shard layouts eventually change. Data grows, traffic distribution shifts, tenants become larger, or individual shards approach their capacity limits.

Suppose a system starts with four shards and later needs eight.

A naive migration could stop writes, copy records to their new shards, update routing, and restart traffic. That may work for small systems but is often unacceptable for large production databases.

An online migration needs to handle reads and writes while data is moving.

A simplified process might be:

1. create the new shard;
2. copy historical data from the source shard;
3. capture writes occurring during the copy;
4. apply outstanding changes to the destination;
5. verify data consistency;
6. change the routing map;
7. monitor the new shard;
8. remove old copies after a safe rollback period.

The difficult part is maintaining correctness around the routing cutover.

Consider this sequence:

T1: Copy user 500 to new shard
T2: User 500 updates profile on old shard
T3: Router switches user 500 to new shard

If the T2 update is not replicated to the destination, the user sees stale data after the migration.

Production resharding mechanisms therefore commonly use change-data capture, dual writes, migration logs, version checks, or controlled write routing during the transition.

Resharding should be considered during the original design because a sharding scheme that distributes data well today may become difficult to change several years later.

Designing a Sharded Database

Consider a SaaS platform where customers create projects, tasks, comments, and audit events. Most operations occur inside one customer account.

A useful ownership model could be:

tenant_id
   |
   +-- projects
   +-- tasks
   +-- comments
   +-- audit_events

Using tenant_id as the shard key keeps most related data together.

A routing layer maps each tenant to a shard:

class ShardRouter:
    def __init__(self, shard_map):
        self.shard_map = shard_map

    def get_shard(self, tenant_id: str):
        shard_id = self.shard_map[tenant_id]
        return connections[shard_id]

Large tenants can later be moved explicitly:

tenant_123 → shard_02
tenant_456 → shard_05
tenant_999 → dedicated_shard_12

This is more operationally complex than simple modulo hashing, but it allows the platform to isolate exceptionally large customers.

Global features need separate consideration. An administrative query such as "show the 100 most active tenants across the entire platform" should generally not synchronously scan every production shard.

A better design may stream operational data into an analytical system:

Database Shards → Events / CDC → Analytics Store

The transactional shards remain optimized for tenant-local requests, while global analytical queries execute elsewhere.

This separation prevents global reporting requirements from forcing inefficient cross-shard OLTP queries.

When Not to Shard

Sharding should usually solve a demonstrated scaling problem rather than an anticipated problem that may never occur.

Before introducing shards, several simpler options may provide enough capacity:

• fix inefficient queries;
• add missing indexes;
• remove unnecessary database requests;
• increase database CPU, memory, or storage performance;
• add read replicas for read-heavy workloads;
• cache frequently requested data;
• partition very large tables;
• archive old or rarely accessed data.

Suppose database CPU is high because one endpoint repeatedly executes a full table scan. Splitting the database across eight shards may reduce the amount scanned per node, but the inefficient access pattern still exists eight times.

Fixing the query and adding the correct index is usually the better solution.

Similarly, if the workload is 90% reads and the primary is healthy, replication may provide the required capacity without partitioning data ownership.

Sharding becomes more attractive when write throughput, dataset size, geographic placement, or single-node capacity is the actual limiting factor.

Conclusion

Database sharding divides a dataset across multiple independent database nodes so storage and workload no longer depend on a single server. It can unlock substantial horizontal scale, particularly when one primary database can no longer handle the required data volume or write throughput.

The trade-off is complexity. Shard keys affect routing, transactions, joins, indexes, hotspots, migrations, and operational tooling. A good sharding design keeps common operations local, distributes real workload rather than only data volume, and provides a practical path for moving data as the system evolves.

Sharding is not simply a way to create more database servers. It is a decision about data ownership. Once ownership is distributed, the rest of the architecture must understand and respect those boundaries.

More Articles to Read

• Database Sharding Strategies and Trade-Offs
• Database Best Practices for Scalable Applications
• Designing High-Performance Database Schemas