Replication Lag: Why Newest Rows Go Stale First

Under replication lag, the newest rows become stale first because they have the least time to propagate to replicas before a read occurs. A row written milliseconds ago is more likely to be missing from a replica than a row written an hour ago, which has had ample time to replicate. This article explains the mechanics, causes, and practical implications of this phenomenon.

What Is Replication Lag?

Replication lag is the delay between a write being accepted on the primary database and that write appearing on a replica. In asynchronous replication—the default in many systems like MySQL, PostgreSQL, and MongoDB—the primary acknowledges the write immediately, then ships the change to replicas in the background. The lag is the time difference between the write timestamp on the primary and the moment the replica applies it. This delay can range from milliseconds to minutes or even hours under heavy load or network issues.

Why New Rows Are Affected First

Consider a table where rows are inserted continuously. At time T, a replica has applied all writes up to T-5 seconds. A row written at T-1 second is not yet on the replica, while a row written at T-10 seconds is. The newest row (T-1) is missing, so a read from the replica sees it as “stale”—even though the replica is only 5 seconds behind. The row written 10 seconds ago is already present, so it appears fresh. Thus, the newest rows are the ones that appear stale first, because they are the ones that haven’t had time to catch up.

How Asynchronous Replication Works

In a typical setup, the primary writes to its transaction log (binlog in MySQL, WAL in PostgreSQL). A replication thread on the primary sends these log entries to the replica, which writes them to its relay log and then applies them. The replica tracks its position in the log. The lag is the difference between the primary’s current log position and the replica’s applied position. This is often measured in seconds using commands like SHOW REPLICA STATUS or pg_stat_replication.

Common Causes of Lag

• High write volume: When the primary handles many writes per second, the replication stream can become a bottleneck, especially if the replica’s hardware is slower.
• Long-running transactions: A transaction that updates millions of rows on the primary takes time to replicate, delaying all subsequent writes.
• Network latency: Geographic distance between primary and replica adds round-trip time to each log batch.
• Replica load: If the replica is serving heavy read traffic, it may have fewer resources to apply incoming changes.
• Single-threaded apply: Some databases apply changes on the replica in a single thread, which can’t keep up with a multi-threaded primary.

How to Measure and Monitor Lag

Most databases provide a lag metric. In MySQL, Seconds_Behind_Source in SHOW REPLICA STATUS is a common indicator, though it can be unreliable (it resets to 0 if the replica is idle). In PostgreSQL, you can query pg_stat_replication for write_lag, flush_lag, and replay_lag. For a more accurate measure, you can write a heartbeat row that the primary updates every second and compare the timestamp on the replica to the current time. This gives you a true end-to-end lag.

Impact on Applications

For applications that read from replicas to scale read traffic, lag can cause anomalies. A user who posts a comment and immediately refreshes might not see it if the read goes to a replica that hasn’t caught up. This is often called “read-your-writes” inconsistency. Another issue is “monotonic reads”: if a user reads a value from one replica and then from another that is further behind, they might see the value go backwards. To mitigate this, many applications route reads for a specific user to the same replica (sticky sessions) or use session consistency.

Mitigation Strategies

• Read-after-write consistency: For critical reads, route them to the primary or wait for the replica to catch up (e.g., use MASTER_GTID_WAIT in MySQL).
• Reduce lag: Upgrade replica hardware, use parallel replication, or move replicas closer to the primary.
• Use synchronous replication: For the highest consistency, some systems allow synchronous replication where the primary waits for at least one replica to acknowledge the write. This eliminates lag but increases write latency.
• Cache invalidation: Invalidate cached items when a write occurs, forcing a fresh read from the primary.

Frequently Asked Questions

Why does the newest row appear stale even if lag is only 1 second?

Because the replica applies changes in order. If the lag is 1 second, a row written 0.5 seconds ago is not yet applied, so a read that hits the replica will not see it. The row written 2 seconds ago is already there. So the newest row is the one that’s missing.

Can replication lag be zero?

Only with synchronous replication, where the primary waits for a replica to acknowledge the write. This adds network round-trip time to every write, increasing write latency. In practice, many systems use asynchronous replication for performance, accepting some lag.

Does reading from the primary avoid stale rows?

Yes, because the primary has the latest data. But that defeats the purpose of scaling reads. A common compromise is to route only the first read after a write to the primary, then switch back to replicas.

How can I tell if my replica is lagging?

Use the database’s status commands or set up a heartbeat table. A heartbeat row updated every second on the primary will show the true lag when you read its timestamp on the replica.

Is lag worse with sharded databases?

Each shard has its own replication stream, so lag can vary per shard. This can cause cross-shard inconsistencies. Monitoring each shard’s lag separately is essential.

Related

• Philips Azur Performer Plus GC4522 Specs, Variants & Care
• Malta or Luxembourg for an Offshore Company? 6 Benefits
• Is Kiwi Safe? Facts on Allergies & Risks