Database Sharding

By Author: scalebase
Total Articles: 1
Comment this article

Database Sharding is a popular concept used for scaling databases. Basically, it means that instead of storing data in one database, you distribute the application data across multiple databases. Why would you want to do that? Simply put, Database Sharding is the #1 way to scale your database. It’s done by many SaaS companies, the most popular web sites out there, and even big enterprises.

What is Sharding

Horizontal partitioning is a database design principle whereby rows of a database table are held separately, rather than splitting by columns (as for normalization). Each partition forms part of a shard, which may in turn be located on a separate database server or physical location.

But I think the best explanation uses an example, so let’s take the following table:

This is a small table containing a list of customers. Every database out there can handle such a table. But what will happen if instead of 7 rows the table has to store 7 million rows? Now, theoretically, this should be supported. But usually there will be lots of operations on such a large table – so we have many read and ...
... write operations on this table every second. Now, “At Scale, Everything breaks” (as Google VP Engineering, Urs Hölzle, says here). So our nice customer table will probably become a bottleneck. Why? Because it doesn’t fit in the database server cache anymore, because of database isolation management, and for other reasons, all of which cause the database to crawl under load.

Welcome to Sharding. If we take the customers table, and split it to 4 different databases, each database will contain 1.75 million rows. That’s still a lot – but less than 7 million rows. This will result in improved database performance.

Sharding Complexities

Sharding is great. However, writing sharding code is difficult. It requires you to rewrite most of your Data Access Layer from scratch. And while it’s difficult to do when you write your own SQL code, it’s even more complex when using O/R mapping tools, as most are not “sharding oriented”.

But even after writing the initial sharding code, you might run into issues. For instance, a common problem occurs when scaling requires adding more shards. Usually, internally written sharding code supports a fixed number of shards, and adding shards requires massive code rewrites – as well as the major downtime required when moving data from one shard to another.

Other parts of the infrastructure also change when using a sharded database. For example, the reporting application must now be aware of the sharding logic, since you want to collect data from multiple databases rather than just one. And if the reporting application is an off-the-shelf product, you’re out of luck. You’ll have to write the reporting application from scratch.

Backup is an issue. Database Administration is an issue. And more complexities just continue to pop up.

So Database Sharding. is a great solution for Database scaling, but it’s complex and costly. Unfortunately, most of the costs are hidden and only come up after the initial sharding is performed.