Mastering Database Sharding: When, How, and Pitfalls of Partitioning

Overview

Sharding (分库分表) is a technique to split data across multiple tables and databases. It reduces pressure on CPU, memory, disk, and I/O, and limits the impact of a single‑node failure. However, sharding introduces challenges such as transaction consistency, primary‑key collisions, cross‑database joins, and distributed aggregations.

What is Sharding?

Table sharding (分表) : A logical table is divided into several physical tables according to a rule, decreasing lock granularity and index size.

Database sharding (分库) : Rows are distributed across multiple databases, relieving a single server’s resource limits.

Why Shard?

Performance : When a table reaches millions of rows, large index trees become a bottleneck and concurrent traffic may exceed a single server’s capacity.

Availability : Spreading data over N databases reduces the outage scope from 100% to roughly 1/N of services.

Problems Introduced by Sharding

Transaction consistency

Design the schema so that all tables involved in a transaction reside in the same database.

If this is impossible, use distributed‑transaction mechanisms (e.g., transactional messages, TCC, Seata) to achieve eventual consistency.

Primary‑key uniqueness

Auto‑increment with a large step can avoid immediate collisions but does not solve migration or scaling conflicts.

Prefer globally unique identifiers such as UUID, Snowflake, or database‑allocated ID segments.

Cross‑database joins

Avoid joins in production. Perform separate queries on each shard and assemble results in the application layer, taking transaction tolerance into account.

Cross‑database aggregations Aggregations like GROUP BY or ORDER BY require special handling.

Approach 1: “Race‑horse” Top‑N Merge

// Two databases, each with one table
// First round: fetch N rows from each DB
SELECT * FROM db1_table1 WHERE $col > 0 ORDER BY $col LIMIT 0,N;
SELECT * FROM db2_table1 WHERE $col > 0 ORDER BY $col LIMIT 0,N;
// Application merges the two result sets, tracks offsets K1 and K2,
// and returns the global top N.
// Subsequent rounds use the stored offsets to continue fetching.

Approach 2: Centralized Index Table

Store frequently aggregated fields in a single table (e.g., Redis, Elasticsearch, or a dedicated MySQL table). First query this table for primary keys, then fetch the detailed rows from the appropriate shards. This adds an extra round‑trip and may introduce consistency concerns.

Characteristics of a Good Sharding Scheme

Business fit : Choose a partitioning dimension (time, user ID, business domain) that matches access patterns.

Sustainability : The scheme should scale smoothly as data and traffic grow.

Minimal data migration : Ideal migration path is same‑db‑same‑table → same‑table‑different‑db → different‑db‑same‑table → different‑db‑different‑table.

Data‑skew control : Keep the skew ratio ((max‑sample − min‑sample) / min‑sample) below 5%.

How to Shard

Vertical Splitting

Vertical table split : Move rarely used or large columns (e.g., TEXT) to an extension table linked by a primary‑key foreign key, reducing the main table size.

Vertical database split : Separate distinct business domains (orders, users, products) into different databases, lowering per‑database resource pressure and reducing single‑node failure impact.

Horizontal Splitting

Horizontal table split : Distribute rows of a logical table across multiple tables within the same database. This reduces lock granularity but still shares the same I/O resources.

Horizontal database split : Distribute rows across multiple databases, alleviating single‑node pressure and improving read/write throughput.

Common Horizontal Sharding Strategies

Range Sharding

Data is partitioned by value ranges (e.g., by year for orders). TiDB’s underlying TiKV also uses a range‑based approach.

Requires pre‑creating databases/tables.

Hot‑spot risk: recent data concentrates in the latest shard.

Pagination across shard boundaries is complex.

Hash Sharding

Hash the sharding key (usually the primary key) and map the result to a database and table.

Simple Independent Hash

const (
    DbCnt   = 10 // number of databases
    TableCnt = 100 // number of tables per DB
)

func GetTableIdx(userID int64) (int64, int64) {
    hash := hashCode(userID)
    return hash % DbCnt, hash % TableCnt
}

When DbCnt and TableCnt share a common factor, data skew can occur.

Unified Hash (single slot)

func GetTableIdx(userID int64) (int64, int64) {
    hash := hashCode(userID)
    slot := DbCnt * TableCnt
    return hash % slot % DbCnt, hash % slot / DbCnt
}

This reduces skew but changes table indices after scaling.

Two‑Stage Partitioning

func GetTableIdx(userID int64) (int64, int64) {
    hash := hashCode(userID)
    slot := hash % (DbCnt * TableCnt)
    dbIdx := slot / TableCnt
    tblIdx := slot % TableCnt
    return dbIdx, tblIdx
}

Keeping TableCnt constant during expansion preserves table indices.

Gene Method

func GetTableIdx(userID int64) (int64, int64) {
    hash := hashCode(userID)
    return atoi(hash[0:4]) % DbCnt, atoi(hash[4:]) % TableCnt
}

Different segments of the hash act as independent “genes” for DB and table selection, reducing skew.

Routing (Relation) Table

Maintain a separate index table that maps each sharding key to its target DB/table. Reads first consult the routing table, then fetch the actual row. Writes may use random or round‑robin selection. This eliminates data migration during scaling but adds an extra lookup and requires distributed‑transaction support for consistency.

Segmented Index Table

Store range‑based mappings (e.g., ID intervals) instead of a row‑by‑row index, reducing the size of the routing table while still enabling fast lookups.

Consistent Hashing

Apply a consistent‑hash ring (as used by Redis Cluster) to assign shards, providing smooth scaling with minimal data movement.

Conclusion

Sharding is an effective tool for overcoming CPU, memory, disk, and I/O bottlenecks and for limiting the impact of single‑node failures. It must be applied only when necessary, with careful consideration of transaction handling, global primary‑key generation, cross‑shard joins, and aggregation strategies. Evaluate business access patterns, ensure scalability, minimize data migration, and control data skew. When the database becomes a performance bottleneck, mature distributed databases such as TiDB can also be considered as an alternative.