Database sharding is its own craft. At the point a single database can’t keep up, you split the data across multiple nodes. It sounds simple. In practice it’s one of the most complex database migrations you’ll ever do.
I’ve had the sharding conversation on dozens of projects. On some we did it, on others we found a different path. Here are the decision criteria and notes from real implementations.
What sharding is, briefly
Sharding kicks in when your data volume exceeds what one DB server can handle. 2TB of data, 50K QPS, a single PostgreSQL instance can’t cope.
The answer: split the data into “shards”. Each shard on its own DB instance. The application sends queries to the right instance based on the shard key.
shard_0: user_id 0-999999
shard_1: user_id 1000000-1999999
shard_2: user_id 2000000-2999999Or hash-based sharding:
shard = hash(user_id) % 4When is sharding actually required?
Most projects finish without ever needing to shard. Before you decide you need to:
1. Have you exhausted vertical scaling?
DB server with 512GB+ of RAM, 64+ cores, NVMe SSD. At that point vertical is usually enough. AWS RDS x1e.32xlarge exists and has 4TB of RAM.
2. Are read replicas enough?
If the workload is read-heavy, replicas handle it. One master, many replicas, read load spread across them.
3. Have you archived?
Move old data (1+ years) out of the hot table into an archive. If the hot data drops to 100GB, one server is fine.
If you’ve tried those three and you’re still over capacity, sharding comes into the conversation.
The cost of sharding
Before you commit, know what sharding really costs:
1. No cross-shard queries. No JOINs across shards. The application layer has to aggregate. A few-hour job becomes a few-week one.
2. Weaker transactional guarantees. Writes across multiple shards aren’t atomic. You’ll need distributed transaction patterns (saga, 2PC).
3. Rebalancing is hard. When a shard grows, you add a node and redistribute data. On a live system that’s hours or days of work.
4. Failover is complex. What if one shard goes down? Partial outage? You have to run replicas per shard too.
5. Backup and restore are complicated. Every shard needs its own backup. Cross-shard consistency on backups is difficult.
6. Schema migrations are 4x harder. N shards means N migrations. You have to track each shard’s version.
Operational overhead goes up 2 to 3x. Small teams can’t carry it.
Picking a shard key
If you’re going to shard, the most important decision is the shard key:
User-based sharding (most common): shard key is user_id. All of one user’s data lives on one shard. User-scoped queries hit a single shard. Ideal for basic SaaS.
Tenant-based sharding: for multi-tenant SaaS. Each tenant on their own shard. You can even sell “your own shard” to enterprise customers.
Geographic sharding: European users on the EU shard, US users on the US shard. Latency benefits and compliance (GDPR).
Time-based sharding: old data on one shard, new data on another. Good for logging and analytics.
The wrong choice is effectively irreversible. Even if you realise it six months in, re-sharding is a 3 to 6 month project.
The hot shard problem
The worst nightmare with sharding: a hot shard. All the load pile onto one shard.
Example: e-commerce sharded by customer_id. One mega-customer places thousands of orders. Their shard is 5x busier than the rest. Other shards sit idle.
Fixes:
- Re-sharding: move the hot customer to their own dedicated shard. A manual call.
- Sub-sharding: split a large customer across multiple shards (composite key).
- Dynamic shard allocation: a shard manager monitors load and rebalances continuously.
All of those add infrastructure. A shard manager is either custom-built or something like Vitess.
Alternative: managed solutions
Before you roll your own sharding, look at managed solutions:
CockroachDB: SQL-compatible distributed database. Transparent sharding, no app changes. Automated rebalancing.
YugabyteDB: PostgreSQL-compatible distributed. Similar profile to CockroachDB.
Vitess: a sharding layer built on top of MySQL. Developed at YouTube, open source.
AWS Aurora: scales vertically a very long way (128TB of storage, 64 cores). Most projects never need to shard on top of it.
These cut operational complexity. But cost and lock-in trade-offs are real.
A real migration story
On one SaaS project the call was: archive plus vertical scale, not sharding.
State: a single PostgreSQL instance with 500GB of data, 30K QPS. CPU at 70%, IOPS at 80%.
What we tried before sharding:
- Query optimisation: we optimised the top 10 slow queries. IOPS dropped 30%.
- Partitioning: PostgreSQL native partitioning pushed the old data aside. The hot table fell to 80GB.
- Read replicas: we added 2 replicas and pushed analytics queries to them. The primary saw 50% relief.
- Vertical upgrade: we moved to db.r5.24xlarge (768GB RAM).
Total change: three weeks of work. We didn’t need sharding for another 18 months. Sharding would have been a 3 to 6 month project.
The lesson: sharding is a last resort. Exhaust the other options first.
If you are sharding, a checklist
When you really do need to shard:
- Think about the shard key twice. The wrong choice is irreversible in six months.
- Be ready for cross-shard queries. Build an aggregate layer in application code.
- Define the transaction pattern. Saga or eventual consistency.
- Per-shard monitoring. Detect hot shards early.
- Automated backup and restore. Manual won’t scale.
- Replicas for every shard. A failover plan.
- A schema migration tool. With 30 shards on average you can’t live without one.
- Load test with production data volume.
If those eight aren’t in place, don’t start sharding. Half-built sharding is death.
Takeaway
Sharding is a last-resort scaling tool. Vertical scaling plus replicas plus partitioning plus archiving covers most projects. When you really do need it, managed solutions (CockroachDB, Vitess) are safer than rolling your own.
Make the call with a combination of data volume, QPS, and tolerance. Not “I think we need to scale”. Set up monitoring, watch the trend, plan 12 to 18 months out.