Database replication

In the most common setup all writes go to the primary, which ships changes to replicas. PostgreSQL does this with streaming replication over WAL, MySQL with binlog replication. Replicas serve reads only.

If reads outnumber writes 10 to 1 (course catalog, lesson pages), two replicas take most of the load off the primary. Reports and analytics should also run on a separate replica so heavy queries do not slow down production.

Asynchronous replication: the primary acknowledges a write immediately and the replica catches up later. Fast, but if the primary dies, the last milliseconds or seconds of writes can be lost.

Synchronous: the primary waits for at least one replica to confirm (synchronous_commit and synchronous_standby_names in PostgreSQL). Nothing is lost, but every write is slower by a round trip to the replica, so the synchronous standby is kept in the same region.

Lag is usually milliseconds, but under heavy writes or a long query on the replica it grows to seconds. The classic bug: a user changes their name, the page rereads the profile from a replica and shows the old one. The fix is read-your-writes: read from the primary for a few seconds after the user's own write.

Failover: when the primary dies, one replica is promoted to primary and the app switches to it. Patroni does this for PostgreSQL, orchestrator for MySQL, and managed databases (RDS, Cloud SQL) do it automatically in 1 or 2 minutes.