Sharding Pattern
- thierry sinassamy
Problem
Issue related to storage (finite amount of disk storage, …).
Single server hosting the data store might not be able to provide the necessary computing power to support the load.
Performance is governed by the rate the server can receive requests and send replies (Network bandwidth).
If users are located in different regions, it might not be possible to store the entire data in one data store.
Solution
Three strategies are commonly used when selecting the shard key and deciding how to distribute data across shards.
The Lookup strategy. In this strategy the sharding logic implements a map that routes a request for data to the shard that contains that data using the shard key.
The Range strategy. This strategy groups related items together in the same shard, and orders them by shard key—the shard keys are sequential.
The Hash strategy. The purpose of this strategy is to reduce the chance of hotspots (shards that receive a disproportionate amount of load).
Sharding in Practice
Example of lookup shard map
SELECT ShardKey, DatabaseServer
FROM BookDataShardMapExample of code - single shard access
...
// All data for this book is stored in a shard based on the book's ISBN check digit,
// which is converted to an integer 0 - 10 (special value 'X' becomes 10).
int isbnCheckDigit = book.Isbn.CheckDigitAsInt;
// Establish a pooled connection to the database shard for this specific book.
using (SqlConnection sqlConn = await shardedDatabaseConnections.OpenShardConnectionForKeyAsync(key: isbnCheckDigit, cancellationToken))
{
// Update the book's Library of Congress catalog information
SqlCommand cmd = sqlConn.CreateCommand();
cmd.CommandText = @"UPDATE LibraryOfCongressCatalog
SET ControlNumber = @lccn,
...
Classification = @lcc
WHERE BookID = @bookId";
cmd.Parameters.AddWithValue("@lccn", book.LibraryOfCongress.Lccn);
...
cmd.Parameters.AddWithValue("@lcc", book.LibraryOfCongress.Lcc);
cmd.Parameters.AddWithValue("@bookId", book.Id);
await cmd.ExecuteNonQueryAsync(cancellationToken);
}
...