Database Sharding Strategies for Growing Apps

Your database is 500 GB. Queries that used to take 10 ms now take 8 seconds. You have added every index that makes sense. You have tuned every query. You have scaled vertically until the biggest available instance is too small.

This is the moment most growing apps eventually face. The database is the bottleneck. The next step is sharding – splitting your data across multiple database servers so no single server has to handle everything.

Sharding is one of the hardest things you can do to a database. Get it right, and your app scales almost indefinitely. Get it wrong, and you have created years of pain. This guide is the honest version of what sharding involves, when you actually need it, and how to do it without destroying your team in the process.

What Sharding Actually Is

Sharding means splitting one logical database into multiple physical databases, each holding a subset of the data. The application knows how to route each query to the right shard based on some key.

Imagine a users table with 100 million rows. Instead of one database holding all 100 million, you have 10 databases each holding 10 million. A query for user #12345 goes to shard 5 (because of how the user ID maps). A query for user #67890 goes to shard 8.

The application no longer talks to “the database.” It talks to “the shard router,” which figures out which physical database to use for each query.

This is fundamentally different from replication. Replication is multiple copies of the same data for redundancy or read scaling. Sharding is splitting the data so each server holds different rows.

When You Actually Need Sharding

Most applications never need sharding. Do not jump to it.

You probably need sharding if:

• Your single database server is hitting CPU, RAM, or I/O limits consistently
• Your dataset is too large to fit in the memory of one machine (typically >1 TB for OLTP)
• You have written queries that scan large portions of the table, and indexing does not help
• You need write throughput higher than a single server can handle
• You have tried vertical scaling (bigger instance), read replicas, caching, and still hit walls

You probably do NOT need sharding if:

• Your database is under 100 GB and your queries are fast
• Most queries are single-row lookups by primary key
• You have not tried caching yet (Redis or similar often solves the “slow database” problem)
• You have not optimized your schema or indexes
• Your problem is one specific query, not systemic scale

Sharding is expensive, complex, and risky. It should be the last resort, not the first response to slow queries.

The Two Main Approaches: Horizontal vs Vertical

Horizontal sharding (the usual kind). Different rows live in different shards. Shard 1 has users 1-10M, shard 2 has users 10M-20M, etc. This is what most people mean by “sharding.”

Vertical sharding (also called “splitting”). Different tables (or columns of the same table) live in different databases. Users table on one server, orders table on another. Less common term but same idea.

Vertical splitting is usually easier and less risky. If your users table is huge and your sessions table is small, putting them on separate servers is straightforward. The application connects to both.

Horizontal sharding is what most people mean when they say “sharding.” It is also harder. Let us focus on that.

Choosing a Shard Key

The shard key is the column you use to decide which shard a row lives on. Get this wrong, and every future problem traces back to it.

Good shard keys:

• Used in most queries (so queries can target one shard)
• Evenly distributed (so one shard does not get all the data)
• Stable (the value does not change for a given row)
• Non-monotonic (avoid auto-increment IDs – they create hot spots)

Examples of good shard keys:

• user_id for a multi-tenant app where most queries are user-scoped
• tenant_id for B2B SaaS where queries are always within one tenant
• Hash of email or phone for user lookup
• Geographic region if the app is region-scoped

Examples of bad shard keys:

• Auto-increment ID (new rows all go to the last shard)
• Status flags (everything pending might cluster on one shard)
• Date columns (creates time-based hot spots)
• Categories with uneven distribution (most users in one category)

The choice of shard key is the single most important decision in sharding. It determines which queries are fast, which are slow, and whether rebalancing is possible later.

Common Sharding Strategies

Five patterns cover most real sharding implementations.

1. Range-Based Sharding

Shard by ranges of the shard key. Users 1-1M on shard 1, 1M-2M on shard 2, etc.

Pros: Easy to understand. Range queries within a shard are fast. Adding new shards is straightforward.

Cons: Hot spots at the high end (most recent data is on the last shard). Rebalancing requires moving large ranges. Bad for monotonic keys.

Best for: Time-series data, logs, append-mostly workloads.

2. Hash-Based Sharding

Hash the shard key modulo the number of shards. shard = hash(user_id) % 10.

Pros: Even distribution. No hot spots. Simple to implement.

Cons: Range queries are expensive (must hit all shards). Adding shards requires rehashing almost everything.

Best for: User-scoped OLTP workloads where most queries are by ID.

3. Directory-Based Sharding

A lookup table maps each shard key to a specific shard. The directory can be cached or stored separately.

Pros: Flexible. Can rebalance without rehashing. Can move individual shards.

Cons: The directory itself becomes a single point of failure. Lookup adds latency. Operational complexity.

Best for: Large-scale systems where rebalancing is frequent (Spotify, Pinterest).

4. Geographic Sharding

Shard by geographic region. US users on US shards, EU users on EU shards.

Pros: Low latency for users (data is geographically close). Compliance benefits (data residency).

Cons: Cross-region queries are slow. Uneven distribution (small markets have tiny shards). Failover complexity.

Best for: Apps with strong regional requirements (latency or legal).

5. Hybrid Sharding

Combine multiple strategies. Tenant ID for primary sharding, user ID for secondary routing within tenant.

Pros: Matches real workloads better than single strategies.

Cons: Most complex to implement and reason about.

Best for: Multi-tenant SaaS at scale. Most production sharding systems end up here.

There is no single right answer. Most production systems end up with a hybrid of strategies.

Cross-Shard Queries: The Hidden Cost

Once you shard, queries that span shards become expensive.

Before sharding: “Count all users.” One query, fast.

After sharding: “Count all users.” One query per shard, plus a merge. Slow.

Cross-shard queries include:

• Aggregations across shards (count, sum, average)
• Joins between tables on different shards
• Full-text search across all data
• Reporting queries that span everything

Most sharded systems avoid cross-shard queries entirely. They denormalize data to keep related rows on the same shard. They use separate analytics databases (read-only copies optimized for reporting). They accept that some queries are simply not possible in real-time.

This is a fundamental architectural shift. Pre-sharding, you can ask any question of your database. Post-sharding, you can only ask questions that fit within a single shard (or accept the cost of cross-shard queries).

Transactions Across Shards

Single-row transactions are fine. Multi-row transactions across shards are extremely hard.

A transfer between two users on different shards requires a two-phase commit. Most distributed databases support this but it is slow and failure-prone.

The pattern that works: avoid cross-shard transactions entirely.

Design your schema so related data lives on the same shard. If you are sharding by user_id, the orders, payments, and preferences for that user should also live on the same shard. Cross-shard transactions then become unnecessary.

For unavoidable cases, use sagas. A saga is a sequence of local transactions that eventually reach consistency. If any step fails, compensating actions undo the previous steps. It is more complex than ACID transactions but works across distributed systems.

This is the kind of thing that makes sharding hard. Pre-sharding, you take transactions for granted. Post-sharding, you have to redesign your application around the constraint.

Rebalancing Shards

Eventually you need to add more shards. Or move data between shards. This is where most sharding projects get stuck.

The naive approach: take the database offline, copy data to new shards, switch over. Works for small datasets. Catastrophic for production.

The better approach: live migration with logical replication. Set up the new shard, replicate writes to both old and new, gradually copy reads, switch over, decommission old.

The sophisticated approach: use a sharding system that handles rebalancing automatically. Vitess, CockroachDB, Yugabyte, MongoDB sharded clusters all support this. You set the desired shard count and they handle the moving.

If you can avoid manual rebalancing, do. Most sharding pain comes from moves, not from the initial split.

When Not to Shard

Some workloads look like sharding problems but are not.

Read-heavy with cacheable data. Add Redis or Memcached. Most “slow database” issues are actually “no cache” issues. Cheaper and simpler than sharding.

Write-heavy but small data. Vertical scaling (bigger instance, faster disks) often handles 5-10x write load. Cheaper than sharding.

Analytics queries. Use a separate read replica or analytics database. Do not shard your primary database to support reporting.

Hot keys. If 1% of users generate 90% of writes, sharding by user_id does not help. Find the hot keys and handle them separately.

Search workloads. Use a search engine (Elasticsearch, Meilisearch). Do not try to shard your primary database for search.

Sharding is for OLTP scale on transactional data. For everything else, there is usually a better answer.

Tools That Make Sharding Easier

You do not have to build a sharding system from scratch.

Vitess (MySQL). Open source sharding middleware. Used by YouTube, Slack, Square. Handles routing, rebalancing, schema changes.

CockroachDB (Postgres-compatible). Distributed SQL database that shards automatically. Strong consistency. Good for new apps that want to scale without manual sharding.

YugabyteDB (Postgres-compatible). Similar to CockroachDB. Open source. Distributed SQL with automatic sharding.

MongoDB Sharded Clusters. Built-in sharding for MongoDB. Production-tested. Handles rebalancing.

PlanetScale (MySQL). Managed sharded MySQL. Branching workflow, no foreign keys, schema changes without locks.

Amazon Aurora Limitless. AWS managed sharded MySQL/Postgres. Newer, less battle-tested.

Google Cloud Spanner. Globally distributed SQL. Expensive. Used by big companies.

For most teams starting fresh, CockroachDB or YugabyteDB removes most sharding complexity. For teams with existing MySQL, Vitess is the standard answer.

Conclusion

Sharding is the right answer for some problems and a terrible answer for others. Most applications never need it. The ones that do usually need it because they have already exhausted simpler options.

The cost of sharding is not just infrastructure. It is application complexity. Cross-shard queries, distributed transactions, rebalancing, monitoring – all become real concerns. Some applications are architected in ways that make sharding impossible without major rewrites.

Start with caching, query optimization, and vertical scaling. Move to read replicas if reads are the bottleneck. Only shard when you have hard evidence that nothing else will work.

If you do shard, use a battle-tested tool (Vitess, CockroachDB, YugabyteDB). Do not build a sharding system yourself. The complexity will eat your team for years.

The goal is to never need sharding. The reality is some apps do. Knowing the difference is the whole job.