Replica lag and read-your-writes

Day 27: Replica Lag and Read-Your-Writes, Now That Agents Read Their Own Writes

Your agent inserts an order, reads it back 10 milliseconds later, finds nothing, and inserts it again. Now the customer has two orders, and the transcript makes the model look like it's hallucinating. It isn't. Read replicas are usually the first scaling move a team makes, and they quietly drop a guarantee almost all code assumes: that you can read what you just wrote. Humans rarely trip over this. Agents trip over it all the time.

1. Start with a transcript

Give an agent a SQL tool pointed at your staging database and ask it to create an order for a customer. Here's what comes back:

→ execute_sql: INSERT INTO orders (customer_id, sku, qty) VALUES (4821, 'PLAN-PRO', 1)
← ok, 1 row affected

→ query_sql: SELECT id FROM orders WHERE customer_id = 4821 AND sku = 'PLAN-PRO'
← 0 rows

agent: The insert does not appear to have persisted. Let me fix that and retry.

→ execute_sql: INSERT INTO orders (customer_id, sku, qty) VALUES (4821, 'PLAN-PRO', 1)
← ok, 1 row affected

→ query_sql: SELECT id FROM orders WHERE customer_id = 4821 AND sku = 'PLAN-PRO'
← 2 rows

agent: The order now exists. Task complete.

That's two orders where there should be one. Before you read on, pick who you'd blame: the model, the prompt, the tool or the database. Most people pick the model. But the model did exactly what a careful engineer would do with the evidence in front of it. The evidence was wrong.

2. What actually happened, as a timeline

Treat the 300ms as an illustration. Real lag can be far lower or far higher. What matters is the shape. The write commits on the leader. The read lands on a replica that hasn't replayed that write yet. And the replica answers honestly, about a moment that's already gone.

3. Why replicas exist at all

Quick recap from the leader/follower day. Every write goes through one leader, but any follower can serve reads, so reads scale out and writes don't. The cost is that each replica is a slightly old copy of the truth, behind by however long it takes to ship and replay the change log.

4. The guarantee you lost: read-your-writes

• ▹Read-your-writes (also called read-after-write consistency): once a client writes something, that same client's later reads always see it. Other clients might not see it yet, and that's allowed.
• ▹Monotonic reads: a client never sees data move backwards. Once you've seen version 5, you'll never see version 4 again.
• ▹They're separate guarantees. You can have either one without the other, and replicas behind a round-robin load balancer often give you neither.

Here's what 'time going backwards' looks like in practice. Replica A is 50ms behind and replica B is 3s behind. A user posts a comment and refreshes. A serves the page, and the comment is there. They refresh again, B serves it, and the comment's gone. Nothing was lost. Their reads just bounced between two copies of different ages. The usual fix for monotonic reads is to make each session sticky to one replica, say by hashing the user or session ID. But stickiness doesn't buy you read-your-writes. The sticky replica can still be behind your last write.

5. Why humans hide this bug and agents expose it

A person submits a form, the page redirects, they read, they think, they click. That gap usually runs from a few hundred milliseconds to several seconds, which is longer than typical replica lag on a healthy cluster. The human's own slowness hides the inconsistency, and plenty of read-replica setups shipped for years on that lucky accident.

Agents flip this around. Write-then-verify is the heart of how a well-behaved agent works: create the resource, read it back, confirm, move on. The gap between the write and the verifying read is often a few milliseconds. For an agent, read-after-write isn't an edge case. It happens on every step.

• ▹Retries turn lag into duplicate writes. The verify fails, the agent concludes the write failed, and it writes again. Without idempotency, every retry is another row, another charge, another ticket.
• ▹Loops. The agent keeps 'fixing' a problem that would have resolved itself 300ms later, then has to clean up the extra copies it made along the way.
• ▹It looks like hallucination. In the transcript the agent says 'the insert did not persist', which is false. Reviewers blame the model, add 'do not retry inserts' to the prompt, and leave the real bug where it was.
• ▹Multi-agent setups make it worse. Agent A writes a record and hands the ID to agent B. B's first read hits a replica, and as far as B can tell, that ID doesn't exist.

My rule for any team giving agents database access: when an agent does something irrational right after a write, check where the read was routed before you touch the prompt.

6. Measure it before you fix it

On PostgreSQL, run this on the primary:

-- On the primary: one row per connected standby
SELECT application_name,
       state,
       write_lag,
       flush_lag,
       replay_lag,                                   -- time until the standby has APPLIED it
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS bytes_behind
FROM pg_stat_replication;

-- On a replica: rough lag in time
SELECT now() - pg_last_xact_replay_timestamp() AS approx_lag;
-- Caveat: on an idle primary this keeps growing even when fully caught up,
-- because no new transactions are arriving to replay.

On MySQL, run `SHOW REPLICA STATUS` and look at `Seconds_Behind_Source` (`Seconds_Behind_Master` in older versions). It's computed from event timestamps, so it can lie to you: it's NULL when the SQL thread is stopped, and it can read 0 while the I/O thread is itself behind. Many teams use a heartbeat table instead. Percona Toolkit's pt-heartbeat, for example, writes a timestamp on the source every second and reads it back on the replica.

Watch the p99 and the max, not the average. On a quiet cluster the average can be tiny. The lag your users and agents actually hit comes from spikes:

• ▹Big maintenance on the primary (VACUUM, index builds, bulk UPDATEs, backfills) produces a burst of WAL that replicas have to chew through.
• ▹On a Postgres hot standby, long-running queries can conflict with replay. Replay waits for the query, up to max_standby_streaming_delay (30s by default), before cancelling it. So an analytics query on a replica can hold up your application reads.
• ▹On MySQL, single-threaded or poorly parallelised apply means one big transaction on the source blocks everything queued behind it.
• ▹Cross-region replicas add network round-trip time to every change, and you pay that cost permanently.
• ▹A replica that's still catching up after a restart or failover can be minutes behind.

An agent running hundreds of write-verify cycles an hour will hit your p99 many times a day. Design for the spike.

7. Fix 1: session pinning (leader-after-write)

After a session writes, send all of that session's reads to the leader for N seconds, with N comfortably above your p99 lag. Once the window closes, reads go back to the replicas.

import time

class PinningRouter:
    def __init__(self, leader, replica, pin_seconds=2.0):
        self.leader, self.replica = leader, replica
        self.pin_seconds = pin_seconds
        self.pinned_until = {}            # session_id -> monotonic deadline

    def write(self, session_id, sql, params=()):
        self.leader.execute(sql, params)
        self.pinned_until[session_id] = time.monotonic() + self.pin_seconds

    def read(self, session_id, sql, params=()):
        pinned = time.monotonic() < self.pinned_until.get(session_id, 0)
        conn = self.leader if pinned else self.replica
        return conn.execute(sql, params).fetchall()

Rails ships exactly this as automatic role switching. Set `config.active_record.database_selector = { delay: 2.seconds }` with the DatabaseSelector resolver, and GET/HEAD requests read from the replica unless that session wrote inside the delay window. The Rails Multiple Databases guide walks through the setup.

• ▹Reach for it when you want one config change that fixes most read-your-writes bugs.
• ▹Pitfall: it's blunt. A session that writes often barely touches a replica, and a write-heavy agent fleet can end up pinning most of its traffic to the leader.
• ▹Pitfall: a fixed N is a guess. When a lag spike goes past N, the bug comes back, right when the system is already under stress.
• ▹Pitfall: the session ID has to survive across processes. If an agent's write and its verify run on different workers that don't share pin state, you have no pinning at all.

8. Fix 2: causal tokens (LSN / GTID)

Instead of guessing a time window, carry an exact position. When a write commits, return its log position: the WAL LSN in Postgres, the GTID set in MySQL. When the same client reads, it passes that token along, and only a replica that has replayed at least that far may serve the read. If none has, wait briefly or fall back to the leader.

def write_with_token(leader, sql, params=()):
    leader.execute(sql, params)                      # autocommit: committed here
    # With synchronous_commit=on (the default), the commit is flushed before we get here,
    # so the current WAL write position is >= the end of our commit record
    return leader.execute('SELECT pg_current_wal_lsn()::text').fetchone()[0]

def read_at_least(replica, leader, lsn, sql, params=(), timeout=1.0):
    deadline = time.monotonic() + timeout
    while time.monotonic() < deadline:
        caught_up = replica.execute(
            'SELECT pg_last_wal_replay_lsn() >= %s::pg_lsn', (lsn,)
        ).fetchone()[0]
        if caught_up:
            return replica.execute(sql, params).fetchall()
        time.sleep(0.02)
    return leader.execute(sql, params).fetchall()    # bounded wait, then leader

MySQL works the same way. Read the session's GTID after the write (via `@@gtid_executed` or session GTID tracking), then run `SELECT WAIT_FOR_EXECUTED_GTID_SET('<gtid_set>', 1)` on the replica before reading. MongoDB's causally consistent sessions do the same thing with `afterClusterTime`.

• ▹Use it when you need precision. The read waits exactly as long as it has to and no longer, and you can still use replicas immediately after a write.
• ▹This is what makes 'consistency' work as a tool parameter: the write tool returns a token, and the read tool accepts one.
• ▹Pitfall: the token has to travel. Across services, queues and agent handoffs it belongs in the payload, the same way a trace ID does. If agent A hands agent B a record ID without the token, B is back to guessing.
• ▹Pitfall: always bound the wait and fall back to the leader. An unbounded wait on a stalled replica turns your consistency fix into an outage.

9. Fix 3: staleness budgets per query

Not every read has to be fresh. Give each query or endpoint an explicit staleness budget, then route on that budget and the lag you've measured.

# Staleness budgets, reviewed like any other contract
STALENESS_BUDGET = {
    'payment_confirmation':  0,      # leader only
    'order_status_after_checkout': 0,
    'user_profile_page':     5,      # seconds
    'search_results':        30,
    'admin_dashboard':       300,
    'weekly_report':         3600,
}

def route(query_name, current_replica_lag_s):
    budget = STALENESS_BUDGET[query_name]
    return 'replica' if current_replica_lag_s <= budget else 'leader'

• ▹Dashboards, analytics and search can go to replicas. Payment confirmations, idempotency checks and anything read straight after a write go to the leader.
• ▹Put the number in the PR. When someone adds an endpoint, the review should say something like 'this read tolerates 5s of staleness, and here's why'. A hidden ORM default that sends every SELECT to a replica is exactly how you get the transcript from section 1.
• ▹If lag goes over budget, route to the leader. Don't serve data you've already decided is too old.
• ▹Caches are replicas too. A 60-second cache TTL is a 60-second staleness budget, so keep both in the same table.

10. Agent harness design

If you hand agents database or API tools, design those tools so this bug can't happen silently. Here's what that looks like:

query_tool = {
    'name': 'query_orders',
    'description': (
        'Read orders. Use consistency=strong when verifying something you just wrote. '
        'Use bounded_staleness for reporting or exploration.'
    ),
    'input_schema': {
        'type': 'object',
        'properties': {
            'filter': {'type': 'object'},
            'consistency': {
                'type': 'string',
                'enum': ['strong', 'bounded_staleness'],
                'default': 'strong',
            },
            'after_token': {
                'type': 'string',
                'description': 'Causal token returned by a previous write tool call.',
            },
        },
        'required': ['filter'],
    },
}

create_order_tool = {
    'name': 'create_order',
    'description': 'Create an order. Retrying with the same request_id is safe and returns the original order.',
    'input_schema': {
        'type': 'object',
        'properties': {
            'customer_id': {'type': 'integer'},
            'sku': {'type': 'string'},
            'request_id': {'type': 'string', 'description': 'Idempotency key, a UUID chosen by the caller.'},
        },
        'required': ['customer_id', 'sku', 'request_id'],
    },
}

• ▹Make read consistency an explicit argument (strong or bounded_staleness) and default to strong. A wrong choice should cost you some leader load, never correctness.
• ▹Make write tools idempotent with request keys: a UNIQUE constraint on request_id plus INSERT ... ON CONFLICT (request_id) DO NOTHING RETURNING. RETURNING gives you no row on a conflict, so when it comes back empty, look up the existing row on the leader. Then even if every other fix fails, a retry can never create a duplicate.
• ▹Verification reads go to the leader by default. If your harness has a 'verify' or 'check result' step, route it with consistency=strong or pass the causal token. Verifying against a stale copy is worse than not verifying at all, because it produces confident wrong conclusions.
• ▹Return a causal token from every write tool, and have the harness, not the model, attach it to the next read. Don't count on the LLM remembering to pass it.
• ▹Log the routing decision (leader or replica, plus lag at read time) in the tool result. The next time you're staring at a strange transcript, that one field saves you an hour.

11. Pre-flight check before adding a replica

A replica is a consistency trade-off, not a free performance upgrade. Before you add one, rule out the cheaper fixes:

• ▹Run EXPLAIN (ANALYZE, BUFFERS) on the slow queries and use pg_stat_statements to see which ones actually dominate. A replica won't fix a missing index or an N+1 pattern (agent-written ORM code is full of them). You'll just run the same bad query on two machines and pay for both.
• ▹Look at connection pooling. Plenty of 'the database is overloaded' incidents are really too many connections, especially when agent fleets and serverless functions each open their own. PgBouncer or your platform's pooler may fix it outright.
• ▹Look at caching. A small cache in front of hot, staleness-tolerant reads can take off more load than a replica, and its staleness is spelled out in the TTL.
• ▹If you still need replicas, ship them together with the measurement queries, the staleness budget table and the routing pattern you picked. Without those, you've added a consistency bug along with the capacity.

12. Exercise for today

Build a local primary and replica with an artificial apply delay, point a tiny 'agent' loop at it, watch it double-write, then fix it three ways.

docker network create pglab

# Primary (defaults already allow streaming replication: wal_level=replica)
docker run -d --name pg-primary --network pglab -p 5432:5432 \
  -e POSTGRES_PASSWORD=pw postgres:16
sleep 5
docker exec pg-primary psql -U postgres -c "CREATE ROLE repl WITH REPLICATION LOGIN PASSWORD 'pw';"
docker exec pg-primary bash -c 'echo host replication repl all scram-sha-256 >> $PGDATA/pg_hba.conf'
docker exec pg-primary psql -U postgres -c 'SELECT pg_reload_conf();'

# Replica: base backup, then start with a 500ms replay delay
docker run -d --name pg-replica --network pglab -p 5433:5432 \
  -e PGPASSWORD=pw --entrypoint bash postgres:16 -c '
  until pg_basebackup -h pg-primary -U repl -D /var/lib/postgresql/data -R; do sleep 1; done
  chown -R postgres:postgres /var/lib/postgresql/data
  chmod 700 /var/lib/postgresql/data
  exec gosu postgres postgres -c recovery_min_apply_delay=500ms'

# Verify replication is streaming
docker exec pg-primary psql -U postgres -c 'SELECT application_name, state, replay_lag FROM pg_stat_replication;'

# agent_loop.py  (pip install 'psycopg[binary]')
import time, uuid, psycopg

w = psycopg.connect('postgresql://postgres:pw@localhost:5432/postgres', autocommit=True)
r = psycopg.connect('postgresql://postgres:pw@localhost:5433/postgres', autocommit=True)

w.execute('DROP TABLE IF EXISTS orders')
w.execute('CREATE TABLE orders (id bigserial PRIMARY KEY, customer int, sku text, request_id uuid UNIQUE)')
time.sleep(1)  # let the DDL reach the replica

def naive_agent(customer, sku):
    for attempt in range(3):
        w.execute('INSERT INTO orders (customer, sku) VALUES (%s, %s)', (customer, sku))
        rows = r.execute('SELECT id FROM orders WHERE customer=%s AND sku=%s',
                         (customer, sku)).fetchall()
        if rows:
            return rows
        print(f'attempt {attempt}: not visible, retrying...')

naive_agent(4821, 'PLAN-PRO')
time.sleep(1)
print('orders on leader:', w.execute('SELECT count(*) FROM orders').fetchone()[0])

• ▹Run it. You should see retries and more than one order on the leader. That's the transcript from section 1, reproduced on your laptop.
• ▹Fix A, pinning: wrap the connections in the PinningRouter from section 7 with pin_seconds=2. Re-run and confirm there's exactly one order. Then drop pin_seconds to 0.2, below the 500ms delay, and watch the bug come back.
• ▹Fix B, causal tokens: use write_with_token and read_at_least from section 8, and print how long each read waited. Expect roughly 500ms, with no leader fallback unless you shorten the timeout.
• ▹Fix C, staleness budget: name the verify query 'order_status_after_checkout' with a budget of 0 so it routes to the leader. Then add a 'dashboard_count' query with a 5s budget that stays on the replica.
• ▹Bonus: add request_id to the insert with INSERT ... ON CONFLICT (request_id) DO NOTHING, reusing one UUID across retries, and strip out all the routing fixes. You still get exactly one order, just a confused agent. Routing fixes the confusion; idempotency limits the damage when routing fails.
• ▹Stretch: raise recovery_min_apply_delay to 5s and re-run Fix A with pin_seconds=2. That's what a lag spike bigger than your pin window looks like.

13. Bridge to Day 28

Read-your-writes belongs to a family of session guarantees, alongside monotonic reads, monotonic writes and writes-follow-reads, first set out in the Bayou work on weakly consistent replicated data. Today every write still went to one leader, so there was always a single source of truth to fall back on. Tomorrow that goes away. We'll look at what happens when the writes themselves land in more than one place, with multi-leader and leaderless replication, and at conflicts that no amount of read routing can fix.

Extend your knowledge

• ▹Martin Kleppmann, Designing Data-Intensive Applications, the Replication chapter (chapter 5 in the first edition, chapter 6 in the second). Spend the most time on the section about problems with replication lag: read-your-writes, monotonic reads and consistent prefix reads.
• ▹Terry et al., 'Session Guarantees for Weakly Consistent Replicated Data' (1994, the Bayou project). This is where read-your-writes, monotonic reads, monotonic writes and writes-follow-reads were first defined.
• ▹PostgreSQL documentation: the 'Hot Standby' chapter (query conflicts, max_standby_streaming_delay) and the pg_stat_replication view. MySQL documentation for WAIT_FOR_EXECUTED_GTID_SET.
• ▹Rails Guides, 'Multiple Databases with Active Record'. The automatic role switching section is a compact, real-world implementation of session pinning, and it's worth reading line by line.