Replication lag and the read-your-own-writes problem

• Replication lag – the time delay between a write committing on the primary database and that change becoming visible on an asynchronous read replica, because replicas apply the primary's change stream (WAL in PostgreSQL) with a short delay.
• Read-your-own-writes problem – a user performs a write on the primary, then immediately reads from a lagging replica that has not yet applied the change, so they see stale data and think their update vanished.
• Session consistency – the fix where a user's reads are routed to the primary for a short window after their write, or until the replica has caught up to the write's log position.
• Synchronous replication – a stronger but costlier option where the primary waits for a replica to confirm before acknowledging the commit, eliminating lag at the price of higher write latency.

What is replication lag?

Read replicas exist to offload read traffic from the primary database. A replica continuously receives the primary's change stream – in PostgreSQL the WAL records, in MySQL the binlog – and applies them to stay in sync. But this application is asynchronous by default: the primary commits and acknowledges the client without waiting for replicas, then the changes flow to replicas a short time later. That gap is replication lag, usually milliseconds but occasionally seconds under load, network issues, or heavy write bursts.

Primary DB                 Replica DB
┌──────────┐   WAL stream  ┌──────────┐
│ COMMIT   │──────────────→│ APPLY    │
│ at T=0   │               │ at T=0.2 │  ← 200ms lag
└──────────┘               └──────────┘

Why does replication lag cause the read-your-own-writes problem?

The sequence is: the user submits a write, it commits on the primary, the application immediately issues a read, a load balancer sends that read to a replica, and the replica has not yet applied the change, so it returns the old value. The user sees their update "disappear."

In banking this is alarming. Rajesh transfers Rs.50,000 to Priya, the app routes his next balance read to a lagging replica, and he sees the pre-transfer balance. He calls the bank in a panic.

-- Step 1: Write goes to primary
BEGIN;
UPDATE accounts SET balance = balance - 50000 WHERE account_id = 'SAV2001';
UPDATE accounts SET balance = balance + 50000 WHERE account_id = 'CUR3001';
COMMIT;  -- committed on primary at T=0

-- Step 2: Read goes to replica (still at T=-1, has not applied the write)
SELECT balance FROM accounts WHERE account_id = 'SAV2001';
-- Returns old balance: Rajesh sees Rs.500,000 instead of Rs.450,000

How do you fix it with sticky-to-primary-after-write?

The simplest solution is session consistency: after a user performs a write, route that user's reads to the primary for a short window (a few seconds that comfortably exceeds typical lag). This is often implemented per session or per user.

package com.bank.service;

import java.time.Instant;
import java.util.concurrent.ConcurrentHashMap;

public class ReadRoutingService {

    private static final long STICKY_WINDOW_MS = 3000;
    private final ConcurrentHashMap<String, Instant> recentWriters = new ConcurrentHashMap<>();

    public void recordWrite(String userId) {
        recentWriters.put(userId, Instant.now());
    }

    public String chooseDataSource(String userId) {
        Instant lastWrite = recentWriters.get(userId);
        if (lastWrite != null
                && Instant.now().toEpochMilli() - lastWrite.toEpochMilli() < STICKY_WINDOW_MS) {
            return "primary";
        }
        return "replica";
    }
}

Customers always see their own transfers immediately, and everyone else's reads still enjoy the replicas.

How do you fix it with LSN-based routing?

A more precise variant captures the write's log sequence number (LSN) and only reads from a replica once that replica has applied up to or past that LSN. Otherwise the read falls back to the primary.

package com.bank.service;

public class LsnRoutingService {

    /**
     * After a write, capture the primary's current LSN.
     * On the next read, compare it to the replica's applied LSN.
     */
    public String chooseDataSource(long writeLsn, long replicaAppliedLsn) {
        if (replicaAppliedLsn >= writeLsn) {
            return "replica";  // replica has caught up
        }
        return "primary";      // replica still behind
    }
}

-- PostgreSQL: check replica's applied LSN
SELECT pg_last_wal_replay_lsn();

What is synchronous replication and when do you use it?

Synchronous replication makes the primary wait for at least one replica to confirm it has received (or applied) the write before acknowledging the commit to the client. This eliminates lag for those reads at the price of higher write latency.

-- PostgreSQL: configure synchronous replication
ALTER SYSTEM SET synchronous_standby_names = 'replica1';
-- Now every COMMIT waits for replica1 to confirm

You usually reserve synchronous replication for the most critical writes (balance updates, fund transfers) and leave less critical writes asynchronous.

How do you monitor and mitigate replication lag?

Monitor lag continuously and alert when it exceeds a threshold. Keep replicas well-provisioned so they can keep up with write volume. Avoid long-running queries on replicas that can delay WAL application.

-- PostgreSQL: check replication lag on primary
SELECT client_addr,
       state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes,
       replay_lag
FROM pg_stat_replication;

package com.bank.monitoring;

import java.math.BigDecimal;
import java.sql.*;
import java.util.logging.Logger;

public class ReplicationLagMonitor {

    private static final Logger LOG = Logger.getLogger(ReplicationLagMonitor.class.getName());
    private static final BigDecimal LAG_THRESHOLD_BYTES = new BigDecimal("1048576"); // 1 MB

    public void checkLag(Connection primaryConn) throws SQLException {
        String sql = "SELECT client_addr, pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes FROM pg_stat_replication";
        try (Statement stmt = primaryConn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
                BigDecimal lagBytes = rs.getBigDecimal("lag_bytes");
                String replica = rs.getString("client_addr");
                if (lagBytes.compareTo(LAG_THRESHOLD_BYTES) > 0) {
                    LOG.warning("Replica " + replica + " lag exceeds threshold: " + lagBytes + " bytes");
                }
            }
        }
    }
}

What is the general pattern for classifying reads?

The guiding principle is to classify each read. If it must reflect the user's own recent write, give it a fresh-read guarantee by routing to the primary or waiting for the replica. If it tolerates slight staleness, let it use a replica and enjoy the scalability.

| Read type | Staleness tolerance | Routing |
|-----------|--------------------|---------|
| Balance after transfer | None | Primary or LSN-gated replica |
| Dashboard summary | Seconds | Replica |
| Monthly statement | Minutes | Replica or materialized view |
| Analytics report | Hours | Replica or data warehouse |

What are common mistakes?

The most common mistake is routing all reads to the primary "to be safe," which defeats the purpose of replicas and overloads the primary.

Wrong: All reads → Primary (no scalability benefit)
Right: Classify reads → Fresh reads to primary, stale-tolerant reads to replica

Another mistake is ignoring lag during load testing. Lag is small under normal load but can spike during write bursts, so test with realistic write volumes.

What should a senior engineer know?

A senior engineer should know that replication lag is not just a database concern but an application architecture concern. The routing logic belongs in the application or connection pool layer, not in the database. They should also understand the trade-off between consistency and availability: stronger consistency (synchronous replication) reduces availability because a replica failure blocks writes. The right approach is to use synchronous replication selectively for critical paths and asynchronous replication for everything else.