Database Sharding Strategies: Vertical and Horizontal Partitioning, ID Generation, and Migration Phases

Background

The order table has grown beyond 200 GB with many query dimensions; even with two read replicas and index optimizations, performance remains unsatisfactory. High‑traffic flash‑sale events have pushed the database to its limits, requiring rate‑limiting and asynchronous queues. The existing order model cannot meet evolving business needs, and DDL changes on the original table are too costly, making database partitioning urgent.

Vertical Partitioning

The order database is first split vertically into a basic order database, an order‑process database, etc. (details omitted).

Horizontal Partitioning

Vertical partitioning relieves single‑cluster pressure but still struggles during flash sales. A new unified order model is designed to serve C‑end users, B‑end merchants, customer service, and operations by sharding on userId and shopId, synchronizing to an operation database via PUMA.

Sharding Strategies

1. Query Sharding : Store the mapping between id and database in a separate database.

Advantages: mapping algorithm can be changed freely. Disadvantages: introduces a single point of failure.

2. Range Sharding : Split by time range or id range.

Advantages: table size controllable, natural horizontal scaling. Disadvantages: cannot solve write‑hotspot bottlenecks.

3. Hash Sharding : Typically use mod; the article recommends consistent hashing with mod 2^n. Example: a 32 × 32 scheme (userId last four bits mod 32 for databases, then div 32 mod 32 for tables), yielding 1 024 tables across 8 clusters (4 databases per cluster).

Scalability Scenarios

Scenario 1: Database performance bottleneck

Method 1: Expand to 32 database clusters directly.

Method 2: Adjust sharding rule to (32 × 2^n) × (32/2^n), supporting up to 1 024 clusters.

Scenario 2: Single‑table capacity bottleneck

Even if a table exceeds 200 GB (200 GB × 1 024 ≈ 200 TB), further splitting can be done by keeping the 32 × 32 rule and further fracturing tables, limited by the four‑bit userId (max 8 192 tables). An alternative dimension is shopId with an 8 × 8 rule.

ID Generation

Four common schemes are discussed:

Auto‑increment IDs (simple but single‑point risk).

Clustered IDs with step size (Flickr scheme) – high availability but requires a dedicated cluster.

Twitter Snowflake – high performance and scalability, needs its own cluster and ZooKeeper.

GUID/Random – simple but long and possible collisions.

The chosen solution combines timestamp, user identifier (last four bits of userId), and random number, offering low cost, near‑zero collisions, built‑in sharding, sortable IDs, and acceptable performance.

Transaction & Query Considerations

Transactions are supported because the entire order domain is sharded consistently. Complex queries must include the sharding key; cross‑shard joins are avoided.

Data Migration Phases

Phase 1 : Dual‑write to old and new models, old model reads, daily job reconciliation, and historical data import.

Phase 2 : Complete historical import, new model becomes the source of truth for online queries, continue dual‑write with new model priority, daily reconciliation.

Phase 3 : Old model stops writing; only orders reaching a final state are asynchronously back‑filled. Offline processes still rely on the old model until downstream data warehouses are fully migrated.

Key Reflections

Not every table needs horizontal splitting; use it only when growth demands it.

Separate online and offline query workloads in high‑concurrency scenarios.

Choose sharding dimensions that solve existing problems and simplify development.

Databases require protection; use simple, well‑indexed queries for long‑term capacity planning and horizontal scalability.

Thanks to the DBA and middleware teams for their support.