mufassall

Cheat sheet · Data & storage

Database replication

13 min · Intermediate · mufassall.dtwin.uz/en/l/replication/

← Open the lesson

The problem

A profile service outgrows one database. We add read replicas, meet replication lag and the read-your-writes problem, lose the leader, fail over, and decide what synchronous replication is worth.

What you learn

  • How a leader streams its log to followers, and what replication lag is
  • Why a user can stop seeing their own write, and two ways to fix it
  • What failover does, and why async replication can lose commits
  • The price of a synchronous replica
Final scene
50%20%40%55%

Diagram as code

The lesson's final design as text: paste it into Jira, GitLab, GitHub, Notion or your docs, where Mermaid renders by itself. There are D2 and PlantUML (Confluence, IntelliJ) versions too.

Mermaid: flowchart

flowchart LR
  n_app(["Profile app"])
  n_api["Profile API"]
  n_db_a[("DB A")]
  n_db_b[("DB B")]
  n_db_c[("DB C")]
  n_sentinel["Failover manager"]
  n_app -->|"call"| n_api
  n_api -->|"call"| n_db_b
  n_api -->|"call"| n_db_c
  n_sentinel -->|"call"| n_db_c
  n_api -->|"call"| n_db_c
  n_db_c -->|"call"| n_db_b
  n_db_c -->|"async"| n_db_a

Mermaid: request sequence

sequenceDiagram
  participant n_api as Profile API
  participant n_db_a as DB A
  participant n_db_b as DB B
  participant n_sentinel as Failover manager
  participant n_db_c as DB C
  Note over n_api,n_db_a: One database takes every read and write
  n_api->>n_db_a: SELECT name, bio FROM profiles
  n_db_a-->>n_api: (1 row)
  Note over n_db_a,n_db_b: Two followers copy the leader's log
  n_db_a->>n_db_b: START_REPLICATION SLOT db_b PHYSICAL 3/A1000000
  n_db_b-->>n_db_a: XLogData lsn=3/A1C04F28 len=8192
  Note over n_api,n_db_b: A user saves, reloads and sees the old bio
  n_api->>n_db_b: SELECT bio, updated_at FROM profiles
  n_db_b-->>n_api: bio #124; updated_at
  Note over n_api,n_db_a: Measuring replication lag
  n_api->>n_db_a: SELECT application_name, replay_lag
  n_db_a-->>n_api: application_name #124; replay_lag
  Note over n_api,n_db_b: Read-your-writes: route by the user's last write
  n_api->>n_db_b: SELECT pg_last_wal_replay_lsn()
  n_db_b-->>n_api: caught_up
  Note over n_api,n_db_a: The leader dies
  n_api->>n_db_a: UPDATE profiles SET bio = 'Backend + SRE'
  n_db_a-->>n_api: ERROR: could not connect to db-a:5432
  Note over n_sentinel,n_db_c: Failover: promote the most up-to-date replica
  n_sentinel->>n_db_c: failover: db-a down for 30 s
  Note over n_db_c,n_db_a: The old leader comes back as a follower
  n_db_c->>n_db_a: pg_rewind --source-server=db-c
  n_db_a-->>n_db_c: source timeline 8, target timeline 7
  Note over n_api,n_db_c: One synchronous replica: no acknowledged write is lost
  n_api->>n_db_c: ALTER SYSTEM SET synchronous_standby_names
  n_db_c-->>n_api: ALTER SYSTEM

D2

direction: right

n_app: "Profile app" {
  shape: rectangle
}
n_api: "Profile API" {
  shape: rectangle
}
n_db_a: "DB A" {
  shape: cylinder
}
n_db_b: "DB B" {
  shape: cylinder
}
n_db_c: "DB C" {
  shape: cylinder
}
n_sentinel: "Failover manager" {
  shape: rectangle
}

n_app -> n_api: "call"
n_api -> n_db_b: "call"
n_api -> n_db_c: "call"
n_sentinel -> n_db_c: "call"
n_api -> n_db_c: "call"
n_db_c -> n_db_b: "call"
n_db_c -> n_db_a: "async"

PlantUML: request sequence

@startuml
participant "Profile API" as n_api
database "DB A" as n_db_a
database "DB B" as n_db_b
participant "Failover manager" as n_sentinel
database "DB C" as n_db_c
note over n_api, n_db_a : One database takes every read and write
n_api -> n_db_a : SELECT name, bio FROM profiles
n_db_a --> n_api : (1 row)
note over n_db_a, n_db_b : Two followers copy the leader's log
n_db_a ->> n_db_b : START_REPLICATION SLOT db_b PHYSICAL 3/A1000000
n_db_b -->> n_db_a : XLogData lsn=3/A1C04F28 len=8192
note over n_api, n_db_b : A user saves, reloads and sees the old bio
n_api -> n_db_b : SELECT bio, updated_at FROM profiles
n_db_b --> n_api : bio | updated_at
note over n_api, n_db_a : Measuring replication lag
n_api -> n_db_a : SELECT application_name, replay_lag
n_db_a --> n_api : application_name | replay_lag
note over n_api, n_db_b : Read-your-writes: route by the user's last write
n_api -> n_db_b : SELECT pg_last_wal_replay_lsn()
n_db_b --> n_api : caught_up
note over n_api, n_db_a : The leader dies
n_api -> n_db_a : UPDATE profiles SET bio = 'Backend + SRE'
n_db_a --> n_api : ERROR: could not connect to db-a:5432
note over n_sentinel, n_db_c : Failover: promote the most up-to-date replica
n_sentinel -> n_db_c : failover: db-a down for 30 s
note over n_db_c, n_db_a : The old leader comes back as a follower
n_db_c ->> n_db_a : pg_rewind --source-server=db-c
n_db_a -->> n_db_c : source timeline 8, target timeline 7
note over n_api, n_db_c : One synchronous replica: no acknowledged write is lost
n_api -> n_db_c : ALTER SYSTEM SET synchronous_standby_names
n_db_c --> n_api : ALTER SYSTEM
@enduml

PlantUML: components

@startuml
left to right direction
agent "Profile app" as n_app
component "Profile API" as n_api
database "DB A" as n_db_a
database "DB B" as n_db_b
database "DB C" as n_db_c
component "Failover manager" as n_sentinel
n_app --> n_api : call
n_api --> n_db_b : call
n_api --> n_db_c : call
n_sentinel --> n_db_c : call
n_api --> n_db_c : call
n_db_c --> n_db_b : call
n_db_c --> n_db_a : async
@enduml

Recap

Replicas give read capacity and a spare for failover. They do not give fresh data: async replicas lag, and lag grows under load.

1) Writes go to the leader, reads to replicas. 2) Measure lag and route a user's reads to the leader right after their write. 3) Failover promotes the most up-to-date replica and must fence the old leader. 4) Async can lose acknowledged commits; one synchronous replica prevents it at the cost of latency.

Numbers: at the start and at the end

MetricStartEnd
Request p95850 ms58 ms
Worst replica lag0 ms5 ms
Stale reads0%0%
Commits lost on failover00

Steps

  1. Problem One database takes every read and write

    Every profile view and every edit goes to one database, DB A.

  2. Fix Two followers copy the leader's log

    DB A becomes the leader: it alone takes writes.

  3. Problem A user saves, reloads and sees the old bio

    User u_42 changes their bio.

  4. Case Measuring replication lag

    The leader knows how far each follower is.

  5. Fix Read-your-writes: route by the user's last write

    After a write, the API remembers the leader's log position for that user's session.

  6. Problem The leader dies

    DB A loses its disk.

  7. Fix Failover: promote the most up-to-date replica

    A failover manager has watched DB A for 30 seconds with no answer.

  8. Case The old leader comes back as a follower

    DB A gets a new disk and boots.

  9. Fix One synchronous replica: no acknowledged write is lost

    We make the leader wait: a commit is acknowledged only after at least one of the two followers has the log on disk.

  10. Recap Recap

    Replicas give read capacity and a spare for failover.

Terms

Replication (leader and followers)
The leader takes every write and streams its log to followers, which replay it and keep a copy. Replicas give read capacity and a spare for failover.
Replication lag
How far an asynchronous replica is behind the leader. It grows under load, so a read from a replica can return stale data.
Read-your-writes
A guarantee that users always see their own writes. After a write, that user's reads go to the leader until the replica has caught up.