If your entire business runs on one database server, that server is a single point of failure. When it goes down, so does everything that depends on it. Database replication keeps one or more copies of a database continuously updated on other servers. Those copies can take over if the main server fails, share the load of read queries, or sit in another region for disaster recovery. This article explains how replication works, the trade-offs between its styles, and the pitfalls teams run into.
How database replication works: primary and replicas
In the most common setup, one server is the primary (older documentation says "master"). All writes go to it. One or more replicas (formerly "slaves") receive a stream of changes from the primary and apply them to their own copy of the data.
How the changes travel depends on the engine:
- MySQL records every change in its binary log. Each replica connects to the primary, downloads binlog events into a local relay log, and replays them. Modern setups use GTIDs (global transaction identifiers), which label every transaction uniquely and make it far easier to repoint replicas after a failover.
- PostgreSQL streams its write-ahead log (WAL), the low-level record of every change, to replicas. This is called streaming replication and produces a byte-for-byte physical copy. PostgreSQL also offers logical replication, which sends row-level changes for chosen tables and can work across major versions.
Why replicate? Four common goals
| Goal | How replication helps | What it does not do |
|---|---|---|
| High availability | A replica can be promoted to primary when the primary fails | Promotion is not automatic unless you add failover tooling |
| Read scaling | Reports and read-heavy pages can query replicas | Does not add write capacity |
| Disaster recovery | A replica in another region survives a regional outage | Does not protect against bad data, which replicates too |
| Isolating heavy work | Analytics and backups can run on a replica | Long queries on a replica can still delay its replay |
That last column of the first and third rows deserves emphasis: replication is not a backup. An accidental DELETE or a corrupting bug is copied to every replica within moments.
Synchronous vs asynchronous replication
This is the central trade-off.
Asynchronous
The primary commits a transaction and tells the application "done" immediately. Changes are sent to replicas afterwards. This is the default in both MySQL and PostgreSQL. It is fast, and a slow or distant replica does not hold up the primary. The cost is that if the primary dies suddenly, the last few transactions it confirmed may never have reached any replica. They are lost if you fail over.
Synchronous
The primary waits until at least one replica confirms it has received (or applied) the change before telling the application "done". No confirmed transaction is lost if the primary fails, but every write now pays the network round trip to the replica, and if the replica becomes unavailable, writes can stall unless the system is configured to fall back. PostgreSQL supports this via synchronous_standby_names.
Semi-synchronous
MySQL's middle ground: the primary waits for a replica to acknowledge receiving the change, but not for it to be applied. It narrows the window for data loss with a smaller performance penalty than full synchronous replication.
Replication lag
Replication lag is how far behind the primary a replica is. Under normal load it is often well under a second, but it can grow during large batch updates, schema changes or when the replica's hardware is weaker. You can measure it:
-- MySQL 8.0.22+ (on the replica)
SHOW REPLICA STATUS\G
-- look at Seconds_Behind_Source
-- PostgreSQL (on the primary)
SELECT client_addr, state, replay_lag
FROM pg_stat_replication;Lag causes a classic bug when an application sends reads to replicas. A customer updates their address, the page reloads, the read goes to a replica that has not caught up, and the old address appears. Common fixes:
- Send a user's reads to the primary for a short time after they write ("read your own writes").
- Keep reads that must be current, such as checkout and account pages, on the primary.
- Route only reports, search and other tolerant reads to replicas.
- Alert when lag exceeds a threshold.
Failover: promoting a replica
Failover means making a replica the new primary when the old one fails. Doing it safely involves several steps: confirming the old primary is really down, choosing the most up-to-date replica, promoting it, repointing other replicas to it, and redirecting the application. Most importantly, you must stop the old primary from coming back and accepting writes, a dangerous situation called split brain, where two servers both believe they are in charge and the data diverges.
Tools such as Patroni for PostgreSQL and MySQL InnoDB Cluster or Orchestrator for MySQL automate this. Managed cloud databases offer multi-zone deployments that handle failover for you. Whatever you use, test failover regularly; an untested failover often fails exactly when you need it.
Other topologies
- Cascading replication. Replicas feed other replicas, reducing load on the primary when there are many copies.
- Multi-primary. More than one server accepts writes. Products such as MySQL Group Replication and Galera support it, but conflicting writes must be detected and resolved, and the added complexity is significant. Most businesses are better served by a single primary.
- Delayed replica. Deliberately kept, say, an hour behind, so you can recover from a mistake before it reaches this copy.
The official PostgreSQL high availability documentation and the MySQL replication chapter describe the options for each engine. Our database management and cloud solutions teams set up and monitor replicated databases.
Key takeaways
- Database replication keeps live copies of your data for availability, read scaling and disaster recovery.
- Asynchronous replication is fast but can lose the latest transactions on failover; synchronous replication trades speed for safety.
- Monitor replication lag and route only lag-tolerant reads to replicas.
- Replication copies mistakes too, so keep proper backups alongside it.