--- # === IDENTITY === id: software/system-design/database-sharding/2026 canonical_question: "What are the best database sharding strategies?" aliases: - "database sharding strategies comparison" - "horizontal database partitioning patterns" - "how to shard a database" - "hash vs range sharding" - "consistent hashing for databases" - "database sharding system design" - "shard key selection best practices" - "when to shard a database" entity_type: software_reference domain: software > system-design > database_sharding region: global jurisdiction: global temporal_scope: 2020-2026 # === VERIFICATION === last_verified: 2026-02-23 confidence: 0.93 freshness: quarterly version: 1.0 first_published: 2026-02-23 # === TEMPORAL VALIDITY === temporal_validity: status: stable last_breaking_change: null next_review: 2026-08-22 change_sensitivity: low # === CONSTRAINTS === constraints: - "Never shard before exhausting vertical scaling, read replicas, caching, and query optimization -- sharding adds permanent operational complexity" - "Shard key is immutable after data is distributed -- changing it requires full data migration across all shards" - "Cross-shard JOINs and transactions are either unsupported or orders of magnitude slower -- design schema to keep related data on the same shard" - "Auto-increment primary keys are not globally unique across shards -- use UUIDs, ULIDs, or Snowflake IDs" - "Resharding (adding/removing shards) requires a migration plan -- consistent hashing minimizes data movement but does not eliminate it" # === SKIP CONDITIONS === skip_this_unit_if: - condition: "Database is slow but under 1TB and <10K queries/sec" use_instead: "Optimize indexes, add read replicas, and tune queries first -- see software/debugging/postgresql-slow-queries/2026" - condition: "Need to partition a single database table without distributing across servers" use_instead: "Use native table partitioning (PostgreSQL PARTITION BY, MySQL PARTITION) -- different from sharding" - condition: "Building a new application and want to avoid sharding entirely" use_instead: "Use a distributed NewSQL database (CockroachDB, TiDB, YugabyteDB) that handles sharding transparently" # === AGENT HINTS === inputs_needed: - key: sharding_key_choice question: "What is the primary access pattern for your data? (determines optimal shard key)" type: choice options: ["Single-entity lookup (user_id, tenant_id)", "Range scans (time-series, date ranges)", "Geographic queries (region-based access)", "Mixed/unknown access patterns"] - key: current_scale question: "What is your current database size and query volume?" type: choice options: ["<100GB, <1K qps", "100GB-1TB, 1K-10K qps", "1TB-10TB, 10K-100K qps", ">10TB, >100K qps"] - key: database_engine question: "Which database engine are you using?" type: choice options: ["PostgreSQL", "MySQL", "MongoDB", "Cassandra/ScyllaDB", "Other/undecided"] # === DISTRIBUTION === canonical_source: "https://knowledgelib.io/software/system-design/database-sharding/2026" suggested_citation: "Source: knowledgelib.io -- AI Knowledge Library (verified 2026-02-23)" # === RELATED UNITS === related_kos: depends_on: - id: "software/debugging/postgresql-slow-queries/2026" label: "PostgreSQL Slow Query Optimization" - id: "software/debugging/postgresql-connection-pool/2026" label: "PostgreSQL Connection Pool Tuning" related_to: - id: "software/system-design/chat-app-at-scale/2026" label: "Scalable Chat Application Design" - id: "software/system-design/e-commerce-platform/2026" label: "E-Commerce Platform Design" - id: "software/migrations/monolith-to-microservices/2026" label: "Monolith to Microservices Migration" solves: - id: "software/system-design/database-replication/2026" label: "Database Replication Strategies" alternative_to: - id: "software/system-design/newsql-distributed-databases/2026" label: "NewSQL Distributed Databases (CockroachDB, TiDB)" often_confused_with: - id: "software/system-design/database-partitioning/2026" label: "Database Table Partitioning (single-node)" # === SOURCES (7 authoritative sources) === # Types: official_docs, technical_blog, rfc_spec, academic_paper, community_resource, industry_report # Reliability: high, moderate_high, moderate, moderate_low, low, authoritative sources: - id: src1 title: "Vitess Sharding Documentation" author: Vitess / PlanetScale url: https://vitess.io/docs/23.0/reference/features/sharding/ type: official_docs published: 2025-06-01 reliability: high - id: src2 title: "Hash-Sharded Indexes: Unlock Linear Scaling for Sequential Workloads" author: CockroachDB Engineering url: https://www.cockroachlabs.com/blog/hash-sharded-indexes-unlock-linear-scaling-for-sequential-workloads/ type: technical_blog published: 2024-03-15 reliability: high - id: src3 title: "Understanding Partitioning and Sharding in Postgres and Citus" author: Citus Data / Microsoft url: https://www.citusdata.com/blog/2023/08/04/understanding-partitioning-and-sharding-in-postgres-and-citus/ type: technical_blog published: 2023-08-04 reliability: high - id: src4 title: "4 Data Sharding Strategies We Analyzed When Building YugabyteDB" author: YugabyteDB Engineering url: https://www.yugabyte.com/blog/four-data-sharding-strategies-we-analyzed-in-building-a-distributed-sql-database/ type: technical_blog published: 2024-01-20 reliability: high - id: src5 title: "Sharding Pattern -- Azure Architecture Center" author: Microsoft url: https://learn.microsoft.com/en-us/azure/architecture/patterns/sharding type: official_docs published: 2024-06-01 reliability: high - id: src6 title: "A Guide to Database Sharding: Key Strategies" author: ByteByteGo url: https://blog.bytebytego.com/p/a-guide-to-database-sharding-key type: community_resource published: 2024-09-10 reliability: moderate_high - id: src7 title: "Choose a Shard Key -- MongoDB Manual" author: MongoDB url: https://www.mongodb.com/docs/manual/core/sharding-choose-a-shard-key/ type: official_docs published: 2025-01-01 reliability: high --- # Database Sharding Strategies: A Complete System Design Guide ## TL;DR - **Bottom line**: Choose hash-based sharding for uniform distribution, range-based for time-series/ordered queries, geo-based for multi-region latency, or directory-based for maximum flexibility -- the shard key determines everything. - **Key tool/command**: `SELECT create_distributed_table('orders', 'tenant_id');` (Citus) or `PARTITION BY HASH` (Vitess vschema) - **Watch out for**: Choosing a low-cardinality shard key (e.g., country code or status enum) -- creates hot shards and uneven distribution that cannot be fixed without full migration. - **Works with**: PostgreSQL + Citus, MySQL + Vitess/PlanetScale, MongoDB native sharding, CockroachDB, Cassandra/ScyllaDB, YugabyteDB, TiDB. Concepts apply to any database. ## Constraints - Never shard before exhausting vertical scaling, read replicas, caching, and query optimization -- sharding adds permanent operational complexity - Shard key is immutable after data is distributed -- changing it requires full data migration across all shards - Cross-shard JOINs and transactions are either unsupported or orders of magnitude slower -- design schema to keep related data on the same shard - Auto-increment primary keys are not globally unique across shards -- use UUIDs, ULIDs, or Snowflake IDs - Resharding (adding/removing shards) requires a migration plan -- consistent hashing minimizes data movement but does not eliminate it - Unique constraints cannot span shards -- globally unique columns must include the shard key or use an external uniqueness service ## Quick Reference | Strategy | How It Works | Distribution | Range Queries | Resharding Cost | Best For | Worst For | |---|---|---|---|---|---|---| | Hash-based | `shard = hash(key) % N` | Uniform (even) | Poor (scatter-gather) | High (rehash all data) | High-write workloads, random access by ID | Time-series, range scans | | Consistent hashing | Hash ring with virtual nodes | Uniform | Poor | Low (only K/N keys move) | Elastic scaling, frequent shard add/remove | Range queries, ordered access | | Range-based | `shard = key_range(value)` | Depends on data | Excellent (single shard) | Medium (split/merge ranges) | Time-series, ordered data, date partitions | Monotonic keys (hot last shard) | | Geo-based | `shard = region(location)` | Uneven (population) | Good within region | Low (region reassignment) | Multi-region apps, data residency compliance | Global queries, small datasets | | Directory-based | Lookup table maps key to shard | Configurable | Depends on mapping | Low (update directory) | Complex routing, tenant isolation | High throughput (directory = bottleneck) | | Hash + range (compound) | Hash on tenant, range on time | Balanced | Good within tenant | Medium | Multi-tenant SaaS with time-series | Simple applications | | Schema-based | Each tenant gets own schema | Isolated | Full SQL within tenant | Low (move schema) | Strong tenant isolation, compliance | Large tenant counts (>10K schemas) | | Algorithmic (jump hash) | `jump_consistent_hash(key, N)` | Near-perfect | Poor | Very low (minimal movement) | Caching layers, stateless routing | Dynamic shard topology | ## Decision Tree ``` START -- What is your primary access pattern? | +-- Single-entity lookup (user_id, order_id, tenant_id)? | +-- Need elastic scaling (frequently add/remove shards)? | | +-- YES --> Consistent hashing (virtual nodes) | | +-- NO --> Hash-based sharding (simple modulo or jump hash) | | +-- Range scans (time-series, date ranges, sequential IDs)? | +-- Data is append-only (logs, events, IoT)? | | +-- YES --> Range-based with time buckets (e.g., monthly shards) | | +-- NO --> Range-based with dynamic range splitting | | +-- Geographic queries (user location, data residency)? | +-- YES --> Geo-based sharding (shard per region/country) | | +-- Multi-tenant SaaS with mixed access patterns? | +-- <1K tenants with strong isolation needs? | | +-- YES --> Schema-based sharding (Citus schema-based or separate databases) | +-- >1K tenants? | +-- YES --> Hash on tenant_id (co-locate all tenant data on same shard) | +-- Unknown or complex routing needs? +-- YES --> Directory-based (lookup table with flexibility to change) +-- DEFAULT --> Start with hash-based on your most-queried entity ID ``` ## Step-by-Step Guide ### 1. Identify your shard key candidates Analyze your query patterns to find the column that appears in the WHERE clause of 80%+ of your queries. The shard key must have high cardinality (thousands+ distinct values) and appear in most queries to avoid scatter-gather operations. [src7] ```sql -- PostgreSQL: Find most common WHERE clause columns from pg_stat_statements SELECT query, calls, mean_exec_time FROM pg_stat_statements ORDER BY calls DESC LIMIT 20; -- MongoDB: Check query patterns from profiler db.setProfilingLevel(2); db.system.profile.find().sort({ts: -1}).limit(20); ``` **Verify**: The top 5 queries all include your candidate shard key in their WHERE clause. If not, re-evaluate. ### 2. Evaluate shard key quality A good shard key has high cardinality, high frequency in queries, and low monotonicity (for hash sharding). Test distribution before committing. [src3] ```sql -- Check cardinality: should be >10x your planned shard count SELECT COUNT(DISTINCT tenant_id) AS cardinality FROM orders; -- Check distribution: no single value should exceed 5% of total rows SELECT tenant_id, COUNT(*) AS row_count, ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 2) AS pct FROM orders GROUP BY tenant_id ORDER BY row_count DESC LIMIT 20; -- Check query coverage: what % of queries include the shard key? -- (manual audit of application code or pg_stat_statements) ``` **Verify**: `cardinality > 10 * planned_shards` and `max(pct) < 5%`. Both must pass. ### 3. Choose your sharding strategy Based on the decision tree, select hash, range, geo, or directory-based sharding. For most OLTP applications, hash-based on tenant_id or user_id is the safest default. [src6] ``` Strategy Selection Checklist: +-- Access pattern: [ ] random lookups [ ] range scans [ ] geographic +-- Write distribution: [ ] uniform [ ] append-heavy [ ] bursty +-- Scaling needs: [ ] fixed shards [ ] elastic [ ] multi-region +-- Tenant isolation: [ ] none needed [ ] soft (same DB) [ ] hard (separate) ``` **Verify**: Your chosen strategy handles your top 5 queries without cross-shard operations. ### 4. Implement sharding with your chosen technology Deploy using a proven sharding middleware or native distributed database rather than building custom sharding logic. [src1] ```sql -- Option A: PostgreSQL + Citus (hash distribution) -- Install Citus extension, then: SELECT create_distributed_table('orders', 'tenant_id'); SELECT create_distributed_table('order_items', 'tenant_id'); -- Co-locate tables that JOIN frequently: SELECT create_distributed_table('order_items', 'tenant_id', colocate_with => 'orders'); -- Option B: MongoDB native sharding (hashed shard key) sh.enableSharding("mydb"); sh.shardCollection("mydb.orders", { tenant_id: "hashed" }); -- Option C: Vitess (MySQL) -- define in vschema.json -- See Code Examples section below ``` **Verify**: `SELECT COUNT(*) FROM citus_shards;` shows data distributed across all nodes. For MongoDB: `sh.status()` shows balanced chunks. ### 5. Set up monitoring and rebalancing Monitor shard distribution, query routing, and hotspots. Set up alerts for shard imbalance exceeding 20%. [src5] ```sql -- Citus: Check shard sizes and distribution SELECT nodename, COUNT(*) AS shard_count, pg_size_pretty(SUM(shard_size)) AS total_size FROM citus_shards GROUP BY nodename; -- MongoDB: Check chunk distribution db.adminCommand({ balancerStatus: 1 }); sh.status(); -- Vitess: Check tablet health vtctlclient ListAllTablets ``` **Verify**: Shard sizes are within 20% of each other. No single shard handles >30% of total queries. ## Code Examples ### Python: Consistent Hashing Ring Implementation > Full script: [python-consistent-hashing-ring-implementation.py](scripts/python-consistent-hashing-ring-implementation.py) (42 lines) ```python # Input: List of shard nodes + data keys to route # Output: Shard assignment for each key with minimal disruption on node changes # Deps: pip install hashlib (stdlib) import hashlib from bisect import bisect_right # ... (see full script) ``` ### SQL/Vitess: VSchema Configuration for Hash-Based Sharding > Full script: [sql-vitess-vschema-configuration-for-hash-based-sh.json](scripts/sql-vitess-vschema-configuration-for-hash-based-sh.json) (31 lines) ```json // Input: Vitess vschema definition for a sharded keyspace // Output: Automatic query routing to correct shards // vschema.json -- defines sharding strategy for Vitess/PlanetScale { "sharded": true, # ... (see full script) ``` ### Go: Application-Level Shard Router > Full script: [go-application-level-shard-router.go](scripts/go-application-level-shard-router.go) (34 lines) ```go // Input: Shard key (tenant_id) and list of database connections // Output: Routes queries to correct shard based on hash // Deps: database/sql, hash/fnv (stdlib) package sharding import ( # ... (see full script) ``` ## Anti-Patterns ### Wrong: Sharding by auto-increment ID with modulo ```sql -- BAD -- Modulo on auto-increment creates hot shard for recent data. -- All new inserts go to shard = (max_id + 1) % N, hammering one shard. -- Resharding from 4 to 5 shards moves ~80% of all data. INSERT INTO orders_shard_%s VALUES (...); -- shard = order_id % 4 ``` ### Correct: Hash the shard key with consistent hashing ```sql -- GOOD -- Hash distributes evenly regardless of key ordering. -- Adding a 5th shard moves only ~20% of data (1/N). -- Use consistent hashing library or built-in (Citus, Vitess). SELECT create_distributed_table('orders', 'tenant_id'); -- Citus handles hashing ``` ### Wrong: Using a low-cardinality column as shard key ```python # BAD -- country_code has ~200 values. With 16 shards, some shards # get USA (40% of data) while others get Liechtenstein (0.001%). shard = hash(row["country_code"]) % 16 # massive skew ``` ### Correct: Use a high-cardinality key, add geo-routing at application level ```python # GOOD -- Shard by tenant_id (millions of values = even distribution). # Handle geo-routing in the application layer, not the shard key. shard = consistent_hash(row["tenant_id"]) # Geo-awareness via a region -> shard-cluster mapping (directory-based) cluster = geo_directory[row["region"]] ``` ### Wrong: Cross-shard JOINs in hot-path queries ```sql -- BAD -- This JOIN hits every shard, aggregates results at coordinator. -- 16 shards = 16 network round trips + merge. Latency: 50-500ms. SELECT o.*, u.name FROM orders o JOIN users u ON o.user_id = u.user_id -- users and orders on different shard keys! WHERE o.created_at > NOW() - INTERVAL '1 hour'; ``` ### Correct: Co-locate related tables on the same shard key ```sql -- GOOD -- Both tables sharded by tenant_id = JOIN stays on one shard. -- Latency: 1-5ms (same as single-node query). SELECT create_distributed_table('orders', 'tenant_id'); SELECT create_distributed_table('users', 'tenant_id', colocate_with => 'orders'); -- Citus co-location -- Query stays on one shard: SELECT o.*, u.name FROM orders o JOIN users u ON o.tenant_id = u.tenant_id AND o.user_id = u.user_id WHERE o.tenant_id = 'abc123'; -- shard key in WHERE = single shard ``` ### Wrong: Building custom sharding logic in application code ```python # BAD -- Homegrown shard routing = bugs, no rebalancing, no failover, # no connection pooling, and months of engineering to maintain. def get_connection(user_id): shard_id = hash(user_id) % len(shards) return psycopg2.connect(shards[shard_id]) # What about transactions? Failover? Schema migrations across shards? # Connection pooling per shard? Monitoring? Rebalancing? ``` ### Correct: Use a proven sharding middleware ```python # GOOD -- Let Citus, Vitess, or ProxySQL handle routing, rebalancing, # failover, and schema migrations. You write normal SQL. import psycopg2 # Connect to Citus coordinator -- it routes automatically conn = psycopg2.connect("host=citus-coordinator dbname=myapp") cur = conn.cursor() cur.execute("SELECT * FROM orders WHERE tenant_id = %s", ("abc123",)) # Citus routes to correct shard transparently ``` ## Common Pitfalls - **Hot shards from monotonic keys**: Sharding by auto-increment ID, timestamp, or ObjectId concentrates all recent writes on one shard. Fix: use hash-based sharding or add a random prefix. For MongoDB: `sh.shardCollection("db.col", { _id: "hashed" })`. [src2] - **Cross-shard query explosion**: Queries without the shard key in the WHERE clause hit all shards (scatter-gather). Fix: always include the shard key in queries. Denormalize data to avoid cross-shard JOINs. Monitor with `EXPLAIN` on Citus or Vitess query planner. [src1] - **Unbalanced shard sizes over time**: Data growth is rarely uniform -- some tenants grow 100x faster. Fix: implement shard splitting (range-based) or tenant migration (hash-based). Citus `rebalance_table_shards()` handles this automatically. [src3] - **Schema migrations across shards**: DDL changes must be applied to every shard atomically. Fix: use your middleware's built-in migration tool (Vitess `ApplySchema`, Citus DDL propagation). Never run DDL directly on individual shards. [src1] - **Connection pool exhaustion**: N shards x M app servers = N*M connections. With 16 shards and 50 app servers, that is 800 connections per shard. Fix: use PgBouncer per shard, or a connection-aware proxy like ProxySQL. [src3] - **Distributed transaction overhead**: Two-phase commit (2PC) across shards adds 2-10x latency. Fix: design for eventual consistency where possible. Use saga pattern for multi-shard workflows. Avoid distributed transactions on the hot path. [src4] - **Backup and restore complexity**: Each shard must be backed up independently but restored to a consistent point-in-time. Fix: use coordinated snapshots (e.g., Citus `citus_create_restore_point()`) or logical replication with consistent cutover. [src5] - **Testing with fewer shards than production**: Bugs that only manifest with >2 shards (routing errors, cross-shard aggregation) go undetected. Fix: run CI tests with production shard count or at minimum 4 shards. [src6] ## Diagnostic Commands ```bash # PostgreSQL + Citus: Check shard distribution psql -c "SELECT nodename, COUNT(*) AS shards, pg_size_pretty(SUM(shard_size)) FROM citus_shards GROUP BY nodename;" # Citus: Find queries hitting multiple shards (scatter-gather) psql -c "SELECT query, calls, mean_exec_time FROM citus_stat_statements WHERE partition_count > 1 ORDER BY calls DESC LIMIT 10;" # MongoDB: Check balancer status and chunk distribution mongosh --eval 'sh.status()' mongosh --eval 'db.adminCommand({ balancerStatus: 1 })' # Vitess: List tablets and their health vtctlclient ListAllTablets | grep -v healthy # Vitess: Check VSchema for routing rules vtctlclient GetVSchema commerce # General: Check shard key cardinality psql -c "SELECT COUNT(DISTINCT tenant_id) AS cardinality, COUNT(*) AS total_rows, COUNT(*) / COUNT(DISTINCT tenant_id) AS avg_rows_per_key FROM orders;" # Monitor: Identify hot shards by CPU/IO # (use your monitoring tool: Prometheus + Grafana, Datadog, etc.) ``` ## Version History & Compatibility | Technology | Version | Sharding Features | Notes | |---|---|---|---| | PostgreSQL + Citus | 13.0 (2025) | Schema-based sharding, shard rebalancer, CDC | Citus 12 introduced schema-based sharding (2023) | | Vitess | 21.x (2025) | VReplication, online resharding, VDiff | Used by Slack, GitHub, PlanetScale. CNCF graduated | | MongoDB | 8.0 (2024) | Hashed + ranged sharding, zone sharding, resharding | Online resharding added in 6.0 (2022) | | CockroachDB | 24.x (2025) | Automatic range splitting, hash-sharded indexes, geo-partitioning | Transparent sharding -- no manual shard management | | TiDB | 8.x (2025) | Auto-sharding, TiFlash columnar, placement rules | MySQL-compatible, auto-splits at 96MB regions | | YugabyteDB | 2024.x | Hash + range sharding, tablespace-based geo-partitioning | PostgreSQL-compatible, automatic tablet splitting | | Cassandra | 5.0 (2024) | Consistent hashing (Murmur3), virtual nodes (vnodes) | Always sharded -- no single-node mode | | ScyllaDB | 6.x (2025) | Tablets (replacing vnodes), Raft-based topology | Cassandra-compatible, 10x lower latency | ## When to Use / When Not to Use | Use When | Don't Use When | Use Instead | |---|---|---| | Single database exceeds 1-5TB and vertical scaling is maxed | Database is <500GB with room to scale vertically | Add more RAM/CPU, optimize queries, add read replicas | | Write throughput exceeds single-node capacity (>50K writes/sec) | Read-heavy workload with low write volume | Read replicas with connection routing | | Data residency requirements mandate geographic isolation | All users are in one region | Single-region deployment with edge caching | | Multi-tenant SaaS needs tenant-level isolation and scaling | <100 tenants with similar data volumes | Schema-per-tenant or row-level security on single DB | | Need to scale horizontally beyond single-machine limits | Application can tolerate ~100ms query latency from unoptimized indexes | Index optimization and query tuning first | | Compliance requires data in specific jurisdictions (GDPR, CCPA) | No regulatory data residency requirements | Single deployment with encryption at rest | ## Important Caveats - Sharding is a one-way door operationally -- once data is distributed, unsharding (merging back) requires a full migration project that can take weeks to months depending on data volume - Choosing the wrong shard key is the most expensive mistake: it requires migrating all data to a new distribution, which means double storage and extended downtime or complex dual-write periods - Distributed databases (CockroachDB, TiDB, YugabyteDB) handle sharding transparently but add latency for cross-range transactions -- benchmark your specific workload before assuming they eliminate sharding complexity - Schema-based sharding (Citus 12+) is simpler for SaaS but breaks down beyond ~10K tenants due to PostgreSQL catalog overhead per schema - Consistent hashing solves resharding data movement but does not solve cross-shard queries, distributed transactions, or global secondary indexes -- those remain hard problems regardless of routing strategy - Cloud-managed sharding services (PlanetScale, Amazon Aurora, Azure Cosmos DB) reduce operational burden but introduce vendor lock-in and higher per-query costs at scale ## Related Units - [PostgreSQL Slow Query Optimization](/software/debugging/postgresql-slow-queries/2026) - [PostgreSQL Connection Pool Tuning](/software/debugging/postgresql-connection-pool/2026) - [Scalable Chat Application Design](/software/system-design/chat-app-at-scale/2026) - [E-Commerce Platform Design](/software/system-design/e-commerce-platform/2026) - [Monolith to Microservices Migration](/software/migrations/monolith-to-microservices/2026)