Database Scaling Strategies: Sharding, Replication, and Caching

In This Guide

• 1. Signs You Need to Scale
• 2. Vertical Scaling — The Quick Fix
• 3. Read Replicas — 80% of Your Reads, Offloaded
• 4. Connection Pooling — The Overlooked Bottleneck
• 5. Caching Layers — Don't Hit the Database at All
• 6. Sharding — The Nuclear Option
• 7. Query Optimization — Free Performance
• 8. The Scaling Playbook — What to Do When
• 9. Frequently Asked Questions

How Read Replicas Work

Application
    │
    ├── WRITE (INSERT/UPDATE/DELETE)
    │       └── Primary Database
    │               │
    │               ├── Async replication ──→ Replica 1 (analytics queries)
    │               ├── Async replication ──→ Replica 2 (API read traffic)
    │               └── Async replication ──→ Replica 3 (search/reporting)
    │
    └── READ (SELECT)
            └── Load Balancer → Replica 1, 2, or 3

Application-Level Read/Write Splitting

// Database router — Laravel example
class DatabaseRouter {
    public function select($query) {
        // Route reads to replica
        return DB::connection('replica')->select($query);
    }

    public function insert($query, $bindings) {
        // Route writes to primary
        return DB::connection('primary')->insert($query, $bindings);
    }
}

// Django — built-in router
DATABASES = {
    'default': {'HOST': 'primary.db.internal'},
    'replica': {'HOST': 'replica.db.internal'},
}
DATABASE_ROUTERS = ['myapp.routers.ReadReplicaRouter']

// Spring Boot — @Transactional(readOnly = true) routes to replica

PgBouncer Configuration

[databases]
myapp = host=primary.db.internal port=5432 dbname=myapp

[pgbouncer]
listen_port = 6432
listen_addr = 0.0.0.0

; Transaction pooling — connection returned after each transaction
pool_mode = transaction

; 20 actual database connections serve 2,000 app connections
default_pool_size = 20
max_client_conn = 2000

; Reserve connections for admin
reserve_pool_size = 5
reserve_pool_timeout = 3

Cache-Aside Pattern — Redis Example

async function getUser(userId) {
    const cacheKey = `user:${userId}`;

    // 1. Check cache
    const cached = await redis.get(cacheKey);
    if (cached) return JSON.parse(cached);

    // 2. Cache miss — query database
    const user = await db.query('SELECT * FROM users WHERE id = $1', [userId]);

    // 3. Store in cache with TTL (5 minutes)
    await redis.setex(cacheKey, 300, JSON.stringify(user));

    return user;
}

// Invalidate on write
async function updateUser(userId, data) {
    await db.query('UPDATE users SET ... WHERE id = $1', [userId, ...data]);
    await redis.del(`user:${userId}`);  // Delete, don't update — avoids race conditions
}

Consistent Hashing — Minimizing Resharding Pain

// Naive hashing — adding a shard moves ALL data
shard = hash(user_id) % 4   // user 12345 → shard 1
shard = hash(user_id) % 5   // user 12345 → shard 0 (DIFFERENT!)

// Consistent hashing — adding a shard moves ~1/N data
class ConsistentHash {
    constructor(nodes, virtualNodes = 150) {
        this.ring = new SortedMap();
        nodes.forEach(node => {
            for (let i = 0; i < virtualNodes; i++) {
                const hash = md5(`${node}:${i}`);
                this.ring.set(hash, node);
            }
        });
    }

    getShard(key) {
        const hash = md5(key);
        // Find first node clockwise on the ring
        const entry = this.ring.firstAfter(hash) || this.ring.first();
        return entry.value;
    }
}

Index Strategy — The Biggest Win

-- BEFORE: Full table scan (120 seconds on 50M rows)
SELECT * FROM orders WHERE customer_id = 12345 AND status = 'active';

-- EXPLAIN shows: Seq Scan on orders (cost=0.00..1847293.00 rows=50000000)

-- FIX: Composite index matching the WHERE clause
CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);

-- AFTER: Index scan (2ms)
-- EXPLAIN shows: Index Scan using idx_orders_customer_status (cost=0.56..8.72 rows=15)

-- RULE: Column order in composite index matters!
-- Put equality conditions first, range conditions last
CREATE INDEX idx_orders_date ON orders(status, customer_id, created_at DESC);
-- Works for: WHERE status = 'active' AND customer_id = 123 AND created_at > '2026-01-01'

Related Articles