Database Replication

How Replication Copies Data

Database replication is the process of copying changes from one database server to one or more others, so the same data exists in more than one place. The server that accepts writes is the primary. Servers that receive copies are replicas. Older documentation calls these roles master and slave, and many systems still use those words.

Changes are recorded on the primary, usually in a change log, and each replica applies them in the same order. A replica therefore holds a nearly current copy that can serve reads or take over if the primary fails.

Synchronous and Asynchronous Replication

In asynchronous replication, the primary confirms a write to the application without waiting for replicas to receive it. This is fast, but a replica may lag behind, so a read sent to it can return slightly old data. If the primary fails before a change has been copied, that change can be lost.

In synchronous replication, the primary waits until at least one replica has confirmed the change. Nothing is lost when the primary fails, but every write takes longer, and a slow or unreachable replica can hold up the system. Many setups use a middle ground that waits for one replica and otherwise carries on. This choice is one of the trade-offs described in the CAP theorem.

Read Scaling and Failover

Read scaling is the most common reason to add replicas. Reports, search pages and other read-heavy work can be sent to replicas, leaving the primary free to handle writes. Failover is the second main use: if the primary goes down, a replica can be promoted to take its place, which shortens downtime.

Multi-primary setups let several servers accept writes. They can improve write availability, but two servers may change the same row at once, so conflict handling becomes a design problem. Replication copies all of the data. To split the data itself across servers, see database sharding. Many hosted services set up replicas for you, as covered in managed databases.