Concept 2 · Data Storage and Management Strategies·9 min read

Implementing Database Sharding and Partitioning

Decomposing a single, massive dataset into smaller chunks spread across nodes — trading query simplicity for horizontal scale.

Database sharding and partitioning are strategies used to decompose a single, massive dataset into smaller, manageable chunks across multiple database nodes.

Vertical scaling adds more power — CPU, RAM — to a single server. Horizontal scaling via sharding instead distributes data across an entire cluster, theoretically overcoming the storage and throughput limits of one machine.

Model 01

ShardingHorizontal Partitioning

Sharding is the process of splitting a logical dataset into horizontal fragments, where each fragment is stored on a separate physical server. Unlike partitioning — often done on a single instance to improve local query performance — sharding explicitly addresses the scaling bottlenecks of one physical database server.

The core of a sharded architecture is the Shard Key — the column (or set of columns) used to determine which shard a row belongs to. Choosing this key is the most critical design decision.

A poor shard key choice leads to "hot shards" — one server handling 90% of the traffic, defeating the purpose of the distribution.

Strategy 01

RangeRange-Based Sharding

Data is partitioned based on ranges of values. For example, a Users table could be sharded by user_id:

Shard 1: user_id 1 to 1,000,000
Shard 2: user_id 1,000,001 to 2,000,000

This is simple to implement but leads to uneven distribution. If your application creates many new users, the shard holding the highest range experiences significantly more write pressure than the others.

Strategy 02

HashHash-Based Sharding

Data is partitioned by applying a hash function to the shard key, then taking the modulo of the number of shards:

Shard ID = hash(user_id) % total_shards

Routing a write with a hash-based shard key

Incoming Write Request

↓

Calculate Hash

shard_id = hash % 3

↓

Shard 0

Node A

Shard 1

Node B

Shard 2

Node C

This effectively randomizes the data distribution, preventing hot spots. But it makes range-based queries — "find all users created between Monday and Friday" — extremely expensive, since you must broadcast the query to every shard and aggregate the results.

Reference

Range vs. Hash at a Glance

Requirement	Range-Based	Hash-Based
Distribution	Uneven — skews toward newest range	Even — randomized by hash
Range Queries	Cheap — hits one contiguous shard	Expensive — broadcast to every shard
Hot Spot Risk	High, on sequentially-growing keys	Low, distribution is randomized
Typical Use Case	Time-series, ordered IDs	User/session data, high write volume

Single-Instance Technique

Partitioning Strategies Within a Single Node

Partitioning is often used within a single database engine to improve maintenance and query performance for massive tables without the complexity of a distributed network.

List Partitioning

Rows are assigned to a partition based on a specific list of values. A Logs table might be partitioned by region_code, with separate partitions for 'US', 'EU', and 'APAC'.

Range Partitioning

Commonly used for time-series data. A Transactions table partitioned by created_at date lets you purge old data by dropping the entire partition — a metadata operation — instead of running a massive, locking DELETE.

Composite Partitioning

A combination of strategies — for example, sharding by tenant_id across servers, then partitioning each shard locally by transaction_date.

Implementation

Implementing a Shard Map

To route requests, the application or a middleware layer needs a "Shard Map" — a look-up table that maintains the association between the shard key range and the physical connection string of the database instance.

# Simplified Shard Router
class ShardRouter:
    def __init__(self):
        # Maps shard IDs to database connection strings
        self.shard_map = {
            0: "db-node-0.internal:5432",
            1: "db-node-1.internal:5432",
            2: "db-node-2.internal:5432"
        }

    def get_connection(self, user_id):
        shard_id = hash(user_id) % 3
        return self.shard_map[shard_id]

# Usage
router = ShardRouter()
conn = router.get_connection(user_id=89234)
# Output: "db-node-2.internal:5432"

Costs

Trade-offs and Operational Complexity

Sharding introduces significant architectural overhead:

Join Complexity

Performing a JOIN across shards is complex and slow. You have to ensure related data — Users and their Orders, for example — is co-located on the same shard using the same shard key.

Rebalancing

If one shard becomes too full, it has to be split and its data moved to a new server. This is time-consuming and often requires downtime or a complex background migration.

Cross-Shard Transactions

Operations spanning multiple shards are inherently difficult, often requiring two-phase commit protocols or a distributed transaction manager to keep them atomic.

Summary

Data Placement, Not Data Consistency

Sharding is a powerful technique for horizontal scaling, shifting the bottleneck from a single physical server to the logic of your partitioning strategy. Hash-based sharding gives the best load distribution but sacrifices efficient range queries — always aim for data co-location to minimize the need for cross-shard operations. Partitioning and sharding are purely about data placement; next, we’ll explore how to keep that data consistent across those nodes.

Quiz Review

Check your understanding

Question 1 of 10

What does horizontal scaling via sharding actually do to a dataset?

✓It distributes data across multiple nodes in a cluster, rather than adding more power (CPU, RAM) to a single server the way vertical scaling does.

Plain hash % N has a nasty property: changing N (adding or removing a shard) reshuffles almost every key’s target shard, not just the ones that need to move.

Consistent Hashing

Shards are placed as points on a fixed hash ring, and each key maps to the next shard clockwise from its hash. Adding or removing a shard only remaps the keys between it and its neighbor — a small, bounded slice of the data instead of nearly all of it.

Virtual Nodes

A physical shard is represented by many points on the ring instead of one, so load spreads more evenly and a single new shard doesn't absorb one disproportionately large arc.

Why It Matters Operationally

It turns rebalancing from "migrate most of the dataset" into "migrate the fraction that actually belongs on the new shard" — the difference between a routine operation and a maintenance window.

Plain modulo hashing and consistent hashing solve the same routing problem — the difference only shows up the moment the shard count changes.

A single-shard transaction gets ACID guarantees for free from the local database engine. The moment a transaction touches two different shards, there’s no single engine watching both — something else has to coordinate.

Phase 1 — Prepare

A coordinator asks every participating shard to do the work and confirm it's ready to commit, without actually committing yet. Each shard locks the affected rows and replies "ready" or "abort."

Phase 2 — Commit

If every shard replied "ready," the coordinator tells them all to commit for real. If any shard said "abort," it tells them all to roll back instead — all or nothing, across machines that don't otherwise know about each other.

The Painful Part

Between the two phases, every participating shard is holding locks and waiting on the coordinator. If the coordinator crashes mid-protocol, those shards can be stuck blocked until it recovers — which is exactly why cross-shard transactions are avoided whenever data can instead be co-located.

Two-phase commit buys atomicity across shards at the cost of availability during the protocol. It’s the CAP theorem’s trade-off showing up again, one layer down.

The database engine can only join rows it can see locally. Once related rows live on different physical servers, a "join" stops being one operation and becomes an application-level fan-out — unless you design around it.

Co-locate by the Same Shard Key

If Users are sharded by user_id, shard Orders by the owning user's user_id too. Now a user's orders always live on the same physical node as the user, and the join never has to cross the network.

Denormalize the Reference Data

For small, slow-changing lookup data (like a product catalog), duplicate it onto every shard instead of joining to it remotely. You trade some storage and eventual staleness for a local, fast read.

When You Genuinely Can't Co-locate

The application has to run the query on each relevant shard and merge the results in code — the same broadcast-and-aggregate pattern range queries hit under hash-based sharding.

Co-location isn’t a database feature — it’s a modeling decision. You choose the shard key so the joins you actually run stay on one machine.

Neither strategy is universally better — the right one follows directly from which queries you run most often.

Choose Range When Queries Are Range-Shaped

Time-series dashboards, ordered pagination, and "give me everything between X and Y" access patterns all want the underlying data physically contiguous, which only range-based sharding gives you.

Choose Hash When Writes Dominate

High-volume, independently-keyed writes — user records, session data, event streams keyed by ID — benefit far more from even load distribution than from any range-scan capability.

Some Systems Do Both

Composite schemes hash on a coarse key (like tenant_id) to spread load evenly across shards, then range-partition within each shard by a time column — getting even write distribution and cheap local range scans at once.

Start from your access patterns, not the algorithm. The shard key is a bet on what you’ll query — get it wrong and you’re re-sharding in production.