toolkit

Database Sharding Visualizer

Runs in browser

Visualize how Hash, Range, and Directory sharding distribute data across nodes

Sharding Strategy

3
Formula: djb2(key) % 3
Same key always maps to same shard.

Look Up Key

Insert Records

Insert records or to see shard distribution

Related Tools

Simulators

Consistent Hashing

Simulators

CAP Theorem

Guides

Database Indexing

About Database Sharding

Sharding (horizontal partitioning) is the technique of splitting a large database across multiple physical nodes, each holding a subset of the data. It's the go-to scaling strategy when a single database node can no longer handle the load — whether that's storage capacity, write throughput, or query latency. Understanding sharding trade-offs is critical for designing systems that serve millions of users.

Three Sharding Strategies

1. Hash Sharding

shard = hash(key) % N

The most common strategy. Applies a hash function to the shard key and takes modulo N to determine the target shard. Distributes data uniformly regardless of key distribution, making it excellent for avoiding hotspots.

Downside: Adding or removing shards requires re-hashing all keys. A cluster expansion from 3→4 shards moves ~75% of data. Consistent hashing solves this by only remapping K/N keys on average.

2. Range Sharding

Assigns contiguous key ranges to each shard. For example, users A-H on shard 0, I-P on shard 1, Q-Z on shard 2. This enables efficient range queries and ordered scans within a single shard.

Downside: Highly susceptible to hotspots. If your keys are timestamps or monotonically increasing IDs, all writes go to the last shard. Google Bigtable and HBase use range sharding but mitigate hotspots with automatic split-and-merge.

3. Directory Sharding

A lookup table (the "directory") maps each key to its shard. This is the most flexible approach — any key can be placed on any shard, and resharding is just updating the directory.

Downside: The directory becomes a single point of failure and a potential bottleneck. Every query requires a directory lookup first. Must be cached aggressively and replicated for availability.

Choosing the Right Shard Key

The shard key is the most critical decision in sharding design. A bad shard key can make your sharded database slower than a single node. The ideal shard key has:

  • High cardinality: Many distinct values to distribute across shards. Boolean fields are terrible shard keys.
  • Uniform distribution: Avoids hotspots. user_id is usually better than country.
  • Query alignment: Most queries should be able to target a single shard. If your app always queries by user_id, that's your shard key.
  • Monotonic avoidance: Auto-incrementing IDs or timestamps cause all writes to hit the same shard.
Shard Key Cardinality Distribution Verdict
user_id High ✅ Uniform ✅ Excellent
created_at High ✅ Monotonic ❌ Terrible
country Low ⚠️ Skewed ❌ Poor
compound(user_id, date) Very High ✅ Uniform ✅ Best

The Cross-Shard Query Problem

The biggest pain point of sharding is cross-shard queries. When a query needs data from multiple shards, your application must:

Scatter-Gather

Send the query to all shards, collect results, merge and sort in the application layer. Latency is bounded by the slowest shard. Used by Elasticsearch for distributed search.

Cross-Shard Joins

Virtually impossible at scale. Solutions: denormalize data so joins are local, use materialized views, or move related data to the same shard (e.g., shard orders by user_id, not order_id, so user + order queries are local).

Distributed Transactions

Two-phase commit (2PC) works but is slow and blocks on coordinator failure. Better alternatives: saga pattern (compensating transactions) or eventual consistency with outbox pattern. CockroachDB and Spanner handle this transparently.

Resharding: The Operational Nightmare

Eventually, you'll need to add more shards. This process — resharding — is one of the hardest operational challenges in database management. Strategies:

Consistent Hashing

Instead of hash(key) % N, use a hash ring. Adding a node only moves K/N keys on average instead of rehashing everything. Used by DynamoDB, Cassandra, and Redis Cluster.

Double-Write Migration

Write to both old and new shard layouts simultaneously. Backfill the new layout from the old one. Once verified, switch reads to the new layout and stop writing to the old one. Minimizes downtime but doubles write load temporarily.

Virtual Shards (Over-Provisioning)

Create many more logical shards than physical nodes (e.g., 256 virtual shards on 4 physical nodes). When you need to scale, just move virtual shards between nodes — no data reorganization needed. Used by Vitess (YouTube's MySQL sharding layer).

Production Systems

MongoDB

Supports both hash and range sharding. Uses mongos as a query router and config servers to store shard metadata. Automatic chunk splitting and migration. Shard key is immutable once set — choose carefully!

Vitess (YouTube / PlanetScale)

MySQL sharding middleware. Uses virtual shards (called "vindexes") for flexible resharding. Supports both hash and lookup-based sharding. Handles cross-shard queries by rewriting SQL and merging results. Powers YouTube, Slack, and GitHub.

CockroachDB / Google Spanner

Auto-sharded NewSQL databases. Range-sharded with automatic split-and-merge. Handle distributed transactions transparently using Raft consensus per range. No manual shard management needed — the database is "sharded from the start."

Redis Cluster

Uses 16,384 hash slots (virtual shards). Each node owns a subset of slots. CRC16(key) % 16384 determines the slot. Resharding moves slots between nodes without downtime. Supports hash tags to co-locate related keys.

Alternatives to Sharding

Sharding is a last resort. Before sharding, exhaust simpler scaling options:

  • Read replicas: Offload read traffic to followers. Works when reads vastly outnumber writes (most web apps).
  • Vertical scaling: Bigger machine, more RAM, faster SSDs. Surprisingly effective — a single PostgreSQL instance can handle a lot more than you think.
  • Caching: Redis/Memcached in front of the database eliminates most read load.
  • Table partitioning: Split a table within the same database instance (by date, status, etc.). No distributed systems complexity.
  • CQRS (Command Query Responsibility Segregation): Separate read and write models. Write to a normalized store, project to denormalized read stores.

⚠️ The Hotspot Problem

When one shard receives disproportionately more traffic than others. Common causes:

  • Sequential IDs: Auto-increment keys send all inserts to the last shard
  • Celebrity accounts: One user generates 1000x more data than average
  • Time-based keys: All "today's" data goes to one shard
  • Unpadded ranges: Range shard [A-D] may have 10x more users than [U-Z]

Solutions: Compound shard keys (user_id + timestamp), random salt/prefix, consistent hashing with virtual nodes, or application-level splitting of hot keys.

💡 System Design Interview Tips

  • Default to hash sharding with consistent hashing for most use cases
  • Use range sharding only when range queries are critical (time-series, analytics)
  • Always mention cross-shard query challenges and how you'd handle joins
  • Discuss resharding strategy — interviewers love this topic
  • Know when NOT to shard — often caching + read replicas is sufficient
  • Mention virtual shards (over-provisioning) to simplify future scaling

Further Reading