Database Scaling Patterns: Replicas, Shards, Pools, and Query Tuning
Master database scaling with read replicas, sharding, materialized views, denormalization, connection pooling, and query optimization techniques.
Why Database Scaling Is Hard
Application servers are often easy to scale horizontally because they can be stateless. Databases are harder because they own durable state, consistency, indexes, transactions, locks, and replication.
Key idea: Scale databases by first reducing unnecessary work, then splitting reads, then splitting writes. Sharding is powerful, but it should not be your first move.
Scaling Decision Path
Start with evidence. A database can be slow because of missing indexes, too many connections, lock contention, poor query plans, hot rows, network latency, or storage saturation.
Read Replicas
Read replicas copy data from a primary database and serve read traffic.
| Benefit | Trade-off |
|---|---|
| Scales read throughput | Replication lag |
| Reduces primary load | Read-after-write inconsistency |
| Improves regional latency | More operational complexity |
| Supports analytics reads | Replica staleness |
Read-After-Write Strategies
| Strategy | How It Works |
|---|---|
| Read your writes from primary | Route fresh reads to primary for a short window |
| Session consistency token | Read from replica only after it catches up |
| Sticky read routing | Same user reads primary briefly after writing |
| UI delay or optimistic update | Show local state until replica catches up |
Replica lag is a product behavior: If a user updates a profile and immediately sees old data, that is not just a database detail. Design the read path intentionally.
Sharding
Sharding splits data across multiple databases so each shard owns a subset of the data.
Sharding Strategies
| Strategy | How It Works | Risk |
|---|---|---|
| Hash-based | hash(key) % shard_count | Rebalancing when shard count changes |
| Range-based | Key ranges per shard | Hot ranges and uneven growth |
| Directory-based | Lookup service maps key to shard | Directory becomes critical dependency |
| Geo-based | Data lives near users | Cross-region queries get harder |
| Tenant-based | Each tenant maps to shard | Large tenants can become hot |
Choosing a Shard Key
A good shard key:
- Appears in most queries.
- Spreads load evenly.
- Avoids hot partitions.
- Keeps transactions local.
- Has stable ownership.
Bad shard keys create expensive scatter-gather queries.
Rebalancing
Eventually, shards become uneven or you need more capacity. Rebalancing moves data.
| Approach | Notes |
|---|---|
| Consistent hashing | Reduces movement when adding nodes |
| Virtual shards | Many logical shards mapped to fewer physical nodes |
| Live migration | Copy data while serving traffic, then cut over |
| Dual writes | Temporarily write to old and new locations |
Rebalancing needs observability, backpressure, and rollback. It is a distributed systems project, not a quick schema change.
Materialized Views and Denormalization
Sometimes the best scaling move is to precompute expensive reads.
| Pattern | Best For | Trade-off |
|---|---|---|
| Materialized view | Expensive aggregate queries | Refresh complexity |
| Denormalized table | Fast read model | Write amplification |
| Search index | Text and faceted search | Eventually consistent |
| Analytics store | OLAP queries | Data pipeline complexity |
Use read models when product queries are stable and expensive to compute repeatedly.
Connection Pooling
Databases cannot handle unlimited connections. Each connection consumes memory and scheduling overhead.
Pooling Problems at Scale
| Problem | Fix |
|---|---|
| Too many app instances create too many connections | Use PgBouncer, ProxySQL, or managed poolers |
| Long transactions hold connections | Keep transactions short |
| Pool too small | Requests queue in app |
| Pool too large | Database thrashes |
| Serverless burst opens many connections | Use pooler or data API |
Pool size should be based on database capacity and query duration, not on arbitrary defaults.
Query Optimization
Query Plan Basics
Practical Rules
- Use
EXPLAINorEXPLAIN ANALYZE. - Add indexes for common filters, joins, and ordering.
- Avoid
SELECT *on hot paths. - Paginate with stable keys for large lists.
- Watch N+1 query patterns.
- Archive cold data when tables grow huge.
- Track slow queries continuously.
Index Trade-offs
| Benefit | Cost |
|---|---|
| Faster reads | Slower writes |
| Faster sorting | More disk |
| Better joins | More maintenance |
| Unique constraints | Potential write contention |
What to Remember for Interviews
- Measure before scaling: know whether the bottleneck is reads, writes, locks, CPU, IO, or connections.
- Read replicas scale reads: but introduce replica lag.
- Sharding scales writes: but complicates queries, transactions, and rebalancing.
- Precompute expensive reads: materialized views and denormalization trade write complexity for read speed.
- Connection pools protect databases: uncontrolled connections can take down a system.
Practice: Design database scaling for an order-history page with 100 million orders, read-heavy traffic, occasional writes, and enterprise tenant isolation.