When a Single Database Hits Its Limits: Sharding Principles and Migration Path

What Sharding Is

Sharding splits a large table into multiple smaller pieces called shards, each stored on a separate database instance. Vertical sharding separates tables or columns by business module or access pattern; horizontal sharding distributes rows across identical tables on different instances. In practice teams first apply vertical sharding, then horizontally shard the largest tables. Example: Meituan’s order system first separates the order database into a base order DB and an order‑process DB, then horizontally shards the order table into 32 × 32 tables (1024 tables total) by user ID.

Three Common Sharding Schemes

Hash Sharding

A shard key (e.g., order ID) is hashed; the hash value modulo the number of shards selects the target shard. This yields uniform data distribution but forces range queries to scan all shards.

Range Sharding

Rows are assigned to shards based on a value range (e.g., creation year). Range queries are efficient because only the relevant shard is accessed, but data can become skewed if some ranges grow faster.

Directory (Lookup) Sharding

A lookup table records the shard for each row. Queries first read the lookup table, then fetch the row from the indicated shard. This provides manual control over placement and easy re‑allocation, at the cost of an extra lookup and a new single point of failure. Notion’s Postgres sharding uses this approach, assigning each team’s data to a dedicated shard.

Trade‑offs

Hash : uniform distribution; range queries require scanning all shards; expanding the cluster needs re‑hashing; suited for large‑volume, point‑lookup‑heavy workloads.

Range : potential skew; range queries are efficient; new shards can be added naturally as time progresses; suited for time‑series data.

Directory : manual control of placement; queries need an extra lookup; most flexible for tenant‑isolated data.

Teams often combine schemes, e.g., directory sharding by tenant ID followed by hash sharding within each tenant, or use consistent hashing as a compromise.

Problems After Sharding

Hotspotting

Data distribution can become uneven as business changes. A large customer may concentrate many rows in a single shard, creating a hotspot. Mitigations include resharding the hot shard or choosing a shard count divisible by many numbers (e.g., 32, 64, 128) to simplify future rebalancing.

Data Co‑location

Joins across tables that reside on different shards become distributed joins, which are costly. Co‑location stores related tables (e.g., order and order_detail) on the same shard using the same shard key, allowing local joins. If queries need multiple shard‑key dimensions, some joins will inevitably cross shards.

Cross‑Shard Queries

When a query lacks the shard key, the system must broadcast the query to all shards and merge results, degrading performance as shard count grows. Meituan forbids queries without a shard dimension; if unavoidable, they use offline warehouses or rewrite queries to include a shard key.

Routing Layer

Application code must determine the target shard for each SQL statement. A minimal routing implementation looks like:

// Compute target shard from shard key
int shardIndex = hash(userId) % shardCount;
// Retrieve the corresponding DataSource
DataSource ds = shardDataSourceMap.get(shardIndex);

Embedding such logic throughout the codebase becomes hard to maintain as shard counts or rules change. Larger projects therefore extract routing into a separate layer. Options include:

SDK‑style integration (e.g., ShardingJDBC) that intercepts JDBC calls, rewrites SQL, and merges results.

Standalone proxy processes (e.g., ShardingProxy, Vitess) that handle routing centrally but add network latency and an extra component to operate.

Migration: From a Single Instance to Shards

The most challenging phase is moving live data without downtime. Notion’s four‑step framework, adopted by many teams, is:

Dual‑write : Application writes to both the old database (authoritative) and the new shards. Failures on the new side are logged for later reconciliation.

Backfill : Historical data is batch‑migrated to shards via offline jobs. Meituan performed daily backfill tasks with continuous reconciliation.

Verification : Compare old and new data using row counts, checksums, or field‑by‑field diff. Full verification may be costly, so teams often start with sampling.

Cut‑over : Gradually shift read traffic to shards, monitor latency and errors, then shift writes. Finally, demote the old database to read‑only and eventually decommission it.

Meituan’s migration followed three phases that map to the above steps: dual‑write with old‑DB reads, then dual‑write with new‑shard reads, and finally retiring the old DB after downstream services were updated.

Tool Ecosystem

Existing tools can handle sharding without building a custom solution:

Apache ShardingSphere : Provides ShardingJDBC (SDK) and ShardingProxy (proxy). Widely used in Java stacks.

Distributed databases that embed sharding: Google Spanner, CockroachDB, TiDB. They eliminate application‑level routing but require migration from MySQL/Postgres and bring higher operational overhead.

Choosing between middleware and a distributed database depends on existing infrastructure. If MySQL/Postgres is already in use, Vitess or Citus may be lower‑cost; for greenfield projects with massive expected scale, a native distributed database may be preferable.

Conclusion

Sharding trades operational complexity for capacity. It should be considered only after hardware upgrades, read‑only replicas, and query optimizations have been exhausted. Selecting a shard key that covers the majority of queries and executing a careful, staged migration (dual‑write, backfill, verification, cut‑over) are critical to avoid hotspotting, expensive cross‑shard joins, and data inconsistency.