Databases
Sharding
Twitter in 2010 received 400 million tweets per day - 4,600 writes per second on average, with peaks hitting 150,000 per second during World Cup matches. A single MySQL server cannot handle this. Twitter sharded MySQL by user_id: each shard owns a slice of users and all their tweets. The trade-off was immediate: cross-user analytics became expensive scatter-gather operations.
- **Shopify / Vitess**: Shopify adopted Vitess (originally built at YouTube) to transparently shard MySQL. Applications send standard SQL; Vitess routes queries to the correct shard. Online resharding is handled by VReplication without downtime.
- **Instagram**: custom PostgreSQL sharding with 10,000 logical shards mapped to physical servers. User IDs encode their shard number, enabling O(1) routing. Adding capacity means moving logical shards, not individual rows.
- **Uber**: shards PostgreSQL by city_id. All rides, drivers, and pricing for a city live on one shard, eliminating cross-shard JOINs for 95% of operational queries.
Why Sharding Exists
A single database node has limits: disk capacity, write throughput, and connection count. Vertical scaling (adding CPU/RAM/disk) is finite and expensive. Sharding splits the dataset horizontally across multiple independent nodes - each node owns a subset of the data (a shard). Writes and reads scale linearly with the number of shards.
Sharding is a last resort. It eliminates ACID transactions across shards, makes JOINs across shards expensive, complicates schema migrations, and requires application-level shard routing. Shopify ran a single giant MySQL instance for years before adopting Vitess - and Vitess still aims to hide sharding complexity from the application.
A database consistently hits 80% CPU and 95% disk I/O. Which approach should be tried before sharding?
Hash vs Range Sharding
Hash sharding applies a hash function to the shard key and distributes rows evenly across shards. Range sharding assigns contiguous key ranges to shards. Each approach has distinct trade-offs for write distribution and query patterns.
Instagram sharded PostgreSQL using a modified hash approach: 10,000 logical shards mapped to a smaller number of physical servers. Adding physical servers requires moving logical shards, not resharding data - all user IDs encode their shard in the ID itself via a custom ID generator.
A time-series metrics database shards by timestamp range. What problem will it encounter?
Shard Key Design
The shard key determines data distribution and query routing. A good shard key distributes writes evenly, collocates data that is queried together, and avoids cross-shard operations for the most frequent queries. A bad shard key creates hotspots or forces scatter-gather for every query.
A multi-tenant SaaS application shards by user_id instead of tenant_id. What problem arises?
Resharding: Adding Capacity
Resharding moves data between shards when capacity changes - either adding new shards or rebalancing uneven ones. Live resharding must move data without downtime, ensure no reads or writes are lost during the move, and update routing tables atomically.
Shopify migrated to Vitess to enable transparent MySQL sharding. Vitess handles shard routing at the query level - applications send standard MySQL queries and Vitess routes them to the correct shard. Resharding is done online without application changes.
Consistent hashing adds a new node to a 10-node ring. How much data needs to move?
Cross-Shard Queries and Transactions
Cross-shard queries and transactions are the primary operational burden of sharding. A query that spans multiple shards requires scatter-gather: the query is sent to all relevant shards, results are collected and merged at the application layer. Cross-shard ACID transactions require distributed transaction protocols.
Stripe avoids most cross-shard transactions by sharding on merchant_id and keeping all of a merchant's payments, refunds, and charges on the same shard. The rare cross-merchant operation uses saga-based compensation rather than distributed 2PC.
A sharded database needs to execute SELECT COUNT(*) FROM users WHERE country='US'. What happens?
Key Ideas
- **Sharding is a last resort** - exhaust read replicas, caching, vertical scaling, and partitioning first. Sharding eliminates ACID across shards and complicates every query.
- **Hash sharding** distributes writes evenly but makes range queries scatter-gather. **Range sharding** enables efficient range queries but risks write hotspots on sequential keys.
- **Shard key design** is the critical decision: high cardinality, even frequency distribution, and query collocation (multi-tenant = shard by tenant_id).
- **Consistent hashing** minimizes data movement on resharding: adding one node to N moves only 1/(N+1) of data.
- **Cross-shard transactions** require distributed 2PC or saga patterns. Best avoided by collocating related data on the same shard.
Related Topics
Sharding sits at the intersection of scalability architecture:
- Replication — Replication scales reads; sharding scales writes. Each shard typically has its own replica set for read scaling and high availability.
- Partitioning — Partitioning splits data within a single node; sharding splits across nodes. Partitioning is the first step; sharding is needed when one node is insufficient.
- Consistent Hashing — The ring-based distribution algorithm that makes resharding efficient in Cassandra, DynamoDB, and distributed caches.
Вопросы для размышления
- A social network shards posts by post_id. A user's feed requires fetching posts from 50 different users. How would you minimize cross-shard fan-out for feed generation?
- Vitess hides sharding from applications. What are the limits of this abstraction - what queries will Vitess not be able to route efficiently?
- Instagram encodes the shard number in the user ID. What happens when Instagram needs to add more shards than the ID format supports?