Database Sharding - System Design

Database sharding is a technique for horizontal scaling of databases, where the data is split across multiple database instances, or shards, to improve performance and reduce the impact of large amounts of data on a single database.

It is basically a database architecture pattern in which we split a large dataset into smaller chunks (logical shards) and we store/distribute these chunks in different machines/database nodes (physical shards).

• Each chunk/partition is known as a "shard" and each shard has the same database schema as the original database.
• We distribute the data in such a way that each row appears in exactly one shard.
• It's a good mechanism to improve the scalability of an application. 

Methods of Sharding

Sharding can be done using Range-based, Hash-based, Directory-based, or Geographic-based partitioning to distribute data across multiple servers.

1. Key Based Sharding

• Key-Based Sharding (Hash-Based Sharding) uses a hash function on a shard key (e.g., customer ID, email, IP, zip code).
• The generated hash value decides which shard will store the data.
• The shard key must come from the same column to keep data placement consistent and correct.
• Shard keys work like a primary key/unique identifier for each row.

For example:

You have 3 database servers and each request has an application id which is incremented by 1 every time a new application is registered.

To determine which server data should be placed on, we perform a modulo operation on these applications id with the number 3. Then the remainder is used to identify the server to store our data.

Advantages:

• Key-based sharding assigns each key to a specific shard, ensuring uniform and consistent data distribution.
• It can be optimized to efficiently handle queries over consecutive key ranges.

Disadvantages:

• Uneven data distribution can occur if the sharding key isn’t well-distributed.
• Scalability may be limited when certain keys receive heavy traffic or data is skewed.
• Choosing the right sharding key is crucial for effective sharding.

2. Horizontal or Range Based Sharding

In Horizontal or Range Based Sharding, we divide the data by separating it into different parts based on the range of a specific value within each record. Let's say you have a database of your online customers' names and email information. You can split this information into two shards.

• In one shard you can keep the info of customers whose first name starts with A-P
• In another shard, keep the information of the rest of the customers. 

Advantages:

• Scalability: Horizontal or range-based sharding allows for seamless scalability by distributing data across multiple shards, accommodating growing datasets.
• Improved Performance: Data distribution among shards enhances query performance through parallelization, ensuring faster operations with smaller subsets of data handled by each shard.

Disadvantages:

• Complex Querying Across Shards: Coordinating queries involving multiple shards can be challenging.
• Uneven Data Distribution: Poorly managed data distribution may lead to uneven workloads among shards.

3. Vertical Sharding

In Vertical Sharding, we split the entire column from the table and we put those columns into new distinct tables. Data is totally independent of one partition to the other ones. Also, each partition holds both distinct rows and columns. We can split different features of an entity in different shards on different machines.

For example:

On Twitter users might have a profile, number of followers, and some tweets posted by his/her own. We can place the user profiles on one shard, followers in the second shard, and tweets on a third shard.

Advantages:

• Query Performance: Vertical sharding can improve query performance by allowing each shard to focus on a specific subset of columns. This specialization enhances the efficiency of queries that involve only a subset of the available columns.
• Simplified Queries: Queries that require a specific set of columns can be simplified, as they only need to interact with the shard containing the relevant columns.

Disadvantages:

• Potential for Hotspots: Certain shards may become hotspots if they contain highly accessed columns, leading to uneven distribution of workloads.
• Challenges in Schema Changes: Making changes to the schema, such as adding or removing columns, may be more challenging in a vertically sharded system. Changes can impact multiple shards and require careful coordination.

4. Directory-Based Sharding

In Directory-Based Sharding, we create and maintain a lookup service or lookup table for the original database. Basically we use a shard key for lookup table and we do mapping for each entity that exists in the database. This way we keep track of which database shards hold which data.

The lookup table holds a static set of information about where specific data can be found.

In the above image, you can see that we have used the delivery zone as a shard key:

• Firstly the client application queries the lookup service to find out the shard (database partition) on which the data is placed.
• When the lookup service returns the shard it queries/updates that shard.  

Advantages:

• Flexible Data Distribution: Directory-based sharding allows for flexible data distribution, where the central directory can dynamically manage and update the mapping of data to shard locations.
• Efficient Query Routing: Queries can be efficiently routed to the appropriate shard using the information stored in the directory. This results in improved query performance.
• Dynamic Scalability: The system can dynamically scale by adding or removing shards without requiring changes to the application logic.

Disadvantages:

• Centralized Point of Failure: The central directory represents a single point of failure. If the directory becomes unavailable or experiences issues, it can disrupt the entire system, impacting data access and query routing.
• Increased Latency: Query routing through a central directory introduces an additional layer, potentially leading to increased latency compared to other sharding strategies.

Ways to optimize database sharding for even data distribution

Here are some simple ways to optimize database sharding for even data distribution:

• Use Consistent Hashing: This helps distribute data more evenly across all shards by using a hashing function that assigns records to different shards based on their key values.
• Choose a Good Sharding Key: Picking a well-balanced sharding key is crucial. A key that doesn’t create hotspots ensures that data spreads out evenly across all servers.
• Range-Based Sharding with Caution: If using range-based sharding, make sure the ranges are properly defined so that one shard doesn’t get overloaded with more data than others.
• Regularly Monitor and Rebalance: Keep an eye on data distribution and rebalance shards when necessary to avoid uneven loads as data grows.
• Automate Sharding Logic: Implement automation tools or built-in database features that automatically distribute data and handle sharding to maintain balance across shards.

Alternatives to database sharding

Below are some of the alternatives to database sharding:

1. Vertical Scaling: Vertical Scaling means upgrading the same server by adding more CPU, RAM, or storage to handle more load, but it has a fixed hardware limit so it can’t scale forever.
2. Replication: Replication means creating database copies on multiple servers for load balancing and high availability, but replicas may face synchronization issues.
3. Partitioning: Partitioning means splitting data into smaller parts within the same server to improve performance for large datasets without using multiple servers.
4. Caching: Caching means storing frequently used data in Redis/Memcached to reduce database load and speed up responses.
5. CDNs: CDNs mean using a Content Delivery Network for read-heavy data to reduce direct database access and deliver content faster.