Mastering Million-Request Database Design: Vertical & Horizontal Sharding Strategies

High Concurrency Scenario

In high‑concurrency systems, the database often becomes the performance bottleneck. When QPS reaches hundreds of thousands or even millions, single‑database and single‑table performance, storage, and lock contention become limiting factors.

Database bottlenecks manifest as CPU, memory, disk I/O, and network bandwidth limits, as well as throughput drops caused by many concurrent connections and lock contention, leading to slow queries and exhausted connection pools.

Sharding (splitting databases and tables) is the key technique to break through these limits.

Vertical Sharding (Vertical Splitting)

Vertical sharding separates data of different business domains or modules into distinct database instances, reducing load on each database, simplifying tables, and allowing independent scaling and maintenance.

Example for an e‑commerce system:

• User module → user_db • Product module → product_db • Order module → order_db • Payment module → payment_db

Each database runs and is maintained independently, easing pressure on a single database.

Implementation Steps:

Business analysis: identify clear business domains (user, order, product, logs, etc.).

Assess data volume and access patterns (read/write ratio, transaction boundaries).

Design boundaries: decide which tables or modules to split, keep coupling minimal, avoid cross‑database transactions.

Data migration: plan online or offline migration, ensure data consistency and rollback strategy, often using dual‑write or sync relay.

Access layer refactoring: adjust application configuration and routing logic, possibly introducing middleware or service calls to reach different databases.

Horizontal Sharding (Horizontal Splitting)

After vertical sharding, a single table in a business domain (e.g., the Order DB) may still have massive data volume or concurrency pressure, requiring horizontal sharding. The core idea is to distribute rows of the same table across multiple tables or database instances based on a sharding key.

For example, a table order with 1 billion rows can be split into 100 tables order_00 … order_99.

Implementation Steps:

Determine the sharding key (e.g., user_id, order_id) that is frequently accessed and evenly distributes data; avoid time fields that cause hotspots.

Design sharding algorithm:

Modulo: table_index = user_id % 4 Range: split by ID or time intervals.

Consistent hashing: facilitates dynamic scaling.

Configure routing layer: use sharding middleware or framework to automatically route SQL to the correct table.

Read‑write separation and cache optimization: combine master‑slave replication and read‑write splitting middleware for further performance gains.

Challenges After Sharding

Sharding improves scalability and concurrency handling but introduces issues such as increased transaction complexity (cross‑database transactions require flexible or eventual consistency), distributed ID generation (use Snowflake or similar), more complex queries (avoid cross‑table joins), and higher maintenance overhead (data migration, backup, recovery, and stable middleware).