How does database replication work and what consistency issues does it create?

Replication keeps multiple copies of the data for durability, read scaling and failover. In leader-follower replication, all writes go to the leader and followers apply the write log. Reads can be served by followers, but only if the application can tolerate the delay before a follower catches up.

Replication modes

• Asynchronous: the leader acknowledges before followers apply, so failover can lose recent writes
• Synchronous: the leader waits for at least one follower, which improves durability but adds latency and can block on a slow replica
• Semi-synchronous: wait for one replica, not all, as a middle ground
• Multi-leader: writes accepted in several regions, requiring conflict resolution
• Leaderless: quorum reads and writes across replicas, as in Dynamo-style stores

Replication lag creates visible anomalies. A user updates a profile, then reads from a lagging replica and sees the old value. Read-your-writes fixes this by routing a user's reads to the leader or to a replica known to have applied the write, often tracked with a log sequence number or a session token.

Failover is where replication gets dangerous. Promotion must ensure the chosen follower has all acknowledged writes, otherwise data is lost. Two leaders can accept conflicting writes during a partition, producing split brain. Use fencing, quorum-based election and a clear rule for which node may accept writes.