System Design: Database Scaling & Replication
Master database scaling architectures, master-slave topologies, read replica offloading, sharding strategies, connection pooling, and automatic failover.
1. Database Replication Topologies
Database replication involves copying data from one physical database server to one or more secondary servers. Replication provides high availability, fault tolerance, and query performance scaling.
Master-Slave vs Master-Master Replication Topologies
System DesignComparing single-primary write delegation against multi-primary write acceptance models
Master-Slave (Primary-Replica) Architecture
A single Primary node accepts all write transactions (INSERT, UPDATE, DELETE) and streams write-ahead log updates to one or more Replica nodes. Replicas process read-only queries.
- Pros:Simple write coordination. Eliminates write conflict risks. Excellent for read-heavy workloads.
- Cons:Primary node remains a single point of failure for write operations until failover promotes a replica.
Master-Master (Multi-Primary) Architecture
Multiple primary database nodes accept write operations simultaneously and asynchronously synchronize data state across each other.
- Pros:High write availability across multiple geographical data centers.
- Cons:Complex write conflict resolution algorithms required when two nodes update the same row concurrently.
2. Synchronous vs Asynchronous Replication
The timing mechanism of how data modifications stream from the primary node to replicas determines data consistency guarantees and write latency.
Synchronous Replication
The primary node commits a write transaction only after receiving write confirmation from replica nodes. Guarantees zero data loss if primary crashes, but increases client write latency.
Asynchronous Replication
The primary node commits the write transaction immediately and confirms success to the client without waiting for replicas. Yields low write latency, but introduces replication lag and potential data loss on crash.
Semi-Synchronous Replication
The primary node waits for at least ONE replica to acknowledge log receipt before confirming write completion. Balances strong durability with acceptable write speeds.
3. Read Replicas
Read replicas are read-only copies of the primary database. By routing incoming SELECT queries away from the primary database, read replicas scale read throughput linearly.
Read Load Offloading
Applications configure database connection splitters that route SELECT queries to read replica pools while directing write queries to the primary database.
Replication Lag Consideration
Because asynchronous replicas update milliseconds behind the primary node, an immediate read right after a write (e.g. updating user bio then viewing profile) might display old state. Read-after-write consistency routes immediate post-write reads to the primary node.
Review how the Node.js database client wrapper below splits read and write queries across distinct connection pools.
PostgreSQL Connection Pool & Read-Write Query Router
Implementing automated query splitting between primary master pools and read replica pools
Press Run to execute the code and see output here.
4. Database Sharding Strategies
When data volume exceeds the storage or memory limits of a single database machine, database sharding partitions rows across multiple distinct database physical hosts. Choosing the right shard key routing strategy is crucial for avoiding single-shard hotspots.
Database Sharding Routing Models
System DesignComparing Range-Based, Hash-Based, Directory-Based, and Geo-Based shard routing strategies
Range-Based Sharding
Groups data rows into explicit contiguous numerical ranges (e.g. User IDs 1 to 100,000 on Shard 1, 100,001 to 200,000 on Shard 2). Easy to implement, but vulnerable to write hotspots on the newest active range.
Hash-Based Sharding
Applies a hash function to the shard key (e.g. hash(userId) % numShards) to distribute rows uniformly across all physical shard nodes. Prevents sequential hotspots.
Directory-Based Sharding
Maintains a centralized lookup table or service mapping shard key values to physical database hosts. Highly flexible for re-balancing, but introduces lookup service dependency.
Geo-Based Sharding
Partitions database records according to user physical geographic region (e.g. US, EU, Asia). Reduces network latency by keeping data geographically close to local users and satisfies data sovereignty regulations.
5. Database Federation
Federation (functional partitioning) splits database instances by distinct functional application domains instead of splitting individual table rows.
Domain Functional Splitting
Instead of placing User profiles, E-commerce Orders, and Product Catalogs in a single monolithic database, Federation assigns each domain its own dedicated database server instance.
- Benefits:Reduces monolithic database lock contention and allows independent domain scaling.
- Trade-off:Eliminates cross-table SQL JOIN queries between domains, requiring application-level data stitching.
6. Connection Pooling
Opening a new TCP database connection requires authentication handshakes, memory allocations, and backend thread spawns, consuming significant CPU overhead. Connection pooling maintains a warm cache of reusable database connections.
Pool Capacity Management
Connection pools maintain pre-established database connections. When an API request needs database access, it borrows an active connection from the pool, executes the query, and releases the connection back to the pool instantly.
Proxy Connection Poolers (PgBouncer)
In microservice environments with thousands of application instances, external connection proxy poolers like PgBouncer manage tens of thousands of client connections while keeping direct database connections at optimal limits.
7. Database Failover and Recovery
Database failover is the automated or manual process of promoting a standby replica node to primary master status when the active primary node experiences a hardware crash or network failure.
Heartbeat Monitoring & Consensus
Cluster orchestrators (Patroni, Orchestrator) continuously ping database nodes with health check heartbeats. If the primary node fails to respond across consecutive intervals, the orchestrator triggers failover.
Virtual IP & DNS Traffic Rerouting
During failover, the cluster updates virtual IP bindings or internal DNS records so application servers instantly point write traffic to the newly promoted primary node.
Split-Brain Prevention
Using distributed consensus algorithms (Raft, Etcd) to ensure network partitions do not lead to two nodes assuming primary status simultaneously.
Examine how the Shard Manager algorithm below handles hash-based key routing alongside automated standby failover detection.
Shard Key Router & Failover Health Check Handler
Implementing hash-based routing with automated failover detection and standby node rerouting
Press Run to execute the code and see output here.
Database Scaling & Replication Knowledge Verification
1. What is the main role of database read replicas?
2. How does synchronous database replication guarantee strong data consistency?
3. What characterizes Hash-Based database sharding?
4. What is the primary function of database connection pooling?
5. What occurs during automatic database failover?