advancedScalabilityFree Guide

Database Sharding

Definition

Database sharding is a horizontal scaling technique that distributes data across multiple independent database instances (shards), with each shard holding a non-overlapping subset of the total dataset.

Introduction to Database Sharding

Sharding is the approach used by the world's largest databases — social media platforms, financial systems, and global SaaS applications — when a single database server can no longer handle the data volume or write throughput. Unlike replication (which copies data), sharding splits data into distinct partitions stored on separate servers.

Key Takeaways

  • Each shard holds a distinct subset of data — shards don't share data
  • Shard key determines which shard a row belongs to
  • Range-based sharding: shard by value ranges (user_id 1-1M on shard 1, 1M-2M on shard 2)
  • Hash-based sharding: hash(shard_key) % num_shards — even distribution but no range queries
  • Cross-shard queries (JOINs across shards) are expensive and complex
  • Resharding (rebalancing) is complex and requires careful planning

Real-World Examples & SQL Schema

1

Hash-Based Shard Routing

-- Application-layer shard routing logic
-- Determine which shard to query based on user_id

-- Pseudo-code for shard selection:
-- shard_id = user_id % num_shards
-- connection = get_connection(shard_id)

-- Example with 4 shards:
-- user_id = 42: 42 % 4 = 2 → Shard 2
-- user_id = 99: 99 % 4 = 3 → Shard 3
-- user_id = 100: 100 % 4 = 0 → Shard 0

-- Query runs on the correct shard:
SELECT * FROM users WHERE user_id = 42;

Hash-based sharding distributes data evenly but requires knowing the shard key for every query.

Run code in Playground

Primary Use Cases

Databases that have outgrown vertical scaling limits

Write throughput beyond what a single primary can handle

Datasets too large to store on a single server

Multi-tenant SaaS applications (each tenant on a separate shard)

Geographic data distribution requirements (users in EU on EU shards)

Frequently Asked Questions

What is the difference between sharding and partitioning?
Partitioning splits a table's data within a single database instance — the data is physically separated but managed by one database server. Sharding splits data across multiple independent database servers — each shard is a completely separate database instance. Partitioning improves query performance by limiting scans; sharding provides horizontal scalability by distributing load across multiple servers.
What is a shard key and how do you choose one?
A shard key is the column used to determine which shard a row belongs to. A good shard key: has high cardinality (many unique values for even distribution), is included in the majority of queries (avoids scatter-gather to all shards), does not create hotspots (avoid incrementing IDs or timestamps where all new writes go to one shard), and aligns with your access patterns (e.g., user_id for user-centric applications).
When should you consider sharding your database?
Consider sharding when: vertical scaling is no longer cost-effective, write throughput exceeds what a single primary database can handle, dataset size exceeds what a single server can efficiently store and index, and you have exhausted other optimization strategies (indexing, query optimization, read replicas, caching). Sharding adds significant application complexity — it should be a last resort after all other scaling strategies are exhausted.

Related Concepts

ReplicationPartitioningIndexingTransactions