Database Replication Topologies — Master-Slave, Multi-Master, Active-Active

In this tutorial, you'll learn about Database Replication Topologies. We cover key concepts, practical examples, and best practices to help you understand and apply this topic effectively.

Database Replication topologies define how data is copied across database nodes — from single-leader (master-slave) for read scaling, to multi-leader (active-active) for multi-region writes, to leaderless Replication for maximum availability in Distributed Systems.

What You'll Learn

You'll understand single-leader, multi-leader, and leaderless Replication topologies, synchronous vs asynchronous Replication, Conflict Resolution strategies, failover mechanisms, and how to choose the right topology based on consistency, latency, and availability requirements.

Why It Matters

Choosing the wrong Replication topology causes data loss, high latency, or application downtime. Doda Browser uses multi-leader Replication across three geographic regions so users always write to their nearest data center; picking single-leader would add 200ms latency for half of all writes.

Real-World Use

An e-commerce platform used single-leader Replication with async Replication to a single replica. During a flash sale, the replica fell 30 seconds behind the leader, users saw "out of stock" for products that had available inventory, and the company lost $200,000 in sales before discovering the issue.

Replication Topologies Learning Path

flowchart LR
  A[Database Design] --> B[Database Replication]
  B --> C[Replication Topologies]
  C --> D[Distributed Systems]
  C:::current
  classDef current fill:#f90,color:#fff,stroke:#333,stroke-width:2px

Single-Leader Replication (Master-Slave)

The most common topology. One node (leader) accepts writes; one or more followers replicate changes for reads.

flowchart TD
    Client -->|Writes| Leader[(Leader
Read/Write)]
    Leader -->|Replication Stream| Follower1[(Follower 1
Read-Only)]
    Leader -->|Replication Stream| Follower2[(Follower 2
Read-Only)]
    Leader -->|Replication Stream| Follower3[(Follower 3
Read-Only)]
    Client -.->|Reads| Follower1
    Client -.->|Reads| Follower2
    style Leader fill:#d4edda
    style Follower1 fill:#fff3cd
    style Follower2 fill:#fff3cd
    style Follower3 fill:#fff3cd

Configuration

-- PostgreSQL: Configure streaming replication
-- On the leader (postgresql.conf)
wal_level = replica
max_wal_senders = 5
wal_keep_size = 1024  -- MB
synchronous_standby_names = 'FIRST 1 (replica1, replica2)'

-- On the follower (postgresql.conf)
primary_conninfo = 'host=leader-ip port=5432 user=replicator password=secret'
primary_slot_name = 'replica1'

-- Create a replication slot on the leader
SELECT pg_create_physical_replication_slot('replica1');

Expected behavior: The leader accepts writes and streams WAL changes to followers. Followers apply changes in real-time. Client reads can be distributed across followers for read scaling.

Synchronous vs Asynchronous Single-Leader

# Synchronous: Leader waits for replica acknowledgment
# Pros: Zero data loss on leader failure
# Cons: Higher write latency (network round-trip to replica)
# Use case: Financial transactions, critical data

# Asynchronous: Leader acknowledges immediately
# Pros: Lower latency, leader not slowed by replicas
# Cons: Small data loss window on leader crash
# Use case: Reporting, analytics, non-critical data

# PostgreSQL: Mix both — one sync replica, rest async
synchronous_standby_names = 'FIRST 1 (sync_replica)'
# This guarantees at least one replica has the data
# while other replicas don't slow writes

Multi-Leader Replication (Active-Active)

Multiple nodes accept writes and replicate to each other. Each leader is also a follower for the other leaders.

flowchart LR
    US[US Region
Leader A] <-->|Bi-directional
Replication| EU[EU Region
Leader B]
    US <-->|Bi-directional
Replication| ASIA[Asia Region
Leader C]
    EU <-->|Bi-directional
Replication| ASIA
    Client1[US Users] -->|Writes| US
    Client2[EU Users] -->|Writes| EU
    Client3[Asia Users] -->|Writes| ASIA
    style US fill:#d4edda
    style EU fill:#d4edda
    style ASIA fill:#d4edda

PostgreSQL Logical Replication

-- Multi-leader with PostgreSQL logical replication

-- On Leader A: Create a publication
CREATE PUBLICATION pub_leader_a FOR ALL TABLES;

-- On Leader A: Subscribe to Leader B
CREATE SUBSCRIPTION sub_leader_b
CONNECTION 'host=leader-b dbname=mydb user=replicator password=secret'
PUBLICATION pub_leader_b;

-- On Leader B: Create a publication
CREATE PUBLICATION pub_leader_b FOR ALL TABLES;

-- On Leader B: Subscribe to Leader A
CREATE SUBSCRIPTION sub_leader_a
CONNECTION 'host=leader-a dbname=mydb user=replicator password=secret'
PUBLICATION pub_leader_a;

Expected behavior: Both nodes accept writes. Changes to Leader A replicate to Leader B and vice versa. If a user updates the same row on both leaders simultaneously, a conflict occurs that must be resolved.

Conflict Resolution Strategies

-- MySQL multi-source replication: Handle conflicts with LWW
-- Configure each leader with different auto_increment offsets
-- Leader A: 1, 3, 5, 7...
auto_increment_increment = 2
auto_increment_offset = 1

-- Leader B: 2, 4, 6, 8...
auto_increment_increment = 2
auto_increment_offset = 2
-- This prevents auto-increment conflicts

-- For data conflicts, use timestamp-based resolution
UPDATE users SET name = 'Alice', updated_at = NOW()
WHERE id = 1 AND updated_at < '2026-06-22 10:00:00';

Leaderless Replication (No Primary)

Every node accepts reads and writes. Clients send reads to multiple nodes and resolve differences.

flowchart TD
    Client -->|Write to all| N1[Node 1]
    Client -->|Write to all| N2[Node 2]
    Client -->|Write to all| N3[Node 3]
    Client -->|Read from all + compare| N1
    Client -->|Read from all + compare| N2
    Client -->|Read from all + compare| N3
    N1 <-->|Gossip| N2
    N2 <-->|Gossip| N3
    N1 <-->|Gossip| N3

Dynamo-Style Quorum

# Leaderless replication with configurable consistency
# N = total replicas, W = write quorum, R = read quorum

class DynamoStyleReplicator:
    def __init__(self, nodes: list, N: int = 3, W: int = 2, R: int = 2):
        self.nodes = nodes
        self.N = N  # Replication factor
        self.W = W  # Write quorum (minimum nodes to confirm write)
        self.R = R  # Read quorum (minimum nodes for consistent read)

    def write(self, key: str, value: str) -> bool:
        """Write to N nodes, wait for W acknowledgments."""
        preferred_nodes = self._get_preference_list(key)
        acknowledgments = 0
        for node in preferred_nodes[:self.N]:
            try:
                node.put(key, value, version=self._get_version(key))
                acknowledgments += 1
                if acknowledgments >= self.W:
                    return True  # Write quorum satisfied
            except ConnectionError:
                continue
        return False  # Failed to reach write quorum

    def read(self, key: str) -> str:
        """Read from N nodes, return the latest version."""
        preferred_nodes = self._get_preference_list(key)
        versions = []
        for node in preferred_nodes[:self.N]:
            try:
                result = node.get(key)
                versions.append(result)
                if len(versions) >= self.R:
                    break  # Read quorum satisfied
            except ConnectionError:
                continue
        # Resolve conflicts: return the version with highest timestamp
        latest = max(versions, key=lambda v: v.timestamp)
        return latest.value

Expected behavior: With N=3, W=2, R=2, the system tolerates 1 node failure while maintaining consistency. Write succeeds when 2 of 3 nodes acknowledge. Read returns the latest version from 2 of 3 nodes.

Comparing Topologies

Choosing a Topology

Common Replication Topology Errors

1. Using Async Replication for Critical Writes

Async Replication means if the leader crashes before replicating, data is lost. Always use synchronous Replication for writes that cannot be lost.

2. Ignoring Conflict Resolution in Multi-Leader

Multi-leader setups will have conflicts. If you don't plan for Conflict Resolution, you'll get data corruption. Test conflict scenarios before production.

3. Single-Leader with Uneven Read Distribution

Adding 10 replicas doesn't give 10x read throughput if your load balancer distributes unevenly. Use connection poolers with read/write splitting (PgBouncer, ProxySQL, HAProxy).

4. Cross-Region Synchronous Replication

Synchronous Replication across continents adds 200-300ms to every write. Use sync Replication within a region, async between regions.

-- BAD: Synchronous replication across continents
-- Every write waits 200ms for a replica in Singapore
synchronous_standby_names = '*'

-- GOOD: Synchronous within region, async between regions
-- Within-region replica: synchronous
-- Cross-region replica: asynchronous (via cascading)

5. Not Testing Network Partitions

In a Network Partition, multi-leader systems diverge. When the network heals, they must reconcile. Test this scenario: disconnect two leaders, write different data to each, then reconnect.

6. Forgetting Connection String Management

After failover, applications still point to the failed leader. Use a connection proxy or DNS-based routing that tracks the current leader.

# Use a connection pooler that understands replication topology
# pgBouncer configuration with automatic failover
[databases]
mydb = host=leader1,leader2,leader3 port=5432 dbname=mydb \
       auth_user=replicator pool_mode=transaction

# ProxySQL for MySQL: automatic read/write splitting
mysql_servers:
  - { hostgroup_id: 0, hostname: "writer-1", port: 3306 }
  - { hostgroup_id: 1, hostname: "reader-1", port: 3306 }
  - { hostgroup_id: 1, hostname: "reader-2", port: 3306 }

mysql_query_rules:
  - { rule_id: 1, match_pattern: "^SELECT", destination_hostgroup: 1 }
  - { rule_id: 2, match_pattern: ".*", destination_hostgroup: 0 }

7. Assuming All Replicas Are Identical

Replicas may have different hardware specifications or versions. A slow replica can cause Replication lag for all others if synchronous Replication waits for the slowest replica.

Practice Questions

1. What is the main difference between single-leader and multi-leader Replication?

Single-leader allows writes only on one node; followers are read-only. Multi-leader allows writes on any node, which then replicate to each other. Single-leader is simpler but has a single point of failure for writes.

2. How does quorum-based consistency work in leaderless Replication?

With N replicas, W write nodes, and R read nodes: a write succeeds if at least W nodes acknowledge. A read returns the latest version if the read quorum overlaps with the write quorum (R + W > N). This guarantees at least one node with the latest data is in the read set.

3. When would you choose multi-leader over single-leader Replication?

Multi-region deployments where users should write to their closest data center, offline-first applications that sync later, or when zero-downtime maintenance requires multiple writable nodes.

4. What is the Conflict Resolution challenge in multi-leader Replication?

Two leaders may receive conflicting updates to the same data simultaneously. LWW (last-write-wins) can lose data. Application-merge requires manual handling. CRDTs work only for specific data types.

5. Challenge: Design a Replication topology for a global ride-sharing app.

Your app operates in 50 countries. Riders and drivers must see real-time availability. Rides must be recorded with zero data loss. Low latency is critical for matching. Answer: Use multi-leader Replication with a leader per region (US, EU, Asia). Within each region, use single-leader with synchronous Replication for ride data (zero loss). Use async Replication between regions for historical data. Use Redis with leaderless Replication for real-time location data. Conflict Resolution: LWW for location data, application-level merge for ride history.

FAQ

Try It Yourself

Set up a multi-leader Replication cluster with PostgreSQL:

1. Spin up three PostgreSQL instances (Docker recommended)
2. Configure each as both publisher and subscriber (logical Replication)
3. Create a table and insert data on each node
4. Observe data replicating to other nodes
5. Create a conflict: update the same row on two nodes simultaneously
6. Observe how PostgreSQL handles the conflict (or fails to)
7. Implement an application-level Conflict Resolution handler

What's Next

Built by the developers of Doda Browser, DodaZIP, and Durga Antivirus Pro.