Database Replication: Read Replicas, Lag, and Failover

An order is created successfully, but the confirmation screen immediately says that it does not exist. The write may be correct; the application may simply have sent the next read to a replica that has not caught up yet. That small gap between a primary and its copies is the central engineering problem in database replication.

Database replication keeps copies of data on more than one database node. It can increase read capacity, reduce regional read latency, and give a team a warm recovery target. It also changes the consistency contract of every read path. This guide explains the trade-offs, the routing rules that keep users from seeing contradictory data, and the operational work behind a credible failover plan. Start with database transactions and ACID properties if commit and durability are still unfamiliar.

Table of Contents

What Is Database Replication?

Database replication is the process of maintaining one or more copies of a database by sending changes from a source node to other nodes. In the most common topology, a single primary accepts writes and one or more replicas replay those changes. Older material may call this primary-secondary or leader-follower replication; the important property is that one node owns write ordering.

A replica is not automatically a backup. A mistaken DELETE, a bad migration, or corrupted application data can be faithfully copied to every replica. Replication improves availability and read capacity; point-in-time recovery and independently retained backups protect against destructive history.

The design starts with a question more precise than “Do we need replicas?”: which reads may be behind, by how much, and what must happen when the primary is unavailable? That answer determines routing, acknowledgement mode, monitoring, and recovery procedures.

The Primary-Replica Data Flow

Most relational engines record committed changes in an ordered log: PostgreSQL uses WAL and MySQL uses the binary log. Replicas receive and apply that stream. They do not invent a new write order, which makes the model easier to reason about than multi-writer systems.

flowchart TD
    Client[Client] --> App[Application]
    App -->|Writes| Primary[(Primary Database)]
    Primary -->|Committed change log| ReplicaA[(Read Replica A)]
    Primary -->|Committed change log| ReplicaB[(Read Replica B)]
    App -->|Fresh reads| Primary
    App -->|Lag-tolerant reads| ReplicaA
    Monitor[Monitoring] -->|Lag and health| Primary
    Monitor -->|Lag and health| ReplicaA
    Monitor -->|Lag and health| ReplicaB

For a simple write, the sequence is:

1. The application sends an INSERT, UPDATE, or DELETE to the primary.
2. The primary validates and commits the transaction using its normal transaction rules.
3. The database records the committed change in its replication log.
4. Each replica receives, persists, and replays the change into its local data files.
5. A read router chooses either the primary or a replica according to the request’s freshness requirement.

Step 5 is application behavior, not a database magic trick. A connection pool, ORM, proxy, or service code must know which endpoint is appropriate. Sending every SELECT to a replica is a common shortcut that eventually creates a confusing customer-facing bug.

Why Teams Use Replication

Scale read-heavy workloads

Many applications read more often than they write: product catalogs, public profiles, analytics dashboards, and content feeds are typical examples. Replicas can serve selected read traffic while the primary continues to serialize writes. This buys capacity without changing table ownership or application-level shard routing.

It does not scale writes. Every primary write still has to execute on the primary, and replicas add log shipping and replay work. When write throughput or dataset size is the limiting factor, database sharding is a different, more complex tool.

Reduce latency for distant readers

A replica near users in another region can make a read faster by avoiding a cross-region round trip. This is useful for data where bounded staleness is acceptable, such as a public catalog. It is a poor fit for a just-submitted payment, inventory reservation, or permission change unless the request is deliberately pinned to the primary.

Improve recovery options

A healthy replica can become a candidate for promotion if the primary fails. The word candidate matters. A replica may be lagging, its promotion may be manual, and client traffic must still be moved safely. Managed platforms differ: some distinguish ordinary read replicas from high-availability standbys, so verify the exact failover behavior instead of assuming every copy is promotable.

Read Replicas and Request Routing

The simplest safe rule is: writes always go to the primary; reads go to a replica only when the product can tolerate stale data. The difficult part is making that rule explicit at an endpoint and session level.

Read-your-writes consistency

Suppose a customer changes their shipping address. The update commits at 10:00:00.000, but a replica applies it at 10:00:00.400. If the profile page immediately reads from that replica, the customer sees the old address and assumes the change failed.

There are three practical ways to prevent that result:

• Primary stickiness: after a write, route that user’s reads to the primary for a short, measured window. This is simple but a fixed window is only a guess when lag varies.
• Commit-position waiting: record the log position returned by the write and use a replica only after it has replayed that position. This is more precise but depends on engine and platform support.
• Return the write result: render the confirmed resource from the successful write response, then use normal replica reads on later navigation.

The first option is often enough for a modest service. The second gives a stronger promise. In both cases, document the guarantee in product terms: “your own changes appear immediately” is clearer than “we use replicas.”

Replication Lag and Stale Reads

Replication lag is the gap between a change becoming committed on the primary and becoming visible on a replica. It can be measured as elapsed time, un-replayed log bytes, transaction count, or each stage of receive, flush, and replay. One number is useful, but no single metric describes every failure mode.

Lag grows when the primary produces changes faster than a replica can receive or apply them. Typical causes include a slow disk, insufficient CPU, a long-running query competing for resources, network congestion, a large migration, or an undersized replica asked to serve expensive reports.

sequenceDiagram
    participant U as User
    participant A as Application
    participant P as Primary
    participant R as Replica
    U->>A: Save new address
    A->>P: UPDATE address
    P-->>A: Commit succeeds at log position 800
    A-->>U: Show saved address
    P-->>R: Stream change at position 800
    U->>A: Reload profile
    A->>R: SELECT address
    R-->>A: Old address before replay
    Note over A,R: Route this read to primary or wait for position 800

Treat lag as a budget

Set an explicit freshness budget per route. A public leaderboard might allow five seconds; a session view after an account change might allow zero stale reads. Then make the router enforce it:

1. Export replica health and replay-lag metrics.
2. Stop sending eligible reads to a replica that exceeds its budget.
3. Fall back to the primary, shed nonessential traffic, or show a controlled retry if primary capacity cannot absorb it.
4. Alert on sustained lag and investigate the bottleneck rather than only increasing the alert threshold.

Do not use average lag as the safety condition. A replica that is usually current can still be minutes behind during a migration, precisely when an application needs a safe fallback behavior.

Synchronous vs Asynchronous Replication

Replication acknowledgement determines what a successful write means during a failure.

| Mode | When the primary acknowledges | Main benefit | Main cost | | --- | --- | --- | | Asynchronous | After local commit, before a replica confirms | Low write latency and high primary availability | Recent acknowledged writes can be missing after primary loss. | | Synchronous | After one or more replicas confirm a configured stage | Lower data-loss window | Higher write latency and less availability if replicas are unreachable. | | Semi-synchronous | After a limited replica acknowledgement, with fallback rules | Tunable compromise | More operational complexity and platform-specific semantics. |

Asynchronous replication is common for read replicas because a distant or slow replica should not hold every write hostage. It creates a recovery point objective (RPO) that can be greater than zero: if the primary dies before the replica catches up, some acknowledged changes may be absent from the promoted node.

Synchronous replication improves that RPO only if the exact acknowledgement point meets the requirement. “Received” is not always “replayed and queryable.” Read the database or managed-service documentation before promising zero data loss. It is a reliability decision, not just a toggle.

Failover Is a Workflow, Not a Button

Failover promotes a suitable replica and redirects writes to it after the primary is unavailable. A sound plan must prevent split brain, where the former primary and the newly promoted node both accept writes. Two diverging histories are much harder to repair than one brief outage.

A practical failover sequence

1. Detect failure using multiple signals; do not promote merely because one health check timed out.
2. Fence the old primary: remove its network path, revoke its lease, or otherwise guarantee it cannot accept writes.
3. Choose the most current healthy replica and record the known replication position and expected RPO.
4. Promote it, verify it accepts writes, and update the single writer endpoint used by applications.
5. Drain or reconnect application pools, then run a small write and a read-after-write smoke test.
6. Rebuild the old primary as a replica only after its data has been reconciled with the new primary.

Define both recovery targets before the incident:

• RPO (Recovery Point Objective): the maximum data loss the business accepts. With asynchronous replication, it may be the lag at promotion.
• RTO (Recovery Time Objective): the maximum time until the service can safely resume. It includes detection, promotion, DNS or proxy propagation, connection-pool recovery, and verification.

A successful database promotion is not a complete application recovery. Credentials, endpoint configuration, background workers, migration jobs, and read routers can still point to the former primary. Run controlled failover drills and measure the entire path.

Replication vs Backups, Caching, and Sharding

These techniques solve different problems and often coexist.

For example, a product page might use a cache for hot objects, a replica for catalog reads that miss the cache, and a primary for price changes. None of those layers removes the need for backups. If the data has outgrown one writer, sharding may eventually be necessary, but add it only after read optimization and replication no longer address the real constraint.

Production Checklist

Before treating replication as production-ready, verify these concrete decisions:

• A single, documented writer endpoint exists and every write path uses it.
• Each read endpoint has a stated freshness requirement and a primary fallback.
• Dashboards show replica availability, receive and replay lag, CPU, disk, connection count, and replication errors.
• Alerts use a business-relevant lag threshold and a sustained duration to avoid noise.
• Replica capacity is sized for its own queries, not copied blindly from the primary.
• Backups and point-in-time recovery are tested independently of replicas.
• Promotion authority, fencing, connection reset, and verification steps are in a runbook.
• Failover is rehearsed under realistic traffic, and the observed RPO and RTO meet the target.

Interview Questions

1. Why can a read replica return stale data?

A primary can commit a transaction before an asynchronous replica has received and replayed its log record. The replica is correct for an earlier point in time, but not current. I would route read-after-write and correctness-critical reads to the primary, then allow lag-tolerant traffic on replicas with a monitored freshness budget.

2. Does a read replica automatically provide high availability?

Not necessarily. Some platforms expose ordinary read replicas only for read scale and require a separate standby or promotion procedure for failover. I would verify promotion eligibility, replication mode, endpoint switching, and split-brain protection before claiming an HA design.

3. How would you prevent split brain during failover?

I would fence the old primary before enabling writes on the new one. The mechanism can be a managed-service lease, a database cluster quorum, or network isolation, but the property is the same: only one node can hold write authority. Health checks alone are not enough because a network partition can make a healthy primary appear dead.

4. When would you choose synchronous replication?

I would choose it when the business cannot accept losing acknowledged writes and can accept the added latency and reduced write availability. I would first define the required RPO, the number and location of acknowledgers, and exactly whether the acknowledgement means received, durable, or replayed.

5. What is the difference between replication and a backup?

Replication maintains a near-current copy for serving traffic or recovering quickly. A backup preserves recoverable history. Replication can copy an accidental deletion immediately, while a backup or point-in-time recovery can restore the state before that deletion. Production systems need both.

6. A replica is ten minutes behind. What do you do?

First, remove it from read routing for paths whose freshness budget it violates. Then identify whether the issue is network, disk, CPU, query contention, or a replay bottleneck. I would avoid promoting it without understanding the resulting RPO, and I would restore it to traffic only after its replay position and capacity are healthy.

Conclusion

Database replication is valuable because it makes a database service more than one machine, but it does not make the consistency problem disappear. A reliable design makes the trade-offs visible:

1. Use replicas for reads whose freshness requirements are explicit.
2. Keep read-after-write and correctness-critical paths on the primary or wait for a known commit position.
3. Measure lag as a routing and recovery signal, not just an operations chart.
4. Separate replication from backups and distinguish ordinary replicas from real failover capability.
5. Practice fencing, promotion, client recovery, and verification before an outage forces the decision.

The next database performance topic is debugging slow SQL queries, where query plans and indexes help determine whether replication is even the right scaling lever.

References

1. PostgreSQL Documentation: Hot Standby
2. Google Cloud: About Replication in Cloud SQL for MySQL
3. Cloudflare D1: Global Read Replication

YouTube Videos

1. “Database Replication & Sharding Explained” - Hayk Simonyan
   https://www.youtube.com/watch?v=jLEp1XI_L6Q
2. “Database Replication Explained (in 5 Minutes)” - Exponent
   https://www.youtube.com/watch?v=bI8Ry6GhMSE