Read-Your-Writes Consistency with Read Replicas

Problem statement: A user updates their profile, the write commits on the primary, the next page load reads from a replica that has not yet applied that change, and the user sees their old data — the classic read-your-writes violation that appears the instant you start routing reads off the primary.

Symptom Identification

This class of bug is easy to reproduce and maddening to catch in code review, because the code is correct in isolation — it only breaks under the timing of asynchronous replication. Watch for these signals:

  • “I saved it but it didn’t save” support tickets that the user cannot reproduce a minute later. The write did land; the immediate read hit a lagging replica.
  • A create-then-redirect flow where the destination page 404s or shows an empty list, but a manual refresh a second later renders correctly.
  • Toggling a setting that reverts on the next request, then sticks on the one after that.
  • Integration tests that pass against a single database but fail intermittently in staging where a real replica sits behind the primary.
  • Metrics showing a read landing on a replica whose replay_lsn is behind the LSN your own request just committed.

The common thread is that the stale window is exactly one replication-lag interval wide and always follows the reader’s own write. If a hard refresh fixes it, you are almost certainly looking at a read-your-writes gap rather than a caching or application-state bug.

Root Cause Analysis

Under asynchronous replication, the primary commits and acknowledges a write before the replica has received or applied the corresponding WAL. A read dispatched to that replica in the intervening window sees a snapshot that predates the write. Nothing is broken — this is the defining trade-off of read scaling, and the reason evaluating consistency models for distributed reads matters before you split traffic.

Read-your-writes (also called read-after-write) is a session-level guarantee: within one session, any read reflects at least that session’s own prior writes. It is strictly weaker than global strong consistency and strictly stronger than eventual consistency. The key insight is that you do not need every replica to be current — you only need the replica serving this read to have applied this session’s last write. That reframes an unbounded problem (“keep replicas fresh”) into a bounded one (“compare one position against one threshold”).

The mechanism that makes it tractable is that both PostgreSQL and MySQL expose a monotonic commit coordinate. PostgreSQL uses the WAL Log Sequence Number (LSN); MySQL uses the Global Transaction Identifier (GTID) set. Capture that coordinate at write time, carry it in the session, and gate replica reads on it. When no replica qualifies in time, fall back to the primary — which is always current by definition.

Architecture: The Token Round-Trip

The sequence below shows the full round-trip: the write returns a commit position, the session carries it, and the read path either finds a caught-up replica or falls back to the primary.

Read-your-writes token round-trip A client writes to the primary and receives a commit LSN token. The client stores the token in its session. On the next read, the router compares the token against the replica replay position; if the replica has caught up the read is served there, otherwise it falls back to the primary. Client Router Primary Replica 1. UPDATE profile 2. commit → token LSN 0/3A2F1B8 3. store in session 4. read + token 5. replay_lsn >= token? 6a. caught up → serve read 6b. timeout → primary 7. fresh row returned The session token is the highest LSN the client has committed — it only moves forward

Step-by-Step Resolution

Step 1: Capture the commit position at write time

Read the commit coordinate in the same round-trip as the write, so you never miss the position of a transaction you just committed.

sql
-- PostgreSQL: capture the WAL position right after commit
-- Run this on the SAME primary connection that performed the write.
SELECT pg_current_wal_lsn() AS commit_lsn;
sql
-- MySQL: capture the GTID set that now includes your transaction
SELECT @@GLOBAL.gtid_executed AS committed_gtids;

For PostgreSQL, pg_current_wal_lsn() returns the current write position on the primary, which is at or past the LSN of the transaction you just committed. For MySQL with GTIDs enabled (gtid_mode = ON, enforce_gtid_consistency = ON), @@GLOBAL.gtid_executed is the authoritative set of applied transactions.

Inline verification: The returned value must be non-empty and must increase across successive writes. On PostgreSQL, SELECT pg_wal_lsn_diff('0/3A2F1B8', '0/3A2F000') > 0 confirms ordering; on MySQL the GTID set should include your server’s UUID with a rising interval.

Step 2: Persist the token in session-scoped storage

Store the highest LSN or GTID set the session has produced. In-process, a request-scoped context variable is enough; across devices you need a shared store (see the edge cases below).

python
# Request-scoped token, advanced on every write (Python / async-safe)
from contextvars import ContextVar

_write_token: ContextVar[str | None] = ContextVar("write_token", default=None)

def record_commit(lsn: str) -> None:
    current = _write_token.get()
    # Monotonic: only ever move the watermark forward
    if current is None or lsn_gt(lsn, current):
        _write_token.set(lsn)

Inline verification: After a write, _write_token.get() returns the LSN from Step 1. After a second write it returns the larger of the two — never the smaller.

Step 3: Gate replica selection on the token

Before dispatching a read, ask each candidate replica for its current replay position and route only to a replica that has caught up to the token.

sql
-- PostgreSQL: current apply position on the replica
SELECT pg_last_wal_replay_lsn() AS replay_lsn;
-- Route here only if replay_lsn >= session token
python
def pick_replica(token: str, replicas) -> "Conn | None":
    for r in replicas:
        replay = r.execute("SELECT pg_last_wal_replay_lsn()").scalar()
        if lsn_ge(replay, token):
            return r          # this replica has applied the session's write
    return None               # none caught up — caller falls back to primary

Inline verification: Immediately after a write, pick_replica may return None (expected during the lag window). A moment later the same call returns a replica whose replay_lsn is at or past the token.

Step 4: Wait for catch-up, then fall back to the primary

Rather than reject the read when no replica qualifies, give the replica a bounded chance to catch up. MySQL has a purpose-built blocking function; PostgreSQL is polled.

sql
-- MySQL: block until this replica has executed the session's GTID set,
-- or time out after 1 second (returns 0 on success, 1 on timeout).
SELECT WAIT_FOR_EXECUTED_GTID_SET(@committed_gtids, 1);
python
# PostgreSQL: poll the replay position within a deadline, else use primary
import time

def read_your_writes(query, token, replicas, primary, timeout_ms=500):
    deadline = time.monotonic() + timeout_ms / 1000
    while time.monotonic() < deadline:
        r = pick_replica(token, replicas)
        if r is not None:
            return r.execute(query)
        time.sleep(0.02)
    return primary.execute(query)   # bounded fallback — primary is always current

Inline verification: On MySQL, WAIT_FOR_EXECUTED_GTID_SET returns 0 once the replica applies the set. In the Python path, log which branch served the read; the primary-fallback branch should fire rarely under healthy lag.

Step 5: Enforce monotonic reads

Advance the stored token to the highest replay position you have actually read from, so a later read can never be served by a replica that is behind an earlier read — preventing time from appearing to run backward.

python
def after_read(served_replay_lsn: str) -> None:
    current = _write_token.get()
    if current is None or lsn_gt(served_replay_lsn, current):
        _write_token.set(served_replay_lsn)   # watermark never regresses

Inline verification: Issue two reads in a row against a fleet with mixed lag. The second read must land on a replica at or past the first read’s position; assert the token is non-decreasing across the pair.

Configuration Snippet

MySQL primary and replica settings that make GTID-based read-your-writes reliable:

ini
# my.cnf on primary and every replica
[mysqld]
gtid_mode                     = ON
enforce_gtid_consistency      = ON
# Replicas apply in parallel to keep the catch-up window short
replica_parallel_type         = LOGICAL_CLOCK
replica_parallel_workers      = 8
# Preserve commit order so GTID visibility is monotonic on the replica
replica_preserve_commit_order = ON

PostgreSQL replica settings that expose an accurate replay position and keep lag low:

ini
# postgresql.conf on each replica
hot_standby                   = on
hot_standby_feedback          = on      # reduce query cancellations during catch-up
max_standby_streaming_delay   = 15s
# Report replay position frequently so the router sees fresh data
wal_receiver_status_interval  = 1s

Verification and Rollback

Confirm the guarantee holds

sql
-- On the primary: capture a token
SELECT pg_current_wal_lsn();               -- e.g. 0/3A2F1B8

-- On a replica: it must reach that position before you route the read there
SELECT pg_last_wal_replay_lsn() >= '0/3A2F1B8'::pg_lsn AS caught_up;

Drive an end-to-end check from application code: perform a write, immediately issue the dependent read, and assert the value reflects the write across a few hundred iterations. Any failure means the token is not being captured, stored, or compared correctly.

sql
-- MySQL end-to-end assertion inside a test harness
SELECT WAIT_FOR_EXECUTED_GTID_SET(@committed_gtids, 1);  -- expect 0
SELECT display_name FROM users WHERE id = @uid;          -- expect the new value

Roll back safely

If token tracking misbehaves — for example the fallback rate spikes and overloads the primary — disable the replica gate and pin the affected reads to the primary while you diagnose:

  1. Set a feature flag ryw_mode = primary_only so every gated read goes straight to the primary. Correctness is preserved; only read-scaling headroom is lost temporarily.
  2. Confirm with SELECT pg_is_in_recovery(); returning f on the served connection for those reads.
  3. Once replica lag and the token-comparison path are healthy again, flip the flag back to replica_gated and watch the fallback-rate metric return to baseline.

Because the primary is always current, routing more reads to it is always a safe, correctness-preserving fallback — the only cost is throughput.

Edge Cases and Gotchas

The token must be monotonic, or reads go backward

If you overwrite the session token with each new position instead of taking the maximum, a read served by a slightly-behind replica can lower the watermark and let the next read land on an even older snapshot. Always advance the token to max(current, new). This is the difference between plain read-your-writes and the stronger monotonic-reads guarantee, and it costs nothing to add. The freshness-based routing decision tree treats the monotonic watermark as the minimum acceptable replay position for each dispatch.

LSN comparison is not string comparison

A PostgreSQL LSN like 0/3A2F1B8 is a two-part hex value. Comparing tokens as plain strings breaks at boundaries — 0/9FFFFFF sorts after 0/A000000 lexically but is numerically smaller. Compare with pg_wal_lsn_diff() on the database side, or cast to the pg_lsn type, or parse both halves as integers in application code. The same care applies to MySQL GTID sets, which are unions of intervals and must be compared with GTID_SUBSET() semantics, not string equality.

Connection poolers can hide the write connection

In transaction-pooling mode, the connection that ran your write is released back to the pool before you read pg_current_wal_lsn(), so you may capture a position from a different backend. Capture the LSN in the same transaction or statement batch as the write, before the pooler reclaims the connection. When you push routing into the application, ORM middleware for automatic query routing is the natural place to record the commit token, because it already intercepts the write on its way out.

FAQ

Is read-your-writes the same as strong consistency?

No. Read-your-writes is a session guarantee: a client always sees its own prior writes, but it may still observe stale data written by other clients. Strong consistency guarantees every reader sees the latest committed state globally, which requires routing all reads to the primary or a synchronous replica. Read-your-writes lets most reads stay on replicas and only pays the primary-read cost for the narrow window after a session’s own write.

How large should the LSN wait timeout be?

Size the wait timeout to your p99 replication lag under normal load, typically 100 to 800 milliseconds. Waiting longer than that ties up a request thread for a case that should be rare, so fall back to the primary once the timeout expires rather than blocking indefinitely. Track how often the fallback fires; a rising fallback rate is an early signal that replica lag is degrading.

Does read-your-writes work across multiple devices for the same user?

Only if the LSN or GTID token is stored in a shared, cross-device store rather than a single client cookie. A write from a phone advances a token that a web session must also see, so the token has to live in a distributed store such as Redis keyed by user ID. If each device keeps its own token, the guarantee holds per device but not across the user’s whole account.

← Back to Evaluating Consistency Models for Distributed Reads