Why Sharding Fails: Hidden Pitfalls of Database Partitioning in E‑commerce

1. Sharding Can't Solve Distributed Transaction Issues

When tables participating in the same transaction use different shard keys, they may be placed on different databases, making it impossible to guarantee strong consistency with a single local transaction. For example, a coupon system shards user data by UserId but inventory by coupon‑template ID, forcing a distributed transaction that is hard to resolve.

2. Sharding Limits Non‑Shard Attribute Index Queries

Using UserId as the shard key means queries that filter by other dimensions (e.g., merchant ID) must scan every shard, which is impractical at scale. Work‑arounds include creating a separate shard on merchant ID or offloading such queries to ElasticSearch, both of which increase system complexity and reduce data consistency.

3. Global Primary Key Problem

Auto‑increment IDs lose their usefulness after sharding because each shard generates its own sequence. A distributed ID generator (UUID, Snowflake, Meituan Leaf, Baidu UidGenerator, etc.) is required to produce globally unique identifiers, adding an extra technical component to the architecture.

4. Global Unique Key Constraints Are Hard

When a unique constraint involves a non‑shard attribute (e.g., an OrderId that must be unique across all fulfillment records), sharding prevents enforcing that constraint across all databases, forcing developers to accept eventual consistency or implement additional validation logic.

5. Continuous Need to Build Custom Tooling

Sharding introduces many auxiliary pieces of infrastructure: routing middleware such as ShardingSphere or Mycat, distributed ID services, binlog‑based DTS for cross‑shard queries, ElasticSearch for secondary indexes, and admin consoles for DDL operations. Maintaining this “wheel‑building” ecosystem becomes a permanent burden.

6. Scaling Out Becomes Painful

Expanding from, say, 8 to 16 shards typically requires downtime, complex data migration tools, and extensive testing. Over‑provisioning from the start raises hardware costs, while under‑provisioning forces risky, time‑consuming expansions later.

7. Operational Overhead Grows Exponentially

Each additional shard multiplies DBA workload: monitoring, backup, schema changes, and instance provisioning. Reports of teams deploying dozens or hundreds of MySQL instances illustrate the massive operational pressure sharding creates.

8. Business Logic Is Forced to Compensate for Database Limits

Sharding pushes consistency, indexing, and uniqueness problems onto the application layer, requiring developers to design eventual‑consistency patterns or complex compensating transactions.

9. Distributed Databases as an Alternative

Distributed databases such as OceanBase provide native global indexes, global unique constraints, and two‑phase‑commit distributed transactions, eliminating many of the pain points of sharding while delivering comparable performance, scalability, and lower operational cost.

In summary, sharding is often a stop‑gap solution for scaling single‑node databases; moving to a true distributed database can simplify architecture and reduce the long‑term burden on both developers and operations.