Sharding Strategies: Range, Hash, Directory
Compare range, hash and directory-based sharding, and pick a shard key that balances load and stays stable.
Sharding Strategies: Range, Hash, Directory is a free SQL Academy lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
What Is Sharding?
Splitting one logical database across multiple physical servers ("shards"), each holding a subset of the data. Done when one server can't handle the workload anymore.
Sharding ≠ Replication
- Replication — same data on many servers (for HA, read scaling)
- Sharding — different data on different servers (for write scaling, capacity)
You often combine both: each shard replicated for HA.
Three Sharding Strategies
- Range — shard by value range (id 1-1M on shard A, 1M-2M on shard B)
- Hash — hash the shard key, modulo N
- Directory — separate table maps key → shard
Range Sharding
Simple, works well for time-series and ordered IDs. Risk: hot shards if recent data gets all the traffic.
-- Conceptually:
-- Shard A: user_id 1 - 1,000,000
-- Shard B: user_id 1,000,001 - 2,000,000
-- Shard C: user_id 2,000,001 - 3,000,000Hash Sharding
Even distribution by default. Hard to add shards (re-sharding moves all keys):
-- shard_id = hash(user_id) % N
-- N=4: any user_id evenly distributed across 4 shardsDirectory Sharding
A lookup table maps each key to its shard:
CREATE TABLE shard_routing (
user_id BIGINT PRIMARY KEY,
shard_id INT NOT NULL
);
-- Looking up a user costs a directory query first; cache it.Consistent Hashing
Modulo hashing is brittle when adding shards. Consistent hashing minimises the keys that need to move:
-- Each shard owns a ring segment.
-- Adding a new shard moves only ~1/N of the keys.Picking a Shard Key
The shard key determines everything. Good shard keys:
- Distribute evenly
- Are present in most queries (avoids cross-shard fan-out)
- Are immutable (or rarely change)
<p>Common picks: user_id, tenant_id, customer_id. Avoid: timestamps for write-heavy workloads (creates hot shards).</p>Tenant-Per-Shard
Multi-tenant SaaS: each tenant on a dedicated shard. Simple to reason about, easy to isolate noisy tenants.
Reshardable Design
Design with future re-sharding in mind:
- Use virtual shards (e.g. 1024 logical, mapped to physical)
- Make it easy to migrate a logical shard to a different physical server
- Avoid app code that hard-codes shard counts
Cross-Shard Queries
The hardest problem. JOINs and reports across shards require fan-out + aggregate logic in the app. Cover in next lesson.
Transactions Across Shards
Atomic cross-shard transactions need two-phase commit (2PC) or sagas. Typical advice: design so transactions stay within one shard.
Recap
Three strategies; pick by your traffic shape.
- Range — simple, hot-shard risk
- Hash — even but rigid
- Directory — flexible but adds latency
- Consistent hashing for graceful re-sharding
Quick Check
You shard a users table by hash(user_id). You go from 4 shards to 5. How many keys must move?
Frequently asked questions
Is the “Sharding Strategies: Range, Hash, Directory” lesson free?
Yes — the full text of “Sharding Strategies: Range, Hash, Directory” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.
What will I learn in “Sharding Strategies: Range, Hash, Directory”?
Compare range, hash and directory-based sharding, and pick a shard key that balances load and stays stable. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start SQL Academy?
No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Sharding Strategies: Range, Hash, Directory” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this SQL Academy lesson?
Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
All lessons in this course
- Sharding Strategies: Range, Hash, Directory
- Cross-Shard Queries: The Hard Problem
- Citus and Distributed Postgres
- When NOT to Shard