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

---

Real-World Examples

Major companies use different scaling strategies based on their needs:

Scale Up: Small to Medium Applications

The Challenge: A growing SaaS application needs more database capacity but wants to keep architecture simple.

The Solution: Many startups use scale-up initially:

• Start: Single database server (4 CPU, 16GB RAM)
• Growth: Upgrade to larger server (16 CPU, 64GB RAM)
• Benefit: Simple, no code changes, immediate improvement

Example: A B2B SaaS application:

• Year 1: 1,000 users → Single server handles load
• Year 2: 10,000 users → Upgrade to 4x larger server
• Year 3: 50,000 users → Upgrade to 16x larger server

Impact: Handled 50x growth with scale-up. Simple operations. Cost-effective for moderate scale.

Limitation: Eventually hits hardware limits. At 100,000+ users, need scale-out.

Scale Out: Large-Scale Applications

The Challenge: Large applications need to scale beyond single server limits. Need high availability and fault tolerance.

The Solution: Major platforms use scale-out:

• Facebook: Thousands of MySQL servers, read replicas, sharding
• Amazon: Distributed databases, read replicas across regions
• Netflix: Multiple database clusters, read replicas for global access

Example: E-commerce platform scaling:

• Start: Single database (handles 1,000 orders/day)
• Growth: Add read replicas (handles 10,000 orders/day)
• Scale: Shard database (handles 1,000,000 orders/day)

Impact: Scales to millions of users. High availability (survives server failures). Global distribution.

Read Replicas: Instagram’s Architecture

The Challenge: Instagram serves billions of photo views daily. Most operations are reads (viewing photos, profiles, feeds).

The Solution: Instagram uses extensive read replicas:

• Primary: Handles writes (photo uploads, comments, likes)
• Replicas: Handle reads (photo views, profile views, feed generation)
• Scale: 100+ read replicas worldwide

Why Read Replicas?

• 99% of operations are reads
• Read replicas distribute load
• Primary focuses on writes

Impact: Handles billions of reads daily. Primary not overloaded. Fast read performance globally.

Connection Pooling: High-Traffic APIs

The Challenge: APIs handling thousands of requests per second. Creating database connections is expensive (50ms per connection).

The Solution: Major APIs use connection pooling:

• GitHub: Connection pool of 100 connections, reused across requests
• Stripe: Connection pool per API server, handles millions of requests
• Twitter: Connection pooling essential for high throughput

Example: API handling 10,000 requests/second:

• Without pool: 10,000 connections × 50ms = 500 seconds overhead
• With pool: 100 connections reused, 5ms per request = 50 seconds overhead
• 10x improvement

Impact: Reduced connection overhead by 90%. Handles 10x more requests. Lower latency.

Read-Write Splitting: E-commerce Platforms

The Challenge: E-commerce platforms have read-heavy workloads (product browsing) and write operations (orders, inventory updates).

The Solution: E-commerce platforms use read-write splitting:

• Writes: Go to primary (orders, inventory updates)
• Reads: Go to replicas (product catalog, search, recommendations)

Example: Product page load:

• Read: Product data from replica (fast, doesn’t block writes)
• Write: Order creation goes to primary (ensures consistency)

Impact: Product pages load faster. Order processing not blocked by reads. Better user experience.

---

Key Takeaways from Real Examples

1. Scale up first - Simple, works for moderate scale
2. Scale out for large scale - Needed for millions of users
3. Read replicas for read-heavy - Distribute read load, improve performance
4. Connection pooling essential - Reduces overhead, improves throughput
5. Read-write splitting - Optimize for different workloads

---

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

💡 Tip: Click dropdown to switch between languages

Python

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

💡 Tip: Click dropdown to switch between languages

Python

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?