Home/Learn/System Design/Database Replication

Database Replication

Intermediate

Data Management

Database replication copies data from a primary (master) to one or more replicas (slaves). It improves read throughput, provides fault tolerance, and enables geographic distribution.

Overview

Database replication maintains copies of data on multiple servers. The most common setup is single-leader (master-slave): all writes go to the primary, which propagates changes to read replicas via a replication log (WAL in PostgreSQL, binlog in MySQL). Read replicas handle read queries, offloading the primary. This improves read throughput linearly with the number of replicas. If the primary fails, a replica is promoted (failover). Replication can be synchronous (replica confirms before the write is acknowledged — strong consistency, higher latency) or asynchronous (write acknowledged immediately — lower latency, potential data loss on failover). Multi-leader replication allows writes to multiple nodes (useful for multi-region), but requires conflict resolution. Leaderless replication (Cassandra, DynamoDB) allows reads and writes to any node, using quorum reads/writes and anti-entropy for consistency.

Single-Leader Replication

All writes go to one primary node. The primary streams changes to read replicas asynchronously (or synchronously). Applications direct reads to replicas and writes to the primary.

Architecture + Java — read/write splitting

// Single-leader (master-slave) replication
//
// Clients
//   │ writes          │ reads
//   ▼                 ▼
// ┌─────────┐    ┌──────────┐
// │ Primary  │───→│ Replica 1│  (async replication)
// │ (Master) │───→│ Replica 2│
// │          │───→│ Replica 3│
// └─────────┘    └──────────┘
//
// MySQL binlog replication
// PostgreSQL WAL streaming replication

// Spring Boot read/write splitting
@Configuration
public class DataSourceConfig {

    @Bean @Primary
    public DataSource writeDataSource() {
        return createDataSource("jdbc:mysql://primary:3306/app");
    }

    @Bean
    public DataSource readDataSource() {
        return createDataSource("jdbc:mysql://replica:3306/app");
    }
}

// @Transactional(readOnly = true) → routes to read replica
// @Transactional                  → routes to primary

Sync vs Async Replication

Synchronous replication ensures replicas have the latest data but increases write latency. Asynchronous replication is faster but replicas may lag behind the primary.

Conceptual + Config — replication modes

// Synchronous: primary waits for replica ACK before confirming write
// Write → Primary → Replica ACK → Client ACK
// Pros: no data loss on failover, strong consistency
// Cons: write latency increased by network round-trip, blocked if replica down

// Asynchronous: primary confirms write immediately
// Write → Primary → Client ACK ... (replica catches up later)
// Pros: low write latency, primary not blocked
// Cons: replication lag, data loss if primary fails before replication

// Semi-synchronous (MySQL): at least ONE replica ACKs synchronously
// Balance: one guaranteed replica, minimal latency impact

// PostgreSQL: synchronous_commit levels
synchronous_commit = on          # wait for WAL flush on primary
synchronous_commit = remote_write # wait for replica to receive WAL
synchronous_commit = remote_apply # wait for replica to apply WAL (strongest)
synchronous_commit = off          # async (fastest, risk of loss)

// Replication lag monitoring
// MySQL: SHOW SLAVE STATUS → Seconds_Behind_Master
// PostgreSQL: pg_stat_replication → replay_lag

Failover & Multi-Leader

Automatic failover promotes a replica to primary when the leader fails. Multi-leader replication allows writes to multiple nodes, enabling multi-region active-active, but requires conflict resolution.

Conceptual — failover and multi-leader

// Automatic failover
// 1. Health check detects primary failure
// 2. Elect most up-to-date replica as new primary
// 3. Redirect writes to new primary
// 4. Reconfigure remaining replicas to follow new primary

// Multi-leader: each region has its own primary
//
//  US-East Primary ◄──────► EU-West Primary
//       │                        │
//       ▼                        ▼
//  US Replicas              EU Replicas
//
// Conflict resolution strategies:
// 1. Last-Write-Wins (LWW) — timestamp-based, simple but can lose data
// 2. Application-level — merge conflicts in business logic
// 3. CRDTs — conflict-free replicated data types (counters, sets)

// AWS RDS Multi-AZ: synchronous standby in another AZ
//   Automatic failover in ~60-120 seconds
// AWS Aurora: up to 15 read replicas, ~10ms replication lag
//   Failover in ~30 seconds

Key Points to Remember

1Single-leader replication: all writes to primary, reads from replicas — simplest and most common.
2Async replication is fast but risks data loss; sync replication is safe but slower.
3Replication lag means reads from replicas may return stale data — design for eventual consistency.
4Automatic failover promotes a replica to primary — monitor replication lag to pick the best candidate.
5Multi-leader enables multi-region writes but requires conflict resolution (LWW, CRDTs, app-level).

Interview Questions

What is the difference between synchronous and asynchronous replication?

EasyInfosys

How does read/write splitting improve database performance?

EasyTCS

What happens when a primary database fails? Explain the failover process.

MediumAmazon

How do you handle replication lag in a read-heavy application?

MediumFlipkart

Design a multi-region active-active database setup with conflict resolution.

HardGoogle

Ask Aria about Database Replication

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

Database Sharding

SQL vs NoSQL