Database Sharding: Vertical vs Horizontal Partitioning and Hash vs Range Strategies

In medium to large projects, when data volume becomes large, it is necessary to split the data, using either vertical or horizontal partitioning.

Vertical partitioning is straightforward: a single database is divided into multiple databases based on business domains, such as separating an order database and a user database.

Horizontal partitioning means splitting the same business data across multiple tables.

For example, order data reaches 40 million rows; MySQL recommends keeping a single table under one million rows, so horizontal splitting into four tables (or more) is needed, possibly combined with database splitting to distribute load.

Database and table splitting schemes commonly use hash modulo and range strategies, with a routing algorithm that maps a routing key to a specific storage location.

Hash modulo scheme

Assuming an estimated 40 million orders and each table can hold 10 million, four tables are designed. The routing key (e.g., id ) is modulo‑divided by the total number of tables; for instance, id=12 % 4 = 0, so the order goes to table 0, while id=13 % 4 = 1 goes to table 1.

Advantages : orders are evenly distributed across the four tables, avoiding hotspot problems.

Hotspot definition : when many operations concentrate on a single table, creating a performance bottleneck.

Because order timestamps are clustered, orders created in the same period tend to reside in the same table, leading to hotspots.

Disadvantages : data migration and scaling become difficult. If the business grows and the order count exceeds 40 million, adding four more tables changes the modulo base to 8, causing existing IDs (e.g., id=12 ) to map to a different table than before, making previously stored data unreachable.

To resolve this, a full data migration is required to re‑hash all existing rows into the new table layout, which is painful and often cannot be done without downtime.

Is there a solution that avoids migration?

Range scheme

Range partitioning splits data by ID ranges.

For example, IDs 0–12 go to table 0, IDs up to 13 million go to table 1, etc. The range for each table is defined in advance, and routing is performed by checking the ID range.

Advantages : scaling is easy; adding new tables for IDs beyond the current maximum does not require moving existing data.

Disadvantages : hotspots can occur because IDs increase monotonically, causing a large batch of recent orders to concentrate in the same table (e.g., IDs 10 million–20 million all go to table 1), overloading it.

Summary

Hash modulo scheme : no hotspot issues, but scaling requires painful data migration.

Range scheme : avoids migration, but can create hotspot problems.

Is there a scheme that combines the benefits of both—no migration and no hotspots? The author will introduce such a solution in the next article.

If you have questions about this content, please leave a comment below.

Copyright statement: Content sourced from the internet, copyright belongs to the original author. We will indicate the author and source unless it cannot be confirmed. If there is any infringement, please let us know and we will delete it promptly.