Database Replication Types
✅ Common Types of Database Replication
Master-Slave Replication (Single Master / Multiple Slaves)
Master-Master Replication (Active-Active)
Multi-Master Replication (Write Everywhere with Conflict Resolution)
Synchronous Replication
Asynchronous Replication
Semi-Synchronous Replication
Snapshot Replication
Logical Replication
1. Master-Slave Replication (Single Master / Multiple Slaves)
How it works: One primary node handles all writes, and multiple read-only replicas handle reads.
✅ Pros:
Simple and widely supported
Great for read scaling
Easy to separate read-heavy and write-heavy workloads
Can promote a slave to master (with failover logic)
❌ Cons:
Replication lag is common
Master is a single point of failure unless clustered
Writes cannot be load-balanced
How to implement:
PostgreSQL: Streaming Replication with WAL logs
MySQL:
CHANGE MASTER TO,START SLAVE
# Example for PostgreSQL
# On primary:
wal_level = replica
max_wal_senders = 5
hot_standby = on
# On replica:
standby_mode = on
primary_conninfo = 'host=primary_ip user=replicator password=secret'
2. Master-Master Replication (Active-Active)
How it works: Two or more nodes are writeable and replicate to each other.
✅ Pros:
High availability and write failover
Can perform writes on multiple nodes (with conflict resolution)
❌ Cons:
Data conflicts possible — must handle conflict resolution (e.g., last-write-wins, application-side logic)
Complex to configure and manage
Use case: Distributed apps where low write latency is required in multiple regions.
How to implement:
MySQL:
Group Replication,Galera ClusterCouchDB, MongoDB: Built-in multi-master support
3. Multi-Master Replication (Write Everywhere with Conflict Resolution)
Extension of Master-Master with advanced conflict detection/resolution.
✅ Pros:
True distributed write availability
Useful in geographically distributed systems
❌ Cons:
Very complex conflict resolution
Higher network and consistency overhead
May violate strict ACID guarantees
Examples:
CouchDB, Cassandra, Riak
4. Synchronous Replication
How it works: A transaction is only committed on the master after it’s acknowledged by all replicas.
✅ Pros:
Strong consistency
Zero data loss on failover
❌ Cons:
Higher latency
Reduced throughput
If one replica is slow, master slows down
Use case: Financial systems requiring strict consistency
How to enable (PostgreSQL):
propertiesCopyEditsynchronous_commit = on
synchronous_standby_names = 'replica1, replica2'5. Asynchronous Replication
How it works: Master returns success without waiting for replicas.
✅ Pros:
Low latency, high throughput
Good for read scaling
❌ Cons:
Data loss possible on master crash
Replicas may lag behind
Common setup: Postgres WAL shipping, MySQL binlog-based
6. Semi-Synchronous Replication
Middle-ground between sync and async:
Master waits for at least one replica to acknowledge before commit.
✅ Pros:
Better safety than async
Better performance than full sync
❌ Cons:
Not as fast as async
Still risks data loss in edge cases
Example: MySQL semi-sync plugin
7. Snapshot Replication
How it works: Periodic full dump of the entire dataset to replicas.
✅ Pros:
Simple to set up
No real-time processing
❌ Cons:
Heavy bandwidth usage
Not suitable for real-time sync
Used in: Reporting DBs, analytics snapshots
8. Logical Replication
How it works: Replicates specific tables or subsets of data using logical changes (INSERT/UPDATE/DELETE).
✅ Pros:
Flexible (filtering, schema transformation)
Useful for ETL, auditing, change data capture (CDC)
❌ Cons:
Not as fast as physical replication
Schema drift and change management needed
Example: PostgreSQL logical replication using pglogical, Debezium
Last updated