Database.Middle.How does replication work in databases?

Database replication is the process of copying and maintaining database objects and data from one database (the primary/master) to one or more others (the replicas/slaves) to ensure redundancy, high availability, scalability, and disaster recovery.

🧠 Core Idea

One database acts as the source of truth, and changes (inserts, updates, deletes) are replicated to other databases.

[ Primary DB ]
     |
     |  changes streamed
     v
[ Replica DBs ]

🧱 Types of Replication

1. Master-Slave (Primary-Replica)

Writes go to the master, reads can go to slaves.
Replicas are read-only copies.
Used for read scaling and backups.

2. Multi-Master

All nodes can read and write.
Requires conflict resolution.
Used in distributed systems needing high availability across regions.

3. Synchronous vs. Asynchronous

Type	Description	Pros	Cons
Synchronous	Master waits for replica to confirm write	Strong consistency	Slower, higher latency
Asynchronous	Master does not wait	Fast write performance	Possible data lag on failure

⚙️ Common Replication Mechanisms

✅ Statement-Based Replication

Replicates SQL statements (e.g., UPDATE users SET ...)
MySQL supports this (older approach)

✅ Row-Based Replication

Replicates actual row data changes (e.g., before/after values)
More accurate, avoids non-determinism

✅ Logical Replication

Sends logical changes (insert/update/delete) — not raw bytes
PostgreSQL: pglogical, wal2json

✅ Physical Replication

Copies data blocks (pages) directly from the primary (e.g., PostgreSQL WAL)
Exact binary-level copy — faster but not flexible

🔧 Example: PostgreSQL Streaming Replication (Physical)

Master writes changes to WAL (Write-Ahead Log)
Replica reads WAL via streaming connection
Applies changes to replicate state

# On replica
pg_basebackup -h master_host -D /var/lib/postgresql/data -U replicator --wal-method=stream

📦 Benefits of Replication

Benefit	Description
High Availability	Failover to replica if master goes down
Read Scalability	Spread read load across replicas
Disaster Recovery	Use replicas as hot/cold backups
Geo-Distribution	Deploy replicas near users globally

❌ Challenges

Replication lag (in async mode)
Conflict resolution (multi-master)
Data consistency and durability
Monitoring and failover management

🛠️ Tools and Systems with Built-in Replication

System	Built-in Replication Type
PostgreSQL	Streaming (physical), logical
MySQL	Binary log (row-based/statement)
MongoDB	Replica sets
Cassandra	Multi-master, eventual consistency
CockroachDB	Synchronous, distributed SQL engine

✅ Summary

Concept	Description
Replication	Copying data from one DB node to others
Master-Slave	One writeable node, multiple read-only nodes
Synchronous	Safer but slower
Asynchronous	Faster, but can lag
Uses	HA, scalability, backups, failover