The report that ran on a replica and was three minutes stale

The finance team reported that the daily revenue figure on the dashboard and the figure in the emailed report disagreed by about four hundred pounds. Both were computed from the same query. One ran against the primary and one ran against a replica that was three minutes behind, which nobody had told them and nobody had decided.

The symptom

-- the dashboard, at 17:00:02
SELECT SUM(total_cents) FROM orders WHERE DATE(placed_at) = CURDATE();
-- 4,120,800

-- the report, at 17:00:04, same query, different connection
-- 4,079,400

-- the difference: 41,400 pence, in 6 orders placed between
-- 16:57 and 17:00.

SHOW REPLICA STATUSG
  Seconds_Behind_Source: 0        ← and it was lying

Both figures were correct at the moment their connection saw the data, which is the property of a replica and is not a bug. The failure is that neither report said which it was, so a four-hundred-pound discrepancy looked like a data integrity problem rather than a three-minute delay.

Why it happens

Read routing is introduced for capacity and is presented as transparent, which it is for the majority of reads and is not for any read whose result is compared against another. The routing decision is made in a framework configuration and the consequence lands on a person reading a number.

The fix

Measuring lag properly

-- on the source, every second
REPLACE INTO heartbeat (id, ts) VALUES (1, NOW(6));

-- on the replica: wall-clock staleness, which cannot lie
SELECT TIMESTAMPDIFF(MICROSECOND, ts, NOW(6)) / 1000 AS lag_ms
FROM heartbeat WHERE id = 1;
-- 182,400 ms. three minutes.

-- Seconds_Behind_Source reported 0 because it derives from
-- the timestamp of the event being applied, and the replica
-- had finished a batch and was idle.

The built-in metric reads zero on an idle replica regardless of how stale it is, which is the specific failure here — the replica had caught up on events and was three minutes behind because the source had been idle during a quiet period, then a burst arrived. A heartbeat measures the thing that actually matters, which is how old the data can be.

Deciding which reads may go where

written down, per query rather than per connection:

  MUST be primary   anything in a transaction; a read
                    right after a write by the same user;
                    the checkout stock check; anything a
                    person will compare with another number

  MAY be replica    the dashboard      up to 30s
                    the emailed report up to 5m
                    search indexing    up to 5m
                    the data export    any

the rule: would somebody notice if this were N seconds
old, and would they be right to?

Framing it as a staleness budget per consumer rather than a routing rule per connection is what makes it agreeable to the people affected. The finance team were entirely happy with a five-minute-old report and were not happy with a number that disagreed with another number for reasons nobody could explain.

Making the routing explicit at the call site

// implicit: whatever the connection is configured to do
$total = Order::whereDate('placed_at', today())->sum('total_cents');

// explicit, and greppable
$total = Order::on('replica')
    ->whereDate('placed_at', today())->sum('total_cents');

$stock = Product::on('primary')->find($id)->stock;

// and the guard, in a test: every query in the checkout
// path uses 'primary'

Naming the connection at every call site is verbose and is the only version that is auditable — a grep for on('replica') is a list of every read that may be stale, which is a list somebody can review. The implicit version distributes the decision across a configuration file and everybody’s assumptions.

Sticky reads, and the window they cover

// the framework's version covers one request; the NEXT
// request, 400ms later after a redirect, is not covered
DB::connection()->recordsHaveBeenModified();

// so the window has to outlive the request
Cache::put("primary_until:{$user->id}", now()->addSeconds(10), 30);

public function connectionFor(User $user): string
{
    return Cache::has("primary_until:{$user->id}") ? 'primary' : 'replica';
}

The per-request version is the default in most frameworks and covers the case where a controller writes and then reads, which is not the case that produces support tickets. A user who saves a form and is redirected makes a second request, and only a window that outlives the request handles it — sized against the measured lag rather than guessed.

Stating the staleness where it is read

return [
    'total_cents'  => $total,
    'as_of'        => $this->replicaTimestamp(),
    'stale_by_ms'  => $this->replicaLagMs(),
];

// and in the email:
//   "Revenue to 16:57 (data as of 3 minutes before send)"

// which removed the entire category of support question,
// because the discrepancy explains itself.

Putting the timestamp next to the number is the change that actually resolved the complaint, and it is the cheapest part of the whole exercise. A figure that says what moment it describes cannot disagree with another figure — the two are simply about different moments.

The transaction that quietly moves a read

// wrapping a report in a transaction pins the connection
// to the primary, whatever the routing intended
DB::transaction(function () {
    $report = $this->heavyAggregate();   // PRIMARY
    $this->store($report);
});

// correct — a replica read inside a write transaction
// would see a different snapshot — and it means a report
// wrapped for tidiness is now on the primary.
//
// and the one that hides it: every test with
// RefreshDatabase runs inside a transaction.

The test-suite case is what made this invisible for eight months: the routing configuration existed, no test ever used it, and the first exercise of the replica path was production. Asserting on the connection name in one test per routed query is cheap and is the only thing that keeps the configuration honest.

Verifying it worked

$ php artisan replica:lag
  replica-01   182ms
  replica-02   204ms

$ vendor/bin/phpunit --filter ConnectionRouting
Tests: 14 passed
#   checkout stock check      → primary
#   dashboard revenue         → replica
#   post-write read, +2s      → primary
#   post-write read, +30s     → replica

$ curl -s /api/reports/revenue | jq '{total_cents, stale_by_ms}'
{ "total_cents": 4120800, "stale_by_ms": 191 }

# and the alert that did not exist before:
#   replica lag > 30s for 2m → page

Testing the sticky-read window at two seconds and at thirty is the assertion that the window works in both directions, and it needs a clock that can be advanced — which is one of the few cases where freezing time in a test earns its complexity.

What this costs

A routing decision at every call site, which is verbose and is a thing somebody will forget on the next query. The failure mode of forgetting is a read on the primary, which is the safe direction — a query that should have been on the replica is a capacity issue rather than a correctness one, and that asymmetry is worth relying on deliberately.

The sticky-read cache is the fragile part. It is keyed on the user, sized against a lag figure measured once, and silently stops working if the cache is unavailable — at which point every read goes to the replica and the original bug returns. Failing open to the primary rather than to the replica is the correct default and is one line that is easy to write the wrong way round.