Database Replication

Leader and followers

• More read capacity: most apps read far more than they write; add followers to serve the reads.
• Availability: if the leader dies, a follower is promoted (failover).
• Locality: put a follower near users in another region for fast local reads.
• Safe heavy queries: run analytics and backups on a follower without slowing the leader.

Synchronous or asynchronous

Replication lag

Asynchronous followers are usually milliseconds behind, but under load or network trouble it can be seconds or more. That causes visible bugs:

Fixes for read-your-own-writes: read from the leader for anything the user just changed (or for a few seconds after any write by that user), or track the log position of the user's last write and only use followers that have reached it.

import java.util.*;

class ReadRouter {
    private final double stickySeconds;
    private final Map<String, Double> lastWrite = new HashMap<>();

    ReadRouter(double stickySeconds) {
        this.stickySeconds = stickySeconds;
    }

    synchronized void recordWrite(String userId, double now) {
        lastWrite.put(userId, now);
    }

    synchronized String readTarget(String userId, double now) {
        Double last = lastWrite.get(userId);
        if (last != null && now - last < stickySeconds) {
            return "leader";
        }
        return "follower";
    }
}

import threading

class ReadRouter:
    def __init__(self, sticky_seconds):
        self.sticky = sticky_seconds
        self.last_write = {}  # user id -> time of their last write
        self.lock = threading.Lock()

    def record_write(self, user_id, now):
        with self.lock:
            self.last_write[user_id] = now

    def read_target(self, user_id, now):
        with self.lock:
            last = self.last_write.get(user_id)
            if last is not None and now - last < self.sticky:
                return "leader"
            return "follower"

Failover and multi-leader

Failover sounds simple (promote a follower) but has sharp edges: detecting that the leader is really dead and not just slow, choosing the most up-to-date follower, redirecting writes, and making sure the old leader does not come back still thinking it is the leader (split brain). Managed databases automate this, usually in tens of seconds.

Multi-leader replication accepts writes in several places (for example one leader per region) and syncs them. Writes are fast everywhere, but two leaders can change the same record at once, so you need a conflict resolution rule: last write wins (loses data), merge the values, or let the application decide. Leaderless systems (Dynamo-style, such as Cassandra) write to several replicas and use quorums instead.

Common mistakes

• Reading from followers right after a write and showing users stale data.
• Treating followers as backups: a bad DELETE replicates to all of them instantly. Keep real backups too.
• Assuming failover loses nothing while using asynchronous replication.
• Choosing multi-leader without a plan for conflicts.

Interview questions

Try answering out loud before you open each one.

Check yourself

Related topics

• Availability Patterns: Fail-over and ReplicationAvailability and Consistency
• Consistency PatternsAvailability and Consistency
• Sharding (Partitioning)Databases

Further reading

• PostgreSQL documentation: High availability, load balancing, and replication PostgreSQL
• Working with DB instance read replicas Amazon RDS documentation