Read-Your-Writes Consistency with Shard Replicas

Adding read replicas to each shard multiplies read capacity and introduces a specific bug: a user saves a change and the next page load, served by a replica, shows the old value. This guide implements read-your-writes properly — a write position carried through the request, replica selection by replay position, and an explicit fallback — rather than by sending everything to the primary. It applies the guarantees discussed in Consistency Models in Distributed Databases within Database Partitioning Fundamentals & Architecture.

Prerequisites

Step 1 — Capture the write position

Every write returns a log sequence number that identifies its position in the replication stream:

-- after committing, ask where the commit landed
SELECT pg_current_wal_lsn();
 pg_current_wal_lsn
--------------------
 A2C/FE118820

# repository layer: return the LSN alongside the result
def update_profile(conn, user_id, fields):
    with conn.transaction():
        conn.execute("UPDATE profiles SET ... WHERE user_id = %s", (user_id, *fields))
        lsn = conn.query_one("SELECT pg_current_wal_lsn()")
    request_context.set_write_lsn(shard_of(user_id), lsn)
    return lsn

Operational note: The LSN is per shard. A request that wrote to two shards carries two tokens, and each read must be compared against the token for its own shard.

DBA tip: pg_current_wal_lsn() is cheap — it reads an in-memory value. Calling it after every write is not a measurable cost, which is what makes this approach practical.

The token travels with the request; the router compares it per replica A write commits on the primary and returns its log sequence number. The token is stored in the request context. On the next read, the router asks each replica for its replay position and sends the query to any replica whose position is at or beyond the token. If none qualifies, the read goes to the primary. write requestUPDATE profiles primary commitsreturns LSN A2C/FE118820 next read carriesthe same token replica 1 · replayed A2C/FE10behind the token → skip replica 2 · replayed A2C/FE12at or past the token → use replica 3 · replayed A2C/FD90behind the token → skip none qualify → primary correct, and counted as a fallback Only requests that actually wrote carry a token. Everything else — the large majority of reads — uses any replica, which is where the capacity benefit comes from.

Step 2 — Track each replica’s replay position

The router needs current positions, refreshed often enough to be useful:

-- on each replica
SELECT pg_last_wal_replay_lsn() AS replayed,
       now() - pg_last_xact_replay_timestamp() AS behind;
# router: refresh positions on a short interval, not per request
class ReplicaTracker:
    def __init__(self, replicas, interval=0.5):
        self.positions = {r.name: None for r in replicas}
        self._start_poller(replicas, interval)

    def pick(self, shard, required_lsn):
        candidates = [
            name for name, lsn in self.positions.items()
            if lsn is not None and lsn >= required_lsn and name.startswith(shard)
        ]
        return random.choice(candidates) if candidates else None

Operational note: Poll positions in the background rather than per request. A per-request round trip to every replica costs more than the staleness it protects against.

SRE tip: Treat a replica with an unknown position as unusable rather than assuming it is caught up. Unknown means the poller failed, and a failed poller usually means the replica is unhealthy.

Step 3 — Propagate the token beyond the session

The common failure is a token that lives only in the web session. Attach it to the request context and to anything the request schedules:

How far the token has to travel A write in the web request produces a log sequence number. That token is placed in the response, in the queued job payload and in the outbound call to a second service, so every downstream reader can require a replica that has replayed at least that far. A hop that drops the token silently loses the guarantee for everything after it. write commitsLSN A2C/FE118820 HTTP responsetoken in a header queued jobtoken in the payload client's next readsends the token back worker's readuses the token replica chosen byreplay position Every hop is an opportunity to drop the token, and dropping it fails open rather than closed: the read still succeeds and simply returns stale data. Treat the token as part of the message contract so a missing field is a schema error. The second service does not need to know what an LSN is — it only has to pass it through to its own read router. 
# the token crosses process boundaries with the work it belongs to
def enqueue_followup(user_id):
    queue.publish({
        "task": "recompute_profile_score",
        "user_id": user_id,
        "read_after_lsn": request_context.write_lsn(shard_of(user_id)),
    })

def handle_followup(msg):
    replica = tracker.pick(shard_of(msg["user_id"]), msg["read_after_lsn"])
    conn = connect(replica or primary_of(shard_of(msg["user_id"])))
    ...

Operational note: Once the token is in the message, the worker inherits the guarantee. Without it, a job that runs milliseconds after the write reads a replica that has not seen it and computes from stale data.

DBA tip: Tokens are safe to expire. An LSN from five minutes ago is satisfied by every replica, so stale tokens simply stop constraining anything rather than causing errors.

Step 4 — Measure the fallback rate

The metric that matters is how often no replica qualifies:

# healthy: well under 1% of reads that carry a token
rate(read_router_primary_fallback_total[5m])
  / rate(read_router_reads_with_token_total[5m])
The fallback rate moves before the lag alert does During a replica degradation, the share of token-carrying reads that fall back to the primary rises from under one percent to eighteen percent over twenty minutes. The replication lag alert, which fires at 120 seconds sustained for two minutes, only triggers eleven minutes later. The fallback rate is a leading indicator because it reacts to lag of a few hundred milliseconds. 20%12%4%0 fallback rate starts climbing lag alert finally fires primary fallback rate replication lag (scaled) Alert on the fallback rate as well as on lag: it reacts to sub-second degradation that a lag threshold is designed to ignore.

Operational note: A permanently elevated fallback rate is a capacity signal, not a correctness one. It means the primary is serving reads that were supposed to go elsewhere, and the shard’s read capacity is effectively the primary’s.

SRE tip: Break the metric down by shard. A single shard with a high fallback rate points at one unhealthy replica; a fleet-wide rise points at the write rate having outgrown replication.

Verification

Prove the guarantee with a test that would fail without it:

def test_read_your_writes():
    lsn = update_profile(user_id=8842, display_name="New Name")
    # immediately — replicas cannot have caught up yet
    row = read_profile(user_id=8842, read_after_lsn=lsn)
    assert row.display_name == "New Name"

def test_reads_without_token_may_use_any_replica():
    target = router.pick_for_read(shard="shard3", required_lsn=None)
    assert target.startswith("shard3-replica")

Then confirm the routing decision itself, not only the result:

-- on the replica that served the read
SELECT pg_last_wal_replay_lsn() >= 'A2C/FE118820'::pg_lsn AS caught_up;
 caught_up
-----------
 t

What This Costs, and What It Does Not

The mechanism adds one value to the request context, one comparison per routed read, and a background poller per application process. None of that is measurable next to a database round trip, which is why the LSN-token approach is preferable to the two alternatives teams usually consider first.

Sending everything to the primary is simpler and gives up the replicas entirely. Adding an artificial delay after writes — “wait 200 milliseconds before reading” — is simpler still and is wrong in both directions at once: it slows down every request whether or not the replica was behind, and it still fails whenever replication is slower than the guess.

What the token approach does not solve is cross-entity consistency. A request that writes to entity A and reads entity B gets a fresh view of A and no guarantee about B, because the token bounds only the position it captured. That is almost always the right trade — the alternative is a global read barrier — but it should be stated rather than assumed, particularly for workflows where one write is expected to become visible in a related aggregate.

Failure mode table

Failure mode Root cause SRE mitigation
Users see their own change disappear reads were routed to replicas with no freshness constraint carry the write LSN and compare it against each replica’s replay position; fall back to the primary when none qualifies
Background job computes from stale data the token stayed in the web session and never reached the worker put the token in the job payload; treat it as part of the message contract
Primary saturated by reads the fallback path became the normal path because replicas are persistently behind alert on fallback rate, not only on lag; add replica capacity or reduce write volume rather than raising the threshold

FAQ

Why not just send every read to the primary?

It works and it wastes the replicas. On a read-heavy workload the primary becomes the capacity ceiling for the whole shard, which is the constraint replicas exist to remove. The LSN-token approach lets the small fraction of reads that genuinely need freshness go to the primary while the rest use replicas.

Is a sticky session enough?

Only within one session on one connection path. It breaks as soon as a background job, a webhook retry or a second service reads the same entity, because those are outside the session that did the write. Attaching the write position to the entity or to the request context survives those hops; a cookie does not.

What should happen when no replica has caught up?

Fall back to the primary, and count it. A brief fallback is correct behaviour; a rising fallback rate means replication is degraded and the primary is absorbing load it was not sized for. That metric is often the earliest signal of replica trouble, well before lag alerts fire.

Back to Consistency Models In Distributed Databases