Skip to content

Path 1 · lesson 4 of 8

Intermediate30 minPostgreSQL 18.7 (one primary, two streaming replicas) · Apache Cassandra 5.0.9 (three nodes, replication factor 3), all in Docker

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.

PostgresCassandra
ModelSingle leader: one primary takes all writesLeaderless: any replica can take a write
How changes travelThe primary streams its WAL; replicas replay itThe coordinator sends each write to all replicas at once
Replicas accept writes?No, they are read-onlyYes, all of them
A replica is downAsync: writes carry on. Sync: commits waitWrites carry on; the coordinator keeps hints for it
How much to wait forsynchronous_commit and synchronous_standby_namesThe consistency level of each request
Two replication models. The next lesson covers the consistency each one gives readers.

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 output
 application_name |   state   | sync_state
------------------+-----------+------------
 r1               | streaming | async
 r2               | streaming | async
Both replicas are async, the default: the primary does not wait for them before confirming a commit.

Database outputA replica refuses writes

Real database output
ERROR:  cannot execute INSERT in a read-only transaction
Single leader: every write goes to the primary.

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 output
On the primary:
 id | customer | total
----+----------+-------
 42 | alice    | 99.50
(1 row)

On replica r1, a moment later:
 id | customer | total
----+----------+-------
(0 rows)
The order exists and is committed, but not on the replica yet.

Database outputMeasuring the gap

Real database output
 paused | received_not_applied_bytes
--------+----------------------------
 t      |                      21872
(1 row)

 application_name | bytes_behind
------------------+--------------
 r1               |        21872
 r2               |            0
r1 has received the WAL (it is safe on r1's disk) but not applied it: 21,872 bytes behind. r2 is fully caught up. Once replay resumed, the order appeared on r1.

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 output
t+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
Up to 22 MB of changes not yet applied, on an idle laptop with no network between the containers. Each replica's lag moves independently, so two reads from two replicas can disagree with each other too.

Database outputThe lag columns of pg_stat_replication

Real database output
 application_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
Three stages: the replica has written the WAL, flushed it to disk, and replayed it. Only after replay can a query on the replica see the change.

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
----------------
              0
The commit was confirmed only after r1 had the WAL safely on disk, so the order survives losing the primary. But a read on r1 still does not see it. Synchronous replication is about durability, not read-your-writes.

synchronous_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 output
 state  | wait_event_type | wait_event |                            query
--------+-----------------+------------+--------------------------------------------------------------
 active | IPC             | SyncRep    | SET synchronous_commit = remote_apply; INSERT INTO orders VA
The session is stuck in SyncRep until r1 applies the change. When replay resumed, the insert returned and the row was on r1.

The price: a down synchronous replica stops commits

Database outputr1 is stopped; an insert on the primary three seconds later

Real database output
 state  | wait_event_type | wait_event |                    query
--------+-----------------+------------+----------------------------------------------
 active | IPC             | SyncRep    | INSERT INTO orders VALUES (45, 'dave', 3.10)
The primary is healthy, yet the write cannot complete: it waits for a replica that is not there, indefinitely.

Database outputWhat the session reports when the wait is cancelled

Real database output
WARNING:  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
Read this carefully: the row is committed on the primary. Cancelling the wait does not roll it back. An application that treated the cancellation as a failure and retried would insert it twice.

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 output
ONE:
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}
ONE and QUORUM succeed with two of three replicas alive. ALL is refused before anything is written: the coordinator knows c3 is down.

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 output
Hint 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
When c3 came back, c1 replayed the hint and deleted the file; c3's own copy then had the update. Hints are kept for at most 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
Where a write goes in each model.
  • 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
What a synchronous commit waits for, by setting.
  1. With on, the commit is durable on two machines but may not be visible on the replica yet, as the lab showed.
  2. 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.
  3. 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_replication on the primary has replay_lag and the LSN columns; sustained growth means the replica cannot keep up.
  • Never run one synchronous replica alone. Use ANY 1 (r1, r2) or FIRST 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

ProfileController.javaJava
@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 = on does not fix it: the lab showed a synchronously replicated row still invisible on the replica.

Corrected implementation

ProfileController.javaJava
@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_apply on 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: on waits for synchronous replicas to flush; remote_apply for them to replay; remote_write for them to write; local and off do not wait for replicas.
When to change it
Can be set per transaction: use remote_apply only 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 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_names names one replica, and every commit waits for it.
Fix
Use ANY 1 (r1, r2) or FIRST 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