Database Sharding

Definition of Database Sharding

CREATE SCHEMA Shard1;
GO

USE Shard1;
GO

CREATE TABLE Customers(
    CustomerId INT PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL
);
GO

Importance of Understanding Database Sharding

• Increased search performance and capability
• Reduced impact on a single system, enhancing its reliability
• Ability to scale out the database layer horizontally

Understanding Database Sharding Architecture

Essential Components of Database Sharding Architecture

Shard Key: CustomerId,
Shard Map
{
    Shard1:[0-1000],
    Shard2:[1000-2000]
}

Process and Workflow of Database Sharding Architecture

1. Data Partition: Firstly, data must be partitioned into several shards using a shard key – a specific column of data in the database table.
2. Data Distribution: Now, the shards are distributed across multiple servers for load balancing and improved performance.
3. Data Access: When a query is executed, the shard map identifies the right shard and returns the requested data.
4. Data Modification: This is just simple updates or changes in data. The event happens within a shard based on the shard key.

SELECT * FROM Customers WHERE CustomerId >= 1000 AND CustomerId <= 2000

Database Sharding vs Partitioning

Comparing Database Sharding with Partitioning

CREATE TABLE Customers (
    CustomerId INT,
    Name NVARCHAR (100)
)
PARTITION BY RANGE (CustomerId)
( PARTITION lessThanOneThousand VALUES LESS THAN (1000),
  PARTITION lessThanTwoThousand VALUES LESS THAN (2000),
  PARTITION others VALUES LESS THAN (MAXVALUE)
);

Criteria: customerId
Shard Map
{
    Shard1:[0-999],
    Shard2:[1000-1999],
    Shard3:[2000-2999]
}

Differences in Usage: Sharding vs Partitioning

Advantages of Database Sharding

Performance benefits of Database Sharding

SELECT * FROM Customers WHERE CustomerId = 1000;

• Disperses Load: By storing data in several places, Database Sharding spreads the load among many servers. This setup leads to less strain on each individual server and thereby improves the overall performance.
• Boosts Query Speed: With fewer records to go through, a database query can sift through records at a faster rate, reducing response times.
• Fosters Parallel Processing: With data distributed across multiple servers, Database Sharding harnesses the power of concurrent server computation. This essentially means that multiple queries can be processed simultaneously – leading to drastic improvements in performance.

Scalability as an Advantage of Sharding

Criteria: customerId
Shard Map
{
    Shard1:[0-999],
    Shard2:[1000-1999],
    Shard3:[2000-2999]
}

• Infinite Scale-Out Potential: By distributing data among many servers (or shards), more servers can be added as the need arises. This dispersal mechanism allows for theoretically endless 'scale-out' potential.
• Resource Optimisation: Sharding helps to maximise the use of current server resources. By spreading the data load, it effectively prevents any one server from becoming a bottleneck.
• High Availability: Because data is spread across multiple servers, if one server goes down, the application can still operate by retrieving data from other shards.

Practical Examples and Strategies of Database Sharding

Database Sharding Implementation Examples

SELECT * FROM Orders WHERE CustomerID = 4567

Effective Database Sharding Strategies

• Shard Key Selection: The Shard Key is the core around which your sharding is built. It determines how your data is distributed across shards. It's crucial to choose a shard key that avoids 'hotspots', where a lot of data gets concentrated in one shard, creating imbalanced loads.
• Data Discovery: Establishing a method for quickly locating the shard where the required data resides is also important. This is usually achieved by creating a shard map matching shard keys to particular shards. It's essential to keep this map updated and accessible.
• Choosing the Right Sharding Pattern: Different sharding patterns exist and each has its nuances. Patterns involve range sharding, list sharding, and hash sharding. Choose a pattern fitting your data distribution and access patterns.
• Consider Over-Sharding: Over-sharding implies creating more shards than currently needed. This can be a profitable strategy as it saves time and resources you would need if you go to shard again when your data grows.