One database is a single point of failure and a fixed ceiling on read capacity. Keeping copies fixes both — and introduces the problem of two copies disagreeing.
Synchronous or asynchronous
The one decision everything else follows from: does the write wait for the replica?
Synchronous. The primary acknowledges only after a replica confirms. No data loss on failover, and every write pays the network round trip. If the replica is slow, writes are slow; if it is down, writes stop.
Asynchronous. The primary acknowledges immediately and ships the change after. Fast, and a primary that dies before shipping loses acknowledged writes — data the client was told was safe.
Semi-synchronous. Wait for one replica, not all. This is the usual compromise, and it is what most production MySQL and Postgres setups run.
Replication lag, and the bug it causes
Steady state: primary and replica agree.
A user posts a comment, the page reloads, the comment is gone. They post it again.
The write went to the primary; the read went to a replica that had not caught up. Ten milliseconds of lag, and a confusing product.
Topologies
Single leader. All writes to one node, reads anywhere. Simple, no write conflicts, and the leader is a bottleneck and a failover event. This is what you should use unless you have a specific reason not to.
Multi-leader. Writes accepted at several nodes, typically one per region. Low write latency everywhere, and write conflicts are now possible — two regions editing the same row.
Leaderless (Dynamo-style). Write to several nodes, read from several, require quorums: so the sets overlap and a read sees the latest write. Cassandra and DynamoDB.
How the changes travel
Statement-based — ship the SQL. Compact, and breaks on NOW(), RANDOM()
and anything non-deterministic. Largely abandoned.
Write-ahead log shipping — ship the physical log. Efficient and ties replicas to the same storage format and version, so upgrades need downtime. See write-ahead logs.
Logical (row-based) — ship “row X changed from A to B”. Version-independent, supports replicating a subset of tables, and powers change-data-capture pipelines into search indexes and warehouses.
Failover is the hard part
Automatic failover has to answer three questions, and all three have teeth.
Is the leader really dead, or just slow? You cannot tell from outside. A timeout is a guess.
Which replica is most caught up? Promoting a lagging one discards writes.
What if the old leader comes back? Two nodes both believing they are leader is split-brain, and it produces two divergent histories that must be merged by hand. Fencing — a token that invalidates the old leader — is how this is prevented, and it is the part most homegrown failover forgets.
The honest answer is that failover is where consensus belongs. Rather than build it, use Raft or a system that already has.