Database Replication: Copies, Lag, and Failover

One database copy creates one place to serve reads and one failure point.

Replication maintains additional copies on other nodes for read capacity, availability, recovery, or geographic latency.

Replication creates more copies, not a guarantee that every copy shows the same answer at every instant.

The replication mode determines when a write is considered committed and what failure can lose.

A concrete example: product reads and checkout writes

Product pages read from nearby replicas for speed.

Checkout writes inventory reservations to the primary authority. Immediately after purchase, a stale replica may still show the old stock count.

The application routes correctness-sensitive operations to the authoritative path.

Primary-replica replication

One primary accepts writes.

Replicas receive the primary's ordered change log and apply it. They can:

• serve reads,
• support backup,
• and become failover candidates.

The primary remains a write bottleneck.

Change logs

Databases record changes in a write-ahead or replication log.

Replicas consume log positions in order. Tracking positions reveals:

• sent,
• received,
• applied,
• and acknowledged state.

Lag is more precise when expressed in log position as well as time.

Synchronous replication

A primary waits for one or more replicas to confirm before committing.

This reduces committed-data loss if the primary fails, but adds network latency and can reduce availability when replicas are unreachable.

The exact acknowledgment may mean received in memory, persisted, or applied.

Asynchronous replication

The primary commits locally and sends changes later.

Writes are faster and remain available during replica trouble. If the primary fails before replication, recent committed writes can be lost during promotion.

The business must understand that recovery point.

Semi-synchronous modes

Some systems wait for at least one replica to receive or persist a change, but not fully apply it.

This balances durability and latency.

Names differ across databases, so read the exact guarantee.

Replication lag

Lag comes from:

• network delay,
• slow disk,
• large transaction,
• lock,
• replica CPU,
• schema change,
• or replay backlog.

Monitor worst replica and oldest unapplied change, not only an average.

Stale reads

Replica reads may return older data.

This can be acceptable for:

• product browsing,
• analytics,
• or historical reports.

It can be harmful for:

• permission revocation,
• account balance,
• read-after-write confirmation,
• or uniqueness decisions.

Read-your-writes

A user expects to see a change they just saved.

Options include:

• read from primary,
• sticky session,
• wait for replica position,
• pass a consistency token,
• or show optimistic local state.

Choose according to latency and correctness need.

Read scaling

Replicas spread read traffic.

They do not automatically scale:

• writes,
• locks on primary,
• or cross-replica coordination.

Read queries can also delay replay if they consume replica resources.

Failover

Failover promotes a replica when primary is unavailable.

The process needs:

• failure detection,
• candidate selection,
• fencing of old primary,
• routing update,
• client reconnect,
• and data-loss assessment.

Promotion is not only changing a DNS name.

Split brain

If old and new primaries both accept writes, histories diverge.

Consensus, leases, quorum, and fencing reduce this risk. A node that loses authority must be prevented from writing shared storage or serving clients.

Merging arbitrary database writes later is difficult.

Automatic failover

Automation reduces recovery time but can react incorrectly to transient network separation.

Tune:

• detection timeout,
• quorum,
• promotion rules,
• and retry behavior.

Test failure modes instead of assuming managed failover is flawless.

Recovery objectives

Replication policy should connect to:

• recovery point objective: how much recent committed data can be lost,
• recovery time objective: how long service can remain unavailable.

Asynchronous regional replication may satisfy a longer disaster-recovery objective while synchronous zonal replication protects routine host failure. One topology rarely optimizes both latency and zero data loss across long distance.

Promotion readiness

A replica is not a valid failover candidate merely because it exists.

Check lag, storage health, schema version, required extensions, capacity, and backup status. Promotion of an undersized or incompatible replica can replace one outage with a different one.

Rejoining a former primary

After recovery, the old primary cannot simply resume if its history differs.

It may need:

• rewind to common point,
• resynchronization,
• or full rebuild from the new primary.

Preserve evidence before destructive repair when data loss is possible.

Multi-primary replication

Several nodes accept writes.

This improves regional write availability but creates conflict:

• same row changed,
• unique value allocated twice,
• order differs,
• or delete races with update.

Applications need conflict-free operations or explicit resolution.

Logical and physical replication

Physical replication copies storage-level changes and closely mirrors the database.

Logical replication copies row or statement changes and can support selective tables, version migration, or different schemas.

Each has compatibility, performance, and DDL behavior.

Replication is not backup

Accidental deletion, corruption, and malicious change replicate too.

Backups preserve historical points and independent recovery copies.

High availability and point-in-time recovery address different failures.

Schema changes

Replicas must understand changes in the log.

Large migrations can create lag or lock traffic. Logical replication may require compatible schema on both sides.

Stage migrations and monitor replay.

Observability

Track:

• primary status,
• log position,
• lag,
• replay errors,
• replica read latency,
• failover events,
• and client connection failures.

Connect database events to user-facing SLOs.

Knowledge check

1. How do synchronous and asynchronous replication trade latency and data loss?
2. Why can read replicas return stale data?
3. What does fencing prevent during failover?
4. Why can a former primary require rebuilding?
5. Why is replication not a backup?

The one idea to remember

Database replication copies an ordered stream of changes to additional nodes for reads and recovery. Correct use requires explicit commit guarantees, lag-aware routing, safe failover and fencing, client reconnection, and separate historical backups.