Database Replication

---

Definition

---

Replication Types

---

Replication Topologies

---

Replication Lag

// REPLICATION LAG: Delay between primary and replicas

// Scenario:
// 1. User updates profile on primary
// 2. User reads from replica (hasn't received update yet)
// 3. User sees stale data

// SOLUTIONS:

// 1. Read-your-writes consistency
// Route user's reads to primary after their writes
class UserService {
    void updateProfile(User user, Profile profile) {
        primaryDb.update(user, profile);
        // Set cookie/session flag
        session.setAttribute("recentWrite", System.currentTimeMillis());
    }

    Profile getProfile(User user) {
        // If recent write, read from primary
        Long recentWrite = session.getAttribute("recentWrite");
        if (recentWrite != null &&
            System.currentTimeMillis() - recentWrite < 10000) {
            return primaryDb.getProfile(user);
        }
        return replicaDb.getProfile(user);
    }
}

// 2. Monotonic reads
// Same user always reads from same replica
// Prevents going "back in time"

// 3. Consistent prefix reads
// Ensure related writes appear in order
// If A→B→C on primary, replica shows A→B→C (not B→C→A)

// MONITORING REPLICATION LAG
// PostgreSQL
SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn,
       replay_lsn, replay_lag
FROM pg_stat_replication;

// MySQL
SHOW SLAVE STATUS\G
// Look for: Seconds_Behind_Master

---

Failover

---

Conflict Resolution

// MULTI-LEADER CONFLICTS
// When same data is modified on different leaders

// Example conflict:
// User edits title on Leader A: "Hello World"
// User edits title on Leader B: "Hello Universe"
// When they sync, which wins?

// RESOLUTION STRATEGIES:

// 1. Last-Write-Wins (LWW)
// Timestamp-based, later write wins
class Document {
    String content;
    long timestamp;

    Document merge(Document other) {
        return this.timestamp > other.timestamp ? this : other;
    }
}

// 2. Merge function
// Application-specific merge logic
class Counter {
    Map<String, Integer> values; // node -> value

    int getValue() {
        // Sum all node values
        return values.values().stream().mapToInt(i -> i).sum();
    }

    void increment(String nodeId) {
        values.merge(nodeId, 1, Integer::sum);
    }
}

// 3. Custom resolution
// Present conflict to user or use business rules
class DocumentService {
    Document resolve(Document local, Document remote) {
        if (local.equals(remote)) return local;

        // Auto-merge if possible
        if (canAutoMerge(local, remote)) {
            return autoMerge(local, remote);
        }

        // Flag for manual resolution
        return createConflictRecord(local, remote);
    }
}

// 4. CRDTs (Conflict-free Replicated Data Types)
// Data structures that mathematically guarantee convergence
// Examples: G-Counter, PN-Counter, LWW-Register, OR-Set

---

Tips & Tricks

---