Introduction

A single database server has finite storage, CPU, memory, connection, and write-processing capacity. Read replicas can distribute reads, but every replica still stores a copy of the complete dataset, and writes commonly continue through one primary write path.

Sharding addresses a different scaling problem by dividing one logical dataset into smaller subsets and placing those subsets on different database servers.

Logical dataset
      |
      +-- Shard A: subset of records
      +-- Shard B: subset of records
      +-- Shard C: subset of records
      +-- Shard D: subset of records

The complete logical dataset is formed from the records distributed across all shards.

Every sharded system must answer one routing question:

Given a record or request,
which shard owns the data?

The four common answers are:

• Place it according to a key range
• Place it according to a hash of the key
• Look up the owning shard in a directory
• Place it according to a geographic or regulatory boundary

Prerequisites

What Is Sharding?

Sharding is horizontal data partitioning. Rows or records from one logical dataset are divided across several physical databases or partitions.

Users table:

Users 1 to 1,000,000
      |
      +-- Shard 1
      +-- Shard 2
      +-- Shard 3
      +-- Shard 4

The application, database platform, middleware, or routing service uses a shard key to identify the shard responsible for a record.

Sharding vs Replication vs Partitioning

Sharding with Replication

Shard A
  +-- Leader
  +-- Replica 1
  +-- Replica 2


Shard B
  +-- Leader
  +-- Replica 1
  +-- Replica 2


Shard C
  +-- Leader
  +-- Replica 1
  +-- Replica 2

Sharding decides which shard owns the record. Replication decides how many copies of that shard's data exist and how those copies remain synchronized.

The Shard Key

The shard key is the value used to route a record or request to its owning shard.

Common shard-key candidates include:

• Tenant ID
• Account ID
• User ID
• Order ID
• Timestamp
• Country or region code
• A composite such as tenant ID and record ID

A strong shard key should support:

• Balanced storage distribution
• Balanced request distribution
• Efficient routing
• Common query patterns
• Data locality for related records
• Manageable rebalancing
• Required tenant and security boundaries

Hot Shards

A hot shard receives substantially more traffic or data growth than the other shards.

Shard A:

10% of requests


Shard B:

12% of requests


Shard C:

68% of requests


Shard D:

10% of requests

A hot shard can result from:

• A monotonically increasing key
• One unusually busy tenant
• A popular record or partition
• Uneven geographic traffic
• Poorly selected range boundaries
• A low-cardinality shard key
• Seasonal or event-driven demand

Range Sharding

Range sharding divides an ordered keyspace into contiguous intervals. Each interval is assigned to a shard.

Shard A:

Customer IDs 1 to 999,999


Shard B:

Customer IDs 1,000,000 to 1,999,999


Shard C:

Customer IDs 2,000,000 to 2,999,999

Range boundaries can be based on numeric IDs, timestamps, alphabetical keys, dates, prices, or another sortable value.

Range-routing Flow

Shard key = 1,450,210
      |
      v
Compare with configured boundaries
      |
      v
Matched range:

1,000,000 to 1,999,999
      |
      v
Route to Shard B

Range-sharding Advantages

• Simple conceptual routing
• Ordered records remain close together
• Range scans can target one or a few shards
• Time-based archival can align with shard boundaries
• Adjacent ranges can be split when they grow

Range-sharding Limitations

• Sequential writes can concentrate on the latest range
• Uneven value distribution creates uneven shard sizes
• Boundary changes can require data movement
• A popular range can become a traffic hotspot
• An initially balanced keyspace can become unbalanced over time

Time-range Sharding

Shard 1:

January to March


Shard 2:

April to June


Shard 3:

July to September


Shard 4:

October to December

Time-range sharding is useful when queries commonly request bounded time intervals and older data becomes less active.

However, new writes typically target the current time range, potentially creating one hot active shard while historical shards remain mostly idle.

Conceptual Range Router

<?php

declare(strict_types=1);

final class RangeShardRouter
{
    public function findShard(
        int $customerId
    ): string {
        return match (true) {
            $customerId < 1_000_000
                => 'shard-a',

            $customerId < 2_000_000
                => 'shard-b',

            $customerId < 3_000_000
                => 'shard-c',

            default
                => 'shard-d'
        };
    }
}

Production boundaries should be configuration-driven, versioned, validated, and updated through a controlled resharding process rather than hard-coded into every application instance.

Hash Sharding

Hash sharding applies a deterministic hash function to the shard key and maps the resulting value to a shard or hash range.

A simplified direct-modulo model is:

\[ ShardNumber = Hash(ShardKey) \bmod NumberOfShards \]

Tenant ID:

tenant-1042


Hash result:

8,315,729


Shard count:

4


Shard:

8,315,729 mod 4
=
Shard 1

Hash-routing Flow

Extract shard key
      |
      v
Apply deterministic hash
      |
      v
Map hash value to shard
      |
      v
Route request

Hash-sharding Advantages

• A suitable hash can distribute keys evenly
• Sequential IDs are spread across shards
• Point lookups are efficient when the shard key is available
• Natural key ordering is less likely to create one current-range hotspot

Hash-sharding Limitations

• Adjacent keys can be placed on unrelated shards
• Range queries can require scatter-gather execution
• Changing the shard count can move many records with simple modulo hashing
• A single hot key remains hot even when other keys are balanced
• Operations without the shard key can require a global lookup

Conceptual Hash Router

<?php

declare(strict_types=1);

final class HashShardRouter
{
    public function __construct(
        private int $shardCount
    ) {
        if ($this->shardCount < 1) {
            throw new InvalidArgumentException(
                'Shard count must be positive.'
            );
        }
    }

    public function findShard(
        string $tenantId
    ): int {
        $unsignedHash =
            (int)sprintf(
                '%u',
                crc32($tenantId)
            );

        return $unsignedHash
            % $this->shardCount;
    }
}

This is a teaching example. Production systems must use the selected platform's supported hashing and placement algorithm. Changing hash algorithms or shard counts without a migration plan can route existing keys incorrectly.

Modulo Hashing and Resharding

With direct modulo hashing, changing the shard count changes the result for many keys.

Before:

hash(key) mod 4


After adding a shard:

hash(key) mod 5


Result:

Many existing keys map
to different shard numbers.

Moving from four to five shards can therefore require substantial data movement.

Consistent Hashing

Consistent hashing maps hashes onto a logical ring. Shards own sections of that ring, often through several virtual positions.

Hash ring:

0 --------------------------- Maximum
     |       |       |       |
   Shard A Shard B Shard C Shard D


Key placement:

Hash the key
      |
      v
Locate its ring position
      |
      v
Route to the responsible shard

When a shard is added or removed, the design aims to move only the affected hash ranges rather than recalculating placement over the full keyspace.

Consistent hashing reduces remapping but does not remove the operational need to copy data, track ownership, preserve availability, and complete migration.

Directory Sharding

Directory sharding stores an explicit mapping between a shard key and the shard that owns it.

Shard directory:

tenant-101 -> shard-a
tenant-102 -> shard-c
tenant-103 -> shard-b
tenant-104 -> shard-a

Any tenant or key can be assigned to any compatible shard. The routing decision comes from the directory rather than a fixed range or hash formula.

Directory-routing Flow

Request contains tenant ID
      |
      v
Query or read cached directory entry
      |
      v
Resolve owning shard
      |
      v
Route operation to shard

Directory-sharding Advantages

• Flexible key-to-shard placement
• One hot or large tenant can be moved independently
• Shard assignment does not need to follow a mathematical formula
• Different tenants can receive different placement policies
• Incremental rebalancing can be performed key by key or group by group

Directory-sharding Limitations

• The directory becomes critical routing infrastructure
• A directory lookup or cache is required before routing
• Stale directory entries can send requests to the wrong shard
• Directory updates and data movement must be coordinated
• Directory availability and recovery require explicit design

Conceptual Shard Directory

CREATE TABLE shard_directory
(
    tenant_id BIGINT PRIMARY KEY,
    shard_id VARCHAR(50) NOT NULL,
    placement_version BIGINT NOT NULL,
    placement_status VARCHAR(30) NOT NULL,
    updated_at TIMESTAMP NOT NULL
);

A placement version helps routers recognize stale cached mappings. The migration status can indicate whether a tenant is stable, copying, validating, or being switched.

Directory Lookup Example

<?php

declare(strict_types=1);

final class DirectoryShardRouter
{
    public function __construct(
        private ShardDirectory $directory
    ) {
    }

    public function findShard(
        int $tenantId
    ): ShardPlacement {
        $placement =
            $this->directory->findByTenantId(
                $tenantId
            );

        if ($placement === null) {
            throw new RuntimeException(
                'No shard placement exists for the tenant.'
            );
        }

        if (!$placement->isActive()) {
            throw new RuntimeException(
                'The shard placement is not active.'
            );
        }

        return $placement;
    }
}

Geo Sharding

Geo sharding assigns data to shards according to a geographic, jurisdiction, residency, or regional ownership key.

Region key:

IN
    -> India shard group


EU
    -> European shard group


US
    -> United States shard group


APAC
    -> Asia-Pacific shard group

The routing key can be based on a trusted account region, tenant residency policy, business location, or another approved ownership attribute.

Geo-routing Flow

Request arrives
      |
      v
Authenticate caller
      |
      v
Resolve trusted tenant
or account region
      |
      v
Select regional shard group
      |
      v
Route operation

Geo-sharding Advantages

• Data can be placed closer to its primary users
• Regional requests can avoid unnecessary cross-region data access
• Placement can align with documented data-residency rules
• Regional failures can be isolated when the architecture supports it
• Regional teams or services can own local data paths

Geo-sharding Limitations

• Regional traffic and storage can be highly uneven
• Users or tenants can move between regions
• Global queries require cross-region fan-out or separate global projections
• Cross-region transactions are expensive and failure-sensitive
• Residency, backup, replication, and disaster-recovery rules require careful alignment
• A geographic attribute supplied by the public client cannot be trusted automatically

Geo Sharding vs Geospatial Querying

A system can use geo sharding for regional ownership and geospatial indexes inside each shard for location-based searches.

Complete Strategy Comparison

Point Queries

A point query retrieves a specific record using its shard key.

SELECT
    course_id,
    title,
    status
FROM courses
WHERE tenant_id = :tenant_id
  AND course_id = :course_id;

If tenant_id is the shard key, the router can identify one shard before executing the query.

Scatter-Gather Queries

A scatter-gather query sends work to several or all shards and combines their results.

Global query
      |
      +-- Query Shard A
      +-- Query Shard B
      +-- Query Shard C
      +-- Query Shard D
      |
      v
Merge, sort, aggregate,
or paginate results

Scatter-gather can increase:

• Query latency
• Coordinator CPU and memory
• Network traffic
• Failure probability
• Pagination complexity
• Load on otherwise unrelated shards

Global Sorting and Pagination

Pagination becomes harder when records are distributed among shards.

Shard A returns top results

Shard B returns top results

Shard C returns top results

Coordinator:

- Merges result streams
- Applies global ordering
- Selects page boundary
- Produces continuation state

Offset pagination can become inefficient and unstable across changing shards. Cursor-based pagination can carry per-shard continuation state or a global ordering key, depending on the data model.

Global Secondary Indexes

A query may not contain the shard key.

Primary placement:

tenant_id -> shard


Lookup request:

Find user by email address


Problem:

Email alone does not reveal
the owning tenant shard.

Possible design directions include:

• A global lookup index mapping email to tenant and shard
• A directory service
• A separate searchable projection
• A globally unique identifier containing routing information
• A controlled scatter-gather query for infrequent administration

A global index introduces its own consistency, availability, uniqueness, repair, and migration requirements.

Data Colocation

Records commonly accessed or updated together should be colocated where practical.

Tenant 17 shard:

- Tenant profile
- Users
- Courses
- Enrollments
- Progress
- Tenant configuration

Sharding related tables by the same tenant key can keep many transactions local to one shard.

Data belonging to several tenants or global reference data requires a separate strategy.

Cross-Shard Transactions

A cross-shard transaction reads or writes data owned by more than one shard.

Operation:

Move data from Tenant A
to Tenant B


Shard A:

Remove or update source


Shard B:

Create or update destination

Cross-shard transactions can require distributed coordination, increase latency, and expose partial-failure scenarios.

Possible approaches include:

• Redesign the ownership boundary to keep the operation local
• Use database-supported distributed transactions
• Use an idempotent workflow or saga
• Use an outbox and durable events
• Define compensating actions

Global Uniqueness

A unique constraint inside one shard does not automatically guarantee uniqueness across every shard.

Shard A accepts:

username = learner42


Shard B also accepts:

username = learner42


Each local unique index succeeds.

Possible directions include:

• Scope uniqueness by tenant or shard
• Use globally unique generated identifiers
• Maintain a strongly coordinated global registry
• Reserve namespaces or identifier ranges
• Detect and resolve duplicates according to business policy

Tenant-based Sharding

Multi-tenant applications commonly use tenant_id as the shard key.

Benefits

• Tenant data can remain colocated
• Tenant-scoped queries target one shard
• Tenant migration can be handled as an ownership move
• Isolation and placement policy can align with the tenant boundary

Risks

• A very large tenant can exceed one shard's capacity
• A busy tenant can create a hot shard
• Global reporting requires cross-shard aggregation
• Tenant moves require coordinated directory and data migration

Large-tenant Strategy

Small and medium tenants:

Shared tenant shards


Large tenant:

Dedicated shard


Extremely large tenant:

Sub-sharded by tenant_id
plus another high-cardinality key

Directory sharding is useful when selected tenants need dedicated placement without changing every other tenant's assignment.

Resharding

Resharding changes ownership of part of the dataset.

Source shard owns key range
      |
      v
Create target shard
      |
      v
Copy historical data
      |
      v
Capture new changes
      |
      v
Bring target current
      |
      v
Validate target
      |
      v
Switch routing
      |
      v
Drain old ownership
      |
      v
Remove old copy after retention

Exact procedures depend on the selected database. The design must define how writes are handled while data is moving.

Resharding Write Strategies

Shard Split vs Shard Move

Shard Routing and Security

Routing must not replace authorization.

Client sends:

tenantId = 17


Application must:

1. Authenticate caller
2. Determine authorized tenant context
3. Compare route tenant with trusted context
4. Resolve shard
5. Authorize requested resource
6. Execute query

A caller must not access another tenant's shard merely by changing a tenant identifier in the URL or request body.

Directory Security

The shard directory contains sensitive infrastructure routing information.

Protect it with:

• Least-privilege access
• Private network paths
• Authenticated and authorized updates
• Versioned placement records
• Audited ownership changes
• Encrypted communication and storage according to policy
• Backup and tested recovery

Shard Failure

A shard can become unavailable while other shards continue serving traffic.

Shard A:

Healthy


Shard B:

Unavailable


Shard C:

Healthy


Impact:

Only data owned by Shard B
is unavailable when isolation
and routing are working correctly.

Each shard should have an appropriate replication and failover strategy. Shard-level blast-radius isolation is useful only when shared routing, directory, identity, and network components remain available.

Caching Shard Mappings

Applications can cache range, token, directory, or geo-placement metadata to avoid a central lookup for every request.

Request
   |
   v
Check local mapping cache
   |
   +-- Fresh mapping:
   |      route request
   |
   +-- Missing or stale:
          fetch current mapping
          update cache
          route request

The router needs a way to detect stale mappings after a tenant or key range moves.

Possible controls include:

• Placement versions
• Bounded cache lifetime
• Invalidation notifications
• Wrong-owner responses containing safe redirect metadata
• A lookup retry after routing failure

Choosing a Sharding Strategy

Choose Range Sharding When

• Range scans are a primary access pattern
• Ordered locality is valuable
• Boundaries can be split and rebalanced safely
• Monotonic-key hotspots can be controlled

Choose Hash Sharding When

• Point lookups dominate
• Broad key distribution is important
• Natural ordering is not needed for most queries
• Scatter-gather range queries remain uncommon

Choose Directory Sharding When

• Placement must be controlled independently for each tenant or key
• Large tenants need dedicated shards
• Selective movement is more important than formula-based routing
• A highly available placement directory can be operated reliably

Choose Geo Sharding When

• Regional latency is a primary requirement
• Data ownership naturally follows a geographic boundary
• Documented residency constraints influence placement
• Cross-region queries and transactions are limited or explicitly designed

Hybrid Sharding

Production systems frequently combine strategies.

Step 1:

Geo shard by residency region


Step 2:

Directory-map tenant to
a shard group in that region


Step 3:

Hash-distribute tenant records
inside the assigned shard group


Step 4:

Range-partition historical events
inside each physical shard

Hybrid designs can solve several placement requirements but increase routing, migration, monitoring, and operational complexity.

Learning-platform Examples

Conceptual Sharding Policy

sharding:
  primaryKey:
    type: trusted-tenant-id

  placement:
    strategy: directory

  directory:
    cacheEnabled: true
    placementVersionRequired: true
    unavailablePolicy: controlled-failure

  tenantPolicies:
    default:
      placement: shared-shard-group

    largeTenant:
      placement: dedicated-shard

    regulatedTenant:
      placement: approved-regional-shard-group

  migration:
    copyHistoricalData: true
    captureConcurrentChanges: true
    validateBeforeCutover: true
    rollbackPlanRequired: true

  queries:
    crossShard:
      restricted: true

    globalAnalytics:
      target: analytical-projection

  observability:
    shardSize: enabled
    shardTraffic: enabled
    routingFailures: enabled
    hotKeyDetection: enabled
    migrationProgress: enabled

This is conceptual configuration. Product-specific routing, resharding, replication, transaction, and failover guarantees must be verified against the selected database.

Observability

Useful sharding metrics include:

• Storage size by shard
• Read and write rate by shard
• CPU, memory, and I/O by shard
• Connection usage by shard
• Request latency by shard
• Rows or documents by shard
• Hot-key and hot-tenant traffic
• Scatter-gather query count
• Cross-shard transaction count
• Shard-directory lookup latency
• Routing-cache misses
• Wrong-owner or stale-mapping responses
• Resharding copy and catch-up progress
• Shard replication lag
• Regional traffic and storage distribution

Structured Routing Event

{
  "shardKeyType": "tenant",
  "shardId": "regional-shard-group-3",
  "placementVersion": 42,
  "routingStrategy": "directory",
  "operationType": "read",
  "result": "routed"
}

Avoid writing confidential tenant data, credentials, database connection details, or raw routing secrets to routine logs.

Alert Conditions

Alert when:

• One shard receives disproportionate traffic
• Shard storage distribution becomes significantly uneven
• A shard approaches its capacity boundary
• Scatter-gather query volume increases unexpectedly
• Cross-shard transaction failures increase
• The shard directory becomes unavailable
• Routing-cache entries repeatedly become stale
• Requests are routed to the wrong owner
• Resharding stops or falls behind incoming changes
• One regional shard group approaches saturation
• Shard replication falls below its safe requirement
• A tenant exceeds the safe capacity of its assigned shard

Troubleshooting Workflow

1. Identify the sharding strategy and shard key.
2. Extract the trusted shard-key value from the failing request.
3. Resolve the expected owning shard.
4. Compare the router's placement version with the current metadata.
5. Check the target shard's health and replication state.
6. Check CPU, memory, storage, connections, and I/O by shard.
7. Check for hot tenants, keys, ranges, or regions.
8. Check whether the query contains the shard key.
9. Check for accidental scatter-gather execution.
10. Check global index or directory consistency.
11. Check active data migrations and dual-write behaviour.
12. Check cross-shard transaction and retry state.
13. Check recent boundary, token, directory, or regional placement changes.
14. Rebalance or reshard through the database's approved procedure.

Common Sharding Mistakes

Recommended Test Cases

Sharding Best Practices

Practice Exercise

Design a sharding strategy for your online learning platform.

Requirements

1. Identify the database capacity limit that requires sharding.
2. List the most frequent point, range, and global queries.
3. Identify the most frequent transactions.
4. Evaluate tenant ID as a shard key.
5. Model small, medium, and very large tenants.
6. Compare range, hash, directory, and geo placement.
7. Keep enrollment and progress transactions local where practical.
8. Design a global course-search projection.
9. Define how email-to-account lookup finds the correct shard.
10. Define global uniqueness requirements.
11. Replicate each shard for availability.
12. Design a tenant-move process with rollback.
13. Define behaviour during stale directory routing.
14. Test one shard failure.
15. Monitor data, traffic, latency, and hot keys by shard.

Sharding-design Template

Frequently Asked Questions