Scaling Databases

Why Scale Databases?

As your application grows, your database becomes a bottleneck. More users mean more queries, and a single database server can only handle so much.

Simple Analogy

Think of scaling like a restaurant. One chef (single database) can only serve so many customers. Add more chefs (replicas) and a reservation system (connection pool), and you can serve many more customers efficiently!

---

Scaling Strategies

Vertical Scaling (Scale Up)

Vertical scaling means making your existing server bigger—more CPU, more RAM, faster disks.

Pros:

• ✅ Simple (just upgrade hardware)
• ✅ No code changes needed
• ✅ Works immediately

Cons:

• ❌ Expensive (bigger servers cost more)
• ❌ Hardware limits (can’t scale infinitely)
• ❌ Single point of failure

---

Horizontal Scaling (Scale Out)

Horizontal scaling means adding more servers to share the load.

Pros:

• ✅ Can scale infinitely (add more servers)
• ✅ Cost-effective (cheaper servers)
• ✅ High availability (if one fails, others continue)

Cons:

• ❌ More complex (need replication, load balancing)
• ❌ Requires code changes
• ❌ Data consistency challenges

---

Read Replicas

Read replicas are copies of the primary database that handle read operations. They replicate data from the primary and distribute read load.

How Read Replicas Work

Key Characteristics:

• Primary handles all writes
• Replicas handle reads
• Replication is asynchronous (eventual consistency)
• Load distribution across multiple servers

---

Read-Write Splitting

Read-write splitting routes writes to the primary and reads to replicas.

Benefits:

• ✅ Distributes read load
• ✅ Primary focuses on writes
• ✅ Better overall performance

---

Replication Lag

Replication lag is the delay between a write on the primary and when it appears on replicas.

Problem: Users might read stale data immediately after writing.

Solution: Read-after-write consistency—read from primary for a short time after writing.

---

Connection Pooling

Connection pooling maintains a cache of database connections that can be reused, reducing connection overhead.

The Problem: Connection Overhead

Connection overhead:

• Creating connection: ~20ms
• Authentication: ~10ms
• Closing connection: ~5ms
• Total overhead: ~35ms per query

With pooling: Reuse existing connections, overhead: ~1ms

---

How Connection Pooling Works

Pool Management:

• Min pool size: Keep minimum connections ready
• Max pool size: Maximum concurrent connections
• Idle timeout: Close idle connections after timeout
• Connection timeout: Wait time if pool is full

---

LLD ↔ HLD Connection

How database scaling affects your class design:

Connection Pool Implementation

Python

Connection Pool

from queue import Queue
from threading import Lock
import psycopg2
from contextlib import contextmanager

class ConnectionPool:
    def __init__(self, connection_string, min_size=5, max_size=20):
        self.connection_string = connection_string
        self.min_size = min_size
        self.max_size = max_size
        self.pool = Queue(maxsize=max_size)
        self.lock = Lock()
        self.active_connections = 0

        # Initialize pool with min connections
        for _ in range(min_size):
            conn = psycopg2.connect(connection_string)
            self.pool.put(conn)

    @contextmanager
    def get_connection(self):
        conn = None
        try:
            # Get connection from pool or create new one
            if not self.pool.empty():
                conn = self.pool.get_nowait()
            elif self.active_connections < self.max_size:
                conn = psycopg2.connect(self.connection_string)
                self.active_connections += 1
            else:
                # Wait for available connection
                conn = self.pool.get(timeout=5)

            yield conn
        finally:
            # Return connection to pool
            if conn:
                self.pool.put(conn)

    def close_all(self):
        while not self.pool.empty():
            conn = self.pool.get()
            conn.close()

Java

Connection Pool

import java.sql.*;
import java.util.concurrent.*;
import javax.sql.DataSource;

public class SimpleConnectionPool {
    private final String url;
    private final String username;
    private final String password;
    private final int minSize;
    private final int maxSize;
    private final BlockingQueue<Connection> pool;
    private final AtomicInteger activeConnections = new AtomicInteger(0);

    public SimpleConnectionPool(String url, String username, String password,
                                 int minSize, int maxSize) {
        this.url = url;
        this.username = username;
        this.password = password;
        this.minSize = minSize;
        this.maxSize = maxSize;
        this.pool = new LinkedBlockingQueue<>(maxSize);

        // Initialize pool
        for (int i = 0; i < minSize; i++) {
            pool.offer(createConnection());
        }
    }

    public Connection getConnection() throws SQLException {
        Connection conn = pool.poll();
        if (conn == null && activeConnections.get() < maxSize) {
            conn = createConnection();
            activeConnections.incrementAndGet();
        }
        if (conn == null) {
            try {
                conn = pool.poll(5, TimeUnit.SECONDS);
            } catch (InterruptedException e) {
                throw new SQLException("Timeout waiting for connection");
            }
        }
        return conn;
    }

    public void returnConnection(Connection conn) {
        if (conn != null) {
            pool.offer(conn);
        }
    }

    private Connection createConnection() {
        try {
            return DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

Read-Write Splitting Implementation

Python

Read-Write Splitting

class ReadWriteRouter:
    def __init__(self, primary_pool, replica_pools):
        self.primary_pool = primary_pool
        self.replica_pools = replica_pools
        self.replica_index = 0

    def execute(self, query, params=None, is_write=False):
        # Route writes to primary, reads to replicas
        if is_write or self._is_write_query(query):
            pool = self.primary_pool
        else:
            # Round-robin across replicas
            pool = self.replica_pools[self.replica_index]
            self.replica_index = (self.replica_index + 1) % len(self.replica_pools)

        with pool.get_connection() as conn:
            cursor = conn.cursor()
            cursor.execute(query, params)
            if is_write or self._is_write_query(query):
                conn.commit()
                return cursor.rowcount
            else:
                return cursor.fetchall()

    def _is_write_query(self, query):
        query_upper = query.strip().upper()
        return query_upper.startswith(('INSERT', 'UPDATE', 'DELETE', 'CREATE', 'DROP', 'ALTER'))

Java

Read-Write Splitting

import java.sql.*;
import java.util.*;

public class ReadWriteRouter {
    private final ConnectionPool primaryPool;
    private final List<ConnectionPool> replicaPools;
    private int replicaIndex = 0;

    public ReadWriteRouter(ConnectionPool primaryPool, List<ConnectionPool> replicaPools) {
        this.primaryPool = primaryPool;
        this.replicaPools = replicaPools;
    }

    public ResultSet executeQuery(String query, Object... params) throws SQLException {
        // Route reads to replicas
        ConnectionPool pool = getReplicaPool();
        Connection conn = pool.getConnection();
        PreparedStatement stmt = conn.prepareStatement(query);
        setParameters(stmt, params);
        return stmt.executeQuery();
    }

    public int executeUpdate(String query, Object... params) throws SQLException {
        // Route writes to primary
        Connection conn = primaryPool.getConnection();
        try {
            PreparedStatement stmt = conn.prepareStatement(query);
            setParameters(stmt, params);
            int result = stmt.executeUpdate();
            conn.commit();
            return result;
        } catch (SQLException e) {
            conn.rollback();
            throw e;
        } finally {
            primaryPool.returnConnection(conn);
        }
    }

    private ConnectionPool getReplicaPool() {
        // Round-robin across replicas
        ConnectionPool pool = replicaPools.get(replicaIndex);
        replicaIndex = (replicaIndex + 1) % replicaPools.size();
        return pool;
    }

    private void setParameters(PreparedStatement stmt, Object... params) throws SQLException {
        for (int i = 0; i < params.length; i++) {
            stmt.setObject(i + 1, params[i]);
        }
    }
}

---

Key Takeaways

Remember

• Scaling Strategies: Vertical (bigger server) vs Horizontal (more servers)
• Read Replicas: Copies that handle reads, improve performance, eventual consistency
• Read-Write Splitting: Route writes to primary, reads to replicas
• Replication Lag: Delay between primary write and replica update (100-500ms)
• Connection Pooling: Cache connections for reuse, 10x performance improvement
• LLD Implementation: Connection pool classes, read-write routers, handle replica lag
• Trade-offs: More replicas = better read performance but more complexity

---

What’s Next?

Now that you understand scaling, let’s explore sharding and partitioning to distribute data across multiple databases:

Next up: Sharding & Partitioning — Learn horizontal vs vertical partitioning and how to implement shard-aware repositories.

Practice Exercise

Design a connection pool for your application. What should the min/max sizes be? How would you handle connection failures? What about read-write splitting?