Database Sharding Strategies

Scale databases horizontally by distributing data across multiple shards. Covers sharding strategies, shard key selection, cross-shard queries, rebalancing, and the operational complexity that comes with sharded systems.

Database sharding strategies are the techniques used to distribute a large, high-traffic database across multiple physical servers, enabling scalability beyond a single machine's capacity. They matter when a database’s single-node performance, storage, or concurrency limits become bottlenecks—when queries take tens of milliseconds on a single instance, writes stall under peak load, or storage grows beyond a single disk array.

Choosing a Shard Key

The shard key determines how rows are mapped to shards. It must balance selectivity, distribution, and query patterns.

Shard key selection affects query routing and join performance. For example:

-- Shard by tenant_id; all tenant-specific data lives in a shard
CREATE TABLE users (
    tenant_id UUID NOT NULL,
    user_id UUID NOT NULL,
    name TEXT,
    created_at TIMESTAMPTZ,
    PRIMARY KEY (tenant_id, user_id)
);

A poorly chosen shard key leads to hot shards—one or few shards handling the majority of reads/writes. A tenant_id that maps 10,000 tenants across 100 shards but where 80% of traffic comes from 5 tenants results in 5 hot shards and 95 cold ones.

Shard key choice also affects data skew. A common failure mode: a key like user_id where 20% of users generate 80% of all transactions, causing uneven load across shards. Use dynamic rebalancing or range-based sharding to mitigate.

Implementing Range-Based Sharding

In range-based sharding, shards are assigned ranges of the shard key. This works well when the shard key is ordered (e.g., user_id as UUID or created_at as timestamp).

Shards are typically defined as contiguous intervals:

ShardRange (tenant_id)
0[00000000-0000-0000-0000-000000000000, 33333333-3333-3333-3333-333333333333)
1[33333333-3333-3333-3333-333333333333, 66666666-6666-6666-6666-666666666666)
2[66666666-6666-6666-6666-666666666666, ∞)

A query for tenant_id = 'a7b8c9d0-e1f2-3456-7890-a1b2c3d4e5f6' is routed to shard 1.

This strategy supports efficient range queries:

-- Efficient across multiple shards
SELECT * FROM users
WHERE tenant_id BETWEEN 'a0000000-0000-0000-0000-000000000000' AND 'b0000000-0000-0000-0000-000000000000'
  AND created_at > '2023-01-01';

But it is vulnerable to data skew and hot regions. A new tenant with 1 million users can dominate a shard, and a new product launch might generate 1 million records in 1 hour, all in a single range.

Rebalancing requires splitting and merging shards:

A shard split is asynchronous and non-blocking. During split:

Failure modes:

Implementing Hash-Based Sharding

Hash-based sharding uses a hash function to map shard keys to shards. It is ideal for even data distribution and high cardinality keys.

Use a consistent hash function with a ring of shards:

Example:

-- Hash tenant_id using murmur3, then assign to 128 shards
SELECT 
    tenant_id,
    hash(tenant_id) % 128 AS shard_id
FROM users;

A common implementation uses a shard map stored in a central metadata table:

-- Metadata table: shard_map
CREATE TABLE shard_map (
    shard_id INT PRIMARY KEY,
    shard_key_start UUID,
    shard_key_end UUID,
    host TEXT,
    port INT,
    status TEXT CHECK (status IN ('active', 'draining', 'inactive'))
);

When a new tenant is created, the system computes the shard ID and writes to the appropriate host.

Key advantages:

But hash-based sharding introduces data movement during scaling and complexity in routing.

Failure modes:

Cross-Shard Queries and Joins

Single-shard queries are efficient, but cross-shard queries require coordination.

A distributed query must:

  1. Route queries to relevant shards.
  2. Combine results from all shards.
  3. Apply final filtering and sorting.

Example:

-- Distributed query: all orders for tenants created in 2023
SELECT o.*
FROM orders o
JOIN users u ON o.user_id = u.user_id
WHERE u.created_at >= '2023-01-01'
  AND u.created_at < '2024-01-01';

The coordinator (application or database proxy) must:

This process is asynchronous and subject to partial failures.

Common failure modes:

To mitigate, use query pushdown and materialized summaries.

Sharding and Replication

Each shard is typically replicated for high availability and read scalability.

Each shard runs a primary-replica pair:

Replication lag is measured in seconds or milliseconds. A read from a replica may return stale data.

Example:

-- Query: get user by ID
SELECT * FROM users
WHERE tenant_id = 'a1b2c3d4-e5f6-7890-a1b2-c3d4e5f6a1b2'
  AND user_id = '12345678-90ab-cdef-1234-567890abcdef';

Failover and promotion:

Failure modes:

Rebalancing and Scaling

Rebalancing is the process of redistributing data across shards to maintain balance.

Trigger events:

Rebalancing steps:

  1. Identify candidate shards for splitting (e.g., size > 100 GB).
  2. Create new shards (e.g., split shard 5 into 5a and 5b).
  3. Migrate data from old shard to new ones.
  4. Update shard map.
  5. Update routing configuration (e.g., in a config file or via API).
  6. Update application-level caches.

Example:


shards:
  - id: 0
    key_range: [null, "33333333-3333-3333-3333-333333333333")
    host: db-shard-01.example.com
    port: 5432
    status: active
  - id: 1
    key_range: ["33333333-3333-3333-3333-333333333333", "66666666-6666-6666-6666-666666666666")
    host: db-shard-02.example.com
    port: 5432
    status: active
  - id: 2
    key_range: ["66666666-6666-6666-6666-666666666666", null)
    host: db-shard-03.example.com
    port: 5432
    status: active

After a rebalance, the application must:

Failures:

Consistency Models and Transaction Boundaries

Sharded databases support eventual consistency across shards.

For strong consistency, use distributed transactions.

A two-phase commit (2PC) across shards:

  1. Coordinator sends PREPARE to all participating shards.
  2. Each shard writes transaction log and replies PREPARE_OK.
  3. Coordinator sends COMMIT to all shards.
  4. Shards commit and reply COMMIT_OK.

Example:

-- Transfer $100 from account A to account B
BEGIN
  -- Shard 1: A
  UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A';
  -- Shard 2: B
  UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B';
  -- Coordinator: commit all
  COMMIT;

But 2PC is blocking and vulnerable to failure.

Failure modes:

To improve performance, use compensating transactions and eventual consistency.

Use idempotent operations in distributed updates:

-- Idempotent transfer
INSERT INTO transfers (transfer_id, from_account, to_account, amount, status)
VALUES ('t12345', 'A', 'B', 100, 'pending')
ON CONFLICT (transfer_id) DO UPDATE SET status = 'pending';

This ensures that retries and partial failures do not double-transfer funds.

Failure Modes and Observability

Key failure modes:

Observability:

Failure detection:

A critical error in production:

ERROR 500: Shard 3 unavailable during query execution
  - Primary: db-shard-03.example.com:5432
  - Last heartbeat: 2023-10-05T12:34:56Z
  - Last write: 2023-10-05T12:34:50Z
  - Replication lag: 12.4s
  - Query: SELECT * FROM users WHERE tenant_id = 'a1b2c3d4-e5f6-7890-a1b2-c3d4e5f6a1b2'
  - Result: 503 Service Unavailable

This error indicates a failed shard, replication lag, and routing issue.

Summary of Key Design Choices

Each decision affects performance, availability, and operational complexity. The right strategy is not one-size-fits-all, but a deliberate alignment of data, query patterns, and infrastructure.

This page was rewritten on 17 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.