When MySQL Hits Its Limits: Scaling with Horizontal and Vertical Sharding

MySQL theoretically supports up to 2^32 rows, but practical limits are lower due to myisamdatapointersize (default 6 bytes, yielding 2^48‑1 rows) and a maximum storage size of 256 TB. In real projects, performance issues appear far before these caps, prompting the need for sharding.

Database Sharding

Sharding splits a single logical database into multiple physical databases hosted on separate instances, providing two main benefits: reduced load per instance and easier capacity expansion.

Horizontal Sharding

Horizontal sharding distributes rows of a table across several databases based on a chosen rule (e.g., date range, ID modulo, consistent hashing). Each instance holds a subset of rows, while the full set of tables remains present on every instance.

Common horizontal sharding rules include:

Partition by date range – store different date intervals in separate databases.

Modulo on primary key – compute id % N and route rows accordingly.

Consistent hashing – map rows to nodes using a hash ring for balanced distribution.

Vertical Sharding

Vertical sharding separates tables themselves across instances, typically grouping tables by business domain. Each instance holds a complete set of rows for its assigned tables, while the overall schema is split.

Vertical sharding simplifies maintenance and aligns with micro‑service boundaries, but it can create single‑instance performance bottlenecks and makes cross‑database joins difficult.

Pros and Cons

Horizontal Sharding

Advantages

Excellent scalability – once a sharding rule is chosen, adding capacity is straightforward.

Improves stability and load capacity; well‑abstracted split rules allow most joins to be handled by the database.

Disadvantages

Distributed transaction consistency becomes hard.

Designing effective sharding rules requires high expertise.

Cross‑database join performance degrades.

Vertical Sharding

Advantages

Business‑oriented separation makes services clearer and works well with micro‑services.

System integration and expansion are easier.

Data maintenance is simpler.

Disadvantages

Single‑instance performance bottlenecks limit table scaling.

Cross‑database joins are challenging.

Transaction handling becomes more complex.

Conclusion

Although MySQL’s theoretical storage limit is high, developers should address sharding well before hitting that ceiling because performance degradation signals the need for horizontal or vertical partitioning. This article provides a conceptual foundation for distributed database middleware that will be explored in future posts.