How to Scale an Order System Beyond 200GB with Vertical & Horizontal Sharding

Background: The order table exceeded 200 GB, with many query dimensions; even with two replicas and index optimizations, queries remained suboptimal. High‑traffic events caused bottlenecks, requiring rate limiting and async queues. The original order model could not meet new business needs, and DDL changes were painful, making sharding urgent.

Goal: Ensure the order capacity will not be a concern for the next ten years.

Vertical Sharding

First, the order database was vertically split into a base order database, an order workflow database, etc. (Details omitted).

Horizontal Sharding

Vertical sharding eased single‑cluster pressure but still struggled during flash sales. A new unified order model was designed to serve C‑end users, B‑end merchants, customer service, and operations. Data is partitioned by userId and merchantId, and synchronized to an operational database via PUMA.

Sharding Strategies

1. Query Sharding – Mapping of id to database is stored in a separate database.

Pros: Mapping algorithm can be changed arbitrarily. Cons: Introduces an extra single point of failure.

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

Pros: Table size is controllable and naturally horizontally scalable. Cons: Does not solve concentrated write bottlenecks.

3. Hash Sharding – Usually using mod. Consistent hashing with mod 2^n is recommended.

Example: For a unified order database, a 32×32 scheme is used. userId's last four digits mod 32 determine the database, and the same value div 32 mod 32 determines the table, yielding 1024 tables across 8 clusters (primary‑replica).

Scalability Scenarios

Scenario 1 – Database performance bottleneck: expand to 32 clusters directly, or adjust the sharding rule to (32×2^n)*(32/2^n) to reach up to 1024 clusters.

Scenario 2 – Single table reaches capacity (e.g., 200 GB). By further splitting tables within each database, up to 8192 tables can be created using the four‑digit suffix.

Alternative dimension: sharding by shopId with an 8×8 rule, storing only the order main table for shop‑level queries.

Unique ID Scheme

Various approaches exist: auto‑increment, clustered step (Flickr), Twitter Snowflake, GUID/Random. The chosen solution avoids independent clusters and uses a business‑aware ID: timestamp + user identifier (last four digits of userId) + random number. Benefits: low cost, near‑zero collisions, embeds sharding rule, sortable, though slightly longer and marginally slower than int/bigint.

Transaction & Query Considerations

Transactions are supported because the entire order aggregate is split consistently. After vertical sharding, joins are eliminated; after horizontal sharding, queries must include the sharding key. Non‑sharded dimension queries are discouraged.

Data Migration Phases

Phase 1 – Dual‑write to old model, queries use old model, daily reconciliation jobs, historical data migration.

Phase 2 – After historical data is verified, dual‑write continues but new model becomes authoritative for online queries.

Phase 3 – Old model stops writing; only completed orders are asynchronously back‑filled. Offline processes still rely on the old model until downstream data warehouses are updated.

Key Takeaways: Not every table needs horizontal splitting; only high‑growth tables. Separate online and offline query paths, isolate transaction vs. operational queries, choose sharding dimensions that simplify development, and protect the database with simple, well‑indexed queries for long‑term capacity planning.