Database Scaling

Read replicas distribute read traffic across multiple database copies.

How Read Replicas Work

Write Path:
Application → Primary Database (write)

Read Path:
Application → Read Replica (read)

Replication:
Primary → Replica 1 (async)
Primary → Replica 2 (async)
Primary → Replica 3 (async)

Read Replica Architecture

                    ┌─────────────┐
                    │Application  │
                    └──────┬──────
                           │
               ┌───────────┼───────────┐
               │           │           │
        ┌──────▼──┐  ┌─────▼───┐  ┌────▼─────┐
        │ Primary │  │ Replica │  │ Replica  │
        │ (write) │  │ (read)  │  │ (read)   │
        └────┬────┘  └─────────┘  └──────────┘
             │
        Replication
             │
        ┌────▼────┐
        │ Replica │
        │ (read)  │
        └─────────┘

Read Replica Benefits

Benefit	Description
Read scalability	Handle more read traffic
Reduced primary load	Primary focuses on writes
High availability	Replica takes over on failure
Geographic distribution	Replicas near users

Replication Lag

Problem: Async replication has delay

Primary: Write X = 1
Replica: Still has X = 0 (lag)
Client reads replica: Gets old value

Solutions:
1. Read-after-write consistency
2. Monitor replication lag
3. Route reads to primary when lag is high

Read Replica Configuration

-- PostgreSQL
CREATE REPLICA CONNINFO 'host=replica1 port=5432';

-- MySQL
CHANGE MASTER TO
  MASTER_HOST='primary',
  MASTER_USER='repl_user',
  MASTER_AUTO_POSITION=1;
START REPLICA;

When to Use Read Replicas

Use when:
- Read-heavy workloads (>80% reads)
- Need high read availability
- Can tolerate replication lag
- Geographic distribution needed

Avoid when:
- Write-heavy workloads
- Strong consistency required for reads
- Low latency reads critical

Sharding splits data across multiple databases for horizontal scaling.

How Sharding Works

Without Sharding:
All data in one database

With Sharding:
Shard 1: Users A-M
Shard 2: Users N-Z

Each shard is independent database

Sharding Architecture

                    ┌─────────────┐
                    │  Shard     │
                    │  Router    │
                    └──────┬──────
                           │
               ┌───────────┼───────────┐
               │           │           │
        ┌──────▼──┐  ┌─────▼───┐  ┌────▼─────┐
        │ Shard 1 │  │ Shard 2 │  │ Shard 3  │
        │ A-F     │  │ G-M     │  │ N-Z      │
        └─────────┘  └─────────┘  └──────────┘

Sharding Strategies

Strategy	How It Works	Pros	Cons
Range	Hash(key) % num_shards	Simple	Hotspots
Hash	Hash(key) % num_shards	Even distribution	Rebalancing hard
Directory	Lookup table	Flexible	Extra latency
Geographic	By region	Low latency	Uneven data

Consistent Hashing

Hash ring with virtual nodes:

Node A (0-30%)
Node B (30-60%)
Node C (60-100%)

Key K1 → hash(K1) → Node B
Key K2 → hash(K2) → Node A

Benefits:
- Minimal key movement on adding/removing nodes
- Even distribution

Sharding Challenges

1. Cross-shard queries
   - JOINs across shards difficult
   - Aggregations require scatter-gather

2. Distributed transactions
   - Two-phase commit
   - Saga pattern

3. Rebalancing
   - Adding shards requires data movement
   - Consistent hashing helps

4. Hotspots
   - Uneven data distribution
   - Shard key selection critical

When to Use Sharding

Use when:
- Data exceeds single database capacity
- Write throughput exceeds single database
- Need geographic distribution
- Horizontal scaling required

Avoid when:
- Data fits in single database
- Complex cross-shard queries needed
- Team lacks sharding experience
- Can use read replicas instead

Connection pooling reuses database connections to reduce overhead.

Why Connection Pooling

Without Pooling:
Request → Create Connection → Use → Close → Response
        (100ms overhead)     (10ms)
Total: 110ms per request

With Pooling:
Request → Get Pooled Connection → Use → Return → Response
        (1ms overhead)           (10ms)
Total: 11ms per request

10x improvement!

Connection Pool Architecture

Application Servers
    │
    ├── Connection Pool (min=5, max=20)
    │   ├── Conn 1 (active)
    │   ├── Conn 2 (active)
    │   ├── Conn 3 (idle)
    │   ├── Conn 4 (idle)
    │   └── Conn 5 (idle)
    │
    └── Database

Pool Configuration

# HikariCP (Java)
maximumPoolSize: 20
minimumIdle: 5
connectionTimeout: 30000  # 30 seconds
idleTimeout: 600000       # 10 minutes
maxLifetime: 1800000      # 30 minutes

# Node.js (pg-pool)
max: 20
min: 5
idleTimeoutMillis: 30000
connectionTimeoutMillis: 2000

Pool Sizing Formula

Connections per instance = (Core count * 2) + Effective spindle count

Example:
- 4 CPU cores
- SSD (1 effective spindle)
- Connections = (4 * 2) + 1 = 9

Total connections = connections_per_instance * num_instances

Connection Pool Monitoring

Monitor:
- Active connections
- Idle connections
- Waiting threads
- Connection creation rate
- Connection timeout rate

Alert if:
- Pool exhaustion (all connections active)
- High wait time
- Connection leaks

Connection Pool Best Practices

1. Right-size the pool: Too many = resource waste, too few = waiting
2. Set timeouts: Prevent hung connections
3. Monitor pool stats: Track usage patterns
4. Use PgBouncer/ProxySQL: External connection pooling
5. Close connections properly: Prevent leaks