Read-Your-Writes and Monotonic Reads in Code

Scenario

Example app: a social platform. A user edits their profile bio, is redirected to their profile page, and that page reads from a replica. The same user's notifications page, refreshed twice, alternates between two replicas with different lag.

What you see in production

• "I changed my bio but it still shows the old one" — the most common support ticket after enabling replicas.
• Notifications appear and disappear between refreshes: the first replica is caught up, the second isn't (a monotonic reads violation).

The naive code

Every read picks any replica; nothing remembers that this user just wrote:

def get_profile(user_id):
    with replica_session() as s:                    # random replica every time
        return s.get(Profile, user_id)

@Transactional(readOnly = true)                     // routed to a random replica
public Profile profile(long userId) { return profiles.findById(userId).orElseThrow(); }

Reproduce it

A router that keeps a user on the primary for a few seconds after they write, and otherwise picks a replica:

STICKY_MS = 2000
last_write_at = {}                     # user -> time of last write (in a cookie or session in real life)

def route(user, now, is_write=False):
    if is_write:
        last_write_at[user] = now
        return "primary"
    if now - last_write_at.get(user, -10**9) < STICKY_MS:
        return "primary"               # recent writer: read your own writes
    return "replica"

events = [("ada", 0, True), ("ada", 50, False), ("bob", 60, False), ("ada", 1500, False), ("ada", 2600, False)]
for user, now, is_write in events:
    print(f"t={now:4} {user:3} {'WRITE' if is_write else 'read '} -> {route(user, now, is_write)}")

t=   0 ada WRITE -> primary
t=  50 ada read  -> primary
t=  60 bob read  -> replica
t=1500 ada read  -> primary
t=2600 ada read  -> replica

Root cause

Replicas provide eventual consistency. Users expect two session guarantees:

• Read-your-writes: after I change something, I see the change.
• Monotonic reads: once I've seen something, I don't later see an older state.

Neither holds when each read goes to an arbitrary replica.

The fix

Choose one technique per endpoint:

1. Sticky window — after a write, route that user's reads to the primary for N seconds (longer than typical lag). Store the timestamp in the session or a cookie so it works across servers.
2. Wait for position — remember the primary's WAL position (Postgres LSN / MySQL GTID) after the write; on read, use a replica only if it has replayed past it (or wait briefly).
3. Session pinning — keep a user on one replica for monotonic reads.

def save_profile(user_id, bio, session_store):
    with primary.begin() as conn:
        conn.execute(update(profiles).where(profiles.c.user_id == user_id).values(bio=bio))
        session_store["min_lsn"] = conn.scalar(text("SELECT pg_current_wal_insert_lsn()"))

def read_engine(session_store):
    lsn = session_store.get("min_lsn")
    for replica in replicas:
        if lsn is None or replica_replayed_past(replica, lsn):   # SELECT pg_last_wal_replay_lsn() >= :lsn
            return replica
    return primary

-- after the write, on the primary: remember the executed GTID set
SELECT @@GLOBAL.gtid_executed;
-- on the replica before reading: wait up to 1 s for it to apply those transactions
SELECT WAIT_FOR_EXECUTED_GTID_SET('3E11FA47-71CA-11E1-9E33-C80AA9429562:1-5', 1);   -- 0 = caught up

// Mark recent writers in the HTTP session; a RoutingDataSource reads it from a ThreadLocal set by a filter
public class ReplicaRoutingDataSource extends AbstractRoutingDataSource {
    @Override protected Object determineCurrentLookupKey() {
        boolean readOnly = TransactionSynchronizationManager.isCurrentTransactionReadOnly();
        return readOnly && !RecentWriter.isRecent() ? "replica" : "primary";
    }
}

// 'sticky' => true covers reads in the same request; for the NEXT request store a flag:
session(['wrote_at' => now()->timestamp]);
if (now()->timestamp - session('wrote_at', 0) < 3) {
    $profile = Profile::on('pgsql::write')->find($id);   // force the write connection
}

Trade-offs

• Sticky windows are simple but approximate: if lag exceeds the window, users still see stale data — size it from observed p99 lag and fall back to the primary when lag is high.
• LSN/GTID tracking is exact but needs plumbing through sessions and a check (or wait) per read.
• Both shift some reads back to the primary — measure how much.
• Cross-device read-your-writes (edit on phone, check on laptop) needs the position stored server-side per user, not in a cookie.

Detect it in production

• Log routing decisions with lag at read time; count stale-read reports.
• Synthetic check: write a canary value then read it through the normal path, alerting if it's stale.

Key points

• Users need read-your-writes and monotonic reads even when the system is eventually consistent.
• Implement with sticky-to-primary windows, LSN/GTID position checks, or replica pinning.
• Store the "recent write" marker where every server can see it.