Understanding Database Sharding: Why It Matters and How to Implement It

In the growth path of backend engineers, grasping the concept of sharding is an unavoidable threshold, and database partitioning (sharding) serves as a prime teaching material for this idea; most backend developers encounter it during their career.

Sharding is introduced because the underlying principle (the "Dao") is far more important than any specific technique. While new technologies emerge rapidly, simple and universal principles endure.

What is database sharding? When business growth leads to database bottlenecks—whether performance, capacity, or single‑point failures—sharding offers a solution by distributing data across multiple databases and tables.

Databases naturally become bottlenecks due to performance and capacity limits. For example, a requirement that all requests return within 100 ms leaves only 1–2 ms for the data layer; any fluctuation in the database is amplified ten‑ to twenty‑fold at the application level. Additionally, a single database must maintain the latest, most accurate state, which grows with business complexity and data volume, leading to performance issues on a single machine.

Consequently, many internet companies adopt sharding as an industry‑standard approach to overcome these bottlenecks.

The essence of sharding can be traced back to the history of databases. Early computing involved paper‑tape punched cards; databases emerged to manage collections of such tapes, providing storage, retrieval, and consistency. IBM introduced the first modern DBMS in 1968, and relational databases rose in the 1970s, with Oracle eventually dominating.

Modern databases provide storage, indexing, and transactional consistency. However, as traffic splits and scales, a single‑machine database hits limits because it must guarantee consistency while handling increasing load.

Sharding stretches storage by sacrificing some indexing and consistency guarantees. It reduces the amount of data each node must process, thereby improving speed. Typical strategies include date‑based partitioning for logs, hot‑cold separation for transaction systems, and hash‑based horizontal splitting for long‑lived entities.

Splitting tables addresses the slowdown caused by extremely large tables (e.g., billions of rows) by dividing them into smaller tables, which reduces query time. Splitting databases tackles hardware limits such as network, CPU, or disk saturation by distributing data across multiple machines.

The core goal of sharding is to create a logical “big table” that appears transparent to the business while distributing storage load.

Ensuring indexes requires recognizing that indexes and storage are separate concerns. After data is distributed, a single‑node index can no longer satisfy queries; middleware can provide virtual indexes for simple cases, while complex indexes may need external systems.

Middleware typically sits between the application and data sources, performing virtual indexing, routing requests to the appropriate database/table, and rewriting SQL when necessary. For more complex queries, a separate indexing system (e.g., Elasticsearch) may be employed, acknowledging that external indexes are slower than native ones.

Ensuring consistency remains challenging because sharding disperses data across multiple databases, processes, and machines, weakening atomicity. Common approaches include external consistency mechanisms such as message queues (MQ) to achieve eventual consistency, and soft‑transaction patterns that roll back or compensate when a step fails.

Sharding design example for a transaction system: keep recent three months of orders in a hot database to satisfy strong transactional consistency; older data is moved to cold databases partitioned by date. Middleware determines whether an order ID belongs to hot or cold storage, queries the appropriate database, and uses MQ to synchronize data. Complex offline queries can be handled by a dedicated Elasticsearch cluster.

Summary

Sharding stretches storage by sacrificing some indexing and consistency.

External indexes handle complex query needs.

External consistency mechanisms compensate for lost atomicity.

Sharding resolves the single‑point nature of traditional databases.

Author: Wang Tao, graduated from Northeastern University in 2014, now a R&D engineer.

Source: http://mp.weixin.qq.com/s/2saLudueLts109Vfc1pnVw