1. Horizontal vs Vertical Partitioning
There are two fundamental types of database partitioning:
- Horizontal partitioning (sharding): rows are split across multiple servers. Each server (shard) holds a subset of rows with the same schema. A query for a specific user goes to the one shard that holds that user's data.
- Vertical partitioning: columns are split across servers. The user profile table lives on one server; the user activity log lives on another. This is more like database decomposition or microservices data ownership than traditional sharding.
When most people talk about "database sharding," they mean horizontal partitioning. The rest of this guide covers horizontal sharding strategies.
2. Strategy 1 — Range-Based Sharding
Data is partitioned by ranges of the shard key. For example, with 4 shards by user_id:
RANGE-BASED SHARDING:
Shard 1 → user_id 1 to 1,000,000
Shard 2 → user_id 1,000,001 to 2,000,000
Shard 3 → user_id 2,000,001 to 3,000,000
Shard 4 → user_id 3,000,001 to 4,000,000
Query for user_id = 1,500,000:
→ Router: "1,500,000 is in range [1M, 2M] → Shard 2"
→ Direct query to Shard 2 only ✓
HOTSPOT RISK:
If you shard by created_at (timestamp):
Shard 4 gets ALL new writes → perpetual hotspot
Range-based sharding is simple and efficient for range queries (e.g. "all orders from 2025") — a range query hits only the relevant shards. The major weakness is uneven distribution: if data is not uniformly distributed across ranges, some shards become overloaded (hotspots).
Monotonically Increasing Keys Create Hotspots
Sharding by auto-increment ID or timestamp means all new writes go to the last shard — creating a permanent write hotspot. In HBase and Cassandra, this is called a "hot region." The fix: use a non-sequential shard key, or add a prefix hash to the key to distribute writes evenly across shards while preserving range scan capability within each prefix group.
3. Strategy 2 — Hash-Based Sharding
A hash function is applied to the shard key, and the result modulo N (number of shards) determines the shard: shard = hash(key) % N.
Hash-based sharding distributes data evenly, eliminating hotspots from non-uniform data distributions. The critical weakness is resharding: when you add or remove a shard, almost all keys map to different shards under the new modulo, requiring a massive data migration. This is why consistent hashing was invented.
4. Strategy 3 — Consistent Hashing
Consistent hashing places both keys and servers on a circular hash ring. A key is assigned to the first server clockwise from its hash position. When a server is added or removed, only O(K/N) keys need to be remapped — a dramatic improvement over regular hashing.
CONSISTENT HASH RING (simplified):
Hash ring: 0 ────────────── 360 (degrees/positions)
Servers placed at hash positions:
Server A: position 90
Server B: position 180
Server C: position 270
Key routing (clockwise to next server):
key "user:42" hashes to position 50 → Server A (next clockwise)
key "user:99" hashes to position 120 → Server B (next clockwise)
key "user:77" hashes to position 200 → Server C (next clockwise)
ADD Server D at position 135:
Only keys between 90 and 135 move from Server B to Server D
All other keys unchanged ✓ (only ~K/N keys remapped)
REMOVE Server B at position 180:
Keys between 135 and 180 move to Server C
All other keys unchanged ✓
Virtual nodes (vnodes) are a critical enhancement: instead of placing each server at one position on the ring, each server is placed at multiple positions (e.g. 150 virtual nodes per server). This gives a more even distribution and makes rebalancing smoother. Cassandra uses 256 virtual nodes per server by default.
Consistent Hashing in Production
Apache Cassandra uses consistent hashing with virtual nodes (vnodes) as its core data distribution mechanism. DynamoDB also uses consistent hashing internally. Redis Cluster uses a 16,384-slot hash ring where each slot is assigned to a node — hash(key) % 16384 gives the slot, and the slot-to-node mapping is stored in the cluster metadata.
5. Strategy 4 — Directory-Based Sharding
A lookup table (shard map) stores the mapping from each key (or key range) to its shard. The application queries the shard map on every request to determine which shard to use.
- Maximum flexibility — you can move records between shards by updating the shard map
- Supports heterogeneous shard sizes (e.g. shard VIP customers on a larger, faster shard)
- The shard map itself becomes a hot path and single point of failure — must be cached aggressively (Redis) and replicated
- Used by systems like Slack (for workspace-to-shard mapping) and some gaming companies for player-to-shard assignment
6. Sharding Strategy Comparison
| Strategy | Even Distribution | Resharding Difficulty | Range Queries | Hotspot Risk | Used By |
|---|---|---|---|---|---|
| Range-based | Poor (data-dependent) | Medium | Excellent | High (monotonic keys) | HBase, Bigtable |
| Hash-based | Excellent | Very High (% N change) | None | Low | Simple custom sharding |
| Consistent hashing | Good (with vnodes) | Low (only K/N remapped) | Poor | Low | Cassandra, DynamoDB, Redis Cluster |
| Directory-based | Flexible | Low (update map) | Depends | Configurable | Slack, custom enterprise |
7. Cross-Shard Queries and JOINs
The biggest operational challenge of sharding is handling queries that span multiple shards. A single-shard query is simple. A cross-shard query requires sending the query to multiple shards and merging results in the application layer (scatter-gather):
- Scatter-gather: send the query to all relevant shards, collect results, merge and sort in the application. Works but adds latency proportional to the number of shards.
- Avoid cross-shard JOINs by design: choose a shard key that co-locates related data. For example, shard by user_id and store all of a user's orders, payments, and profile in the same shard — no cross-shard JOIN needed for any user-scoped query.
- Denormalise: store redundant copies of data that is frequently joined across shards.
- Distributed SQL: CockroachDB, Vitess, TiDB, and Amazon Aurora handle cross-shard queries internally, presenting a single-database abstraction over a sharded storage layer.
8. When to Shard vs Scale Vertically
Sharding adds enormous operational complexity. Before sharding, exhaust these options in order:
- Index optimisation: the most common performance problem is missing indexes, not database capacity.
- Read replicas: add 1-5 read replicas to distribute read traffic. Most web apps are 90% reads.
- Caching layer: add Redis in front of the database to absorb read traffic.
- Vertical scaling: move to a larger instance (more CPU, RAM, faster NVMe SSD). Modern cloud instances can handle 100TB+ databases.
- Partition within a single server: PostgreSQL and MySQL support native table partitioning (range/hash/list) within a single server — much simpler than multi-server sharding.
- Shard: only when you have genuinely exhausted the above options and have the engineering resources to manage the additional complexity.
How We Research and Update This Guide
We test the underlying formula or workflow, compare outputs with reliable references, and revise examples whenever the page content changes.
- The workflow or formula is tested directly in the tool and compared against independent reference examples.
- Examples are kept practical so readers can verify the result without hidden assumptions.
- Pages are revised whenever the interface, calculation flow, or surrounding guidance materially changes.
Frequently Asked Questions — Data Partitioning and Sharding
Horizontal partitioning (sharding) splits rows across multiple databases or servers. Each shard has the same schema but contains a subset of rows — for example, users with ID 1-1M on shard 1 and users with ID 1M-2M on shard 2. Vertical partitioning splits columns across databases — for example, storing user profile data in one database and user activity logs in another. Vertical partitioning is similar to normalisation. In most scaling discussions, "partitioning" or "sharding" refers to horizontal partitioning.
Consistent hashing is a technique that maps both keys and servers to a circular hash ring. A key is assigned to the first server clockwise from its hash position on the ring. When a server is added or removed, only the keys that fall between the new/removed server and its predecessor need to be remapped — O(K/N) keys where K is total keys and N is number of servers. Regular hash-based sharding (hash(key) % N) would require remapping almost all keys when N changes. Consistent hashing is used by Cassandra, DynamoDB, and many distributed caches.
A hotspot key (or hot partition) occurs when a disproportionate amount of traffic is directed to one shard. In range-based sharding, a hotspot occurs when a key range receives far more writes than others — for example, if you shard by timestamp and all recent writes go to the last shard. In hash-based sharding, a hotspot occurs when one key receives enormous traffic — for example, a viral post or celebrity user account. Hotspot keys are detected by monitoring per-shard CPU and request rate, and fixed by splitting the hot shard or using key salting.
Cross-shard queries (queries that need data from multiple shards, like a JOIN across shards) are expensive and complex. Common strategies: (1) Scatter-gather — send the query to all shards and merge results in the application layer. (2) Denormalise — store all data needed for a query on the same shard (partition by the query's primary entity). (3) Global tables — replicate small reference tables to all shards. (4) Use a distributed SQL database (CockroachDB, Vitess, TiDB) that handles cross-shard joins internally.
Scale vertically first (bigger server, more RAM, faster SSD) — it is simpler and cheaper than sharding. Sharding adds enormous operational complexity: cross-shard queries, resharding when a shard grows too large, distributed transactions, and multi-shard monitoring. Only shard when vertical scaling is no longer cost-effective or technically feasible (typically beyond ~4-8TB of data per database, or when single-server throughput is genuinely saturated). Many high-traffic systems operate on a single large RDS instance or a primary + read replicas without ever needing sharding.
Directory-based sharding (also called lookup-based sharding) maintains a separate lookup table (shard map) that records which shard each record belongs to. For example, a shard_map table might store {user_id: 42, shard_id: 3}. On every query, the application first looks up the shard for the key, then routes to that shard. This gives maximum flexibility — you can move records between shards without changing the key itself — but adds a lookup overhead and makes the shard map a potential single point of failure.