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 | Who reads from it | Why it's separate |
|---|---|---|
| Primary | Orders, payments, order confirmation, kitchen tickets, partner acknowledgements | The only source of truth. Anything someone acts on right after it's written |
| Replica 1: live | Merchant dashboards, order history, outlet manager screens | Many small, frequent reads that need to stay close to live |
| Replica 2: batch | End-of-day reports, finance settlement checks, exports | Long queries that can push a replica behind, kept away from the dashboards |
- 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 | Why it was tempting | Why we didn't choose it, or not yet |
|---|---|---|
| A second pass of indexes for reporting queries | Free | We did it first. It bought headroom, not a lasting answer |
| A bigger Cloud SQL tier | No code change | We did it once. Cost rises steeply, and it's still one machine serving orders and reports |
| Shard the database | Removes the single-machine limit | 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 |
| Route each user to the primary for a few seconds after a write | Precise | Needs per-user state across app copies, for a handful of known screens |
| Two replicas, primary by default, replica reads opt-in per endpoint | Takes heavy readers off the primary, and mistakes cost capacity, not correctness | Our choice |
- 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 | Where it's covered |
|---|---|
| Duplicate orders and payments | Part 4: idempotency and reconciliation |
| Keeping data consistent once services own their own data | Part 5: splitting the monolith |
| Replica lag alongside every other alert after the split | Part 6: logs and monitoring |
| Replicas still scanning years of old orders | Part 10: data growth |
| Sharding, and the measured trigger for it | Part 10: data growth |
| A cross-region replica as a warm standby | Part 9: zones and regions |
| Primary-by-default routing for every new service | Part 13: the platform |
- 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