Path 1 · lesson 4 of 8
Replication and lag
How Postgres streams changes to replicas and Cassandra writes to all of them at once, why replicas fall behind, and what synchronous commit waits for.
What you will learn
- Explain how a Postgres replica and a Cassandra replica each receive a write
- Measure replication lag and recognise the read-your-writes problem it causes
- Say exactly what synchronous_commit = on and remote_apply wait for, and what a down synchronous replica does to writes
- Describe how Cassandra keeps writing while a replica is down, and how the replica catches up
Before you start
The concept
Replication keeps copies of the same data on several machines, so that losing one machine loses no data and more machines can serve reads. The hard part is not copying: it is that the copies are never updated at exactly the same moment. The gap is replication lag, and every design decision in this lesson is about how big it may get and who notices.
| Postgres | Cassandra | |
|---|---|---|
| Model | Single leader: one primary takes all writes | Leaderless: any replica can take a write |
| How changes travel | The primary streams its WAL; replicas replay it | The coordinator sends each write to all replicas at once |
| Replicas accept writes? | No, they are read-only | Yes, all of them |
| A replica is down | Async: writes carry on. Sync: commits wait | Writes carry on; the coordinator keeps hints for it |
| How much to wait for | synchronous_commit and synchronous_standby_names | The consistency level of each request |
Why it exists
Replicas are how teams scale reads and survive machine failures, so almost every production database has them. And almost every team that adds read replicas meets the same bug: a user saves a form, the page reloads from a replica, and the change is not there. Worse, the lag is usually milliseconds, so the bug appears only under load, during a large migration or a vacuum, exactly when the team is busy.
Synchronous replication looks like the fix and is often misunderstood. It protects durability (a committed write survives losing the primary), not necessarily visibility on the replica, and it makes every write depend on the replica being up. Knowing which guarantee you are buying, and its price, is the Staff-level part.
How it works inside
Postgres: one primary, WAL streamed to replicas
Every change in Postgres is first written to the WAL (lesson 1). A replica connects to the primary, receives the WAL as it is written, saves it to its own disk and replays it, applying each change to its own copy of the pages. The lab starts a primary and two replicas, r1 and r2, created with pg_basebackup.
Database outputThe primary's view of its replicas
Real database outputapplication_name | state | sync_state ------------------+-----------+------------ r1 | streaming | async r2 | streaming | async
Database outputA replica refuses writes
Real database outputERROR: cannot execute INSERT in a read-only transaction
Lag breaks read-your-writes
Real lag comes and goes with load, which makes it hard to show. The lab holds it still by pausing replay on r1 with pg_wal_replay_pause(), which stands in for a replica that is busy, slow or blocked by a long query. Then it writes an order on the primary and reads it back from both.
Database outputThe same read, on the primary and on r1
Real database outputOn the primary: id | customer | total ----+----------+------- 42 | alice | 99.50 (1 row) On replica r1, a moment later: id | customer | total ----+----------+------- (0 rows)
Database outputMeasuring the gap
Real database outputpaused | received_not_applied_bytes --------+---------------------------- t | 21872 (1 row) application_name | bytes_behind ------------------+-------------- r1 | 21872 r2 | 0
Unpaused, lag is normally small but real. While the primary created a 3-million-row table, the lab sampled how far behind each replica was every half second:
Database outputLag during a bulk write
Real database outputt+0ms r1 784 bytes behind | r2 784 bytes behind t+500ms r1 22 MB behind | r2 8944 kB behind t+1000ms r1 16 MB behind | r2 4608 kB behind t+1500ms r1 5376 kB behind | r2 13 MB behind t+2000ms r1 20 MB behind | r2 20 MB behind
Database outputThe lag columns of pg_stat_replication
Real database outputapplication_name | write_lag | flush_lag | replay_lag ------------------+-----------------+-----------------+----------------- r1 | 00:00:00.099907 | 00:00:00.103039 | 00:00:00.123612 r2 | 00:00:00.100111 | 00:00:00.103085 | 00:00:00.123432
Synchronous commit: what it waits for
Setting synchronous_standby_names makes the primary wait for a replica before it confirms a commit. How far it waits is set by synchronous_commit. The default, on, waits until the replica has flushed the WAL to disk. The lab makes r1 synchronous, pauses replay again, and commits.
Database outputSynchronous, but not visible
Real database output application_name | sync_state
------------------+------------
r1 | sync
r2 | async
(2 rows)
The commit returned. On replica r1 (replay paused):
bob_rows_on_r1
----------------
0synchronous_commit = remote_apply waits for the replica to replay the change, which does give read-your-writes on that replica. With replay paused, the commit simply waits:
Database outputA commit waiting for remote_apply
Real database outputstate | wait_event_type | wait_event | query --------+-----------------+------------+-------------------------------------------------------------- active | IPC | SyncRep | SET synchronous_commit = remote_apply; INSERT INTO orders VA
The price: a down synchronous replica stops commits
Database outputr1 is stopped; an insert on the primary three seconds later
Real database outputstate | wait_event_type | wait_event | query --------+-----------------+------------+---------------------------------------------- active | IPC | SyncRep | INSERT INTO orders VALUES (45, 'dave', 3.10)
Database outputWhat the session reports when the wait is cancelled
Real database outputWARNING: canceling wait for synchronous replication due to user request DETAIL: The transaction has already committed locally, but might not have been replicated to the standby. INSERT 0 1
That is why one synchronous replica is rarely configured alone. The next lesson shows the usual answer, a quorum of replicas (ANY 1 (r1, r2)), with which the same insert succeeded in under 5 ms while r1 was still down.
Cassandra: every replica takes the write
Cassandra has no primary. The node a client connects to becomes the coordinator for that request and sends the write to every replica of the partition at the same time. The consistency level says how many must acknowledge before the client gets success. The lab runs three nodes with replication factor 3, so every row lives on all three, and stops node c3.
Database outputc3 is down; the same update at three consistency levels
Real database outputONE:
Consistency level set to ONE.
QUORUM:
Consistency level set to QUORUM.
ALL:
Consistency level set to ALL.
code=1000 [Unavailable exception] message="Cannot achieve consistency level ALL" info={'consistency': 'ALL', 'required_replicas': 3, 'alive_replicas': 2}For the successful writes, the coordinator also kept a hint: a copy of the write addressed to c3, stored on its own disk.
Database outputThe hint file on c1, and after c3 returns
Real database outputHint files c1 is holding for the down node (bytes, file): 186 5239661e-fc4d-4c74-a130-f1ed1b765be6-1794920494056-2.hints --- 3. c3 comes back; the hints are replayed Hint files on c1 after delivery: 0 Read at ONE on c3 (served from c3's own copy): Consistency level set to ONE. items ------- 2
max_hint_window (3 hours by default). A node down for longer needs a repair, covered in the Cassandra path.Hints are best effort. The next lesson turns hints off and shows the consequence: a replica that missed a write serves the old value until something repairs it.
Architecture
Drawing diagram…
Two diagrams. Postgres: a client sends all writes to the primary, which streams WAL to replicas r1 and r2; reads from a replica may be stale. Cassandra: a client sends a write to any node, which acts as coordinator and sends it to all three replicas; for a replica that is down it keeps a hint file.
flowchart LR
subgraph PG["Postgres: single leader"]
C1["Client"] -->|all writes| P["Primary"]
P -->|WAL stream| R1["Replica r1"]
P -->|WAL stream| R2["Replica r2"]
C1 -.->|reads, maybe stale| R1
end
subgraph CA["Cassandra: leaderless, RF 3"]
C2["Client"] --> CO["Coordinator, any node"]
CO -->|write| N1["Replica"]
CO -->|write| N2["Replica"]
CO -->|write| N3["Replica, down"]
CO -.->|hint kept for it| H[("Hint file")]
end- In Postgres the replicas are always behind the primary, by however much WAL they have not replayed. Lag is one number per replica.
- In Cassandra there is no 'behind': each replica may or may not have each write. A replica that missed writes is caught up by hints, read repair or repair, each covered in the next lessons.
- Both let you choose, per commit or per request, how many copies must have a write before the client is told it succeeded.
Step by step
Drawing diagram…
Sequence diagram. The application commits on the primary, which writes WAL locally and streams it to replica r1. The replica writes and flushes the WAL and reports the flushed position; synchronous_commit on stops waiting there. The replica then replays the WAL and reports the applied position; remote_apply stops waiting there. The primary then confirms the commit.
sequenceDiagram participant App as Application participant P as Primary participant R as Synchronous replica r1 App->>P: INSERT, COMMIT P->>P: write WAL locally P->>R: stream WAL R->>R: write and flush WAL R-->>P: flushed up to this position Note over P: synchronous_commit = on stops waiting here R->>R: replay WAL into pages R-->>P: applied up to this position Note over P: remote_apply stops waiting here P-->>App: COMMIT confirmed
- With
on, the commit is durable on two machines but may not be visible on the replica yet, as the lab showed. - With
remote_apply, the commit is visible on the replica when it returns, at the cost of waiting for replay, which a long query on the replica can delay. - With no synchronous replica at all, the primary confirms after its own WAL flush. A primary that dies at that moment can lose the last commits the replicas had not received.
In a real system
- Route reads that must see the user's own write to the primary. A common pattern: after a write, pin that user's reads to the primary for a few seconds, or carry the WAL position of the write and wait for the replica to reach it.
- Alert on replay lag, in seconds and in bytes.
pg_stat_replicationon the primary hasreplay_lagand the LSN columns; sustained growth means the replica cannot keep up. - Never run one synchronous replica alone. Use
ANY 1 (r1, r2)orFIRST 1 (r1, r2)so that one replica failing does not stop every commit. - Treat a cancelled synchronous wait as committed. The warning says so; retrying the write duplicates it unless the write is idempotent.
- In Cassandra, watch for nodes down longer than max_hint_window. After that, hints stop being kept and only a repair brings the node back up to date.
Wrong, then right
A profile service adds a read replica to take load off the primary. The data source routes every read-only transaction to the replica, including the one that loads the profile right after the user saves it.
Incorrect implementation
@PostMapping("/profile")public ProfileView save(@RequestBody ProfileForm form) { profiles.update(form); // primary return profiles.load(form.userId()); // @Transactional(readOnly = true): routed to a replica}Why it is written this way
- The save commits on the primary; the load runs on a replica that may not have replayed it yet.
- Under normal load it works almost every time, so tests and code review do not catch it.
Why it fails
- During a busy period (the lab saw up to 22 MB of unapplied changes during one bulk insert), the user sees their old profile right after saving and saves again, or reports a bug.
- Making the replica synchronous with the default
synchronous_commit = ondoes not fix it: the lab showed a synchronously replicated row still invisible on the replica.
Corrected implementation
@PostMapping("/profile")public ProfileView save(@RequestBody ProfileForm form) { // Return what was just written, from the primary, in the same transaction. return profiles.updateAndReturn(form); // UPDATE ... RETURNING *, on the primary}@GetMapping("/profile/{id}")public ProfileView view(@PathVariable long id, HttpSession session) { // A user who wrote in the last few seconds reads from the primary. boolean recentWriter = session.getAttribute("lastWriteAt") instanceof Instant t && t.isAfter(Instant.now().minusSeconds(5)); return recentWriter ? profiles.loadFromPrimary(id) : profiles.load(id);}Why it is written this way
- The response to the save comes from the write itself (
RETURNING), so it cannot be stale. - For a short window after writing, that user's reads go to the primary; everyone else still uses the replica.
Trade-offs
- The time window is a guess: it must exceed your worst normal lag, so alert when lag approaches it.
- A stricter alternative is to record the primary's WAL position after the write and read from a replica only once
pg_last_wal_replay_lsn()has passed it. remote_applyon one replica also gives read-your-writes there, but slows every commit to the speed of that replica's replay.
Configuration
The settings that decide what replication waits for. Defaults are those of PostgreSQL 18 and Cassandra 5.0, read from a running instance or the shipped configuration.
synchronous_standby_names (Postgres)Default '' (none: all replicas async)
- What it does
- Which replicas a commit waits for.
FIRST n (a, b)waits for the first n by priority;ANY n (a, b)for any n. - When to change it
- Use a list with more replicas than n, so one failure does not stop commits.
synchronous_commit (Postgres)Default on
- What it does
- How far a commit waits:
onwaits for synchronous replicas to flush;remote_applyfor them to replay;remote_writefor them to write;localandoffdo not wait for replicas. - When to change it
- Can be set per transaction: use
remote_applyonly for the writes that must be readable on a replica immediately.
max_standby_streaming_delay (Postgres)Default 30s
- What it does
- How long a replica delays replay for a conflicting query before cancelling the query.
- When to change it
- Raising it lets long reporting queries finish on a replica, at the cost of more lag on that replica.
wal_sender_timeout (Postgres)Default 60s
- What it does
- How long the primary waits before treating a silent replica connection as dead.
- When to change it
- Lower it to detect a vanished replica sooner.
max_hint_window (Cassandra)Default 3h
- What it does
- How long a coordinator keeps writing hints for a down replica.
- When to change it
- A node down for longer must be repaired after it returns. Size it to your realistic recovery time.
hinted_handoff_enabled (Cassandra)Default true
- What it does
- Whether coordinators keep hints for down replicas at all.
- When to change it
- Leave it on; it is the cheapest way a briefly-down node catches up.
Hands-on exercise
Watch replicas fall behind and catch up
Run the lab and see, on real Postgres 18 and Cassandra 5.0, a stale read from a replica, lag during a bulk write, what synchronous commit waits for, a commit stuck behind a down synchronous replica, and Cassandra keeping hints for a down node.
Steps · 0 of 5 done
What you should see
A row visible on the primary and missing on the paused replica, with a few kilobytes received but not applied; lag of megabytes during the bulk write; a synchronous commit that returns while the row is still invisible on r1; a remote_apply commit that waits in SyncRep until replay resumes; an insert stuck in SyncRep while the synchronous replica is down, which commits locally when cancelled. With local the insert does not wait. In Cassandra, ONE and QUORUM succeed and ALL is unavailable with one node down; a hint file appears on the coordinator and disappears once the node is back and has the write.
What can go wrong
Read-after-write from a replica
- Symptom
- Users occasionally see their old data right after saving, mostly at busy times.
- Cause
- The read after the write was routed to an async replica that had not replayed the change.
- Fix
- Read your own writes from the primary, or wait for the replica to pass the write's WAL position.
One synchronous replica
- Symptom
- When a single replica restarts for maintenance, every write on the primary hangs.
- Cause
synchronous_standby_namesnames one replica, and every commit waits for it.- Fix
- Use
ANY 1 (r1, r2)orFIRST 1 (r1, r2)with at least two replicas.
Retrying a cancelled synchronous commit
- Symptom
- Duplicate rows after a replica outage.
- Cause
- The application timed out a commit stuck in SyncRep and retried it. The first attempt had already committed on the primary.
- Fix
- Treat the cancellation warning as success-but-unreplicated; make writes idempotent so retries are safe.
A Cassandra node down past the hint window
- Symptom
- After a node returns from a long outage, reads at ONE sometimes return old values.
- Cause
- Hints stopped after max_hint_window, so the node missed writes that nothing has replayed.
- Fix
- Run a repair on the node after any outage longer than the hint window.
How to troubleshoot
How far behind is each Postgres replica?
Check
SELECT application_name, sync_state, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS bytes_behind, replay_lag FROM pg_stat_replication;
- How to read it
- Run on the primary. Bytes behind and replay_lag that keep growing mean the replica cannot keep up; one that is missing has disconnected.
Writes on the primary hang
Check
SELECT pid, wait_event, now() - query_start AS waiting, query FROM pg_stat_activity WHERE wait_event = 'SyncRep';
- How to read it
- Sessions in SyncRep are waiting for synchronous replicas. Check which ones are connected in pg_stat_replication.
On a replica: is it receiving, and is it applying?
Check
SELECT pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(), pg_is_wal_replay_paused(), now() - pg_last_xact_replay_timestamp() AS since_last_replay;
- How to read it
- Receive moving but replay stuck points at replay conflicts or a paused replica; neither moving points at the connection.
Is a Cassandra coordinator holding hints?
Check
ls -l /var/lib/cassandra/hints/ (on each node), and nodetool status
- How to read it
- Hint files for a node's host ID mean that node is down or was recently; they disappear once delivered.
Key takeaways
- Postgres has one primary that streams WAL to read-only replicas; Cassandra sends every write to all replicas at once.
- Async replicas lag, sometimes by megabytes; reading your own write from a replica can miss it.
- synchronous_commit = on makes a commit durable on the replica, not visible there; remote_apply makes it visible.
- A single synchronous replica that is down stops every commit; a cancelled wait leaves the transaction committed.
- Cassandra keeps writing while a replica is down and keeps hints for it for up to three hours by default.
Practical check
Small scenarios, one decision each. Pick what you would do, then read why.
Scenario 1 of 3
A team sets synchronous_standby_names = 'r1' with the default synchronous_commit so that "reads on r1 always see the latest data". Users still sometimes see stale data when reading from r1.
Why do the reads still miss writes?
Scenario 2 of 3
A Postgres cluster has one synchronous replica. During a network blip to that replica, the application's commits time out after 10 seconds and it retries them. Afterwards, some orders appear twice.
What explains the duplicates?
Scenario 3 of 3
A three-node Cassandra cluster with replication factor 3 loses one node to a disk failure. The replacement takes six hours. Writes use QUORUM throughout.
What must happen once the replacement node is back?
References
References: PostgreSQL 18 documentation: Log-shipping standby servers (streaming and synchronous replication) ↗ · PostgreSQL 18 documentation: Replication settings (synchronous_standby_names, max_standby_streaming_delay) ↗ · PostgreSQL 18 documentation: Write-ahead log settings (synchronous_commit) ↗ · PostgreSQL 18 documentation: Hot standby ↗ · PostgreSQL 18 documentation: pg_stat_replication ↗ · Apache Cassandra 5.0 documentation: Dynamo (replication and consistency levels) ↗ · Apache Cassandra 5.0 documentation: Hints ↗ · Apache Cassandra 5.0 default cassandra.yaml (hinted_handoff_enabled, max_hint_window) ↗ · DeCandia et al., Dynamo: Amazon's Highly Available Key-value Store (SOSP 2007) ↗ · Report a problem