Database Sharding Strategies: When and How to Scale Horizontally
Vertical scaling has limits. When your single PostgreSQL instance hits 10TB or 50,000 queries/second, horizontal sharding is the path forward. But it comes with significant complexity.
When to Shard
Shard only when you have exhausted these options first: read replicas, connection pooling (PgBouncer), query optimization, proper indexing, caching layer. Sharding is a one-way door that adds complexity to every query.
Hash Sharding
Distribute rows by hash(shard_key) % num_shards. Even distribution, but range queries across shards are expensive:
-- Determine shard
shard_id = hash(user_id) % 16
-- Query routes to specific shard
SELECT * FROM orders WHERE user_id = 12345
-- Goes to shard: hash(12345) % 16 = shard_7
Shard Key Selection
The shard key determines everything. Choose based on your most common query pattern. For multi-tenant SaaS, tenant_id is natural. For social media, user_id works. For time-series, timestamp-based range sharding keeps related data together.
Cross-Shard Queries
The hardest part of sharding is queries that span multiple shards. Scatter-gather is expensive. Design your schema so 95% of queries hit a single shard. Accept that cross-shard analytics may need a separate data warehouse.