Pinning Reads to the Primary After Writes During Migrations

A customer updates their shipping address, the page reloads, and the old address is still there. They update it again. Support receives a duplicate-order complaint an hour later. Normally the replica serving that read is a few milliseconds behind the primary and nobody notices; tonight a backfill is running, replicas are eight seconds behind, and every “write then read” flow in the product is showing stale data. Read/write splitting assumes replica lag is small; migrations — backfills, index builds, table rewrites — are exactly when it is not. This guide shows how to preserve read-your-writes consistency during migrations by pinning a session’s reads to the primary after it writes, routing by replication position where your stack supports it, and temporarily sending reads of data under migration to the primary. It belongs to Read/Write Splitting Tactics.

Symptom / Error Signatures

Stale reads during migrations look like application bugs:

• Users see their own changes disappear after a save, then reappear seconds later.
• Duplicate submissions and support tickets spike during migration windows.
• Business logic that reads before writing (checking a balance, a status, an inventory count) makes decisions on stale data, producing conflicts or violations.
• Monitoring shows replica lag well above normal — pg_stat_replication.replay_lag on PostgreSQL, Seconds_Behind_Source on MySQL — during backfills, index builds or rewrites.

Root Cause Analysis

Replicas apply changes asynchronously, so any read from a replica can miss writes committed on the primary within the lag window. In normal operation the window is milliseconds and the effect is rare. Migrations widen it by generating large amounts of WAL or binlog — backfills, index builds, rewrites, and on MySQL long ALTER statements that block the replica’s applier — as described in alerting on replication lag during backfills. The fix is to route reads that need recent writes to a server that has them.

Position-based routing is the most precise. On PostgreSQL, the application records pg_current_wal_lsn() (or the commit LSN) after a write and, before reading from a replica, checks pg_last_wal_replay_lsn() on that replica. On MySQL with GTIDs, the application records the write’s GTID (via session_track_gtids) and uses WAIT_FOR_EXECUTED_GTID_SET on the replica, or lets ProxySQL’s GTID-aware routing do it.

Immediate Mitigation

When a migration is causing stale reads now:

1. Eject lagging replicas from the read pool. Most proxies and drivers support a maximum lag; tighten it for the migration window so reads fall back to replicas that are current, or to the primary.

-- PostgreSQL · on each replica · read-only · seconds of replay lag
SELECT now() - pg_last_xact_replay_timestamp() AS replay_lag;

2. Pin recent writers to the primary. A minimal, framework-agnostic version: after any write, set a short-lived marker in the user’s session and route that session’s reads to the primary while it is present.

# Python · request middleware · pins a session's reads to the primary for PIN_SECONDS after a write
# WARNING: PIN_SECONDS must exceed replica lag during the migration; raise it for the window.
PIN_SECONDS = 15

def after_write(session):
    session["pin_primary_until"] = time.time() + PIN_SECONDS

def choose_database(session, is_write):
    if is_write or session.get("pin_primary_until", 0) > time.time():
        return "primary"
    return "replica"

3. Slow the migration. Tighten the backfill’s lag threshold or batch size so lag returns to normal, as in throttling backfills to protect OLTP latency.

Permanent Fix / Long-Term Pattern

Build read-your-writes into the data-access layer rather than into individual features. The robust design records a replication position with each write and routes reads accordingly:

-- PostgreSQL · after a write, on the primary: record the position the session must see
SELECT pg_current_wal_lsn() AS write_lsn;
-- before a read, on a candidate replica: eligible only if it has replayed past write_lsn
SELECT pg_last_wal_replay_lsn() >= '0/3A7F2B8'::pg_lsn AS caught_up;

On MySQL, enable session_track_gtids = OWN_GTID, capture the GTID returned with each write, and either call SELECT WAIT_FOR_EXECUTED_GTID_SET('<gtid>', 1) on the replica before reading or rely on a GTID-aware proxy. Combine position routing with lag-based ejection so badly lagging replicas are removed entirely during heavy migrations.

Then add migration awareness: for the duration of a migration that rewrites a table, route reads of that table to the primary or to replicas below a strict lag threshold, and restore normal routing afterwards. Pair this with the stale-read guidance in handling stale replica reads after a schema change and the routing rules in routing DDL vs DML traffic during migrations. Budget primary capacity for the extra reads during migration windows.

Background jobs need the same care as web sessions. A worker that writes a row and then enqueues a follow-up job which reads it may land on a lagging replica, so pass the write position (or a “read from primary” hint) along with the job, and have the job honour it. The same applies to webhooks and callbacks that read back data the request has just written.

Verification Checklist

• After a write, the same session’s next read returns the new value even with replica lag injected in staging.
• Replicas beyond the lag threshold are automatically removed from the read pool.
• Position-based routing (LSN or GTID) is used where the stack supports it; otherwise pin duration exceeds expected lag.
• Reads of tables under active migration are routed to the primary or strictly current replicas for the migration’s duration.
• Primary capacity was checked for the extra read load during migration windows.

Frequently Asked Questions

Why do migrations cause stale reads? Backfills, index builds and rewrites generate large amounts of replication traffic, so replicas fall further behind than usual. Reads routed to them miss recent writes for longer.

Is time-based pinning good enough? Often, if the pin window reliably exceeds lag. It is simple and needs no database cooperation. Position-based routing is exact and avoids sending more reads to the primary than necessary.

How do I get the write position in MySQL? Enable session_track_gtids = OWN_GTID so the server returns the transaction’s GTID to the client after commit, then wait for that GTID on a replica with WAIT_FOR_EXECUTED_GTID_SET before reading, or use a proxy that tracks it.

Should all reads go to the primary during migrations? Rarely. It removes stale reads but can overload the primary. Pin only sessions that recently wrote, route only tables under migration, and eject only replicas that exceed the lag budget.