Understanding Database Sharding: Reasons, Methods, and Common Challenges

Database sharding (分库分表) becomes necessary when a single database can no longer handle the load, leading to request blocking, slow SQL queries, storage pressure, and limited write concurrency.

Typical thresholds suggested by Alibaba's P3C guideline recommend sharding when a single table exceeds 5 million rows or 2 GB in size; otherwise, avoid premature partitioning.

Vertical partitioning separates databases by business domain (e.g., order database vs. promotion database) to reduce contention, while vertical table splitting moves rarely used columns into separate tables to simplify development and avoid wide‑table issues.

Horizontal partitioning splits a table into multiple tables based on a key (range or hash) within the same database; when necessary, these tables can be distributed across multiple databases ( horizontal sharding ).

Key challenges and solutions include:

Distributed transactions : use two‑phase commit (2PC) or compensating transactions (TCC); middleware such as Spring JTA or Alibaba Fescar can help.

Cross‑node JOIN queries : mitigate by field redundancy, data abstraction (ETL), global tables, or application‑level assembly.

Cross‑node sorting, pagination, and function calculations : employ a secondary search store (Elasticsearch/Solr) for analytics queries while keeping the primary sharded store for transactional writes.

Global primary key generation : options include UUID (32‑character hex string), Redis‑based incremental IDs with distributed locks, Twitter's Snowflake algorithm (timestamp + machine ID + sequence), or database sequences with a step size equal to the number of nodes.

Scaling and expansion : set the number of tables to a power of two; when adding tables, use the same power‑of‑two strategy to minimize data migration.

Overall, the sharding strategy should start with a single‑database‑single‑table design, move to partitioning only when data volume or access patterns justify it, and consider the trade‑offs of each technique.