Replication and read replicas
Keeping copies of one database on several servers: a primary takes the writes, replicas replay them and serve reads. The lag between the two decides what a reader can see, and a strong answer says how it handles it.
What it is
Replication keeps the same data on more than one database server. In the common shape, one server, the primary (MySQL calls it the source), accepts the writes and records them in a log of changes; the other servers, the replicas or standbys, receive that log and replay it, so each holds a copy that trails the primary by however much it has not yet replayed. A replica that also answers queries is a read replica; PostgreSQL calls it a hot standby. Clients get read and write access on the primary and read access on the replica.
Asynchronous or synchronous. PostgreSQL 17 streaming replication and MySQL 8.4 replication are both asynchronous unless you configure otherwise: the primary commits and answers the client without waiting for any replica to confirm the change. In PostgreSQL the resulting delay is typically under one second when the standby keeps up with the load, and if the primary crashes, transactions that committed but were not yet shipped are lost, in proportion to the delay at that moment. With synchronous replication, a commit waits until a standby confirms that the commit record is on its disk. Setting synchronous_commit to remote_apply goes further: the commit waits until the standby has replayed the transaction and a query there would see it, which lets you spread reads across standbys without a reader seeing a state older than its own write.
Lag has two parts. A change first has to reach the replica over the network, then be replayed there. The standby's status views expose both: a gap between what the primary has sent and what the standby has received points to the network or to an overloaded standby, and a gap between what the standby has received and what it has replayed means it gets changes faster than it can apply them. A replica serves reads from what it has replayed, so a reader's staleness is the sum of the two gaps, and a healthy network with a replica that can't keep up still leaves readers behind.
Reading your own write. With asynchronous replicas, a client that writes to the primary and immediately reads from a replica can get the old value. The usual fixes: route that user's reads to the primary for a short window after they write; record the primary's log position after the write and read from a replica once it has replayed past that position; or send the reads that must be fresh to the primary at all times. Which one fits depends on how many of your reads come right after a write: when that share is small, the first option costs little, and when it's large you're paying for the primary anyway.
More than one writer. Multi-leader (multimaster) replication lets several servers accept writes. It comes in two forms. In the asynchronous form each server works on its own and periodically exchanges changes, and conflicting transactions have to be settled by rules or by people. In the synchronous form each write is sent to the other servers before the commit completes, and heavy write traffic brings locking and slow commits. Logical replication, which ships changes per table and can flow in more than one direction, is the building block for such setups. Our advice: reach for several writers when a single primary can't serve the write path, for instance when each region must keep accepting writes while cut off from the others, and say how conflicts get resolved. See also Consistency models.
When to use it
Add read replicas when reads are the load, the primary is the bottleneck on CPU or I/O because of them, and the reads tolerate a little staleness. Amazon RDS lists the cases it sees: scaling reads past one instance, serving reads while the source is paused for a backup or maintenance (with stale data), running reporting queries away from the production instance, and keeping a copy to promote if the primary fails. That last case makes a replica part of your failover plan, with the loss window that asynchronous replication carries.
Replicate across regions when readers are far from the primary or when a whole region may go down. Aurora Global Database, for example, keeps a primary cluster in one region and up to 10 secondary clusters elsewhere, replicating with latency its documentation puts at typically under a second; the secondaries serve reads near their users and can take over when the primary region fails, and write forwarding lets a secondary cluster send write requests on to the primary. The pattern generalizes: one writing region, reading copies elsewhere, and a plan for a read that reaches a region before the write it's looking for has.
When not to
Don't add replicas to fix write throughput. A standby receives the primary's whole write-ahead log, so ten replicas take the same writes as one and the primary still takes them all; the tool for writes is sharding or a smaller write path. And don't move a read to a replica when it has to be exact at that instant, such as a balance check before a debit or a uniqueness check before an insert: those go to the primary, where no replication lag sits between the write and the read.
Skip synchronous replication across a long distance unless losing a committed write is unacceptable: each commit then waits for a round trip to the far standby, and a slow or unreachable standby slows commits with it. Skip asynchronous multi-leader when one primary can take the writes: conflict handling is then ongoing work, through resolution rules or by users, for as long as it runs that way. And at small scale, a single well-indexed instance with backups is often enough; replication adds a lag you have to reason about in exchange for capacity you may not need yet.
Compared
| Setup | Where writes go | What a replica read can show | When it's the right one |
|---|---|---|---|
One primary, asynchronous replicas | The primary | A state behind by the current lag: small when the replica keeps up, unbounded when it doesn't | Read-heavy loads that tolerate brief staleness; the default to start from |
One primary, synchronous standby | The primary; the commit waits for the standby | Current with | A committed write must survive losing the primary, and the standby is close enough that the wait is acceptable |
Multi-leader (several primaries) | Any of them | Asynchronous: a value another leader has since changed, with conflicts to resolve; synchronous: current, at the cost of slow commits | Each site must accept writes while cut off, or a round trip to one primary is too slow for the write path |
Single instance, no replica | The one server | Current: there is no copy to lag | Small scale where one box serves the reads and backups cover the loss case |
In a picture
Step 1 of 4
A write commits on the primary
The application sends writes to one server. The primary writes the change to its log and acknowledges the commit.
How to say it in an interview
The common mistake
Quiz
Questions that use this
Related
Sources
- PostgreSQL 17 documentation: 26.2. Log-Shipping Standby Servers (streaming and synchronous replication)
- PostgreSQL 17 documentation: 26.1. Comparison of Different Solutions
- MySQL 8.4 Reference Manual: Chapter 19 Replication
- Amazon RDS User Guide: Working with DB instance read replicas
- Amazon Aurora User Guide: Using Amazon Aurora Global Database