Database Sharding Guide — Horizontal Scaling Strategy
Database sharding is a horizontal scaling technique that partitions data across multiple database instances. Each shard holds a subset of the data, and queries must be routed to the correct shard. Sharding distributes read and write load across multiple servers, allowing the database to scale beyond the capacity constraints of a single machine in terms of both storage and throughput. According to a 2024 report by Gartner, over 60 percent of enterprises with databases exceeding 10 terabytes have implemented some form of sharding, making it a standard technique for large-scale data management at companies like Uber, Airbnb, and Shopify.
The fundamental challenge of sharding is that it trades operational simplicity for scalability. A single monolithic database is easy to query with cross-table joins, simple to back up with a single command, and straightforward to maintain with a consistent schema. A sharded database requires careful planning around data distribution across shards, query routing to the correct shard, cross-shard operations that must scatter-gather across all shards, schema change management across potentially hundreds of database instances, and rebalancing when shards grow unevenly or new shards are added. The decision to shard should be made only after vertical scaling options including larger servers and read replicas have been exhausted.
Sharding Strategies
Choosing the right sharding strategy determines query performance, data distribution quality, and operational complexity for the lifetime of the system. Three main strategies exist, each with distinct trade-offs.
Hash-Based Sharding
A hash function maps each row’s shard key to a shard number, distributing data approximately evenly across shards regardless of the key’s natural distribution. Consistent hashing minimizes data movement when shards are added or removed by using a hash ring where each shard is responsible for a contiguous range of hash values, and only neighboring shards on the ring are affected when the cluster size changes. When a shard is added, it takes responsibility for a portion of its neighbor’s range, and only those keys need migration.
Hash-based sharding distributes data evenly across shards regardless of key distribution patterns, preventing hot spots. However, range queries are difficult because adjacent keys in the domain likely reside on different shards, requiring scatter-gather queries that query every shard and combine results in application code. This strategy works best for key-value workloads where most queries access individual records by primary key, such as user session data or product catalog lookups.
Range-Based Sharding
Data is divided by key ranges, each assigned to a shard. For example, users whose IDs fall in a specific range or whose names start with particular letters are stored on designated shards. Range queries are efficient because the relevant shard can be identified directly from the query parameters without scatter-gather. A query for all users created in a specific month hits only the shard responsible for that date range.
The downside is the potential for hot spots where some ranges receive disproportionate traffic. If most user signups occur in January due to New Year resolutions, the January shard handles far more writes than the December shard. Monotonic keys like auto-incrementing IDs cause all new writes to go to the last shard, creating a write hot spot that defeats the purpose of horizontal scaling. Mitigation strategies include using compound keys that combine a monotonic component with a distribution component, or pre-sharding with virtual shards mapped to physical shards.
Directory-Based Sharding
A lookup service maintains a dynamic mapping between shard keys and shard locations. This adds maximum flexibility — data can be moved between shards without changing the application’s hashing algorithm, shards can be split or merged as data grows or shrinks, and the mapping can be optimized based on current load patterns. The lookup service becomes a potential single point of failure and a performance bottleneck, as every write and many reads require a lookup. Caching the mapping in application memory mitigates the bottleneck but introduces consistency concerns when shard assignments change through cache invalidation protocols.
Choosing a Shard Key
The shard key is the most important design decision in any sharded database. A good shard key distributes data evenly across shards with high cardinality to avoid skew, matches the application’s query patterns so that most queries hit a single shard rather than requiring scatter-gather, and remains stable over time since changing the shard key requires full data re-export and re-import.
Common shard key choices include user ID for multi-tenant applications where each user’s data is stored on a single shard, geographic region for location-based services where data can be partitioned by user location, and time-based keys for time-series data where older data is queried less frequently. The shard key should have high cardinality — at least 100 times the number of shards — to ensure even distribution. A shard key with low cardinality creates hot shards that defeat the purpose of sharding.
Real-World Sharding Examples
Major technology companies have published their sharding architectures, providing valuable case studies. Instagram sharded their PostgreSQL database by user ID using hash-based sharding with 1,024 logical shards mapped to physical databases. Each shard stores all data for a subset of users, ensuring that user-specific queries are single-shard operations. Uber moved from a monolithic PostgreSQL to a sharded architecture using MySQL and their own sharding layer called Schemaless, sharding by trip UUID for even distribution.
Shopify uses MySQL with horizontal sharding across thousands of shards, with each merchant’s data stored on a single shard for query simplicity. This design ensures that the vast majority of queries hit a single shard, avoiding cross-shard joins entirely. Their sharding layer handles query routing by merchant ID, maps merchants to shards through a configuration database, and manages schema migrations across thousands of shards through automated rollout tools that apply changes gradually to avoid downtime.
Discord migrated from MongoDB to Cassandra with a sharded architecture designed for chat messages. They shard by guild ID, ensuring that all messages for a server are stored together for efficient retrieval. Their architecture handles billions of messages per day across hundreds of shards, with automatic rebalancing when shards grow unevenly.
Shard Rebalancing Strategies
Over time, data distribution across shards becomes uneven due to organic growth patterns. Some shards may outgrow others, creating hot spots and storage imbalances. Several rebalancing strategies address this challenge.
Fixed shard count with virtual shards maps a large number of logical shards to a smaller number of physical shards. When a physical shard needs to be split, virtual shards are reassigned without changing the hashing algorithm. This approach, used by Instagram with 1,024 logical shards, simplifies rebalancing because only the mapping from virtual to physical shards changes, not the application-level routing logic.
Dynamic rebalancing monitors shard sizes and automatically moves data when imbalance exceeds a threshold. This requires automated data migration tools that can move data between shards without downtime. The migration typically uses a double-write pattern where new writes go to both old and new locations until the migration completes and the old data can be dropped.
Read-only shard replicas provide another scaling dimension. Each shard can have one or more read replicas that handle read traffic while the primary shard handles writes. This is particularly useful for read-heavy workloads and geographic distribution where replicas are placed closer to users.
Monitoring and Observability
Sharded databases require comprehensive monitoring beyond what monolithic databases need. Key metrics include per-shard query latency to identify slow shards, per-shard storage utilization to detect imbalance, per-shard connection counts to identify connection leaks, replication lag for read replicas, and cross-shard query frequency to identify shard key design issues.
Tools like Prometheus with Grafana dashboards provide real-time monitoring of shard health. Automated alerting should trigger when any shard exceeds storage or latency thresholds, allowing operators to take corrective action before the shard becomes a bottleneck.
Challenges and Mitigations
Cross-shard joins are expensive or impossible since related data may reside on different shards. Distributed transactions require two-phase commit protocols like XA or the Saga pattern with compensation logic, adding latency and complexity. Schema changes must be applied to every shard through automated migration tools. Backup and restore procedures must coordinate across all shards for point-in-time consistency. Many applications never need sharding — vertical scaling, read replicas, caching, and database partitioning solve most scaling challenges without sharding’s complexity.
Frequently Asked Questions
What is the difference between sharding and partitioning? Partitioning divides a table within a single database instance. Sharding distributes data across independent database servers for horizontal scaling.
How does consistent hashing help with rebalancing? Consistent hashing minimizes data movement during rebalancing by arranging shards on a hash ring so only neighboring shards are affected when the cluster size changes.
What happens when a shard runs out of space? The shard must be split or data redistributed. Pre-sharding with many virtual shards simplifies this process.
Can I run JOIN queries across shards? Cross-shard joins are possible but require scatter-gather queries, which are slow. Design the shard key to keep related data together.
Is sharding the only way to scale databases? No. Vertical scaling, read replicas, caching, and partitioning solve most scaling challenges without sharding’s complexity.
PostgreSQL vs MySQL — SQL JOINs Explained — Vector Databases