Database Capacity

The connection-pool ceiling, read replicas, write throughput, and sharding strategies.

Overview

Why databases need their own capacity plan

Databases are usually the first thing to break under load and the hardest thing to scale. Database capacity planning is its own subspecialty.

> [!IMPORTANT] Most teams scale apps horizontally with ease but hit database limits painfully. Plan database capacity first and let app capacity follow.

The connection-pool ceiling

PostgreSQL — 100 — 200-500
MySQL — 151 — 500-2000
Aurora — Scales with instance size — Up to ~5000

Each Postgres backend process costs 5-15 MB of memory. On the app side, pools of 5-50 connections per instance are typical, so 100 instances x 20 connections = 2000 connections.

1. Connection pooler — PgBouncer, ProxySQL or RDS Proxy pools many app connections into few DB connections
2. Stricter app-side pooling — Smaller pools, more sharing
3. Bigger instance — More RAM allows more connections

Replication for read scaling

Writes go to the primary and replicate to replicas, so read capacity scales horizontally. Replication lag is typically sub-second but can spike under load.

Stale reads — Replicas trail by milliseconds to seconds; read-your-own-write needs routing back to the primary or a session-aware replica
Write amplification — More replicas means more replication bandwidth
Failover complexity — One replica gets promoted; never allow two primaries to take writes

Write capacity is the harder problem

Replicas help reads, not writes.

Vertical scaling — Bigger primary instance — Easy but capped; the biggest instance often costs $20K+/month
Sharding — Partition data across independent primaries — Operationally heavy
CQRS — Writes go to a write-optimized store; an async pipeline projects to query-optimized read stores — Flexible but complex
Distributed databases — Built-in sharding and replication — Higher cost; removes ops complexity

Hash-based — Partition by hash(key) — Even distribution, but range queries cross shards
Range-based — Partition by key ranges — Good for time-series; hot-shard risk when recent data is hot
Directory-based — Explicit key-to-shard mapping — Flexible; routing layer overhead

Sharding gotchas

Cross-shard transactions — 2PC or the saga pattern
Cross-shard joins — Denormalize, replicate dimension tables, or accept N+1 queries
Re-sharding — Double-write, backfill, cut-over; months long
Hot shards — Power-law skew; consistent hashing with virtual nodes helps

Modern alternatives

Managed databases — RDS, Aurora, Cloud SQL, Cloud Spanner, Cosmos DB — Less operational overhead; more automatic scaling
Serverless databases — Aurora Serverless, Cloud SQL auto-scaling, Cosmos DB — Scale automatically with load
Distributed SQL — CockroachDB, Spanner, YugabyteDB — Horizontal scaling with SQL semantics

Key concepts

Connection pool ceiling: Hard cap on concurrent DB connections. Use a connection pooler (PgBouncer, ProxySQL, RDS Proxy) when app pools exceed it.
Read replicas: Async copies of primary. Scale reads horizontally; writes still go to primary. Sub-second replication lag typical.
Sharding: Partition data across primaries. Hash, range, or directory based. Re-sharding is expensive; plan carefully.
CQRS: Separate write store and read store. Writes optimized for ingestion; reads optimized for queries. High flexibility, complex.
Distributed SQL: Spanner, Cockroach, YugabyteDB. Built-in sharding + replication with SQL semantics. Higher cost; less ops.
Hot shard: Power-law distributions create skew. One shard at 95% load while others at 30%. Consistent hashing + virtual nodes mitigate.

When to use it

Pre-launch — DB capacity planning for new service
High-traffic events — does the DB hold up at peak forecast?
Scaling pain — when current architecture hits its DB ceiling
Cost optimization — am I right-sized? Reserved DB capacity?

Interview questions

My Postgres is hitting connection limits. What do I do?
When do you shard a database vs scale up?
How do you plan for database failover during peak load?