Database Sharding Strategies

Sharding splits a dataset horizontally across multiple database instances so that each holds only a portion of the rows. It is the standard answer when a single node can no longer handle the write throughput or storage volume, and vertical scaling has run out of road. It is also one of the most consequential and least reversible decisions in a data architecture. Choosing the strategy and the shard key well matters far more than the tooling.

Exhaust the Alternatives First

Sharding adds permanent complexity: cross-shard queries, distributed transactions, operational overhead, and difficult schema changes. Before adopting it, check whether these simpler steps would suffice:

Add indexes, fix slow queries, and tune the schema and connection pooling
Scale vertically to a larger instance
Add read replicas and caching for read-heavy workloads
Partition tables within a single server
Archive cold data

Shard when writes or total data size genuinely exceed what one primary can serve, or when you need data isolation by region or tenant.

The Core Strategies

| Strategy | How it works | Strengths | Weaknesses | |---|---|---|---| | Range | Contiguous key ranges map to shards | Efficient range scans, simple to reason about | Hotspots on monotonic keys such as timestamps | | Hash | A hash of the key picks the shard | Even distribution | Range queries scatter across all shards | | Directory | A lookup table maps keys to shards | Maximum flexibility, easy rebalancing | Lookup service is a dependency and bottleneck | | Geo | Shard by user region | Low latency, data residency compliance | Uneven load, cross-region access is slow | | Tenant | One shard (or group) per customer | Strong isolation, simple per-tenant operations | Large tenants become hotspots |

A simple hash approach computes the shard as the hash of the key modulo the shard count. It distributes evenly but has a serious flaw: changing the shard count remaps nearly every key, forcing massive data movement.

Consistent Hashing and Virtual Shards

Consistent hashing places both shards and keys on a ring. Each key belongs to the next shard clockwise, so adding or removing a shard moves only a fraction of keys. A common refinement uses many virtual nodes per physical server to smooth out imbalance.

hash ring (0 ... 2^32)

   key K1 ----> next node clockwise ----> shard B
   add shard D between A and B:
       only keys between A and D move to D

An even more practical pattern is logical shards: create many more logical partitions than physical servers, say 1,024 logical shards across 8 servers. Resharding then means moving whole logical shards between servers, never splitting rows. Systems such as Vitess, Citus, and Cassandra's token ranges work along these lines.

Choosing the Shard Key

The shard key is the decision that cannot easily be changed. Good keys have three properties:

High cardinality: many distinct values, so data spreads out.
Even access: no small set of values receives most of the traffic.
Query alignment: the most frequent queries include the key, so each hits one shard.

For a multi-tenant SaaS product, the tenant identifier is often ideal because nearly every query is tenant-scoped. For a social product, the user identifier works for user-owned data, but a celebrity account creates a hotspot. For time-series data, a pure timestamp key sends all current writes to one shard; combine it with another value, such as device identifier plus time bucket, to spread the load.

Living With Cross-Shard Realities

Once data is split, several things get harder:

Joins and aggregations across shards require scatter-gather queries, which are slow and fail as the slowest shard does. Denormalize, co-locate related data on the same key, or feed an analytics store for global queries.
Transactions spanning shards need two-phase commit or sagas. Prefer designs where a business transaction lives within one shard.
Unique constraints and sequences need globally unique identifiers, such as UUIDs, Snowflake-style IDs, or per-shard ranges.
Schema migrations must roll across every shard, with versioned, backward-compatible changes and good tooling.
Hotspots need monitoring and an escape hatch: split a hot shard, isolate a heavy tenant, or add a cache.

Resharding Without Downtime

Plan for growth from the start. A typical online migration copies data to the new shard in the background, tails the change log to catch up, verifies consistency, then flips routing for a key range and retires the old copy. A routing layer or proxy, rather than application code scattered with shard logic, keeps this manageable. Rehearse it in staging, because the first live reshard is a bad time to discover gaps.

Key Takeaways

Shard only after simpler scaling options are exhausted; it brings permanent complexity.
Pick the strategy to match access patterns: hash for even spread, range for scans, tenant or geo for isolation.
Use consistent hashing or many logical shards so resharding moves data in small pieces.
The shard key must have high cardinality, even access, and alignment with common queries.
Design transactions and queries to stay within one shard, and build tooling for resharding early.