Database Sharding: When, Why, and How

A comprehensive guide to horizontal database sharding. Covers sharding keys, strategies (hash, range, geo), cross-shard queries, rebalancing, and when NOT to shard.

Database sharding is the practice of horizontally partitioning a large database across multiple physical instances to improve scalability, performance, and availability. It matters when a single database instance becomes a bottleneck in read, write, or storage capacity, and when a single point of failure or a scaling limit is imminent.

When to Shard

Sharding becomes necessary when a database’s workload exceeds the capacity of a single node. The decision is triggered by one or more of the following signals:

Sharding is not required when a single database instance handles the workload with room to spare. It is also unnecessary when the data size fits comfortably within memory and storage of a single node, and when replication and read scaling are sufficient.

Why Shard

The primary motivations for sharding are:

The most common failure mode when sharding is overlooked: data becomes too large for a single node, but the application continues to write to a single cluster-wide table, leading to hotspots, lock contention, and eventual performance collapse.

How to Shard: Core Principles

Sharding involves three interdependent components: shard key selection, data distribution, and routing.

Select the Shard Key

The shard key determines how rows are distributed across shards. It must be:

Common choices:

A poorly chosen shard key leads to hot shards—shards that receive disproportionate write or read load. For example, using created_at as the shard key on a time-series database results in the most recent shard being heavily written, while older shards remain underutilized.

The shard key must be part of the primary key or indexed.

Distribute Data Across Shards

Each shard is a self-contained database instance. Data is partitioned such that all rows with a given shard key value reside on the same shard.

For example, with user_id as the shard key:

Data is distributed using range-based, hash-based, or list-based sharding.

  CREATE TABLE users (
    user_id BIGINT PRIMARY KEY,
    name TEXT,
    created_at TIMESTAMPTZ
  ) PARTITION BY RANGE (user_id);

Each partition is stored on a different shard.

  SELECT user_id, hash(user_id) % 16 AS shard_id FROM users;

This spreads data more uniformly but complicates range queries.

  {
    "shard_0": [1, 2, 3, 100, 101],
    "shard_1": [4, 5, 6, 102, 103],
    "shard_2": [7, 8, 9, 104, 105]
  }

Route Queries and Transactions

Once data is sharded, queries must be routed to the correct shard. This requires a shard router or proxy layer.

The router receives incoming SQL queries and determines the target shard based on the shard key in the query.

For example:

SELECT * FROM users WHERE user_id = 1234;

The router computes:

shard_id = hash(1234) % 16

Then forwards the query to the database instance responsible for shard shard_id.

For cross-shard queries, the router must gather results from multiple shards.

A common pattern uses application-level routing:

def get_user(user_id):
    shard_id = hash(user_id) % 16
    conn = get_db_connection(f"db://shard-{shard_id}")
    return conn.execute("SELECT * FROM users WHERE user_id = ?", user_id)

For distributed transactions, a two-phase commit (2PC) protocol is used across shards.

Handle Shard Discovery and Metadata Management

Shard metadata must be maintained and accessible. The router maintains a shard map that maps shard keys to shard locations.

This map is typically stored in a central metadata store—often a lightweight database or a configuration service.

A sample shard map table:

CREATE TABLE shard_map (
    shard_key_type TEXT,
    shard_key_min BIGINT,
    shard_key_max BIGINT,
    shard_name TEXT,
    shard_host TEXT,
    shard_port INT,
    shard_status TEXT
);

Example entries:

shard_key_typeshard_key_minshard_key_maxshard_nameshard_hostshard_port
user_id0999shard_0db-shard0.example.com5432
user_id10001999shard_1db-shard1.example.com5432

The router queries this map on startup and caches it.

Ensure Data Consistency and Balance

As data grows, shards become unevenly loaded. Rebalancing is the process of redistributing data across shards to maintain balance.

Rebalancing is triggered by:

Rebalancing involves:

A common failure: during rebalancing, a query returns data from two different versions of the same table—a phenomenon known as split-brain sharding.

To avoid this, use dual-write and versioned metadata:

Handle Cross-Shard Joins and Aggregations

Joins across shards require careful planning.

For a JOIN users u JOIN orders o ON u.user_id = o.user_id, the router must:

  1. Determine the shard for each user_id in users.
  2. Route the orders query to the corresponding shard for each user_id.
  3. Gather results and merge them.

This is often implemented as a coordinator query:

-- Coordinator query (executed on the router)
SELECT u.user_id, u.name, o.order_id, o.total
FROM (
  SELECT user_id FROM users WHERE user_id IN (1, 2, 3, 4, 5)
) AS u
JOIN (
  SELECT user_id, order_id, total
  FROM orders
  WHERE user_id IN (1, 2, 3, 4, 5)
) AS o ON u.user_id = o.user_id;

For large aggregations, map-reduce-style processing is used:

Manage Failures and Recovery

Shard failure is a common event. A shard may be unreachable, slow, or unavailable.

The system must handle:

Recovery involves:

A common error: a query returns stale data after a shard failover, because the metadata map was not updated in time.

Monitor and Tune Shard Performance

Key metrics to monitor:

Alerts should trigger on:

Example: Deploying a Sharded User Service

A service manages users with user_id as the shard key, using 16 shards.

Step 1: Setup shards

Step 2: Configure router

Step 3: Write data

INSERT INTO users (user_id, name, email) VALUES (1234, 'Alice', '[email protected]');

The router computes:

shard_id = hash(1234) % 16  # → 10

Then forwards the query to db-shard10.example.com.

Step 4: Query user

SELECT * FROM users WHERE user_id = 1234;

Router computes shard_id = 10, sends query to db-shard10, returns result.

Step 5: Rebalance

Step 6: Handle failover

Common Pitfalls and Sharp Edges

Summary

Sharding is not a single operation but a system of decisions and tradeoffs. Choose the shard key carefully. Distribute data uniformly. Route queries intelligently. Manage metadata. Rebalance proactively. Handle failures with care. Monitor continuously.

Sharding scales databases beyond a single server. It is the foundation of any large-scale, high-throughput data platform.

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.