Database Sharding: Strategies and Trade-offs

Database sharding is one of those solutions that creates as many problems as it solves. Here's an honest breakdown of when it's necessary, which strategy to pick, and the operational costs that hit you 6 months after you shard.

When You Actually Need Sharding

• Single node write throughput is maxed out (>50K writes/sec on modern hardware)
• Dataset is too large to fit on the largest available RDS instance
• Read replicas aren't solving your problem — you have a write bottleneck
• Before sharding, try: read replicas, caching, vertical scaling, query optimization, partitioning

Sharding Strategies

Range-based: shard by value ranges (user IDs 1-1M on shard1, 1M-2M on shard2). Easy to understand but creates hot spots — new users always go to the last shard. Hash-based: shard by hash(shard_key) % N. Distributes evenly but makes range queries cross all shards. Directory-based: a lookup service maps each key to a shard. Most flexible but the lookup service becomes a bottleneck.

The Rebalancing Problem

Hash-based sharding with N shards means hash % N. When you add a shard and N becomes N+1, almost every record needs to move. Consistent hashing solves this — only 1/N of records need to move when adding a node. This is why Redis Cluster, Cassandra, and DynamoDB all use consistent hashing.

Cross-Shard Queries

Queries that span shards are the biggest pain point. A 'find all orders for user X' query is fine if you shard by user_id. But 'find all orders placed in the last 24 hours' touches every shard — this is called a scatter-gather query and it's expensive.

• Design your shard key around your most common query access pattern.
• For secondary access patterns, maintain a separate index table (e.g., a global secondary index by email when sharding by user_id).
• Use a separate analytics database (read replica or data warehouse) for aggregate queries — don't run OLAP on your sharded OLTP DB.
• Scatter-gather at the application layer: fan out to all shards in parallel, merge results. Still expensive but feasible with aggressive timeouts.

PostgreSQL Table Partitioning (Before Sharding)

1-- Range partitioning by date — much simpler than sharding
2-- Try this BEFORE considering multi-server sharding
3CREATE TABLE orders (
4    id          BIGSERIAL,
5    user_id     BIGINT NOT NULL,
6    created_at  TIMESTAMPTZ NOT NULL,
7    total       NUMERIC(10,2)
8) PARTITION BY RANGE (created_at);
9
10CREATE TABLE orders_2024 PARTITION OF orders
11    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
12
13CREATE TABLE orders_2025 PARTITION OF orders
14    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
15
16-- PostgreSQL automatically routes queries to the right partition
17-- Partition pruning makes date-range queries 10-100× faster