Database Sharding and Partitioning: Strategies, Practices, and Considerations

Various participants share their experiences and recommendations on how to split databases and tables (sharding) based on business needs, data volume, and access patterns, often using user IDs, time ranges, or hash functions.

Horizontal sharding creates multiple tables with the same schema, while vertical sharding restructures a table by moving less‑used columns to separate tables, both aiming to improve storage capacity and query efficiency.

Redundant data stores (e.g., MySQL, HBase, Cassandra) and caching solutions like Redis with consistent hashing are suggested to handle queries that do not align with the primary sharding key, as illustrated by examples of user information and product inventory.

When queries span multiple shards, approaches include scanning each shard and merging results, using proxy or business‑layer logic, or employing search engines (Elasticsearch, Hadoop/Hive) for large‑scale aggregation.

Pagination across shards depends on the sharding strategy; time‑based shards allow straightforward pagination within a single shard, while cross‑shard listings may require additional mechanisms such as search indexes.

For high‑traffic scenarios, caching, consistent hashing, and careful selection of sharding fields are emphasized to avoid hot spots and maintain performance.

Distributed transactions are generally discouraged; instead, soft‑transaction techniques using atomic locks in Redis or in‑process mutexes are recommended, with full distributed transaction support reserved for complex, large‑scale systems.

Example SQL transaction across shards:

start transaction;
select * from 1 where A = 1234;
update table1 set score = score + x where A = 1234 and score > x;
update table2 set score = score - x where B = 3456 and score > x;
commit; // or rollback

Overall, the discussion highlights that sharding and partitioning decisions must be driven by concrete business queries, data growth patterns, and operational constraints, with careful planning for redundancy, indexing, and eventual consistency.