7 Common Pitfalls of Database Sharding and How to Solve Them

Introduction

Sharding (splitting databases and tables) is an effective way to overcome single‑database performance bottlenecks, but it also introduces new complexities and technical challenges.

This article discusses seven typical problems after sharding and provides corresponding solutions.

1. Global Unique ID Issue

Problem

After sharding, auto‑increment IDs are only unique within each table, causing conflicts when a globally unique identifier is required (e.g., order numbers, user IDs).

Solution

1.1 Use Distributed ID Generators

Recommended tools:

Snowflake : Twitter’s open‑source distributed ID algorithm.

Baidu UidGenerator : An improved version of Snowflake.

Leaf : Meituan’s open‑source solution supporting both segment mode and Snowflake.

Code Example: Snowflake Algorithm

public class SnowflakeIdGenerator {
    private final long epoch = 1622476800000L; // custom timestamp
    private final long workerIdBits = 5L; // machine ID
    private final long datacenterIdBits = 5L; // data center ID
    private final long sequenceBits = 12L; // sequence
    private final long maxWorkerId = ~( -1L << workerIdBits);
    private final long maxDatacenterId = ~( -1L << datacenterIdBits);
    private final long sequenceMask = ~( -1L << sequenceBits);
    private long workerId;
    private long datacenterId;
    private long sequence = 0L;
    private long lastTimestamp = -1L;
    public SnowflakeIdGenerator(long workerId, long datacenterId) {
        if (workerId > maxWorkerId || workerId < 0) throw new IllegalArgumentException("Worker ID out of range");
        if (datacenterId > maxDatacenterId || datacenterId < 0) throw new IllegalArgumentException("Datacenter ID out of range");
        this.workerId = workerId;
        this.datacenterId = datacenterId;
    }
    public synchronized long nextId() {
        long timestamp = System.currentTimeMillis();
        if (timestamp < lastTimestamp) throw new RuntimeException("Clock moved backwards");
        if (timestamp == lastTimestamp) {
            sequence = (sequence + 1) & sequenceMask;
            if (sequence == 0) timestamp = waitNextMillis(lastTimestamp);
        } else {
            sequence = 0L;
        }
        lastTimestamp = timestamp;
        return ((timestamp - epoch) << (workerIdBits + datacenterIdBits + sequenceBits))
                | (datacenterId << (workerIdBits + sequenceBits))
                | (workerId << sequenceBits)
                | sequence;
    }
    private long waitNextMillis(long lastTimestamp) {
        long timestamp = System.currentTimeMillis();
        while (timestamp <= lastTimestamp) timestamp = System.currentTimeMillis();
        return timestamp;
    }
}

1.2 Database Segment Allocation

Maintain a separate global_id table and allocate IDs with a step size per shard, e.g., shard 1 uses odd numbers, shard 2 uses even numbers.

CREATE TABLE global_id (
    id INT PRIMARY KEY AUTO_INCREMENT,
    stub CHAR(1) NOT NULL UNIQUE
);
-- Set step size:
SET @@auto_increment_increment = 2;
SET @@auto_increment_offset = 1;

2. Cross‑Shard Query Complexity

Problem

Aggregations such as total counts or paginated queries must be executed across multiple shard tables, increasing query complexity.

Solution

2.1 Middleware (Recommended)

Use ShardingSphere or MyCAT, which support SQL sharding execution and result merging without changing business code.

2.2 Manual Shard Queries

Query each shard separately and merge results in the application layer.

Example: Aggregated Count

public int countAllOrders() {
    int total = 0;
    for (String db : List.of("db1", "db2", "db3")) {
        String sql = "SELECT COUNT(*) FROM " + db + ".orders";
        total += jdbcTemplate.queryForObject(sql, Integer.class);
    }
    return total;
}

Example: Cross‑Shard Pagination

public List<Order> paginateOrders(int page, int size) {
    List<Order> allOrders = new ArrayList<>();
    for (String table : List.of("orders_1", "orders_2")) {
        String sql = "SELECT * FROM " + table + " LIMIT 100";
        allOrders.addAll(jdbcTemplate.query(sql, new OrderRowMapper()));
    }
    allOrders.sort(Comparator.comparing(Order::getCreatedAt));
    return allOrders.stream()
            .skip((page - 1) * size)
            .limit(size)
            .collect(Collectors.toList());
}

3. Distributed Transaction Issues

Problem

Transactions that span multiple databases (e.g., order table in DB A and inventory table in DB B) cannot rely on a single‑database transaction, risking data inconsistency.

Solution

3.1 Distributed Transaction Frameworks

Seata provides cross‑database transaction support.

@GlobalTransactional
public void createOrder(Order order) {
    orderService.saveOrder(order); // write to DB A
    stockService.reduceStock(order.getProductId()); // update DB B
}

3.2 Saga / Flexible Transactions

Use message queues (e.g., RocketMQ transactional messages) to achieve eventual consistency.

4. Shard Key Design Issues

Problem

Choosing a poor shard key can cause data skew (hotspots) or inefficient routing.

Solution

4.1 Shard Key Design Principles

Even data distribution to avoid hotspots.

Prefer fields that are frequently used in queries.

4.2 Routing Table

Maintain a global routing table that maps a shard key to the target table.

public String getTargetTable(int userId) {
    String sql = "SELECT table_name FROM routing_table WHERE user_id = ?";
    return jdbcTemplate.queryForObject(sql, new Object[]{userId}, String.class);
}

5. Data Migration Challenges

Problem

When scaling from, for example, 4 shards to 8, existing data must be migrated to new shards, which can be complex and impact online services.

Solution

5.1 Dual‑Write Strategy

Write to both old and new tables during migration, then switch to the new tables after completion.

5.2 Incremental Sync

Use Canal to listen to MySQL binlog and synchronize data to new shards.

canal.destinations:
  example:
    mysql:
      hostname: localhost
      port: 3306
      username: root
      password: password
    kafka:
      servers: localhost:9092
      topic: example_topic

6. Pagination Query Issues

Problem

Pagination across multiple shard tables requires merging data before applying a global offset/limit, increasing logic complexity and memory usage.

Solution

Query each shard with its own pagination, then merge, sort, and apply final pagination in the application.

Use middleware that supports global pagination, such as ShardingSphere.

Example: Cross‑Shard Pagination

public List<Order> queryPagedOrders(int page, int size) {
    List<Order> results = new ArrayList<>();
    for (String table : List.of("orders_1", "orders_2")) {
        results.addAll(jdbcTemplate.query("SELECT * FROM " + table + " LIMIT 100", new OrderRowMapper()));
    }
    results.sort(Comparator.comparing(Order::getCreatedAt));
    return results.stream()
            .skip((page - 1) * size)
            .limit(size)
            .collect(Collectors.toList());
}

7. Operational Complexity

Problem

Sharding increases the number of database instances, making monitoring, backup, and fault isolation more difficult.

Solution

Adopt an automated operations platform (e.g., Alibaba Cloud DMS).

Use monitoring stacks such as Prometheus + Grafana for shard‑level metrics.

Conclusion

Sharding trades performance for complexity. While it can significantly improve system scalability, it introduces challenges such as global ID generation, cross‑shard queries, distributed transactions, shard‑key design, data migration, pagination, and operational overhead. Selecting appropriate strategies and tools can mitigate these issues and achieve high performance and reliability.