Read-Your-Writes on Read Replicas: The Session-Consistency Router

Distributed Data · Advanced · 7 min read · published 2026-08-15

What this solves: You added read replicas to take load off the primary, and now users occasionally don't see the record they just created. This shows you how to route reads so each user always sees their own writes without sending all traffic back to the primary.

The Forces at Play

Read replicas are the cheapest scaling win in a relational system: you clone the primary, point your GETs at it, and the primary stops drowning in SELECTs. The catch is that replication is asynchronous. The replica is a few milliseconds behind — until it's a few seconds behind, because someone ran a bulk UPDATE, or a long-running analytic query on the standby blocked WAL replay.

Three pressures collide:

• Throughput wants reads on replicas, as many as possible.
• Causality wants a user who just clicked "Save" to see their own change. Not global linearizability — nobody notices if another user's edit takes 300ms to appear — just their own.
• Operational simplicity wants no per-endpoint reasoning about which reads are "safe."

The naive resolutions all fail. "All reads to primary" gives up the win. "All reads to replica" produces phantom 404s and disappearing form edits that QA can never reproduce. "Sticky the user to the primary for N seconds after a write" is a guess that breaks exactly when lag spikes, which is exactly when it matters.

The pattern that actually works: make the write position a first-class token that travels with the session, and make the read router refuse any replica that hasn't reached it. This is session (monotonic read + read-your-writes) consistency — strictly weaker than strong consistency, and strictly enough for almost every product surface.

The Shape

flowchart TB
    C["Client<br/>holds X-Consistency-Token = LSN"]
    subgraph APP["App instance"]
      H["Handler"]
      R["Read Router"]
      T["Token store<br/>(cookie / JWT claim / Redis)"]
    end
    P[("Primary<br/>current_wal_lsn = 0/9F2A")]
    RA[("Replica A<br/>replay_lsn = 0/9F2A")]
    RB[("Replica B<br/>replay_lsn = 0/8C11<br/>LAGGING")]
    W["Lag Watcher<br/>polls replay_lsn every 100ms"]

    C -->|"1. POST + token"| H
    H -->|"2. write, returns LSN 0/9F2A"| P
    H -->|"3. persist LSN"| T
    H -->|"4. echo token"| C
    C -->|"5. GET + token 0/9F2A"| H
    H --> R
    T -.->|"read session LSN"| R
    W -.->|"replica freshness table"| R
    R -->|"6a. replay_lsn >= token: OK"| RA
    R -.->|"6b. behind token: SKIP"| RB
    R -->|"6c. no replica qualifies: fallback"| P
    W --> RA
    W --> RB
    W --> P

The non-obvious component is the Lag Watcher. Without it, the router would have to ask a replica "where are you?" on every read — an extra round trip per query. Instead one background goroutine/task per app instance polls every standby a few times a second and keeps an in-memory freshness table. Comparing an LSN against a 100ms-old cached value is conservative in the safe direction: you may occasionally route to the primary unnecessarily, never to a stale replica.

How Data Flows Through It

A user renames a project, then the SPA immediately refetches the project list.

1. PATCH /projects/42 lands on app instance #3. The handler opens a transaction on the primary, does the UPDATE, and — in the same transaction — asks for the write position:

BEGIN;
UPDATE projects SET name = $1 WHERE id = 42;
SELECT pg_current_wal_insert_lsn() AS token;
COMMIT;

2. The handler writes 0/9F2A into the session token store and returns it to the client as X-Consistency-Token: 0/9F2A. If the client is a service rather than a browser, it propagates that header downstream, the same way it propagates a trace ID.

3. GET /projects arrives 40ms later, on app instance #7 — a different box, which is why the token must live in the request or a shared store, not in local memory.

4. The Read Router reads the token, consults its freshness table: Replica A is at 0/9F31 (≥ token, eligible), Replica B is at 0/8C11 (behind, skipped). It picks A.

5. Belt-and-braces: on the chosen connection the router can also issue a bounded wait rather than trusting the cache blindly. On MySQL that's SELECT WAIT_FOR_EXECUTED_GTID_SET(@gtid, 0.05). On Postgres you emulate it with a short pg_last_wal_replay_lsn() poll loop, or skip it and accept the 100ms staleness of the watcher.

6. The response is served from Replica A. The user sees the new name. The primary was touched exactly once — for the write.

7. If instead both replicas were behind (batch job running), step 4 finds no eligible target and falls back to the primary. Latency is unchanged; the primary absorbs a small burst of reads precisely during the window when it can't be avoided. That's the tradeoff made explicit rather than accidental.

What Each Piece Owns

Token store. Owns the mapping session → highest write position observed. It must be monotonic: only ever advance the stored LSN, never regress, or you reintroduce non-monotonic reads. It does not own routing decisions and does not know what a replica is.

type ConsistencyToken string // opaque LSN/GTID, never parsed by callers

type ReadRouter interface {
    // Returns a connection guaranteed to have replayed past tok.
    // Falls back to primary if no replica qualifies within budget.
    Pick(ctx context.Context, tok ConsistencyToken) (*sql.Conn, error)
}

Lag Watcher. Owns freshness facts only: "replica B's replay position as of 80ms ago." It does not eject replicas from load balancing, does not page anyone, does not decide policy. It's a sensor.

Read Router. Owns policy: eligibility, load spreading among eligible replicas, the fallback rule, and the wait budget. It deliberately does not own connection health checking (that's the pool's job) and does not own the token lifecycle.

Write path. Owns emitting a token on every mutation. Crucially it does not get to decide that some writes are "unimportant" and skip the token — that's how you get one endpoint that silently breaks the guarantee for everything after it.

Client / caller. Owns propagation. It does not interpret the token. Treat it as opaque: the day you migrate from Postgres LSNs to a logical sequence number, no caller should need to change.

Where It Breaks Down

The first bottleneck is the token store, not the database. If you put session LSNs in Redis, every read now costs a Redis round trip. At 50k reads/sec that's a new critical dependency in your read path. Prefer putting the token in a signed cookie or JWT claim so it rides along free — but then you must handle clients that drop headers (mobile web views, aggressive CDNs), and a dropped token silently degrades to "stale reads allowed."

Fallback stampede. A bulk migration pushes every replica 10 seconds behind. Every read for every recently-writing user now hits the primary — the moment the primary is also handling the migration's WAL generation. Cap it: if more than X% of reads are falling back, either shed to stale-but-labelled responses or throttle the batch job. Decide this before it happens.

Cross-service causality leaks. Service A writes and returns 200. Service B, reacting to an event, reads from a replica with no token — and sees nothing. Tokens must ride the event envelope too:

{ "type": "project.renamed", "id": 42,
  "consistency_token": "0/9F2A", "trace_id": "..." }

Postgres-specific trap: a standby running a long query with hot_standby_feedback off and max_standby_streaming_delay = -1 will pause WAL replay indefinitely. That replica becomes permanently ineligible and quietly stops serving anything, while your dashboards still show it "healthy." Alert on replay lag per replica, not on connection success.

Operational burden: the freshness table becomes a debugging necessity. Expose it on an admin endpoint. Without it, "why is my read going to the primary?" is unanswerable.

When This Is Overkill

If your primary is under 40% CPU, just read from the primary. One database, zero consistency reasoning, and you can serve a startlingly large business that way. Replicas exist for failover long before they exist for reads.

The next-simplest correct design: replicas serve only reads that are definitionally allowed to be stale — analytics dashboards, search indexing, export jobs, admin reporting. No token, no router, just two connection pools and a rule that transactional user-facing reads use the primary pool. This handles most of the load asymmetry with a code review checklist instead of infrastructure.

You've outgrown that when primary read CPU dominates write CPU on the hot user-facing path — concretely, when pg_stat_statements shows your top-10 queries by total time are all SELECTs from request handlers, and you can't cache them because they're per-user. That's the signal that you need replicas serving user reads, and therefore need the token. Building the router before that signal means you've added a distributed freshness protocol to solve a problem a bigger instance would have solved.

Key takeaway: Track the write position (LSN/GTID) your user's last write produced, carry it with the session, and only route that user's reads to a replica that has already caught up to it.

-- after the INSERT, in the same transaction
SELECT pg_current_wal_insert_lsn();