Back to all articles
Platform at ScalePart 03
System designRead replicasMySQLConsistencyGCP

Platform at Scale, Part 3: When the Replica Is a Few Seconds Behind

Janahan Thavendran10 min read
In this article

Platform at Scale, Part 3 · Be a little wrong on purpose, screen by screen, and make the safe choice the default.

The figures in this series are illustrative. They're rounded and changed to protect confidential data, and they show the shape of each problem rather than the company's actual numbers.

A diner pays on the table tablet. The payment goes through. The next screen should say "Order confirmed". For a moment, it says the order can't be found.

Nothing was lost. The order was safe in the primary database. The confirmation screen had asked a copy that hadn't caught up yet. To the diner, it looked like we'd taken their money and lost their dinner.

Who else was reading from the primary

By now the order path had its indexes (Part 1) and menus came from the cache (Part 2). The primary was still running hot at peak, and diners weren't the reason.

Merchants were. Owners watched live sales dashboards that refreshed every few seconds. Outlet managers pulled order history. End-of-day reports ran across every outlet. Finance teams checked settlements. Each of those reads scanned and added up far more rows than placing an order did, and they all ran on the same primary that was taking orders.

That was a business problem, not just a technical one. On a festive Saturday, an owner refreshing their sales dashboard at 7:30 was competing with their own diners trying to pay.

Menus stayed on the cache. They were the same answer requested again and again, which caching removes. These readers were different: each query was different, heavy, and asked for history. They needed somewhere else to run.

A second pass on indexes

Part 1's indexes were built for the order path. Reporting queries had different shapes: date ranges, totals by outlet and day, exports across months. So we went back to the slow query log for a second pass, this time on the reporting queries, and added indexes for the most common ones, like daily sales by outlet.

It bought headroom. But reports scan a lot of data by nature, and the platform kept growing. Eventually the primary simply didn't have room for both orders and reports at peak.

The fix: one place to write, two places to read

We added read replicas in Cloud SQL for MySQL. The primary stayed the only database you could write to. Every order, payment and menu change went there, and the primary sent each change on to the replicas. Replicas had no public IP. Services connected over a private IP with TLS, each with its own database user holding only the grants it needed (Part 14).

We didn't make one big replica for everything. Different readers needed different things:

Database
Primary
Who reads from it
Orders, payments, order confirmation, kitchen tickets, partner acknowledgements
Why it's separate
The only source of truth. Anything someone acts on right after it's written
Database
Replica 1: live
Who reads from it
Merchant dashboards, order history, outlet manager screens
Why it's separate
Many small, frequent reads that need to stay close to live
Database
Replica 2: batch
Who reads from it
End-of-day reports, finance settlement checks, exports
Why it's separate
Long queries that can push a replica behind, kept away from the dashboards

Peak CPU on the primary dropped from 85% to 50%, and 60% of all reads moved off it.

How the code chose: primary or replica

A rule like "read your own writes from the primary" is easy to agree on and easy to forget. It had to live in the code.

Our data-access layer had two connections: one to the primary and one to the replicas. Every read went to the primary by default. An endpoint read from a replica only if it was explicitly marked as safe to be a few seconds behind, like the dashboard or order history endpoints. New endpoints had to declare which they were, and code review checked it. Later, the golden path generated this routing for every new service, so it became a template default instead of something copied from a neighbour (Part 13).

We chose the default on purpose. If someone forgot to mark a new endpoint, it read from the primary. That cost us a little capacity, never correctness. The other way round, a forgotten flag on a new payment screen would have shown stale data about money.

That's how the opening problem was fixed. The order confirmation screen, payment status and kitchen tickets were all set to read from the primary. A diner who just paid always saw their order.

We considered a smarter option: send a user's reads to the primary for a few seconds after they write anything. It's more precise, but it needs to remember each user's last write across every app copy, and Part 1 had just made those copies hold no state. Our "right after a write" screens were few and well known, so marking endpoints was simpler and safer.

Lag comes in bursts

Copies take time to catch up. Most of the time it was well under a second. But lag isn't steady. It comes in bursts, and the bursts come at the worst possible moment.

A replica applies the primary's changes in order. When writes spike, like a festive dinner rush, the replica has more to apply and falls behind. At peak, replication lag at p95 was 2 seconds, and the worst moments reached 30 seconds. An average would have hidden that completely.

Long reports made it worse. A heavy query running on a replica competes with the replica's own work of applying changes, so a big end-of-day export could hold everything on that replica back. That's exactly why batch got its own replica. Reports could run slowly without making the live dashboards stale.

Measuring lag honestly

MySQL reports its own lag figure, but it can mislead. It can show zero when the replica has simply stopped receiving changes, and it jumps around under load.

So we measured lag with a heartbeat. Every second, the primary wrote the current time into a small table. Each replica compared that row with its own clock. The difference was how far behind it really was.

We alerted per replica, with different limits. The live replica alerted at 10 seconds, because merchants watch it in real time. The batch replica could fall 15 minutes behind before anyone needed to act.

When lag gets too high, or a replica goes down

The tempting answer is "just read from the primary instead". At peak, that's the worst thing you can do. It sends every dashboard and report back to the primary at the moment it's busiest, the same stampede Part 2 protected against when Redis fails.

So we decided ahead of time what each failure meant:

  • Live replica lagging: keep serving from it, and show merchants "Updated 40 seconds ago" on dashboards. Behind but labelled is better than wrong or slow.
  • Live replica down: move its readers to the batch replica if it's healthy.
  • Both replicas down: dashboards drop to a limited mode, with only today's totals, read from the primary at a capped rate. Everything else pauses with a clear message.
  • Batch replica lagging or down: reports wait and run later. Nobody needs an end-of-day report at 7:30 on a Saturday.

The principle was simple. When the read side fails, degrade the read side. Never let it pull down the primary that takes the orders.

The trade-off we made

Option
A second pass of indexes for reporting queries
Why it was tempting
Free
Why we didn't choose it, or not yet
We did it first. It bought headroom, not a lasting answer
Option
A bigger Cloud SQL tier
Why it was tempting
No code change
Why we didn't choose it, or not yet
We did it once. Cost rises steeply, and it's still one machine serving orders and reports
Option
Shard the database
Why it was tempting
Removes the single-machine limit
Why we didn't choose it, or not yet
Reads were the bottleneck, not writes or storage. Sharding solves write and storage limits, at the cost of months of work and harder cross-outlet reports
Option
Route each user to the primary for a few seconds after a write
Why it was tempting
Precise
Why we didn't choose it, or not yet
Needs per-user state across app copies, for a handful of known screens
Option
Two replicas, primary by default, replica reads opt-in per endpoint
Why it was tempting
Takes heavy readers off the primary, and mistakes cost capacity, not correctness
Why we didn't choose it, or not yet
Our choice

We traded a little correctness for a lot of capacity, and we kept that trade inside a fence. Only screens nobody acts on immediately could be a few seconds behind. Anything that touched money, the kitchen or a partner stayed on the primary.

What we measured

  • Primary CPU at peak: from 85% to 50%
  • Share of reads served by replicas: 60%
  • Replication lag at peak, per replica: p95 2 seconds, worst 30 seconds
  • "Order not found" errors on the confirmation screen: from about 150 a week to zero
  • How often merchants saw the "Updated X seconds ago" label on a peak night

What it cost

Each replica is a full database instance with its own bill, about $900 a month each. We added the routing in the data-access layer, the heartbeat, the per-replica alerts and the fallback modes. And every new feature had to answer one more question in design review: can this be a few seconds behind?

The correctness cost was real but fenced. Dashboards and history could be a few seconds behind. Money never was.

What this part leaves for later

Gap left here
Duplicate orders and payments
Where it's covered
Part 4: idempotency and reconciliation
Gap left here
Keeping data consistent once services own their own data
Where it's covered
Part 5: splitting the monolith
Gap left here
Replica lag alongside every other alert after the split
Where it's covered
Part 6: logs and monitoring
Gap left here
Replicas still scanning years of old orders
Where it's covered
Part 10: data growth
Gap left here
Sharding, and the measured trigger for it
Where it's covered
Part 10: data growth
Gap left here
A cross-region replica as a warm standby
Where it's covered
Part 9: zones and regions
Gap left here
Primary-by-default routing for every new service
Where it's covered
Part 13: the platform

What I took from it

A read replica makes your system faster and a little bit wrong. The job is deciding, screen by screen, where a little wrong is acceptable and where it never is.

And make the safe choice the default. When someone forgets, it should cost you capacity, not correctness.

Next in the series: Part 4: Two taps, one charge