Database Replication: Logs, Lag, and Failover

Three database nodes contain nearly the same rows. A dashboard calls the cluster highly available, so an application writes to the primary and immediately reads from a replica. The row is missing. Minutes later the primary fails, a replica is promoted, and an order that previously returned success is absent everywhere the application can reach. Replication was running in both cases; its actual contract was never stated.

Replication copies an ordered stream of database changes from a writer to other nodes. Between local commit and a replica serving the change lie transport, durable receipt, recovery or apply, query snapshots, and routing. Each stage can lag independently. Failover adds another requirement: exactly one writable history must remain authoritative, even when the old primary is isolated rather than dead.

The thesis is that replication guarantees come from acknowledgement and promotion rules, not replica count. Physical and logical streams carry different information; synchronous and asynchronous acknowledgement choose different latency, availability, and data-loss bounds; lag determines what replicas can answer; fencing determines whether failover creates one history or two.

Replication Is a Pipeline of Positions #

A primary serializes committed changes into a log or change stream. A sender reads from some source position, transmits records, and receives progress reports from replicas. On a replica, records may be received into memory, written to local storage, flushed durably, and finally replayed into data pages or logical tables. Queries normally see applied state, not merely bytes waiting in a receive buffer.

This creates several meaningful positions:

• Primary generated position: latest change produced by the writer.
• Replica received position: latest byte or event delivered over the network.
• Replica durable position: latest point guaranteed to survive that replica’s restart.
• Replica replay position: latest change incorporated into queryable database state.
• Client-required position: latest commit a request must observe to satisfy its session contract.

Lag is the distance between two named positions. Transport lag means records have not arrived. Flush lag means they arrived but are not durable. Replay lag means durable records are queued behind apply. A single “replica lag: 2 seconds” gauge hides which stage is slow and often estimates time using the timestamp of the last replayed transaction. On an idle database, that estimate can look old despite no missing work; on a busy database, bytes or log positions give a clearer backlog.

The pipeline can stop at any boundary. A network partition grows send lag. Slow replica storage grows flush lag. A long-running query, single-threaded apply, expensive index maintenance, or a conflicting schema operation can grow replay lag. Diagnose the blocked stage before adding replicas or raising a generic timeout.

Physical and Logical Replication Carry Different Contracts #

Physical replication ships low-level changes from the database recovery log or copies changed storage blocks. A standby reconstructs the same engine-specific page and catalog state. It is usually efficient for maintaining a close binary replica and can reproduce DDL, indexes, and internal changes without translating each row. The cost is tight coupling to storage format, engine version, system architecture, and often the whole database cluster rather than selected tables.

Logical replication decodes committed operations into records such as insert, update, and delete for identified tables. A subscriber applies those operations through its own storage engine. It can select or transform data, replicate between some version boundaries that physical streams cannot cross, and feed analytics or service-owned projections. It must identify updated rows, handle schema compatibility, order transactions correctly, and define what happens to DDL, sequences, large objects, and operations the decoder does not represent.

Neither mode makes concurrent independent writes merge safely. Conventional primary-replica topology has one writer for a reason. Bidirectional logical replication needs conflict semantics for the same keys, unique constraints, sequences, and transaction dependencies. Labeling both nodes “active” does not define those semantics.

Use physical replicas for low-overhead high-availability standbys and read scaling when engine coupling is acceptable. Use logical replication for selective movement, migrations, change feeds, and heterogeneous consumers. Some architectures use both: physical standbys protect the primary, while a logical stream supplies downstream data products.

Synchronous and Asynchronous Acknowledgement Choose the Failure Envelope #

With asynchronous replication, the primary can acknowledge commit after satisfying its local durability rule, without waiting for a replica. Normal latency is independent of replica round trips and writes can continue while replicas are unavailable. If the primary is lost before its final log records become durable on a promotable replica, acknowledged transactions can be lost during failover.

With synchronous replication, commit waits for one or more replicas to reach a configured stage. That stage matters. Waiting for receipt protects only against network retransmission. Waiting for the replica to write may still leave volatile caches. Waiting for durable flush protects against replica restart. Waiting for replay also makes the change queryable there but adds apply latency to every commit. Products use different names, so map settings to actual stages.

The required replica set matters too. Waiting for one selected standby has different failure tolerance from waiting for any one of several, a quorum, or replicas in multiple failure domains. A synchronous replica on the same host protects against a process failure but not host loss. A remote region protects a larger failure domain while adding network latency and exposure to partitions.

Many systems permit timeout-based fallback from synchronous to asynchronous operation. That may be an appropriate availability decision, but the durability contract changes at the fallback instant. Emit an explicit event, show which commits used which mode, and prevent operators from reading a green “replication enabled” status as proof of zero data loss.

Worked Example: Promote Only What a Replica Has #

Assume primary P has two asynchronous standbys. Log positions identify commit boundaries:

P:  local durable=930
S1: received=928, durable=920, replayed=918
S2: received=925, durable=925, replayed=925

The application has received success for transactions through position 930 because P acknowledged after local durability. A read routed to S1 can observe only state through 918, despite S1 having later bytes in memory and on disk. S2 is less current in network receipt than S1 but is the better failover candidate because it has durably replayed through 925.

P then becomes unreachable. The failover controller confirms that P cannot continue serving writes, chooses S2, records a new authority epoch, and promotes it. Transactions whose commits fall after 925 may be absent. The recovery point objective realized by this incident is the missing committed interval (925, 930], not the age of a dashboard sample. If those positions represent three acknowledged orders, those three orders require reconciliation.

Suppose a client wrote at position 929 and carries that position as a session token. After promotion, S2 cannot satisfy it. Returning a row from 925 as though read-your-writes held would be false. The router can wait for a node that reaches 929, return an explicit consistency-unavailable response, or invoke a product-specific reconciliation path. It cannot create the missing history by retrying the read.

When P returns, it still has records through 930, but it must not simply resume as primary. S2 may already have accepted a new transaction at position 926 on a new timeline. The histories diverge. P must remain fenced, then be rewound to the common ancestor where supported or fully reseeded from the new primary. Its orphaned tail can be preserved separately for audit and reconciliation, not merged by replaying both logs blindly.

Had commits waited for S2 to flush each record before acknowledgement, the successful interval would have been durable there. But if S2 became unreachable, P would have had to block commits, switch explicitly to a weaker mode, or use another eligible synchronous standby. Zero acknowledged-write loss is an availability and latency policy, not a free property of the topology diagram.

Replica Reads Need an Explicit Freshness Policy #

Read scaling is useful only when each request’s freshness requirement is known. Analytics, search suggestions, and historical reports may tolerate lag. Reading an order immediately after creating it, enforcing a uniqueness-like business decision, or making authorization decisions often cannot. Routing every SELECT to a replica based solely on SQL syntax creates correctness bugs that appear as intermittent missing data.

Common policies include:

• Primary reads: route operations requiring current writer state to the primary.
• Session stickiness: keep a client on the primary for a bounded period after writes; simple but time is only a proxy for progress.
• Position tokens: return the commit LSN or logical offset with a write, then route subsequent reads only to a node replayed through that position.
• Bounded staleness: serve from a replica only when measured position or time lag is inside a stated bound; otherwise fall back or reject.
• Monotonic routing: remember the greatest position a session has observed so later reads never go to an older replica.

Position tokens express causality better than sleeping for an assumed replication delay. They need topology-aware translation across failover timelines and a timeout policy when no replica catches up. They also do not make two independent client sessions share a freshness requirement unless the application propagates the token.

Replica queries can worsen lag. A long snapshot may conflict with replay that needs to remove or change old versions. The database may delay replay, cancel the query, or retain extra history on the primary depending on configuration. “Use replicas so reads cannot affect writes” is therefore too strong: retention slots can fill primary storage, synchronous acknowledgement can wait for a replica, and replay conflicts can trade query completion for freshness.

Failover Requires Authority, Fencing, and Reconfiguration #

A health check proves only that one observer cannot currently communicate with a node. It does not prove that the node is dead or unable to serve clients elsewhere. Promoting a replica while the old primary remains writable creates split brain: two histories accept commits, and ordinary physical replication has no general way to merge them.

Safe failover needs an authority mechanism outside a candidate’s self-assessment. A consensus-backed control store, quorum lease, or managed database control plane can assign a monotonically increasing leadership epoch. Every write path must enforce that authority. Fencing can revoke storage access, terminate the old process, remove network routes, reject stale epochs at proxies, or use infrastructure power controls. Updating DNS alone is weak fencing because cached routes and direct connections may still reach the old primary.

A failover sequence should be explicit:

1. Detect symptoms and distinguish local overload from primary unavailability.
2. Prevent or prove the prevention of further writes on the old primary.
3. Compare eligible replicas using durable, replayable positions and failure-domain policy.
4. Promote exactly one candidate and assign a new authority epoch.
5. Reconfigure writers, readers, connection pools, jobs, and maintenance tools.
6. Verify writes, constraints, replication topology, and application freshness behavior.
7. Rewind or reseed former nodes before they rejoin.

Promotion itself is only part of recovery time. Clients may cache endpoints, pools may retain broken sessions, prepared application work may have unknown outcomes, and replicas may need new upstreams. Measure failover from user-impact start until successful, correctly routed reads and writes resume.

Set RPO and RTO Before Choosing Topology #

The recovery point objective (RPO) is the maximum acceptable data-loss interval expressed in business terms or positions. The recovery time objective (RTO) is the maximum acceptable time to restore the service. An asynchronous cross-region replica may offer low write latency and nonzero RPO. A synchronous cross-region quorum may target zero acknowledged-write loss but increase latency and become unavailable during some partitions. A delayed replica intentionally has high freshness lag so operators can stop replay before an accidental deletion arrives.

Topology should follow those objectives and failure domains. Put replicas on different hosts or zones if host or zone loss is in scope. Size network and storage for peak log generation plus catch-up, not average writes. A replica that falls farther behind during every traffic peak is not spare capacity; it is accumulating recovery risk. Cascading replication saves primary bandwidth but makes downstream freshness and failure dependencies less direct.

Replication does not replace backups. Every replica eagerly applies a valid DROP TABLE, bad application update, or compromised credential’s deletion. Backups and retained logs provide an older independent recovery point; restore drills prove they work. Nor does a replica automatically provide read capacity during failover: promoting it removes that read node and may overload the remaining topology.

State objectives concretely: “No acknowledged payment may be lost after one-zone failure,” or “inventory analytics may be 60 seconds stale, and transactional writes must resume within five minutes.” Then choose acknowledgement stage, eligible promotion set, regions, routing, retention, and automation that can actually meet those statements.

Test and Operate the Failure Paths #

Monitor every pipeline stage: generated, sent, received, durable, and replay positions; byte and time distance; apply throughput; oldest unapplied transaction; synchronous commit wait; replication connection state; retained log bytes; slot or subscription health; query conflicts; and restart/reseed duration. Alert on growing backlog and disk headroom, not just disconnected replicas. A connected replica can be irrecoverably falling behind.

Test freshness with deterministic barriers. Commit a marker, capture its position, query each replica before and after replay reaches that position, and assert the routing policy. Pause network transport, storage flush, and apply separately so dashboards identify the correct lag stage. Apply schema changes under logical replication in a staging topology and verify old/new schema compatibility, transaction ordering, and conflict handling.

Failover drills should kill or isolate the primary at each acknowledgement boundary, including when a replica has received but not flushed a commit and when it has flushed but not replayed it. Verify the chosen candidate is the most advanced eligible node, measure the lost interval against RPO, and ensure a client position token is never silently satisfied by older data. Reintroduce the old primary while it still believes it is leader and prove fencing rejects its writes.

Exercise the whole service: connection retry behavior, unknown transaction outcomes, background workers, sequence generators, read routing, monitoring, backup jobs, and replica reparenting. Then restore from backup and replay to a selected point to cover corruption or operator error that replication cannot solve. Preserve an audit record containing detection time, authority epoch, candidate positions, promotion decision, missing interval, and reconciliation actions.

Takeaways #

• Replication has receive, durable, and replay positions; “lag” is meaningful only between named stages.
• Physical streams reproduce engine state efficiently, while logical streams trade low-level fidelity for selection and transformation.
• Asynchronous commits favor latency and availability but can lose an acknowledged tail; synchronous commits move that trade into the request path.
• Replica reads require primary routing, position tokens, bounded-staleness rules, or another explicit freshness contract.
• Failover must choose the most advanced eligible history and fence the old writer before promotion.
• RPO and RTO are business constraints that determine acknowledgement, topology, and automation.
• Replicas copy mistakes, so independent backups and point-in-time restore drills remain necessary.
• Lag injection, crash-boundary failover, stale-read checks, and split-brain fencing tests turn topology claims into evidence.

A replica is useful because it holds another copy of an ordered history. Availability and correctness emerge only when the system says which prefix is durable, which prefix a reader may observe, and who has authority to extend that history after failure.