Database Replication WAL and Failover Visualization

Database replication is not just a topology diagram. The important lesson is the order of events: a primary appends WAL, replicas receive and replay it, lag changes read freshness and recovery point, and failover must preserve one writable history.

To explore these dynamics dynamically, you can also run the Database Replication simulator sandbox to experiment with async vs. sync mode, replication lag, primary crash, manual promotion, and split-brain scenarios under variable traffic loads.

Mental Model

At its core, database replication is the process of synchronizing state from a single authoritative node (the Primary) to one or more follower nodes (Standbys). Writes must flow through the primary to establish a global order, while reads can be distributed to followers. The primary tracks changes in a Write-Ahead Log (WAL). Replication protocols differ in how they ensure consistency versus performance: synchronous mode prioritizes correctness (waiting for confirmation), whereas asynchronous mode prioritizes write speed (replicating in the background).

Components and Why They Exist

Client App

This component exists to initiate transaction reads and writes toward our database. It routes write operations to the primary node and read queries through the load-balancing read router. In the demo, observe visual query dots traveling across the network: blue for writes, green for reads.

Primary Database

This component exists to accept write operations, establish transactional ordering, and stream logs to replicas. It processes incoming write transactions, appends them locally to the Write-Ahead Log (WAL), and broadcasts log entries to standbys. In the demo, observe the primary database node’s local WAL state transitions from queued to committed.

Sync Standby

This component exists to guarantee high-availability durability without risking data loss if the primary crashes. It receives WAL updates, flushes them to disk, and acknowledges receipt to the primary before the client write finishes committing. In the demo, watch the primary wait for an acknowledgment (ACK) from this node during synchronous commit steps.

Async Standby

This component exists to scale read throughput and provide disaster backup options without increasing client write latencies. It receives replication logs in the background and replays them to catch up with the primary at its own pace. In the demo, watch the replication lag indicator showing the LSN (Log Sequence Number) difference between this node and the primary.

Read Router

This component exists to redirect read traffic away from the primary database, preventing CPU bottlenecks and resource contention. It receives read queries, measures replica lag, and routes client connections to standbys that are reasonably up to date. In the demo, observe read queries (green particles) being routed to standby nodes.

Edge Cases and Misconceptions

• Synchronous is not “instantaneous”: Synchronous replication does not mean replicas apply the WAL at the exact same instant as the primary. It only guarantees the WAL is written to the standby’s disk before the primary returns success. The standby may still take time to replay those changes into its queryable engine.
• Stale reads on followers: Replicating asynchronously creates a data-safety window where reads routed to standbys can return stale data. Applications must be design-tolerant of eventual consistency.
• Split-brain risk: If a network partition occurs and the primary is isolated, replicas might elect a new primary. If the old primary is not fenced, it will continue accepting writes, diverging the dataset.