Database Sharding Explained: When and How to Shard

Database sharding splits one logical database into multiple independent databases, called shards, each holding a subset of the rows. You shard when a single primary can no longer handle your write throughput or data size, even after vertical scaling, indexing, caching, and read replicas. The hardest and most important decision is the shard key, because it determines how evenly load spreads and which queries stay fast.

What is database sharding?

Sharding is horizontal partitioning across machines. Every shard has the same schema but different rows. A user table with a hundred million rows might be split so users 1 through 25 million live on shard 0, the next 25 million on shard 1, and so on. Each shard is usually its own primary with its own replicas.

It is worth separating a few terms that get mixed up:

• Partitioning splits a table into pieces, which may all live on one server. See Partitioning.
• Sharding is partitioning where the pieces live on different servers.
• Replication copies the same data to multiple servers for availability and read scaling. See Replication.

Most sharded systems use both sharding and replication: shards for write scale, replicas within each shard for durability and reads.

When should you shard a database?

Sharding adds permanent complexity, so treat it as a last resort rather than a first step. Before sharding, try these in roughly this order:

1. Add the right database indexes and fix slow queries.
2. Scale the server vertically with more CPU, memory, and faster disks.
3. Add a cache in front of hot reads.
4. Add read replicas to offload read traffic.
5. Archive or move cold data out of the hot tables.
6. Split unrelated tables into separate databases by feature (vertical partitioning).

You likely need sharding when writes, not reads, are the bottleneck; when the dataset or its indexes no longer fit comfortably on one machine; or when a single node failure affects too many users at once.

How to choose a shard key

The shard key is the column used to decide which shard a row belongs to. A good shard key has three properties:

• High cardinality - enough distinct values to spread across many shards.
• Even distribution - no single value or range attracts most of the traffic.
• Query alignment - the most common queries include the key, so they hit exactly one shard.

For a multi-tenant SaaS app, tenant_id is often a good key because nearly every query is scoped to one tenant. For a social app, user_id works for profile and timeline reads. A timestamp is usually a poor key on its own, because all new writes go to the newest shard and create a hotspot.

Watch for "celebrity" keys. If one tenant is a hundred times larger than the rest, even a perfect hash puts all of their rows on one shard. Common fixes are moving that tenant to a dedicated shard or using a compound key that splits them further.

Sharding strategies: range vs hash vs directory

Range-based sharding

Rows are assigned by key ranges: A through F on shard 0, G through M on shard 1, and so on. Range queries on the key stay on one or a few shards, which is great for time-series or alphabetical scans. The downside is hotspots when inserts cluster at one end of the range.

Hash-based sharding

Apply a hash function to the key and use the result to pick a shard. Distribution is even and hotspots from sequential keys disappear, but range queries must fan out to every shard. Using hash(key) % N makes adding shards painful, since most keys move; consistent hashing or a fixed number of logical shards avoids that.

Directory-based sharding

A lookup service stores which shard holds each key or key range. This is the most flexible approach: you can move individual tenants, split hot ranges, and rebalance without changing a formula. The directory becomes a critical dependency, so it must be highly available and heavily cached.

Geographic sharding

Data is placed by region, often to reduce latency or satisfy data residency rules. It is usually combined with one of the strategies above inside each region.

Routing queries to the right shard

Something must translate a key into a shard location. The options are an application-level library, a proxy layer between the app and the databases, or a database that shards natively. A common pattern is to hash into a fixed number of logical shards, far more than physical servers, and map logical shards to servers in a small config table.

const LOGICAL_SHARDS = 1024;

// logical shard -> physical database, stored in config and cached
const shardMap: Record<number, string> = loadShardMap();

function logicalShardFor(userId: string): number {
  return fnv1a(userId) % LOGICAL_SHARDS;
}

function dbFor(userId: string): string {
  return shardMap[logicalShardFor(userId)];
}

function fnv1a(input: string): number {
  let hash = 0x811c9dc5;
  for (let i = 0; i < input.length; i++) {
    hash ^= input.charCodeAt(i);
    hash = Math.imul(hash, 0x01000193) >>> 0;
  }
  return hash;
}

Because the number of logical shards never changes, adding servers only means moving some logical shards and updating the map. No keys are rehashed.

The costs and challenges of sharding

Sharding solves a scale problem by creating several new ones:

• Cross-shard queries - joins and aggregations across shards need scatter-gather or a separate analytics store.
• Cross-shard transactions - atomic updates across shards require two-phase commit or a saga, both slower and more complex than a local transaction.
• Global uniqueness - auto-increment IDs no longer work; use UUIDs or a distributed ID generator.
• Rebalancing - moving data between shards while serving traffic requires careful dual-writes or copy-then-cutover.
• Operational overhead - backups, schema migrations, and monitoring now run per shard.

Designing the data model so the most important operations stay within a single shard avoids most of these problems.

Key takeaways

• Shard only after indexing, vertical scaling, caching, and replicas stop being enough.
• The shard key matters more than the strategy: aim for high cardinality, even load, and query alignment.
• Range sharding favors range scans; hash sharding favors even distribution.
• Use many logical shards mapped to fewer physical servers to make rebalancing manageable.
• Expect cross-shard joins, transactions, and ID generation to need new solutions.

Frequently asked questions

What is the difference between sharding and partitioning?

Partitioning splits a table into smaller pieces, which can live on the same server. Sharding is a form of horizontal partitioning in which those pieces live on separate servers, so each shard handles its own reads, writes, and storage.

What is a good shard key?

A good shard key has many distinct values, spreads traffic evenly, and appears in most queries so they touch a single shard. Tenant ID or user ID are common choices. Avoid monotonically increasing values like timestamps as the sole key.

Can you shard a relational database like PostgreSQL or MySQL?

Yes. You can shard at the application level, use a proxy or middleware that routes queries, or use an extension or distributed SQL database built on these engines. Each approach trades flexibility for operational simplicity.

How do you rebalance shards without downtime?

A common approach is to copy data for the moving range to the new shard, keep it in sync with dual-writes or change data capture, then switch routing once it has caught up. Using a fixed set of logical shards makes this a matter of moving whole logical shards rather than rehashing keys.