Cheat sheet · Data & storage
Database replication
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
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_aMermaid: 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 SYSTEMD2
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
@endumlPlantUML: 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
@endumlRecap
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
| Metric | Start | End |
|---|---|---|
| Request p95 | 850 ms | 58 ms |
| Worst replica lag | 0 ms | 5 ms |
| Stale reads | 0% | 0% |
| Commits lost on failover | 0 | 0 |
Steps
- Problem One database takes every read and write
Every profile view and every edit goes to one database, DB A.
- Fix Two followers copy the leader's log
DB A becomes the leader: it alone takes writes.
- Problem A user saves, reloads and sees the old bio
User
u_42changes their bio. - Case Measuring replication lag
The leader knows how far each follower is.
- 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.
- Problem The leader dies
DB A loses its disk.
- Fix Failover: promote the most up-to-date replica
A failover manager has watched DB A for 30 seconds with no answer.
- Case The old leader comes back as a follower
DB A gets a new disk and boots.
- 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.
- 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.