Mastering Sharding: Strategies, Key Generation, and Seamless Scaling

Implementation Strategy and Demo

Before sharding a database, developers must fully understand the business logic and schema, ideally drawing an ER diagram or domain model to guide shard division. Choose the diagram type based on whether the project follows data‑driven or domain‑driven development.

Vertical partitioning groups tightly related tables into the same shard, using the diagram as a “swimlane” to assign tables. Horizontal partitioning further splits shards when data volume or growth rate requires it. If a group of tables grows slowly, a single shard suffices; if growth is rapid, split the shard into smaller ones, each containing a primary table and its related secondary tables. After horizontal splitting, cross‑shard joins, GROUP BY, and ORDER BY must be avoided and handled at the application layer.

During implementation, follow three phases: preparation, analysis, and deployment. In the analysis phase, decide vertical and horizontal cuts, then break cross‑shard relationships. In the deployment phase, either follow the original design for new projects or retrofit existing systems by filtering and rewriting affected SQL statements.

Example Demonstration with jPetStore

The well‑known jPetStore demo is used to illustrate sharding analysis. Its domain model consists of three modules: User, Product, and Order. Vertical partitioning separates these modules, while horizontal partitioning targets the Account and Order tables for scaling. The following diagram shows the sharding layout:

In the demo, Product‑related tables remain vertically partitioned on a single node, while Account and Order are horizontally split across two shards. An additional optional horizontal split for the Product module is also shown.

Global Primary‑Key Generation Strategies

When data resides on multiple physical nodes, relying on the database’s native auto‑increment is impossible. Common approaches include:

UUID – simple but large and index‑heavy.

Dedicated SEQUENCE table per database, e.g.:

CREATE TABLE `SEQUENCE` (
  `tablename` varchar(30) NOT NULL,
  `nextid` bigint(20) NOT NULL,
  PRIMARY KEY (`tablename`)
) ENGINE=InnoDB;

This method suffers from a single‑point bottleneck.

A more robust solution, used by Flickr, employs multiple ID‑generation servers, each with its own SEQUENCE table. The step size equals the number of servers, and start values are offset, so IDs are distributed evenly (odd IDs from server 1, even IDs from server 2, etc.). If a server fails, another takes over, providing fault tolerance.

Key details of the Flickr scheme:

Each server runs a dedicated MySQL instance containing only the SEQUENCE table.

The stub column is a simple placeholder; a single record can serve many tables unless contiguous IDs are required.

MySQL’s LAST_INSERT_ID() is used; the INSERT and SELECT must share the same connection.

INSERTs are performed with REPLACE INTO to let MySQL generate the ID according to the configured offset and increment.

The SEQUENCE table uses the MyISAM engine for table‑level locking, avoiding ID collisions under concurrency.

Pure JDBC access outperforms Spring JDBC for this pattern.

Application code must balance load across the ID servers and fail over automatically when a server becomes unavailable.

Framework vs. Custom Sharding Implementation

Sharding logic can be placed at four layers:

DAO layer – direct control, no ORM constraints, but higher development effort.

ORM layer – e.g., Hibernate Shards (limited HQL support) or MyBatis plugins (limited connection‑level control).

JDBC API layer – transparent to the application but technically demanding; commercial products like dbShards exist.

Spring template layer – inject sharding into Spring’s data‑access templates (e.g., Cobar Client).

Proxy layer between application server and database – SQL parsing and routing are handled by tools such as MySQL Proxy or Amoeba (the latter lacks transaction support).

Choosing a framework depends on project size, team expertise, and required features; many architects prefer a custom DAO‑level solution for flexibility.

Multi‑DataSource Transaction Handling

Two main models are discussed:

Distributed transactions (two‑phase commit) guarantee atomicity but add latency and hinder horizontal scaling.

Best‑Efforts 1PC sacrifices strict safety for performance and is widely adopted in sharding systems.

Compensating transactions achieve eventual consistency for workloads tolerant of temporary inconsistencies.

Sharding Expansion Scheme Without Data Migration

The proposed expansion approach combines incremental‑range routing (to avoid data movement) with local hash‑based distribution (to prevent hotspots). A ShardGroup defines a writable ID range; when capacity is reached, a new ShardGroup is added, marked writable, and the old group becomes read‑only. Within each group, data is hashed across multiple shards, and large tables may be split into FragmentTables (or partitions) to stay within size limits.

Metadata describing the topology (ShardGroup, Shard, FragmentTable) is persisted in configuration files or a metadata database, allowing the system to reload the model on startup without code changes.

Expansion Example

Initial deployment: two shards each holding two fragment tables, covering IDs 0‑40 million.

After growth to 40 million records, three new shards are added (Shard2, Shard3, Shard4) with a new writable range 40‑100 million, increasing total capacity to 100 million.

When older shards free up space, their groups can be marked writable again and assigned new ID intervals, re‑using “regenerative” storage without further migration.

Overall, the scheme leverages both incremental range and hash routing to eliminate data migration, avoid hotspots, and keep the routing logic unchanged during expansion.