System Design Drill Day 4: Read Replicas and Replication Lag

A customer places an order, gets redirected to "My Orders", and the list is empty. They refresh and the order appears. Anyone who has added read replicas to a busy app will recognise that support ticket.

Replicas are usually the first fix when one MySQL server can't keep up with reads, and they bring a new kind of bug where you write something and then can't see it. This drill adds two read replicas to an online marketplace's database, with the Laravel config, and works through lag, queued jobs and failover.

Core concepts

Picture one MySQL server spending most of its time answering "show me products". The natural move is to make copies. One database, the primary, takes every write. One or more replicas follow its change log (the binlog in MySQL, the WAL in Postgres) and answer reads, so a read-heavy app scales without touching the write path.

Replicas scale reads only. Each one still replays every write, so a write-heavy load just gets copied onto more machines. That problem needs batching, queues, or eventually sharding.

Most replication is asynchronous. The primary commits and moves on, and replicas catch up milliseconds to seconds later. That gap is replication lag, and it grows under load or during a long migration.

Lag is behind the empty "My Orders" page. The user saved an order, the next read went to a replica that hadn't caught up, and the order seemed to vanish. The guarantee that broke is called read-your-writes. Common patches are to send a user's reads to the primary for a short time after they write, to read critical paths from the primary always, and to monitor lag so a slow replica can be pulled from the pool.

Synchronous replication avoids losing data on failover, at the cost of slower writes, since each write waits for a replica. Async keeps writes fast but can lose the last few if the primary dies. Semi-sync sits in between: the primary waits for at least one replica to acknowledge each commit, but if no acknowledgement arrives within a timeout it falls back to async, so it narrows the data-loss window without closing it. For anything involving money I'd still start there.

Pause and think: Your app is mostly writes and the primary's CPU is maxed out. Will adding three replicas help?

Answer:

No. Every replica replays every write, so the primary still does all the write work. You need batching, queues or eventually sharding. Replicas only take read traffic off the primary.

When the primary dies, a replica is promoted to take its place. That's failover. Writes fail while it happens, often for 30 to 120 seconds on managed databases, and the app has to cope.

The design problem

The marketplace's MySQL sits at 80% CPU, and about 90% of queries are reads: product listing, search, product pages, order history. Move to one primary and two read replicas in Laravel.

Sketch the config/database.php changes and decide which flows read from replicas and which from the primary. Explain what happens when a customer places an order, is redirected to My Orders, and the order isn't there, and how the queued inventory and email jobs from Day 3 behave once replicas exist. Finally, cover lag spiking to 10 seconds, a replica dying, and the primary dying.

Try the problem before reading the solution.

Questions to think about

1. Why don't replicas help writes?

2. Why is the order missing from My Orders, and what are two ways to fix it?

3. What do you lose if the primary dies under async replication, and what should checkout do during failover?

Model solution

Config

The connection config with two read hosts, one write host and sticky reads:

'mysql' => [
    'read'  => ['host' => [env('DB_READ_HOST_1'), env('DB_READ_HOST_2')]],
    'write' => ['host' => [env('DB_WRITE_HOST')]],
    'sticky' => true,
    // ...
],

Inside DB::transaction, everything uses the write connection.

Routing

Catalog, search and product pages read from the replicas, behind the Day 2 Redis cache, where a little staleness is harmless. Checkout, stock decrement (lockForUpdate inside a transaction), payment and admin stock edits use the primary. My rule: if a stale read could lead to a wrong decision, read from the primary.

Figure 1: Reads that can tolerate staleness go to replicas. Anything that drives a decision goes to the primary.

The missing order

Laravel's sticky option only lasts for the current request, and the redirect to My Orders is a new request that can land on a lagging replica.

One fix stores last_write_at in the session after a write. A middleware then calls DB::connection()->recordsHaveBeenModified() when that timestamp is within roughly the last 5 seconds, so that user's reads go to the primary. The simpler fix is to always run the order-history query with ->useWritePdo(), and I'd usually start there.

Figure 2: Why the order goes missing: the redirect is a new request that reads a replica that hasn't caught up.

Queued jobs

A worker can pick up a job before the replica has the order and throw a ModelNotFoundException. Dispatch with afterCommit and read the order on the write connection inside the job. The Day 3 idempotency checks stay as they are.

Lag spike

Monitor replica lag (Seconds_Behind_Source, or your provider's equivalent metric) and alert above about 2 seconds. Past a threshold, take that replica out of the pool and send its reads to the primary, which is fine for a while if the cache absorbs most traffic.

Replica dies

Laravel's plain host list doesn't health-check anything. Put the replicas behind a reader endpoint or a proxy, such as an RDS reader endpoint or ProxySQL, so dead nodes drop out automatically.

Primary dies

A replica is promoted. With async replication the last few seconds of writes can be lost, so for orders I'd use semi-sync or a managed synchronous standby (Multi-AZ). Keep in mind that semi-sync degrades to async on timeout, so it isn't a hard no-data-loss guarantee.

During failover, checkout should fail fast with a friendly "try again". The client retries with the same idempotency key, so nobody is charged twice.

Interview one-liner

"Replicas buy read capacity in exchange for consistency, so I route each read by how much staleness it can tolerate."

Conclusion

Every replica you add is another place a user can read stale data, so routing has to be decided per read, not per app. The one to watch is any read that drives a decision: send it to the primary, even if that costs some capacity.

Try it yourself

With Docker, start a MySQL primary and one replica (there are many compose files for this) and point Laravel's read and write hosts at them. Run STOP REPLICA on the replica, create a record, and read it back in a second request.

That reproduces the missing-order bug. Then try the useWritePdo() fix and the session-based "recent writer" middleware, and decide which one you would ship.

---

This is part of my daily System Design Drills series.