Database Sharding

Database sharding (horizontal partitioning) is the practice of splitting a large dataset across multiple database instances (shards), where each shard holds a subset of the data and can be hosted on separate machines, enabling horizontal scalability beyond the limits of a single server.

What It Really Means

Every database hits a ceiling. A single PostgreSQL instance can handle perhaps 10,000 writes per second and store a few terabytes before performance degrades. When your application needs 100,000 writes per second or 100 TB of storage, no amount of vertical scaling (bigger server) will help. You need horizontal scaling: split the data across multiple database instances.

Sharding divides rows across databases based on a shard key. All data for shard key "user_123" lives on Shard 3. All data for "user_456" lives on Shard 7. Each shard is a fully independent database that handles only its portion of the data.

The idea is simple. The execution is not. Sharding introduces cross-shard queries, rebalancing challenges, application-level routing, and operational complexity that makes it one of the most impactful (and most feared) scaling decisions an engineering team will make. Instagram, Discord, Pinterest, Slack, and most large-scale systems use sharding.

How It Works in Practice

Sharding Strategies

Hash-based sharding: Apply a hash function to the shard key and mod by the number of shards: shard = hash(user_id) % num_shards. Distributes data evenly but makes range queries impossible.

Range-based sharding: Assign contiguous ranges to shards: Shard 1 handles user_ids 1-1M, Shard 2 handles 1M-2M. Enables range queries but creates hot spots if traffic concentrates on recent data (e.g., all new users hit the last shard).

Directory-based sharding: A lookup service maps each key to its shard. Maximum flexibility but the directory is a single point of failure and a potential bottleneck.

Consistent hashing: Maps keys and shards onto a ring, minimizing data movement when shards are added or removed. Used by DynamoDB, Cassandra, and other distributed databases.

Shard Key Selection

Choosing the right shard key is the most critical decision:

• Good shard key (user_id): Queries for a single user hit one shard. User data is colocated. Even distribution if user_ids are uniformly distributed.
• Bad shard key (country): A few countries (US, India, China) contain most users, creating massive hot shards while others sit idle.
• Bad shard key (created_at): All recent writes hit the same shard (the one for the current time range), creating a write hot spot.

Real-World: Instagram

Instagram sharded PostgreSQL by user_id. Each user's photos, likes, and comments live on the same shard. The shard ID is embedded in each photo's ID using a custom ID generation scheme:

Given a photo ID, you can extract the shard_id without a lookup table. This eliminates the need for a directory service.

Real-World: Discord

Discord stores messages sharded by (channel_id, bucket). Each bucket covers a 10-day window of messages. This means messages in a channel are distributed across time-based shards. Reading recent messages (the common case) hits one shard. Reading old messages (rare) hits a different shard. This optimizes for the read pattern.

Discord migrated from MongoDB to Cassandra (and later ScyllaDB) specifically because of sharding scalability. Their largest guilds generate millions of messages, and time-bucketed sharding prevents any single shard from growing unbounded.

Real-World: Vitess (YouTube)

Vitess is a database clustering system originally built at YouTube to shard MySQL. It provides a middleware layer that makes a cluster of MySQL shards look like a single database. Vitess handles query routing, connection pooling, and online resharding. It powers YouTube, Slack, HubSpot, and many large MySQL deployments.

Implementation

Trade-offs

Advantages

• Horizontal scalability: Add shards to increase capacity linearly — no single-machine limits
• Performance isolation: A hot shard does not affect other shards, preventing noisy-neighbor problems
• Geographic distribution: Place shards near users for lower latency (e.g., US users on US shards, EU users on EU shards)
• Operational benefits: Back up, restore, and maintain individual shards independently

Disadvantages

• Cross-shard queries: Queries that span multiple shards (scatter-gather) are slow, complex, and do not support joins
• Rebalancing complexity: Adding or removing shards requires migrating data, which is risky and time-consuming
• Application complexity: The application (or middleware) must route queries to the correct shard
• Referential integrity: Foreign keys across shards are impossible. Data consistency requires application-level enforcement.
• Operational overhead: N shards means N databases to monitor, back up, upgrade, and manage

Common Misconceptions

• "Sharding is the first solution for database scaling" — Sharding should be a last resort. Before sharding, try: read replicas for read scaling, connection pooling, query optimization, caching, partitioning within a single database, and vertical scaling. Sharding introduces permanent architectural complexity.

• "You can shard any database easily" — Sharding requires rewriting queries, handling cross-shard operations, building routing logic, and managing data migrations. It is a months-long engineering project, not a configuration change. This is why managed solutions like Vitess, Citus (PostgreSQL), and CockroachDB exist.

• "More shards is always better" — Each shard adds operational overhead. With too many shards, some shards may have very little data, wasting resources. Start with fewer shards (8-16) and split as needed. Powers of 2 make splitting easier.

• "Sharding eliminates the need for read replicas" — Sharding distributes writes; read replicas distribute reads. For a system with 100x read-to-write ratio, you still need read replicas per shard to handle the read load.

• "Resharding is just adding more shards" — Resharding requires migrating data from old shards to new ones while the system is running. Vitess, for example, supports online resharding by setting up replication from old to new shards, then cutting over — but it is still a complex, risky operation.

How This Appears in Interviews

Sharding is one of the most-tested topics in system design interviews:

• "Design a URL shortener" — shard by the short URL hash. Discuss why this is simpler than sharding by user_id. See our system design interview guide.
• "How does Instagram store billions of photos?" — shard PostgreSQL by user_id with shard-embedded IDs. Discuss colocating user data (photos, likes, comments) on the same shard.
• "Your database is too large for a single machine. What do you do?" — walk through the decision tree: optimize queries → add read replicas → vertical scaling → sharding. Discuss shard key selection, routing, and cross-shard query handling.
• "How would you rebalance shards?" — discuss consistent hashing for minimal data movement, virtual shards for flexibility, and online migration strategies.
• "How do you handle joins across shards?" — denormalize data so joins are unnecessary, or use scatter-gather with application-level aggregation.

Related Concepts

• Consistent Hashing — Distributing data across shards with minimal disruption
• CAP Theorem — The trade-offs sharded systems must navigate
• Eventual Consistency — Cross-shard operations often settle for eventual consistency
• Two-Phase Commit — Coordinating transactions across shards
• CQRS — Separating read and write models as an alternative to sharding reads and writes together
• Algoroq Pricing — Practice database design and sharding interview questions