Mastering Database Bottlenecks: When and How to Shard Effectively

1. Database Bottlenecks

Both IO and CPU bottlenecks increase the number of active connections, eventually reaching the database's connection limit, which can cause service outages, reduced throughput, and crashes.

1.1 IO Bottleneck

Disk read IO: hot data exceeds cache, causing many reads and slowing queries → consider sharding and vertical partitioning.

Network IO: excessive data transfer exceeds bandwidth → consider database sharding.

1.2 CPU Bottleneck

SQL issues (joins, GROUP BY, ORDER BY, non‑indexed filters) increase CPU work → optimize SQL, add proper indexes, or move calculations to the service layer.

Large tables cause full scans and high CPU usage → apply horizontal partitioning.

2. Sharding Strategies

2.1 Horizontal Sharding (Database)

Concept: Split data across multiple databases based on a field using strategies such as hash or range.

Result:

Each database has identical schema.

Data in each database is distinct with no overlap.

The union of all databases equals the full dataset.

Scenario: High absolute concurrency where horizontal partitioning alone cannot solve the problem and there is no clear business boundary for vertical sharding.

Analysis: More databases reduce IO and CPU pressure proportionally.

2.2 Horizontal Sharding (Table)

Concept: Split a single table into multiple tables using a field and a strategy (hash, range, etc.).

Result:

All tables share the same schema.

Each table holds distinct rows with no overlap.

The union of all tables equals the full dataset.

Scenario: The system’s concurrency is stable, but a single table has grown large, degrading SQL efficiency and increasing CPU load.

Analysis: Reducing table size improves single‑query performance and eases CPU load.

2.3 Vertical Sharding (Database)

Concept: Split tables into different databases based on business domains.

Result:

Each database may have a different schema.

Data in each database is distinct with no overlap.

The union of all databases equals the full dataset.

Scenario: Absolute concurrency rises and distinct business modules can be isolated.

Analysis: Enables service‑oriented architecture; shared configuration or dictionary tables can be moved to dedicated databases.

2.4 Vertical Sharding (Table)

Concept: Split a table's columns into a main table and one or more extension tables based on column activity.

Result:

Table structures differ.

Each table shares at least a primary‑key column for joins.

The union of all tables equals the full dataset.

Scenario: Concurrency is stable, but a table has many columns, mixing hot and cold data, leading to large rows and random‑read IO.

Analysis: Place hot columns in the main table to keep them cached, move cold columns to extension tables, and join at the service layer instead of using costly joins in the database.

3. Sharding Tools

sharding‑sphere (formerly sharding‑jdbc)

TDDL (Taobao Distributed Data Layer)

Mycat (middleware)

Note: Evaluate the pros and cons of each tool yourself; prioritize official documentation and community support.

4. Sharding Implementation Steps

Assess capacity (current and growth) → choose a uniform key → define sharding rule (hash, range, etc.) → execute (usually double‑write) → plan expansion to minimize data movement.

5. Common Sharding Issues

5.1 Queries without the partition key

When only a non‑partition key is used, mapping or gene‑based strategies can be applied. Images illustrate mapping and gene methods.

Note: For writes, generate a user_id using the gene method (e.g., 3‑bit gene for 8 tables) and route reads by modulo.

5.2 Cross‑shard pagination

Solution: Use NoSQL stores such as Elasticsearch for pagination across shards.

5.3 Scaling (expansion)

Horizontal expansion of databases (adding read replicas) and tables (double‑write migration) are typical approaches.

Step 1: Enable double‑write in the application and deploy.

Step 2: Copy existing data to the new database.

Step 3: Verify data consistency.

Step 4: Remove double‑write configuration and deploy.

Note: Double‑write is a generic migration pattern.

6. Sharding Summary

Identify the real bottleneck before deciding how to shard.

Key selection is critical for even distribution and for supporting non‑partition queries.

Simplify sharding rules as much as possible while meeting requirements.

7. Sharding Example

Example repository: https://github.com/littlecharacter4s/study-sharding