Database Sharding Strategies: When to Use Vertical, Horizontal, and Range Partitioning

Case Background

An interview question asks how to redesign an architecture when a single‑table order database faces massive write traffic after master‑slave separation, causing performance degradation.

How to Determine Sharding: Database vs. Table

When to Shard Tables

Table sharding is appropriate when data volume becomes so large that transaction execution slows down, and reducing the amount of data scanned per query is essential.

When to Shard Databases

Database sharding is needed when a single database instance cannot handle high concurrency, so requests are distributed across multiple instances.

Vertical Partitioning

Vertical partitioning separates data by business domain, e.g., moving product data to a product database and order data to an order database, often accompanying a micro‑service architecture transformation.

Vertical splitting isolates different business data, improving architectural extensibility, but it does not solve data‑size explosion within a single business domain.

Horizontal Partitioning

Horizontal partitioning (sharding) distributes rows of a single large table across multiple tables or databases, often using hash modulo or range rules, to keep each table at a manageable size.

Range (Range) Sharding

Range sharding splits data based on a field interval, such as time or product category, allowing queries to first locate the appropriate partition before scanning.

Range sharding can lead to uneven data distribution; to handle hot‑spot data, vertical scaling (enhancing single‑machine resources) or a metadata‑driven sharding layer can be used.

The metadata‑driven approach stores shard‑routing rules in a table, enabling dynamic re‑sharding without service downtime, though it adds complexity and requires high availability.

How to Solve Data Query Problems After Sharding

Aggregated queries across shards can be addressed by synchronizing summary data to Elasticsearch, storing counters in a dedicated table, or exporting periodic reports to HDFS for big‑data processing.

Summary

When facing database capacity limits and high write concurrency, vertical sharding aligns with business domain separation, while horizontal sharding (hash or range) distributes data across tables or instances; combining both yields a scalable architecture.

Hash sharding is simple and ensures even distribution but ignores business semantics; range sharding respects business attributes and enables more efficient scans, though it may cause data skew.

Metadata‑driven sharding offers the most flexibility but adds implementation complexity and requires high‑availability support.