Database Replication: Primary-Replica, Read Replicas, Lag and Failover

So far Pixly's database is one machine. If it dies, every write fails and, unless the cache is warm, most reads fail too. And even when it is healthy, everything not served by the cache lands on that one machine. Both problems have the same first answer: keep copies of the data on other machines. That is replication.

Replication sounds simple ("just copy the data") but every interesting question in distributed data shows up here first: how up to date is a copy, what happens to writes in flight when a machine fails, and how do clients know which copy to trust. This article answers those questions for the most common setups.

• Why replicate, and what replication does not solve.
• How primary-replica (leader-follower) replication works.
• Synchronous versus asynchronous replication, and what each risks.
• Read replicas for scaling reads, and their limits.
• Replication lag and the anomalies it causes, with fixes such as read-your-writes routing.
• Failover: detection, promotion, redirection, fencing and split brain.
• Multi-leader and leaderless replication, briefly.
• Why replication is not a backup.

What replication does not do: increase write capacity. Every copy must apply every write. If writes are the bottleneck, you need fewer or smaller writes, or partitioning (article 16).

The most common setup has one primary (also called leader or master) and one or more replicas (followers, standbys):

1. All writes go to the primary.
2. The primary records each change in a log (in PostgreSQL, the write-ahead log, or WAL).
3. Replicas receive the log and apply the same changes, in the same order.
4. Replicas serve reads; they reject writes.

           writesClients ----------> PRIMARY ---- change log ----> REPLICA 1  (reads)   |                                    \-------> REPLICA 2  (reads)   +----------------- reads ---------------------^

PostgreSQL's documentation describes this as streaming replication: the standby connects to the primary, which streams WAL records as they are generated, and standbys can serve read-only queries as hot standbys (PostgreSQL: Log-Shipping Standby Servers). Managed services work the same way; AWS describes RDS read replicas as read-only copies to which updates on the primary are copied asynchronously (Working with DB instance read replicas).

The key question: when does the primary tell the client "committed"?

The primary commits locally, replies to the client, and ships the change to replicas afterwards.

• Fast: write latency is unaffected by replicas.
• Available: the primary keeps accepting writes even if every replica is down.
• Risk: if the primary crashes before a change reaches any replica, that committed change is lost on failover.

PostgreSQL's documentation states this directly: streaming replication is asynchronous by default, so if the primary crashes, some committed transactions may not yet have been replicated, causing data loss proportional to the replication delay at the time of failover (PostgreSQL: Log-Shipping Standby Servers).

The primary waits until at least one replica confirms it has the change before replying "committed".

• Durable: an acknowledged write exists on at least two machines; failover to that replica loses nothing.
• Slower: every commit waits for a network round trip to the replica. Inside one region that is often around a millisecond; across regions it can be 50 to 200 ms or more.
• Availability risk: if the synchronous replica is down or slow, writes stall. Setups therefore usually allow "any one of several replicas" to acknowledge. PostgreSQL, for instance, lets you name several standbys and require acknowledgements by priority (FIRST) or by quorum (ANY) (PostgreSQL: Log-Shipping Standby Servers).

A common production shape combines both: one synchronous standby in another availability zone (for zero-data-loss failover within the region) plus asynchronous read replicas (for read scaling) plus perhaps an asynchronous replica in another region (for disaster recovery). AWS's RDS documentation describes a similar combination: a synchronous standby for high availability, which does not serve reads, alongside asynchronous read replicas (Working with DB instance read replicas).

Send read queries to replicas and writes to the primary. With two replicas, read capacity roughly triples (primary plus two copies), as long as the reads can tolerate slightly stale data.

How applications route:

• Separate connection pools in the application: one for the primary, one for replicas, chosen per query.
• A database proxy or load balancer that sends reads to replicas.
• Managed reader endpoints that balance across replicas.

Limits:

• Writes are not scaled. And every replica must apply all writes, so a write-heavy system can leave replicas permanently behind.
• Each replica has the full dataset. Replicas do not help if the data does not fit on one machine.
• Stale reads, the subject of the next section.

Replication lag is how far a replica is behind the primary, in time or in log position. Usually it is small (PostgreSQL's documentation says typically under a second for streaming replication), but it grows during write bursts, long-running queries on the replica, network problems or replica maintenance.

Lag causes visible anomalies. Three you should be able to name:

User edits caption  --> primary (committed)User reloads page   --> replica (not yet applied) --> sees OLD captionUser: "my edit was lost!" and edits again

Read-your-writes consistency means a user always sees their own changes. Ways to provide it:

• Read the user's own data from the primary for a short time after they write (for example 10 seconds, tracked in their session).
• Track the log position of the user's last write and read from a replica only if it has applied that position (otherwise use the primary or wait briefly).
• Route all reads of things the user can edit (their profile, their settings) to the primary, and use replicas for everything else.

A user loads a page from replica A (which is up to date) and sees a new comment. They reload, hit replica B (which lags), and the comment disappears. Monotonic reads means a user never sees older data after having seen newer data. A simple fix: pin each user to one replica (for example, by hashing their user id), so their view only moves forward.

Someone posts a question; someone else answers. A third user's reads hit a replica that has the answer but not yet the question. Consistent prefix or causal consistency prevents this. It matters most in partitioned and multi-leader systems (article 19).

This Java program simulates a primary, a replication log and a lagging replica. A naive replica read returns the old caption; a read-your-writes check uses the replica only if it has applied the client's last write position. The program is a model of the routing rule; real databases expose the log position through their own functions.

// File: ReplicaLagDemo.javaimport java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;public class ReplicaLagDemo {    record Change(long position, String key, String value) {}    static final List<Change> log = new ArrayList<>();          // the primary's change log    static final Map<String, String> primary = new HashMap<>();    static final Map<String, String> replica = new HashMap<>();    static long replicaApplied = 0;                               // last log position applied    static long write(String key, String value) {        primary.put(key, value);        long position = log.size() + 1;        log.add(new Change(position, key, value));        return position;                                          // returned to the client    }    static void replicate(int changes) {                          // the replica catches up gradually        for (int i = 0; i < changes && replicaApplied < log.size(); i++) {            Change c = log.get((int) replicaApplied);            replica.put(c.key(), c.value());            replicaApplied = c.position();        }    }    static String readYourWrites(String key, long clientLastWrite) {        if (replicaApplied >= clientLastWrite) {            return replica.get(key) + " (from replica)";        }        return primary.get(key) + " (from primary: replica is behind)";    }    public static void main(String[] args) {        write("caption:9001", "Sunset");        replicate(1);        long myWrite = write("caption:9001", "Sunset at Marina Beach");        System.out.println("Naive replica read: " + replica.get("caption:9001"));        System.out.println("Read-your-writes:   " + readYourWrites("caption:9001", myWrite));        replicate(1);        System.out.println("After catching up:  " + readYourWrites("caption:9001", myWrite));        System.out.println("Lag in log positions: " + (log.size() - replicaApplied));    }}

Naive replica read: SunsetRead-your-writes:   Sunset at Marina Beach (from primary: replica is behind)After catching up:  Sunset at Marina Beach (from replica)Lag in log positions: 0

When the primary fails, a replica must become the new primary. This is failover, and it is harder than it sounds. It has four steps:

1. Detect that the primary has failed. Usually by missed heartbeats over a timeout. Too short a timeout causes unnecessary failovers on a brief network blip (each costing seconds of unavailability and possibly data); too long means minutes of downtime.
2. Choose the new primary: the replica with the most recent data (the furthest log position), so the least is lost.
3. Redirect clients: update DNS, a virtual IP, a proxy's configuration or a service registry so writes go to the new primary.
4. Fence the old primary: make sure it can no longer accept writes if it comes back, for example by revoking its access or shutting it down.

If the old primary was not really dead (only cut off from the failure detector by a network partition), and nobody fenced it, you now have two primaries both accepting writes. Their data diverges, and merging it afterwards is painful or impossible. This is split brain, and it is the most dangerous failover failure.

Protections:

• Fencing (step 4), so a deposed primary cannot write.
• Majority-based decisions: the failover manager uses a consensus system (article 22) so that only a group holding a majority of votes can promote a primary; a minority side of a partition cannot.
• Fencing tokens on downstream writes, so a stale primary's writes are rejected (article 22).

• Downtime: detection time plus promotion plus redirection, typically seconds to a minute or two for well-configured systems.
• Data loss with asynchronous replication: everything committed on the old primary but not yet replicated. At Pixly's 1,800 writes per second and 2 seconds of lag, up to about 3,600 committed writes could vanish. That is why important data uses a synchronous standby.
• Connection storms: every application reconnects at once to the new primary (connection pooling and backoff help).

Managed databases automate most of this, but you should still be able to explain it, test it and know its data-loss window.

Several nodes accept writes (often one per region) and replicate to each other. Users in each region write locally, with low latency, and each region keeps working if another is cut off.

The cost is write conflicts: two regions change the same row at the same time. Resolution strategies include last write wins by timestamp (simple, but silently discards one write and depends on clocks), application-specific merges, and data types designed to merge automatically (conflict-free replicated data types, or CRDTs). Use multi-leader only when local writes in several regions are a real requirement, and design data so conflicts are rare (for example, each user's data is "owned" by one region).

Any of N replicas accepts reads and writes. A client writes to several replicas and considers the write successful when W acknowledge; it reads from R replicas and takes the newest value. If W + R > N, every read overlaps at least one replica with the latest write. Amazon's Dynamo paper describes this design, along with techniques such as hinted handoff and anti-entropy repair, and it inspired systems such as Apache Cassandra (Dynamo: Amazon's Highly Available Key-value Store). Article 19 works through the quorum arithmetic.

Replication copies every change, including the bad ones. If someone runs DELETE FROM photos by mistake, or a bug corrupts rows, every replica faithfully applies it within a second. To recover you need backups, ideally with point-in-time recovery: periodic full backups plus the archived change log, so you can restore the database to the moment just before the mistake. PostgreSQL documents this as continuous archiving and point-in-time recovery (PostgreSQL: Continuous Archiving and Point-in-Time Recovery). Test restores regularly; an untested backup is a hope, not a plan. Article 24 connects this to recovery objectives.

                     Region: primary (3 availability zones)  App servers --writes--> PRIMARY (zone A)       |                     |-- synchronous --> STANDBY (zone B)    failover target, no reads       |                     |-- async --------> READ REPLICA 1 (zone B)       |                     |-- async --------> READ REPLICA 2 (zone C)       |                     `-- async --------> DR REPLICA (another region)       `--reads--> reader endpoint (replicas 1 and 2)  Backups: nightly full + continuous log archive to object storage

Read routing rules:

Failure walk-through:

• Primary fails: the managed failover promotes the synchronous standby within about a minute; no committed writes are lost; clients reconnect through the same endpoint name.
• A read replica fails: the reader endpoint stops sending it traffic; the other replica and the cache absorb the reads.
• Replica lag spikes during a bulk import: lag is monitored, and replicas lagging more than a threshold are removed from the reader pool until they catch up.
• Bad migration deletes data: restore from the continuous archive to a point before the migration, into a new instance, and copy back the affected rows.

• Expecting replicas to scale writes.
• Sending a user's read straight after their write to a lagging replica.
• Using asynchronous replication for data that must never be lost, without knowing the loss window.
• Ignoring split brain in a failover design.
• Treating replicas as backups.
• Running heavy analytics on the replica that is also the failover target.

• Add replication with a reason: availability (standby), read scaling (replicas), or locality (another region).
• Say whether it is synchronous or asynchronous and what that means for data loss and latency.
• Mention lag and how you handle read-your-writes for the user who just wrote.
• If asked about failover, name the four steps and split brain.
• Say that backups with point-in-time recovery exist separately.

• Next: Database Sharding and Partitioning: How It Works and When You Need It, for when the data or the writes no longer fit on one primary.
• Later: consistency models and quorums (article 19), leader election and fencing (article 22), and recovery objectives (article 24).