Database Sharding
Why do we need Database Sharding?
Say you provide some service in which the data required to be stored is increasing with time. A single machine or database server can store and process a limited amount of data. And if too many users try to access the data simultaneously, then the application may face downtime. So, what could you do to meet the increasing demand of users to store and process a large volume of data? To meet this requirement for your system design you can use Database Sharding, as it will enable parallel processing of data that is being split into smaller parts.
What is Database Sharding?
Database Sharding is a process of storing a large database across multiple database servers (or machines). In Database Sharding large data is split into smaller chunks, called shards, which are stored across several database servers. Each shard is essentially a subset of the larger database that contains a portion of the data. All the database servers are usually implemented with the same underlying technology. They work together to store and process large sets of data.
Database Sharding is used to implement horizontal scaling.
Terminology
- Database: It is an organized collection of structured information or data. It is stored electronically in a machine called a database server.
- Shards: Partitioned data sets are called logical shards and the machine that stores the logical shard is called a physical shard or database node(server). Let’s see what exactly a shard is in a database. A physical shard can contain multiple logical shards.
- Shard key: Developer uses the shard key in determining how to partition the dataset. Each shard key represents a physical shard(i.e, machine). A column in the dataset determines which rows of data group together to form a shard. A shard key can be chosen from an existing column or by creating a new one.
How does Database Sharding work?
Each shard contains a subset of the database’s data and is typically assigned a unique identifier or key based on some data attribute, such as a customer ID or geographical region. When a query is made to the database, it is routed to the appropriate shard based on the key.
It is also important to understand when to shard a database, for example, when the load increases, data grows, etc could be a few reasons when we might be required to shard a database.
To understand it better, let’s say you have a large e-commerce website that sells products worldwide. As your website grows, the database becomes increasingly large and complex, which can slow down queries and make it difficult to manage. To improve performance and scalability, you decide to shard your database. You partition the data based on certain criteria, such as geography, product categories, or customer demographics. For example, you might shard your database by geographic region, creating separate shards for customers in North America, Europe, and Asia.
Each shard operates independently and contains a subset of the overall data. So when a customer in North America searches for a product, their query only hits the North American shard, instead of querying the entire database. This reduces the load on the database and improves response time.
To manage sharding, you need a system that routes queries to the appropriate shard and ensures that data is distributed evenly across all shards. This can be done using a shard key, which is a unique identifier for each shard. The shard key determines which shard a particular piece of data belongs to, and queries are directed to the appropriate shard based on the shard key.
For example, an unsharded database for customer records might look like
| Customer ID | Name | State |
| 1 | Shawn | California |
| 2 | Kane | Washington |
| 3 | Jennifer | Georgia |
| 4 | Chris | Arizona |
Sharding splits different rows of information from the table and stores them on different machines, as the following shows.
Computer A
| Customer ID | Name | State |
| 1 | Shawn | California |
| 2 | Kane | Washington |
Computer B
| Customer ID | Name | State |
| 3 | Jennifer | Georgia |
| 4 | Chris | Arizona |
What are the methods of Database Sharding?
Database sharding uses various methods to the shard key to determine the correct node (database server) for a particular data row. Some of the methods of Database Sharding are:
Range-based Sharding (Dyanamic Sharding)
Range-based sharding splits the database rows based on a range of values of a particular column of the database. A shard key can be assigned for a range of values.
For example, the database can be split according to the alphabet of the customer’s name as follows.
| Name | Shard key |
| Starts with A to J | A |
| Starts with K to R | B |
| Starts with S to Z | C |
When a particular row(or customer let’s say Bruce) is required to be accessed, the application determines the correct shard key( shard key = A) based on the customer’s name. Then the key is matched to its physical node(database server) and then that particular customer’s data can be accessed.
Merits and demerits of Range-based Sharding
It is simpler to implement. As per range-based sharding data might not be distributed evenly, as shard A(containing names starting from A to J) might have much more data entries than the ones with Shard C. Leading to the overloading of data on a single physical node.
Geo Sharding
The database can be split based on geolocation. Let’s say one shard to store data entries for the USA and another for India.
| Name | Shard key |
| Jennifer | USA |
| Rahul | INDIA |
| Robbin | USA |
The application uses the country as a shard key. They store each customer’s data in physical shards that are geographically located in the respective country.
Merits and demerits of Geo Sharding
Geo sharding helps in faster retrieval of data, as the request made by the user is looked into the shard that is nearest to it. But the database distribution can be uneven.
Hashed Sharding
Hashed Sharding assigns a key to each row of a database using a mathematical formula also called a hash function. A Hashed function takes some information from a row as input and produces a hash value. This hash value is used as a shard key and accordingly, it can help you in determining the physical shard for a particular row of a database.
Merits and demerits of Hashed Sharding
The good thing about hashed sharding is that database is evening distributed over multiple shards, but it does not separate the database based on any meaning of the information. There might be an issue while reassigning hash values to the shards while adding new shards to the existing pool of shards.