Use when reasoning about horizontal partitioning of data across nodes for storage capacity and write throughput beyond a single node: the three foundational partitioning schemes (range, hash, directory/lookup), the shard-key choice that determines whether the system scales or hotspots, the resharding problem and how consistent hashing addresses it, cross-shard queries and the joins-and-transactions trade-off, the relationship to replication (sharding partitions data; replication copies each shard), and the failure modes (hot shard, skewed distribution, cross-shard transactions, range-end overload). Do NOT use for replicating the same data across nodes (use replication-patterns), the CAP/PACELC frame (use cap-theorem-tradeoffs), single-node performance tuning (use query-optimization), or indexing within a shard (use indexing-strategy).
Sharding (horizontal partitioning) is the discipline of dividing a database's data across multiple nodes so each node holds a subset — a shard. The unit of judgment is the shard key: the column or columns the system uses to route each row to a specific shard. Three foundational schemes: range partitioning (contiguous shard-key value ranges per shard — strong for range queries BETWEEN x AND y; weak for hotspots at range boundaries and for resharding-by-split), hash partitioning (hash(shard_key) % N — strong for even balance and no range hotspots; weak for range queries which become scatter-gather, and for resharding which rehashes nearly all data), consistent hashing (hash ring with each key routed to the nearest clockwise shard — adding shards moves only 1/N of data; virtual nodes place each physical shard at many ring positions to reduce imbalance), and directory / lookup partitioning (explicit map per key or key-range — strong for arbitrary placement; weak because the directory itself becomes a bottleneck).
Replaces "scale vertically forever" with horizontal capacity for write throughput, storage, and geographic placement. Solves the problem that when write throughput exceeds the primary's capacity, when storage approaches the node's limit, or when geographic placement is regulatory or latency-driven, the simpler single-node tools — replication (scales reads), caching (reduces load), denormalization (eliminates joins), vertical scaling (adds capacity) — are no longer sufficient. Sharding is the scaling tool of last resort — reached for only when the simpler tools are exhausted, because it adds operational complexity proportional to the gain. The shard key is the most consequential design decision: a well-chosen key gives nearly linear scaling; a poorly-chosen key pays operational complexity without capacity gain — hotspots concentrate on one shard, common queries scatter-gather across all shards, transactions require two-phase commit and become slow and failure-prone. The schema must be designed with sharding in mind from the start, or significant refactoring is required when sharding is later introduced.
Distinct from replication-patterns, which owns copying the same data across nodes for fault tolerance and read scaling; this skill owns dividing different data across nodes for write throughput and storage capacity. The two compose in production because each shard is usually replicated, but they answer different questions. It is also distinct from cap-theorem-tradeoffs, indexing-strategy, entity-relationship-modeling, query-optimization, and transaction-isolation; those skills own the theory frame, within-shard retrieval, schema design, single-query tuning, and single-system transactions respectively.
Coverage
The discipline of dividing a database's data across multiple nodes through horizontal partitioning. Covers the three foundational partitioning schemes (range, hash, directory), consistent hashing as the refinement that solves the resharding problem, the shard-key choice as the most consequential design decision, the cross-shard query and transaction trade-offs, the catalog of failure modes (hot shard, skewed distribution, range-boundary overload), the relationship to replication (sharding divides; replication copies; they compose), and the rule that sharding is one of the last optimizations to reach for after replication, caching, and denormalization.
Philosophy of the skill
Sharding is the scaling tool of last resort. Replication scales reads; caching reduces load; denormalization eliminates joins; vertical scaling adds capacity. When write throughput, storage capacity, or geographic data placement exceeds what those tools provide, sharding becomes the answer.
The shard key is the most consequential design decision. It determines which queries are fast (single-shard) and which are slow (scatter-gather), which operations are atomic (single-shard) and which require distributed commit (cross-shard), which growth patterns hotspot and which balance. A team that chooses the shard key well gains nearly linear scaling; a team that chooses it poorly pays operational complexity without capacity gain.
The schema must be designed with sharding in mind from the start, or significant refactoring is required when sharding is later introduced. Queries must filter on the shard key; related data must be co-located on the same shard; cross-shard operations must be rare or accepted as slow. Sharding is a schema architecture, not just an operational technique.
The Three Partitioning Schemes
Scheme
How it routes
Strong for
Weak for
Range
Contiguous key ranges per shard
Range queries (BETWEEN x AND y)
Hotspots at range boundaries; resharding by split
Hash
Hash(key) % N
Even balance; no range hotspots
Range queries become scatter-gather; resharding rehashes
Consistent hashing
Hash ring; key → nearest clockwise virtual node
Adding shards moves only about 1/N of data; virtual nodes smooth imbalance
Range queries still scatter-gather; virtual-node maps must be maintained
Directory
Explicit map per key
Flexibility; arbitrary routing
The directory itself becomes a bottleneck
Hash with consistent hashing is the default for most large-scale systems; range partitioning is used for time-series and naturally-ordered data; directory is rare but useful for arbitrary placement.
The Shard-Key Selection Rules
A good shard key:
Appears in nearly every query's WHERE clause — for shard-locality.
Distributes data evenly — high cardinality; no value dominates traffic.
Is immutable for a row — moving a row between shards is expensive.
Has predictable growth — won't shift hotspots over time.
Matches the natural grain of operations — common transactions are single-shard.
Common keys:
Multi-tenant SaaS: tenant_id. Most queries are tenant-scoped; tenants are independent.
Per-user data: user_id. Most queries are user-scoped.
DeCandia, G., Hastorun, D., Jampani, M., et al. (2007). "Dynamo: Amazon's Highly Available Key-Value Store". SOSP 2007. Industrial application of consistent hashing in sharded systems; basis of Cassandra and DynamoDB.
Corbett, J. C., Dean, J., Epstein, M., et al. (2012). "Spanner: Google's Globally-Distributed Database". OSDI 2012. Modern globally-distributed database with automatic sharding and consensus-based cross-shard transactions.
Kleppmann, M. (2017). Designing Data-Intensive Applications. O'Reilly. Chapter 6 (Partitioning) — modern comprehensive treatment of all three schemes.
MongoDB Manual. "Sharding". Reference for MongoDB's sharded cluster architecture.
Sadalage, P. J., & Fowler, M. (2012). NoSQL Distilled. Addison-Wesley. Practitioner reference covering sharding patterns across NoSQL system types.
Skill Graph context
Classification
Subject: data-engineering
Public: true
Domain: engineering/data
Scope: Teaches horizontal partitioning strategy for data systems: range, hash, and directory schemes; shard-key selection; hotspot and skew control; resharding and consistent hashing; cross-shard query and transaction trade-offs; and the distinction between partitioning data and replicating it. Portable across distributed storage systems. Excludes replica consistency, CAP/PACELC framing, single-node query tuning, and per-shard indexing.
When to use
choose a shard key for a multi-tenant system where 90% of queries are tenant-scoped
diagnose a hot shard caused by skewed shard-key distribution
design the resharding strategy when adding nodes to a hash-sharded cluster
decide whether to accept cross-shard JOIN complexity or denormalize
Triggers: how should we shard, what's the right shard key, hot shard, cross-shard transaction, consistent hashing
Not for
design replication topology for the same data (use replication-patterns)
tune a single slow query (use query-optimization)
design indexes within one shard (use indexing-strategy)
Analogy: Sharding is to a database what tenant-based building partitioning is to a multinational corporation — replicating each office to multiple cities is fault tolerance (replication); putting Sales in Building A and Engineering in Building B because they do not talk to each other is sharding. The shard key is the rule that decides who goes in which building, and the most catastrophic operational outcome is choosing a rule that puts everyone in Building A on Monday morning and Building B on Tuesday morning — the hot shard, where the structure was right but the routing rule was wrong.
Common misconception: |
Keywords
sharding, partitioning, horizontal partitioning, shard key, hash partitioning, range partitioning, consistent hashing, hot shard, resharding, cross-shard query
1---2name: sharding-strategy3description: Use when reasoning about horizontal partitioning of data across nodes for storage capacity and write throughput beyond a single node: the three foundational partitioning schemes (range, hash, directory/lookup), the shard-key choice that determines whether the system scales or hotspots, the resharding problem and how consistent hashing addresses it, cross-shard queries and the joins-and-transactions trade-off, the relationship to replication (sharding partitions data; replication copies each shard), and the failure modes (hot shard, skewed distribution, cross-shard transactions, range-end overload). Do NOT use for replicating the same data across nodes (use replication-patterns), the CAP/PACELC frame (use cap-theorem-tradeoffs), single-node performance tuning (use query-optimization), or indexing within a shard (use indexing-strategy).4license: MIT5---6# Sharding Strategy78## Concept of the skill910Sharding (horizontal partitioning) is the discipline of dividing a database's data across multiple nodes so each node holds a subset — a *shard*. The unit of judgment is the *shard key*: the column or columns the system uses to route each row to a specific shard. Three foundational schemes: *range partitioning* (contiguous shard-key value ranges per shard — strong for range queries `BETWEEN x AND y`; weak for hotspots at range boundaries and for resharding-by-split), *hash partitioning* (`hash(shard_key) % N` — strong for even balance and no range hotspots; weak for range queries which become scatter-gather, and for resharding which rehashes nearly all data), *consistent hashing* (hash ring with each key routed to the nearest clockwise shard — adding shards moves only 1/N of data; virtual nodes place each physical shard at many ring positions to reduce imbalance), and *directory / lookup partitioning* (explicit map per key or key-range — strong for arbitrary placement; weak because the directory itself becomes a bottleneck).1112Replaces "scale vertically forever" with horizontal capacity for write throughput, storage, and geographic placement. Solves the problem that when write throughput exceeds the primary's capacity, when storage approaches the node's limit, or when geographic placement is regulatory or latency-driven, the simpler single-node tools — replication (scales reads), caching (reduces load), denormalization (eliminates joins), vertical scaling (adds capacity) — are no longer sufficient. Sharding is the *scaling tool of last resort* — reached for only when the simpler tools are exhausted, because it adds operational complexity proportional to the gain. The shard key is the most consequential design decision: a well-chosen key gives nearly linear scaling; a poorly-chosen key pays operational complexity without capacity gain — hotspots concentrate on one shard, common queries scatter-gather across all shards, transactions require two-phase commit and become slow and failure-prone. The schema must be designed *with sharding in mind from the start*, or significant refactoring is required when sharding is later introduced.1314Distinct from replication-patterns, which owns copying the *same* data across nodes for fault tolerance and read scaling; this skill owns dividing *different* data across nodes for write throughput and storage capacity. The two compose in production because each shard is usually replicated, but they answer different questions. It is also distinct from cap-theorem-tradeoffs, indexing-strategy, entity-relationship-modeling, query-optimization, and transaction-isolation; those skills own the theory frame, within-shard retrieval, schema design, single-query tuning, and single-system transactions respectively.1516## Coverage1718The discipline of dividing a database's data across multiple nodes through horizontal partitioning. Covers the three foundational partitioning schemes (range, hash, directory), consistent hashing as the refinement that solves the resharding problem, the shard-key choice as the most consequential design decision, the cross-shard query and transaction trade-offs, the catalog of failure modes (hot shard, skewed distribution, range-boundary overload), the relationship to replication (sharding divides; replication copies; they compose), and the rule that sharding is one of the last optimizations to reach for after replication, caching, and denormalization.1920## Philosophy of the skill21Sharding is the scaling tool of last resort. Replication scales reads; caching reduces load; denormalization eliminates joins; vertical scaling adds capacity. When write throughput, storage capacity, or geographic data placement exceeds what those tools provide, sharding becomes the answer.2223The shard key is the most consequential design decision. It determines which queries are fast (single-shard) and which are slow (scatter-gather), which operations are atomic (single-shard) and which require distributed commit (cross-shard), which growth patterns hotspot and which balance. A team that chooses the shard key well gains nearly linear scaling; a team that chooses it poorly pays operational complexity without capacity gain.2425The schema must be designed with sharding in mind from the start, or significant refactoring is required when sharding is later introduced. Queries must filter on the shard key; related data must be co-located on the same shard; cross-shard operations must be rare or accepted as slow. Sharding is a schema architecture, not just an operational technique.2627## The Three Partitioning Schemes2829| Scheme | How it routes | Strong for | Weak for |30|---|---|---|---|31| Range | Contiguous key ranges per shard | Range queries (`BETWEEN x AND y`) | Hotspots at range boundaries; resharding by split |32| Hash | Hash(key) % N | Even balance; no range hotspots | Range queries become scatter-gather; resharding rehashes |33| Consistent hashing | Hash ring; key → nearest clockwise virtual node | Adding shards moves only about 1/N of data; virtual nodes smooth imbalance | Range queries still scatter-gather; virtual-node maps must be maintained |34| Directory | Explicit map per key | Flexibility; arbitrary routing | The directory itself becomes a bottleneck |3536Hash with consistent hashing is the default for most large-scale systems; range partitioning is used for time-series and naturally-ordered data; directory is rare but useful for arbitrary placement.3738## The Shard-Key Selection Rules3940A good shard key:41421. **Appears in nearly every query's WHERE clause** — for shard-locality.432. **Distributes data evenly** — high cardinality; no value dominates traffic.443. **Is immutable for a row** — moving a row between shards is expensive.454. **Has predictable growth** — won't shift hotspots over time.465. **Matches the natural grain of operations** — common transactions are single-shard.4748Common keys:49- **Multi-tenant SaaS**: `tenant_id`. Most queries are tenant-scoped; tenants are independent.50- **Per-user data**: `user_id`. Most queries are user-scoped.51- **Time-series**: `time_bucket(timestamp)`. Recent shards hot; older shards cold (often acceptable for time-series).52- **Geographic**: `region`. Latency benefit; regulatory benefit.5354Bad keys:55- `created_at` (range hotspot at latest range).56- `status` (low cardinality; one value dominates).57- A column not in most query WHERE clauses (scatter-gather every query).5859## Cross-Shard Query Trade-offs6061| Operation | Single-shard | Cross-shard |62|---|---|---|63| Lookup by shard key | Fast | n/a (must include shard key) |64| Lookup not using shard key | Single-shard if data co-located | Scatter to every shard |65| JOIN | Fast within shard | Slow or unavailable |66| Aggregation | Fast | Scatter-gather; partial-aggregate-then-combine |67| Transaction | ACID via single-shard primary | Two-phase commit or distributed consensus; slow and failure-prone |6869Schema design under sharding co-locates related data (store user's orders on user's shard) to make JOINs and transactions single-shard.7071## When Sharding Is The Right Tool7273| Workload property | Tool |74|---|---|75| Read load too high | Replication (read replicas) |76| Cache hit rate possible | Caching layer |77| Joins / aggregations slow | Denormalization, materialized views |78| Single-node CPU/memory exceeded | Vertical scaling |79| Storage approaching node limit | Sharding (or larger disks first) |80| Write throughput exceeds primary | Sharding |81| Geographic latency required | Sharding by region (with replication within region) |82| Multi-tenant isolation required | Sharding by tenant |8384Sharding is the answer when write throughput or storage exceeds single-node capacity. Before that, simpler tools suffice.8586## Verification8788After applying this skill, verify:89- [ ] Sharding is being considered after replication, caching, denormalization, and vertical scaling — not as a first response.90- [ ] The shard key is chosen against the criteria: appears in queries, distributes evenly, immutable, predictable, matches operation grain.91- [ ] Most production queries are single-shard. Scatter-gather queries are recognized as expensive and made rare.92- [ ] Related data is co-located on the same shard for JOIN and transaction locality.93- [ ] Cross-shard transactions are rare. Application design avoids them where possible.94- [ ] Resharding plan exists before launch: how shards are added, how data moves, what downtime is expected.95- [ ] Hot-shard detection is in place. Per-shard load metrics surface hotspots before they become incidents.96- [ ] Consistent hashing is used over modulo hash where resharding is anticipated. Naive hash modulo locks the shard count.97- [ ] The cross-shard query cost is documented and accepted. Reports and analytics that scatter-gather are run on read replicas or designed for the cost.98- [ ] Sharding interacts with replication intentionally — each shard's replication strategy is designed, not defaulted.99100## Do NOT Use When101102| Instead of this skill | Use | Why |103|---|---|---|104| Copying the same data to multiple nodes | `replication-patterns` | replication copies; sharding partitions |105| The CAP / PACELC theoretical frame | `cap-theorem-tradeoffs` | CAP names the trade-off |106| Tuning a slow query | `query-optimization` | query-optimization is single-query |107| Designing indexes within a shard | `indexing-strategy` | indexing is within-node |108| Designing schema | `entity-relationship-modeling` | entity-relationship-modeling is the schema design; this is the partitioning of the schema |109| Single-node transactional behavior | `transaction-isolation` | ACID is the single-system frame |110111## Key Sources112113- Karger, D., Lehman, E., Leighton, T., Panigrahy, R., Levine, M., & Lewin, D. (1997). ["Consistent Hashing and Random Trees: Distributed Caching Protocols for Relieving Hot Spots on the World Wide Web"](https://dl.acm.org/doi/10.1145/258533.258660). *STOC 1997*. The foundational paper on consistent hashing.114- DeCandia, G., Hastorun, D., Jampani, M., et al. (2007). ["Dynamo: Amazon's Highly Available Key-Value Store"](https://www.allthingsdistributed.com/files/amazon-dynamo-sosp2007.pdf). *SOSP 2007*. Industrial application of consistent hashing in sharded systems; basis of Cassandra and DynamoDB.115- Chang, F., Dean, J., Ghemawat, S., et al. (2006). ["Bigtable: A Distributed Storage System for Structured Data"](https://research.google/pubs/pub27898/). *OSDI 2006*. Google's range-partitioning-based sharded store; basis of HBase and many others.116- Corbett, J. C., Dean, J., Epstein, M., et al. (2012). ["Spanner: Google's Globally-Distributed Database"](https://research.google/pubs/pub39966/). *OSDI 2012*. Modern globally-distributed database with automatic sharding and consensus-based cross-shard transactions.117- Kleppmann, M. (2017). *Designing Data-Intensive Applications*. O'Reilly. Chapter 6 (Partitioning) — modern comprehensive treatment of all three schemes.118- MongoDB Manual. ["Sharding"](https://www.mongodb.com/docs/manual/sharding/). Reference for MongoDB's sharded cluster architecture.119- Citus Data. ["Citus Documentation — Distributed Tables"](https://docs.citusdata.com/en/stable/sharding/data_modeling.html). Reference for Postgres + Citus distributed sharding.120- Vitess. ["Vitess Documentation — Sharding"](https://vitess.io/docs/user-guides/configuration-basic/sharding/). Reference for Vitess's MySQL-based sharded architecture (YouTube, Slack, others).121- Lakshman, A., & Malik, P. (2010). ["Cassandra: a decentralized structured storage system"](https://www.cs.cornell.edu/projects/ladis2009/papers/lakshman-ladis2009.pdf). The Cassandra paper; consistent-hashing-based leaderless sharded store.122- Sadalage, P. J., & Fowler, M. (2012). *NoSQL Distilled*. Addison-Wesley. Practitioner reference covering sharding patterns across NoSQL system types.123124## Skill Graph context125126<!-- skill-graph-context:start (generated — do not edit by hand) -->127128**Classification**129- Subject: `data-engineering`130- Public: `true`131- Domain: `engineering/data`132- Scope: Teaches horizontal partitioning strategy for data systems: range, hash, and directory schemes; shard-key selection; hotspot and skew control; resharding and consistent hashing; cross-shard query and transaction trade-offs; and the distinction between partitioning data and replicating it. Portable across distributed storage systems. Excludes replica consistency, CAP/PACELC framing, single-node query tuning, and per-shard indexing.133134**When to use**135- choose a shard key for a multi-tenant system where 90% of queries are tenant-scoped136- diagnose a hot shard caused by skewed shard-key distribution137- design the resharding strategy when adding nodes to a hash-sharded cluster138- decide whether to accept cross-shard JOIN complexity or denormalize139- Triggers: `how should we shard`, `what's the right shard key`, `hot shard`, `cross-shard transaction`, `consistent hashing`140141**Not for**142- design replication topology for the same data (use replication-patterns)143- tune a single slow query (use query-optimization)144- design indexes within one shard (use indexing-strategy)145146**Related skills**147- Verify with: `entity-relationship-modeling`, `replication-patterns`148- Related: `replication-patterns`, `cap-theorem-tradeoffs`, `indexing-strategy`, `entity-relationship-modeling`, `connection-pooling`149150**Concept**151- Mental model: |152- Purpose: |153- Boundary: |154- Analogy: Sharding is to a database what tenant-based building partitioning is to a multinational corporation — replicating each office to multiple cities is fault tolerance (replication); putting *Sales* in Building A and *Engineering* in Building B *because they do not talk to each other* is sharding. The shard key is the rule that decides who goes in which building, and the most catastrophic operational outcome is choosing a rule that puts everyone in Building A on Monday morning and Building B on Tuesday morning — the hot shard, where the structure was right but the routing rule was wrong.155- Common misconception: |156157**Keywords**158- `sharding`, `partitioning`, `horizontal partitioning`, `shard key`, `hash partitioning`, `range partitioning`, `consistent hashing`, `hot shard`, `resharding`, `cross-shard query`159160<!-- skill-graph-context:end -->
Run npx skillmds@latest add jacob-balslev/sharding-strategy in your terminal (requires Node.js), paste this page's agent-chat prompt into Claude, Cursor, or any MCP-connected agent, or download the SKILL.md file and copy it into your agent's skills directory.
Use when reasoning about horizontal partitioning of data across nodes for storage capacity and write throughput beyond a single node: the three foundational partitioning schemes (range, hash, directory/lookup), the shard-key choice that determines whether the system scales or hotspots, the resharding problem and how consistent hashing addresses it, cross-shard queries and the joins-and-transactions trade-off, the relationship to replication (sharding partitions data; replication copies each shard), and the failure modes (hot shard, skewed distribution, cross-shard transactions, range-end overload). Do NOT use for replicating the same data across nodes (use replication-patterns), the CAP/PACELC frame (use cap-theorem-tradeoffs), single-node performance tuning (use query-optimization), or indexing within a shard (use indexing-strategy). It is listed under Coding & Dev Tools on SkillMD.
This skill has not completed SkillMD's automated safety review yet. Capability flags: makes network calls. SkillMD never runs a skill's scripts for you; review the SKILL.md before installing.
This skill is tagged as working with Claude Code, Claude.ai, OpenAI Codex. SKILL.md is an open format, so most agents that read a skills directory can load it too.
Yes. Installing skills from SkillMD is free. This skill is licensed under MIT.
jacob-balslev (@jacob-balslev) published this skill. Their other Agent Skills are listed on their SkillMD profile.
jacob-balslev