Scaling Databases
Why Scale Databases?
Section titled “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.
Scaling Strategies
Section titled “Scaling Strategies”Vertical Scaling (Scale Up)
Section titled “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)
Section titled “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
Section titled “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
Section titled “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
Section titled “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
Section titled “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
Section titled “Connection Pooling”Connection pooling maintains a cache of database connections that can be reused, reducing connection overhead.
The Problem: Connection Overhead
Section titled “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
Section titled “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
Section titled “LLD ↔ HLD Connection”How database scaling affects your class design:
Connection Pool Implementation
Section titled “Connection Pool Implementation”1from queue import Queue2from threading import Lock3import psycopg24from contextlib import contextmanager5
6class ConnectionPool:7 def __init__(self, connection_string, min_size=5, max_size=20):8 self.connection_string = connection_string9 self.min_size = min_size10 self.max_size = max_size11 self.pool = Queue(maxsize=max_size)12 self.lock = Lock()13 self.active_connections = 014
15 # Initialize pool with min connections16 for _ in range(min_size):17 conn = psycopg2.connect(connection_string)18 self.pool.put(conn)19
20 @contextmanager21 def get_connection(self):22 conn = None23 try:24 # Get connection from pool or create new one25 if not self.pool.empty():26 conn = self.pool.get_nowait()27 elif self.active_connections < self.max_size:28 conn = psycopg2.connect(self.connection_string)29 self.active_connections += 130 else:31 # Wait for available connection32 conn = self.pool.get(timeout=5)33
34 yield conn35 finally:36 # Return connection to pool37 if conn:38 self.pool.put(conn)39
40 def close_all(self):41 while not self.pool.empty():42 conn = self.pool.get()43 conn.close()1import java.sql.*;2import java.util.concurrent.*;3import javax.sql.DataSource;4
5public class SimpleConnectionPool {6 private final String url;7 private final String username;8 private final String password;9 private final int minSize;10 private final int maxSize;11 private final BlockingQueue<Connection> pool;12 private final AtomicInteger activeConnections = new AtomicInteger(0);13
14 public SimpleConnectionPool(String url, String username, String password,15 int minSize, int maxSize) {16 this.url = url;17 this.username = username;18 this.password = password;19 this.minSize = minSize;20 this.maxSize = maxSize;21 this.pool = new LinkedBlockingQueue<>(maxSize);22
23 // Initialize pool24 for (int i = 0; i < minSize; i++) {25 pool.offer(createConnection());26 }27 }28
29 public Connection getConnection() throws SQLException {30 Connection conn = pool.poll();31 if (conn == null && activeConnections.get() < maxSize) {32 conn = createConnection();33 activeConnections.incrementAndGet();34 }35 if (conn == null) {36 try {37 conn = pool.poll(5, TimeUnit.SECONDS);38 } catch (InterruptedException e) {39 throw new SQLException("Timeout waiting for connection");40 }41 }42 return conn;43 }44
45 public void returnConnection(Connection conn) {46 if (conn != null) {47 pool.offer(conn);48 }49 }50
51 private Connection createConnection() {52 try {53 return DriverManager.getConnection(url, username, password);54 } catch (SQLException e) {55 throw new RuntimeException(e);56 }57 }58}Read-Write Splitting Implementation
Section titled “Read-Write Splitting Implementation”1class ReadWriteRouter:2 def __init__(self, primary_pool, replica_pools):3 self.primary_pool = primary_pool4 self.replica_pools = replica_pools5 self.replica_index = 06
7 def execute(self, query, params=None, is_write=False):8 # Route writes to primary, reads to replicas9 if is_write or self._is_write_query(query):10 pool = self.primary_pool11 else:12 # Round-robin across replicas13 pool = self.replica_pools[self.replica_index]14 self.replica_index = (self.replica_index + 1) % len(self.replica_pools)15
16 with pool.get_connection() as conn:17 cursor = conn.cursor()18 cursor.execute(query, params)19 if is_write or self._is_write_query(query):20 conn.commit()21 return cursor.rowcount22 else:23 return cursor.fetchall()24
25 def _is_write_query(self, query):26 query_upper = query.strip().upper()27 return query_upper.startswith(('INSERT', 'UPDATE', 'DELETE', 'CREATE', 'DROP', 'ALTER'))1import java.sql.*;2import java.util.*;3
4public class ReadWriteRouter {5 private final ConnectionPool primaryPool;6 private final List<ConnectionPool> replicaPools;7 private int replicaIndex = 0;8
9 public ReadWriteRouter(ConnectionPool primaryPool, List<ConnectionPool> replicaPools) {10 this.primaryPool = primaryPool;11 this.replicaPools = replicaPools;12 }13
14 public ResultSet executeQuery(String query, Object... params) throws SQLException {15 // Route reads to replicas16 ConnectionPool pool = getReplicaPool();17 Connection conn = pool.getConnection();18 PreparedStatement stmt = conn.prepareStatement(query);19 setParameters(stmt, params);20 return stmt.executeQuery();21 }22
23 public int executeUpdate(String query, Object... params) throws SQLException {24 // Route writes to primary25 Connection conn = primaryPool.getConnection();26 try {27 PreparedStatement stmt = conn.prepareStatement(query);28 setParameters(stmt, params);29 int result = stmt.executeUpdate();30 conn.commit();31 return result;32 } catch (SQLException e) {33 conn.rollback();34 throw e;35 } finally {36 primaryPool.returnConnection(conn);37 }38 }39
40 private ConnectionPool getReplicaPool() {41 // Round-robin across replicas42 ConnectionPool pool = replicaPools.get(replicaIndex);43 replicaIndex = (replicaIndex + 1) % replicaPools.size();44 return pool;45 }46
47 private void setParameters(PreparedStatement stmt, Object... params) throws SQLException {48 for (int i = 0; i < params.length; i++) {49 stmt.setObject(i + 1, params[i]);50 }51 }52}Key Takeaways
Section titled “Key Takeaways”What’s Next?
Section titled “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.