Skip to content

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.

On this page

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

SetupWhere writes goWhat a replica read can showWhen 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 remote_apply; otherwise durable there but maybe not yet visible

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

writefreshApplicationPrimary

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

The primary of an asynchronously replicated database dies and you promote a replica. What happens to transactions committed in the last moments?
A user edits their display name and the page reloads showing the old one. Reads go to replicas, writes to the primary. What is the most likely cause?
The primary is saturated by writes. Does adding read replicas help?
You make a standby in a region 80 ms away synchronous. What changes for writers?

Questions that use this

Related

Sources

  1. PostgreSQL 17 documentation: 26.2. Log-Shipping Standby Servers (streaming and synchronous replication)
  2. PostgreSQL 17 documentation: 26.1. Comparison of Different Solutions
  3. MySQL 8.4 Reference Manual: Chapter 19 Replication
  4. Amazon RDS User Guide: Working with DB instance read replicas
  5. Amazon Aurora User Guide: Using Amazon Aurora Global Database
Sign in to report a mistake