Database Replication: Master-Slave, Multi-Master, and Streaming

Database replication copies data from one database server to another. It is the foundation of high availability, read scaling, disaster recovery, and geographic distribution. Choosing the right replication strategy depends on your consistency, latency, and durability requirements.

Synchronous vs Asynchronous

Synchronous Replication

The primary waits for at least one replica to confirm the write before acknowledging the client:

-- PostgreSQL synchronous replication
ALTER SYSTEM SET synchronous_standby_names = 'FIRST 1 (replica1, replica2)';

The transaction commits only after both the primary and the designated synchronous replica have written the WAL to disk. This guarantees zero data loss if the primary fails, at the cost of increased write latency proportional to the network round trip to the replica.

Asynchronous Replication

The primary commits immediately and sends changes to replicas asynchronously:

-- PostgreSQL asynchronous replication
ALTER SYSTEM SET synchronous_standby_names = '';

Asynchronous replication is the default in MySQL, PostgreSQL, and most databases. The replica may lag behind the primary by some number of transactions. If the primary fails before the replica receives the latest changes, data is lost.

Master-Slave Replication

One primary (master) handles writes; one or more replicas (slaves) handle reads.

Clients → [Master] ←→ [Replica 1]
                        ←→ [Replica 2]
                        ←→ [Replica 3]

Read Scaling

Add replicas to distribute read traffic. Each replica has a complete copy of the data:

import psycopg2
from random import choice

replicas = ["replica1:5432", "replica2:5432", "replica3:5432"]

def read_query(sql, params=None):
    conn = psycopg2.connect(host=choice(replicas), dbname="mydb")
    cur = conn.cursor()
    cur.execute(sql, params)
    return cur.fetchall()

def write_query(sql, params=None):
    conn = psycopg2.connect(host="master:5432", dbname="mydb")
    cur = conn.cursor()
    cur.execute(sql, params)
    conn.commit()

Automatic Failover

When the master fails, a replica is promoted to master:

# PostgreSQL — promote replica to master
pg_ctl promote -D /var/lib/postgresql/data

# Or using Patroni for automatic failover
patronictl -c /etc/patroni.yml failover --master postgresql-0 --candidate postgresql-1

The promoted replica becomes the new master, and remaining replicas are reconfigured to follow it. The old master, if it recovers, is re-added as a replica.

Multi-Master Replication

Multiple nodes accept writes simultaneously. Each node replicates its changes to the others.

Active-Passive

One master handles writes; a standby master is ready to take over:

-- MySQL Group Replication
CHANGE MASTER TO MASTER_HOST='node2' FOR CHANNEL 'group_replication';

Active-Active

All nodes handle both reads and writes:

-- Bidirectional replication between two MySQL instances
-- Node A → Node B
CHANGE MASTER TO MASTER_HOST='node-b' FOR CHANNEL 'a_to_b';
-- Node B → Node A
CHANGE MASTER TO MASTER_HOST='node-a' FOR CHANNEL 'b_to_a';

Conflict Resolution

When two masters accept conflicting writes, one must win:

| Strategy | Description | Winner | |—|

Replication Topologies

Primary-Replica (Single Leader)

One primary handles all writes; replicas apply changes asynchronously or synchronously. This is the simplest topology and the default in PostgreSQL, MySQL, and SQL Server. The primary is a single point of failure — automatic failover requires additional tooling (Patroni, Orchestrator, Always On Availability Groups).

Multi-Leader

Multiple nodes accept writes and replicate changes to each other. Useful for multi-datacenter deployments where writing to a local leader reduces latency. Conflict resolution is the main challenge — last-write-wins, CRDTs, or application-level merge logic are common approaches.

Peer-to-Peer (Multi-Master)

All nodes accept writes and replicate to all other nodes. This full mesh topology handles node failures gracefully but conflict resolution complexity grows with the number of nodes. Tools like MySQL Group Replication and Galera Cluster implement peer-to-peer replication.

Synchronous vs Asynchronous Replication

Synchronous replication waits for at least one replica to confirm the write before acknowledging the client. Provides strongest durability guarantee (no data loss) but increases write latency. PostgreSQL synchronous_commit = ‘on’ waits for one synchronous standby.

Asynchronous replication acknowledges the client immediately and replicates in the background. Lower write latency but risks data loss if the primary fails before replication completes. Acceptable for analytics, reporting, and read replicas where some data loss is tolerable.

Replication Lag Monitoring

Replication lag measures the delay between a write on the primary and its appearance on replicas. High lag leads to stale reads and increased data loss risk on failover:

-- PostgreSQL replication lag
SELECT pid, application_name, state,
       pg_size_pretty(pg_wal_lsn_diff(
           pg_current_wal_lsn(), replay_lsn)) AS lag_bytes
FROM pg_stat_replication;

Monitor lag in your database monitoring system. Investigate causes: network latency, slow replica hardware, long-running transactions on the primary, or write-heavy workloads overwhelming the WAL stream.

FAQ

What is the difference between SQL and NoSQL? SQL databases use structured schemas with tables, relationships, and ACID transactions. NoSQL databases offer flexible schemas, horizontal scaling, and various data models (document, key-value, graph, column-family). Choose based on data structure and access patterns.

When should I use an index? Index queries filtering on the indexed column(s), particularly for WHERE clauses, JOINs, and ORDER BY. Avoid over-indexing — each index slows writes and consumes storage. Monitor slow queries to identify indexing opportunities.

What is database normalization? Normalization organizes data to reduce redundancy and improve integrity. First Normal Form (1NF) eliminates duplicate columns. Second Normal Form (2NF) removes partial dependencies. Third Normal Form (3NF) removes transitive dependencies.

How do I choose between PostgreSQL and MySQL? PostgreSQL offers more advanced features (JSONB, full-text search, custom types, better concurrency). MySQL is simpler to configure and widely available. Both are excellent choices; PostgreSQL is often preferred for complex applications.

What is a deadlock in databases? A deadlock occurs when two transactions each hold locks the other needs. The database detects deadlocks and kills one transaction automatically. Reduce deadlocks by accessing tables in the same order across transactions and keeping transactions short.

—|—| | Last writer wins | Highest timestamp wins | LWW | | CRDTs | Conflict-free replicated data types | Merge | | Application-level | Application resolves conflicts | Custom logic | | Avoidance | Partition writes by key per master | No conflicts |

CRDTs (Conflict-free Replicated Data Types) use mathematical structures that merge without conflicts. Examples include increment-only counters, grow-only sets, and last-writer-wins registers. Riak and Redis (with CRDT modules) support CRDT-based replication.

Streaming Replication

Changes are streamed continuously rather than batched. PostgreSQL’s streaming replication sends WAL (Write-Ahead Log) segments as they are generated.

# PostgreSQL primary configuration
wal_level = replica
max_wal_senders = 5
wal_keep_size = 1024  # MB

# PostgreSQL replica configuration
primary_conninfo = 'host=primary port=5432 user=replicator'

Logical Replication

Logical replication streams changes at the row level, not the physical WAL level. This enables selective replication (specific tables), cross-version replication, and heterogeneous targets.

-- Publisher (source)
CREATE PUBLICATION my_pub FOR TABLE users, orders;

-- Subscriber (target)
CREATE SUBSCRIPTION my_sub CONNECTION 'host=primary dbname=mydb' PUBLICATION my_pub;

Logical replication supports cascading (a replica subscribing to another replica) and bidirectional setups with careful conflict handling.

Comparison by Database

Hands-off: When a master goes down

• PostgreSQL with Patroni: Patroni detects the failure, runs a leader election using DCS (etcd/Consul/ZooKeeper), promotes the best replica, and updates the configuration
• MySQL with Orchestrator: Orchestrator detects the failure, promotes the replica with the most advanced GTID position, and re-points remaining replicas
• MongoDB: The replica set elects a new primary via majority vote (typically within 5–10 seconds)

Replication Lag

Asynchronous replication inevitably introduces lag. Monitoring lag is essential:

-- PostgreSQL: check replication lag
SELECT
    application_name,
    pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes
FROM pg_stat_replication;

-- MySQL: check seconds behind master
SHOW SLAVE STATUS\G
-- Look for: Seconds_Behind_Master

High lag risks stale reads and, on failover, data loss. Mitigations include:

• Semi-sync replication — at least one replica must acknowledge every write
• Read-your-writes consistency — route read-after-write queries to the primary
• Lag monitoring alerts — page the on-call team when lag exceeds thresholds

Choosing a Strategy

The most common production pattern is master-slave with asynchronous replication as the default, synchronous replication for critical databases, and a failover automation tool (Patroni, Orchestrator, or cloud managed service) to handle primary failures.

For a comprehensive overview, read our article on Acid Transactions Guide.

For a comprehensive overview, read our article on Database Backup Recovery.