What is database replication, and what are the differences between synchronous and asynchronous replication?

Question

Accepted Answer

Database replication is the process of copying and maintaining data across multiple database servers to provide redundancy, improve availability, and distribute read workloads. Understanding replication modes is fundamental for a mid-level DBA responsible for maintaining highly available database environments.

In asynchronous replication, the primary server commits transactions and sends the changes to replicas without waiting for confirmation that the replica has received or applied them. The primary operates at full speed because it never waits for the replica. The trade-off is that there is always a replication lag, typically ranging from milliseconds to seconds, during which the replica is behind the primary. If the primary fails during this window, any transactions that were committed on the primary but not yet replicated are lost. This means asynchronous replication has a non-zero Recovery Point Objective.

In synchronous replication, the primary waits for at least one replica to confirm that it has received and written the transaction to its WAL before acknowledging the commit to the client. This guarantees zero data loss because any committed transaction exists on at least two servers. The trade-off is increased transaction latency because every commit must wait for the network round trip to the replica and the replica's disk write. For replicas in the same datacenter, this adds 1 to 5 milliseconds per commit. For geographically distant replicas, the latency can be tens of milliseconds, which may be unacceptable for high-throughput OLTP workloads.

In PostgreSQL, synchronous replication is configured with the synchronous_standby_names parameter on the primary. You can specify one or more replicas and choose between FIRST and ANY modes. FIRST mode requires the first listed available replica to confirm. ANY mode requires any N of the listed replicas to confirm, providing flexibility when you have multiple replicas.

Semi-synchronous replication, available in MySQL, represents a middle ground. The primary waits for at least one replica to acknowledge receipt of the transaction, but not for the replica to apply it. This provides stronger durability than pure asynchronous replication with less latency overhead than full synchronous replication.

The choice between synchronous and asynchronous depends on the application's tolerance for data loss versus latency. Financial systems, healthcare records, and other critical applications often require synchronous replication to guarantee no data loss. Web applications, content platforms, and analytics systems typically use asynchronous replication because the slight risk of losing a few seconds of data during a rare primary failure is acceptable given the performance benefit.

As a mid-level DBA, you should be able to implement both modes and advise on which is appropriate based on the business requirements. Consider running synchronous replication to a local replica for durability while running asynchronous replication to a remote replica for disaster recovery, combining both modes in a single topology.