Database Replication

Your database just died. Hardware failure, maybe. Doesn't matter why. What matters is that every application depending on it is now broken. Every user sees errors. Every transaction fails.

This is why databases get replicated—copied to other servers that stay synchronized. When the primary fails, a replica can take over. When reads overwhelm the primary, replicas share the load. When users span continents, replicas bring data closer to them.

But replication isn't free. It introduces hard questions: How synchronized do replicas need to be? What happens when they disagree? Who decides which one becomes the new primary? The answers depend on what you're willing to trade.

The Core Trade-Off: Speed vs. Safety

Every replication decision comes down to one question: Does the primary wait for replicas before confirming a write?

Synchronous Replication

The primary doesn't confirm a write until replicas have it too. If you write data to the primary, you know it exists on at least one other server before you get confirmation.

This is safe. If the primary explodes one second after confirming your write, the data survives on a replica.

But it's slow. Every write now includes network round-trips to replicas. If your replica is 50 milliseconds away, every write takes at least 50 milliseconds longer. If a replica is slow or unreachable, writes stall or fail entirely.

Asynchronous Replication

The primary confirms writes immediately and replicates in the background. Fast. The replica's problems don't become the primary's problems.

But there's a gap—replication lag. The replica is always slightly behind. If the primary fails, any writes that hadn't replicated yet are gone. You confirmed to the user that their data was saved, but it wasn't. Not really.

With asynchronous replication, you're trading the guarantee of "every write is safe" for the reality of "most writes are safe, and everything is faster."

Semi-Synchronous

A compromise: wait for at least one replica, but not all of them. You get some safety without the full latency penalty. Most production systems land here.

Primary-Replica: The Common Architecture

One database accepts writes (the primary). One or more databases receive copies (replicas). Writes flow one direction: primary to replicas. Replicas never write back.

This is simple and it works. The primary is the source of truth. Replicas are followers.

Writes go to the primary. Always. An application that needs to insert, update, or delete data talks to the primary.

Reads can go anywhere. Spread them across replicas to reduce load on the primary. The primary handles the hard work of coordinating writes; replicas handle the volume of reads.

The limitation is obvious: writes don't scale. You can add replicas forever and handle more reads, but every write still funnels through one server. For most applications, this is fine—reads vastly outnumber writes. For write-heavy workloads, it's a bottleneck.

Multi-Primary: When One Isn't Enough

What if multiple databases could accept writes, and they'd synchronize with each other?

This exists. It's called multi-primary (or multi-master) replication. It solves the write scalability problem and eliminates the single point of failure for writes.

It also creates a new problem: conflicts.

User A updates a record on primary 1. User B updates the same record on primary 2. Both writes succeed locally. Now the primaries synchronize. Which version wins?

Conflict resolution strategies exist:

• Last-write-wins: Compare timestamps, keep the later one. Simple, but you silently lose data.
• Application-defined merging: The application knows how to combine conflicting changes. Complex, but accurate.
• Avoid conflicts entirely: Partition data so different primaries handle different records. Works until it doesn't.

Multi-primary shines when conflicts are rare—geographically partitioned data where European users modify European records and American users modify American records. It struggles when the same data gets modified from multiple locations simultaneously.

Replication Lag: The Inevitable Gap

With asynchronous replication, replicas lag behind the primary. Usually milliseconds. Sometimes seconds. Occasionally minutes if something's wrong.

This creates consistency problems that applications must handle:

Read-your-writes: A user submits a form. The write goes to the primary. The page refreshes and reads from a replica. The replica hasn't received the write yet. The user sees old data and thinks their submission failed.

Solution: After a write, read from the primary for a few seconds, or route that user's reads to a specific replica and ensure it's caught up.

Monotonic reads: A user refreshes a page twice. The first request hits replica A (which is current). The second hits replica B (which is behind). The user sees newer data, then older data. Time appears to move backward.

Solution: Route the same user to the same replica consistently.

Applications that can tolerate stale data—dashboards, analytics, search results—can ignore lag entirely. Applications where users expect to see their own changes immediately need lag-aware routing.

Failover: When the Primary Dies

The primary fails. Now what?

Some replica needs to become the new primary. This is failover. It can happen automatically (software decides) or manually (humans decide).

Automatic Failover

Fast. Software detects the failure and promotes a replica within seconds or minutes.

But detection is hard. Is the primary actually dead, or just temporarily unreachable? Network partitions make healthy servers look dead. Promoting a replica while the primary is still alive creates split-brain: two databases both accepting writes, both convinced they're authoritative. When they reconnect, their data conflicts.

Split-brain is terrifying. Avoiding it requires consensus—multiple monitors must agree the primary is dead before promotion happens. This slows failover but prevents catastrophe.

Manual Failover

Slower. Humans investigate, confirm the primary is actually dead, choose which replica to promote, and execute the promotion.

This takes minutes instead of seconds. But humans can handle ambiguous situations better than algorithms. If failures are rare and minutes of downtime are acceptable, manual failover is safer.

Planned Failover

For maintenance, you can fail over gracefully:

1. Stop writes to the current primary
2. Wait for replicas to catch up completely
3. Promote a replica
4. Redirect traffic
5. Maintain the old primary at leisure

No data loss. No rush. Zero-downtime maintenance becomes possible.

Multi-Region Replication

Putting replicas in different geographic regions reduces latency for distant users. European users query European replicas. American users query American replicas.

But cross-region networks are slow—100 to 300 milliseconds of latency. Synchronous replication becomes impractical. Every write would take hundreds of milliseconds.

Multi-region deployments almost always use asynchronous replication and accept lag of seconds to minutes between regions. If the primary region fails entirely, the other region can take over, but recent writes may be lost.

Cloud providers also charge for cross-region bandwidth. Replicating terabytes of data across oceans adds meaningful cost.

How Replication Actually Works

Under the hood, databases replicate changes in different ways:

Row-based replication logs the actual data changes—the before and after values of modified rows. Replicas apply these changes directly. Deterministic and reliable, but generates larger logs.

Statement-based replication logs SQL statements and replicas re-execute them. Efficient, but non-deterministic functions (like NOW() or RAND()) produce different results on replicas. Most databases avoid this now.

Log shipping sends the primary's transaction log to replicas, which replay it. Simple and efficient for disaster recovery.

The method matters less than understanding that replication is never instant. Data must be captured, transmitted, and applied. Each step takes time and can fail.

Choosing Your Trade-Offs

Database replication isn't a feature you turn on. It's a set of trade-offs you choose:

• Speed vs. safety: Asynchronous is fast but risks data loss. Synchronous is safe but slow.
• Simplicity vs. scale: Primary-replica is simple but writes don't scale. Multi-primary scales writes but introduces conflicts.
• Automation vs. control: Automatic failover is fast but can make mistakes. Manual failover is slow but deliberate.

There's no universally correct answer. The right replication strategy depends on what your application can tolerate: How much latency? How much data loss? How much downtime? How much complexity?

Understanding these trade-offs lets you make informed decisions instead of discovering the consequences in production.