Lesson 17 / 30
Database Capacity: IOPS, Connections, Shards
Check a database design against IOPS, connection limits, and data size, and estimate shard counts.
By Rahul Agarwal · 15 years in web development
Three ways a database runs out
Estimate database capacity along three independent axes and take the worst. Throughput and IOPS: count reads and writes per second that reach disk after caches, then multiply by the I/O operations each needs (an indexed lookup may touch several pages; an insert updates every index). Compare with what the storage and the instance can sustain. Connections: many relational databases handle a limited number of concurrent connections efficiently, because each connection uses memory and scheduling resources; Little's Law tells you how many are busy, and a connection pooler helps when many app servers each open their own pool. Data size and memory: performance is best when the hot set of indexes fits in RAM. When one primary cannot meet write throughput or hold the data, you shard: number of shards = max(write load / per-shard write capacity, data size / per-shard storage), with headroom for growth.
Shard count from two limits
Shard count is driven by whichever limit is tighter.
import math
peak_writes = 40_000 # writes/s after batching
per_shard_writes = 5_000 # benchmarked safe limit
data_tb = 60
per_shard_tb = 4 # comfortable size per shard
growth_headroom = 1.5
by_writes = peak_writes / per_shard_writes
by_size = data_tb / per_shard_tb
shards = math.ceil(max(by_writes, by_size) * growth_headroom)
print(f"by writes: {by_writes:.1f}, by size: {by_size:.1f}")
print(f"shards with headroom: {shards}")
Output:
by writes: 8.0, by size: 15.0 shards with headroom: 23
Pick shard counts you can grow
Resharding is painful. Many teams create more logical shards than physical machines at the start and move logical shards later, so growth means relocating data rather than re-hashing everything.
Quick check: Writes need 6 shards, data size needs 10 shards. With no extra headroom, how many shards do you plan?
- 6
- 16
- 10
- 8
Answer
10 — Take the maximum across limits: 10 shards satisfy both the write and size constraints.