📘 Database System Design

Shard Key — Database Scaling

A Shard Key is the column or combination of columns used to decide which shard should store a particular row.

S

Site Administrator

📅 Sep 26, 2026 🕒 7 min read 👁 52 views
☆ Save 𝕏 in f ✆
Shard Key — Database Scaling

A Shard Key is the column or combination of columns used to decide which shard should store a particular row.

Choosing the right shard key is one of the most important decisions when designing a sharded database.

Simple Example

Suppose we have an Orders table and choose CustomerId as the shard key.

flowchart LR
    A[CustomerId] --> B{Shard Router}

    B -->|Customer 1-1M| C[Shard 1]
    B -->|Customer 1M-2M| D[Shard 2]
    B -->|Customer 2M-3M| E[Shard 3]

For example:

CustomerId = 500000
        ↓
Shard 1

CustomerId = 1500000
        ↓
Shard 2

CustomerId = 2500000
        ↓
Shard 3

What Makes a Good Shard Key?

A good shard key should ideally provide:

  • Even distribution of data.
  • Even distribution of traffic.
  • Low chance of creating a hot shard.
  • Frequent use in application queries.
  • Stable values that do not frequently change.
  • Good support for the application's access pattern.

Bad Shard Key Example

Suppose we shard orders by Country.

India      → 70% of data
USA        → 15%
UK         → 5%
Canada     → 3%
Others     → 7%

Shard 1 may receive most of the traffic while other shards remain underutilized.

flowchart LR
    A[Country Shard Key] --> B[India
70%]
    A --> C[USA
15%]
    A --> D[UK
5%]
    A --> E[Others
10%]

This can create a hot shard.

Good Shard Key Example

Suppose CustomerId provides a relatively even distribution.

flowchart LR
    A[CustomerId] --> B[Shard 1
~33%]
    A --> C[Shard 2
~33%]
    A --> D[Shard 3
~34%]

The workload is much easier to distribute.

SQL Server Complete Example

1. Create Shard Databases

CREATE DATABASE OrdersShard1;
GO

CREATE DATABASE OrdersShard2;
GO

CREATE DATABASE OrdersShard3;
GO

2. Create the Same Table in Each Shard

USE OrdersShard1;
GO

CREATE TABLE Orders
(
    OrderId BIGINT PRIMARY KEY,
    CustomerId BIGINT NOT NULL,
    Amount DECIMAL(18,2) NOT NULL,
    OrderDate DATE NOT NULL,
    Status VARCHAR(30) NOT NULL
);
GO

USE OrdersShard2;
GO

CREATE TABLE Orders
(
    OrderId BIGINT PRIMARY KEY,
    CustomerId BIGINT NOT NULL,
    Amount DECIMAL(18,2) NOT NULL,
    OrderDate DATE NOT NULL,
    Status VARCHAR(30) NOT NULL
);
GO

USE OrdersShard3;
GO

CREATE TABLE Orders
(
    OrderId BIGINT PRIMARY KEY,
    CustomerId BIGINT NOT NULL,
    Amount DECIMAL(18,2) NOT NULL,
    OrderDate DATE NOT NULL,
    Status VARCHAR(30) NOT NULL
);
GO

3. Insert Data Based on CustomerId

USE OrdersShard1;
GO

INSERT INTO Orders
    (OrderId, CustomerId, Amount, OrderDate, Status)
VALUES
    (101, 500000, 1000.00, '2026-09-01', 'Completed'),
    (102, 750000, 1500.00, '2026-09-02', 'Pending');
GO

USE OrdersShard2;
GO

INSERT INTO Orders
    (OrderId, CustomerId, Amount, OrderDate, Status)
VALUES
    (201, 1500000, 2500.00, '2026-09-03', 'Completed'),
    (202, 1750000, 3000.00, '2026-09-04', 'Pending');
GO

USE OrdersShard3;
GO

INSERT INTO Orders
    (OrderId, CustomerId, Amount, OrderDate, Status)
VALUES
    (301, 2500000, 4000.00, '2026-09-05', 'Completed'),
    (302, 2750000, 5000.00, '2026-09-06', 'Pending');
GO

Shard Mapping

For this example:

CustomerId 1 - 1,000,000       → Shard 1
CustomerId 1,000,001 - 2,000,000 → Shard 2
CustomerId 2,000,001 - 3,000,000 → Shard 3

Verify the Data

USE OrdersShard1;
GO

SELECT * FROM Orders;

USE OrdersShard2;
GO

SELECT * FROM Orders;

USE OrdersShard3;
GO

SELECT * FROM Orders;

Expected output:

Shard 1

OrderId  CustomerId  Amount   OrderDate    Status
-------  ----------  -------  ----------   ----------
101      500000      1000.00  2026-09-01   Completed
102      750000      1500.00  2026-09-02   Pending


Shard 2

OrderId  CustomerId  Amount   OrderDate    Status
-------  ----------  -------  ----------   ----------
201      1500000     2500.00  2026-09-03   Completed
202      1750000     3000.00  2026-09-04   Pending


Shard 3

OrderId  CustomerId  Amount   OrderDate    Status
-------  ----------  -------  ----------   ----------
301      2500000     4000.00  2026-09-05   Completed
302      2750000     5000.00  2026-09-06   Pending

ASP.NET Core Web API — Shard Routing

Now the important system-design part: how does the Web API select the correct database?

sequenceDiagram
    participant Client
    participant API as ASP.NET Core Web API
    participant Router as Shard Router
    participant DB as SQL Server Shard

    Client->>API: GET /api/customers/1500000/orders
    API->>Router: GetShard(1500000)
    Router-->>API: Shard 2
    API->>DB: Query Orders
    DB-->>API: Order Data
    API-->>Client: JSON Response

Shard Router

public interface IShardRouter
{
    string GetConnectionString(long customerId);
}

public class ShardRouter : IShardRouter
{
    private readonly IConfiguration _configuration;

    public ShardRouter(IConfiguration configuration)
    {
        _configuration = configuration;
    }

    public string GetConnectionString(long customerId)
    {
        return customerId switch
        {
            <= 1_000_000 =>
                _configuration.GetConnectionString("Shard1")!,

            <= 2_000_000 =>
                _configuration.GetConnectionString("Shard2")!,

            _ =>
                _configuration.GetConnectionString("Shard3")!
        };
    }
}

Controller

[ApiController]
[Route("api/customers")]
public class OrdersController : ControllerBase
{
    private readonly IShardRouter _shardRouter;

    public OrdersController(IShardRouter shardRouter)
    {
        _shardRouter = shardRouter;
    }

    [HttpGet("{customerId:long}/orders")]
    public async Task GetOrders(long customerId)
    {
        var connectionString =
            _shardRouter.GetConnectionString(customerId);

        await using var connection =
            new SqlConnection(connectionString);

        await connection.OpenAsync();

        const string sql = """
            SELECT OrderId,
                   CustomerId,
                   Amount,
                   OrderDate,
                   Status
            FROM Orders
            WHERE CustomerId = @CustomerId
            ORDER BY OrderDate;
            """;

        await using var command =
            new SqlCommand(sql, connection);

        command.Parameters.Add(
            "@CustomerId",
            SqlDbType.BigInt).Value = customerId;

        var result = new List<object>();

        await using var reader =
            await command.ExecuteReaderAsync();

        while (await reader.ReadAsync())
        {
            result.Add(new
            {
                OrderId = reader.GetInt64(0),
                CustomerId = reader.GetInt64(1),
                Amount = reader.GetDecimal(2),
                OrderDate = reader.GetDateTime(3),
                Status = reader.GetString(4)
            });
        }

        return Ok(result);
    }
}

Register the Router

builder.Services.AddScoped<IShardRouter, ShardRouter>();

builder.Services.AddControllers();

var app = builder.Build();

app.MapControllers();

app.Run();

Configuration

{
  "ConnectionStrings": {
    "Shard1": "Server=sql01;Database=OrdersShard1;...",
    "Shard2": "Server=sql02;Database=OrdersShard2;...",
    "Shard3": "Server=sql03;Database=OrdersShard3;..."
  }
}

API Request Example

GET /api/customers/1500000/orders

The routing process is:

1500000
   ↓
Shard Router
   ↓
Shard 2
   ↓
OrdersShard2
   ↓
SELECT ... WHERE CustomerId = 1500000
   ↓
JSON Response

Expected response:

[
  {
    "orderId": 201,
    "customerId": 1500000,
    "amount": 2500.00,
    "orderDate": "2026-09-03",
    "status": "Completed"
  }
]

Range-Based vs Hash-Based Shard Key

Range-Based Sharding

CustomerId 1-1M       → Shard 1
CustomerId 1M-2M      → Shard 2
CustomerId 2M-3M      → Shard 3

Simple to understand, but ranges can become uneven as the data grows.

Hash-Based Sharding

Instead of fixed ranges, calculate a hash:

Shard = Hash(CustomerId) % NumberOfShards

For example:

Hash(1001) % 3 → Shard 1
Hash(2002) % 3 → Shard 2
Hash(3003) % 3 → Shard 3
flowchart LR
    A[CustomerId] --> B[Hash Function]
    B --> C{Modulo 3}

    C --> D[Shard 1]
    C --> E[Shard 2]
    C --> F[Shard 3]

Hash-based distribution can provide better balancing, but changing the number of shards can cause many keys to move. Consistent hashing is one technique used to reduce that movement.

Hot Shard

A hot shard occurs when one shard receives significantly more traffic or data than others.

flowchart LR
    A[Shard Key] --> B[Shard 1
10% Traffic]
    A --> C[Shard 2
80% Traffic]
    A --> D[Shard 3
10% Traffic]

    C --> E[HOT SHARD]

A good shard key attempts to distribute both data and workload evenly.

Changing a Shard Key

Changing a shard key after the system contains billions of rows can be expensive because existing data may need to be redistributed.

flowchart LR
    A[Old Shard Key] --> B[Existing Data]
    B --> C[Redistribution]
    C --> D[New Shard Layout]

Therefore, shard-key selection should be considered carefully during system design.

Can We Query Without the Shard Key?

Suppose the shard key is CustomerId, but the query is:

SELECT *
FROM Orders
WHERE Status = 'Pending';

The system may not know which shard contains the required rows.

It may need to query multiple shards:

flowchart TD
    A[Query Status = Pending] --> B[Shard Router]

    B --> C[Shard 1]
    B --> D[Shard 2]
    B --> E[Shard 3]

    C --> F[Results]
    D --> F
    E --> F

This is called a scatter-gather query and can be expensive.

Interview Scenario

Interviewer: What is the most important consideration when selecting a shard key?

Answer: I would look at both data distribution and access patterns. The shard key should distribute data and traffic reasonably evenly, avoid hot shards, and preferably be present in the application's common queries so the system can route a request directly to one shard. I would also consider how difficult it would be to rebalance the system later.

Common Interview Questions

  • What is a shard key?
  • How do you choose a shard key?
  • What makes a good shard key?
  • What is a hot shard?
  • Range-based vs hash-based sharding?
  • What happens if a query does not contain the shard key?
  • What is scatter-gather?
  • Can a shard key be changed?
  • How does ASP.NET Core route a request to the correct database?
  • How would you prevent uneven shard distribution?

Key Takeaways

  • Shard key determines where data is stored.
  • A good key should distribute data and traffic evenly.
  • It should match common application access patterns.
  • CustomerId is a common example for customer-centric applications.
  • A poor key can create a hot shard.
  • Queries without the shard key may require scatter-gather operations.
  • Changing the shard key later can be expensive.
  • ASP.NET Core can use a shard-router component to select the SQL Server connection.

Conclusion

In a system-design interview, remember:

Choose Shard Key
      ↓
Distribute Data
      ↓
Distribute Traffic
      ↓
Route Request
      ↓
Correct Shard
      ↓
Query Database

The shard key is the foundation of a successful sharding strategy.

← Previous Sharding — Database Scaling Next → Consistent Hashing — Database Scaling

Comments & Ratings (0)

Log in or create a student account to comment and rate this article.

Be the first to comment on this article.

Never miss a new post 📬

Get an email whenever a new article, project, cheatsheet or video drops.

✅

ℹ️