A read model that is allowed to be wrong for four seconds

The operations dashboard ran a query with eleven joins and four subqueries, took 3.4 seconds, and was loaded by nine people every few minutes throughout the working day. Every attempt to optimise it had ended in an index that helped one clause and hurt another, because the query was not slow — it was asking a question the schema was not shaped to answer.

The symptom

mysql> EXPLAIN ANALYZE SELECT ... 11 joins ... ;
-> Nested loop left join (cost=884102 rows=41)
   (actual time=0.8..3390 rows=204 loops=1)
     -> Table scan on <temporary>
        (actual time=2841..2844 rows=8814 loops=1)

3.39 seconds, 204 rows out.

-- and the shape of it:
--   orders → lines → variants → products → categories
--   orders → customers → addresses
--   orders → shipments → carriers
--   plus three aggregate subqueries

Two hundred and four rows produced from eight thousand eight hundred intermediate ones, through a temporary table. Every join is correct, every foreign key is indexed, and the normalisation is exactly right for the write path.

Why it happens

A normalised schema is optimised for writing a fact once and keeping it consistent. A dashboard wants a row per question, with everything already joined and aggregated, which is the opposite arrangement.

One model serving both works for a long time and stops working at a size that depends on the query rather than on the data volume. The failure is not gradual: it is fine, then somebody adds a filter and it is eleven joins.

The fix

A table shaped like the question

CREATE TABLE order_summary (
  order_id        BIGINT UNSIGNED PRIMARY KEY,
  placed_at       DATETIME(6) NOT NULL,
  state           VARCHAR(32) NOT NULL,
  customer_id     BIGINT UNSIGNED NOT NULL,
  customer_name   VARCHAR(255) NOT NULL,     -- duplicated
  customer_tier   VARCHAR(16) NOT NULL,      -- duplicated
  line_count      INT UNSIGNED NOT NULL,
  total_cents     BIGINT NOT NULL,
  carrier         VARCHAR(64) NULL,
  shipped_at      DATETIME(6) NULL,
  refreshed_at    DATETIME(6) NOT NULL,

  KEY idx_state_placed (state, placed_at),
  KEY idx_customer (customer_id, placed_at)
) ENGINE=InnoDB;

The duplication is deliberate and is the whole point — the customer name is in two places and one of them is authoritative. That is only defensible because the summary is derived: it must be rebuildable from the write model at any moment, and rebuilding must be a routine command rather than an emergency procedure.

mysql> EXPLAIN ANALYZE
    -> SELECT * FROM order_summary
    -> WHERE state = 'awaiting_dispatch' AND placed_at > ?
    -> ORDER BY placed_at DESC LIMIT 200;

-> Index range scan on order_summary using idx_state_placed
   (actual time=0.04..1.8 rows=204 loops=1)

3.39s → 0.002s.

Projecting from events rather than polling

final class OrderSummaryProjector
{
    public function onOrderPlaced(OrderPlaced $e): void
    {
        $this->upsert($e->orderId);
    }

    public function onOrderShipped(OrderShipped $e): void
    {
        $this->upsert($e->orderId);
    }

    public function onCustomerRenamed(CustomerRenamed $e): void
    {
        // the denormalised copy has to follow its source
        DB::table('order_summary')
            ->where('customer_id', $e->customerId)
            ->update(['customer_name' => $e->name, 'refreshed_at' => now()]);
    }
}

The rename handler is the one that gets forgotten, and it is where denormalisation actually costs something: every duplicated column needs a listener for every event that could change its source. Enumerating those is the design work, and missing one produces a summary that is quietly wrong forever rather than for four seconds.

Building the summary row with a single query rather than incrementally is the choice that keeps the projector simple — it re-derives the whole row from the write model on any relevant event, which is more work per event and removes a class of drift.

private function upsert(int $orderId): void
{
    DB::statement(<<<'SQL'
        INSERT INTO order_summary (order_id, placed_at, state, customer_id,
            customer_name, customer_tier, line_count, total_cents, refreshed_at)
        SELECT o.id, o.placed_at, o.state, c.id, c.name, c.tier,
               COUNT(l.id), o.total_cents, NOW(6)
        FROM orders o
        JOIN customers c ON c.id = o.customer_id
        LEFT JOIN order_lines l ON l.order_id = o.id
        WHERE o.id = ?
        GROUP BY o.id
        ON DUPLICATE KEY UPDATE
            state = VALUES(state), line_count = VALUES(line_count),
            total_cents = VALUES(total_cents), refreshed_at = VALUES(refreshed_at)
    SQL, [$orderId]);
}

The staleness budget, agreed rather than assumed

the conversation that had to happen, with the operations
team rather than within engineering:

  "the dashboard may be up to N seconds behind. what is
   the largest N that does not change how you work?"

  answers: 30s for the volume figures
           5s  for the dispatch queue — they act on it
           0   for the order they just edited

so: 4s target on the projector, and the third case is
handled differently. it is not a caching problem.

Asking rather than assuming produced a much larger budget than engineering had guessed for two of the three cases, and an uncompromising zero for the third. That third case is read-your-own-writes and no staleness budget solves it.

// after a write, read from the write model for this user
// for a short window — the only reliable answer
session()->put('summary_bypass_until', now()->addSeconds(10));

public function forDashboard(User $user): Collection
{
    return $this->bypassActive()
        ? $this->fromWriteModel()      // slow, correct, rare
        : $this->fromSummary();        // fast, 4s stale
}

Routing one user to the slow path for ten seconds after they write is inelegant and works. The alternatives — waiting for the projection synchronously, or merging the pending write into the read — are each more code and more failure modes for a case that affects one person for a few seconds.

Rebuilding, which must be routine

// php artisan projection:rebuild order_summary --since=2021-01-01
public function rebuild(?CarbonInterface $since = null): void
{
    $query = Order::query()->when($since, fn ($q) => $q->where('placed_at', '>=', $since));

    $query->chunkById(1000, function (Collection $orders): void {
        DB::transaction(function () use ($orders): void {
            foreach ($orders as $order) {
                $this->upsert($order->id);
            }
        });

        $this->progress->advance($orders->count());
    });
}

chunkById rather than chunk matters here because rows are being written while the rebuild walks: offset-based chunking skips rows when the set shifts underneath it, which produces a rebuild that silently misses some. That is the specific failure a rebuild exists to fix.

Rebuilding into the live table rather than into a new one is the simpler choice and means the dashboard is inconsistent during the rebuild. Building into order_summary_new and swapping with RENAME TABLE is atomic and costs the disk, and is what a table this size deserves.

A reconciliation that runs whether or not anybody asks

-- nightly: does the summary agree with the source?
SELECT COUNT(*) AS mismatched FROM order_summary s
JOIN orders o ON o.id = s.order_id
WHERE s.state <> o.state
   OR s.total_cents <> o.total_cents;

-- and the rows that exist in one and not the other
SELECT COUNT(*) FROM orders o
LEFT JOIN order_summary s ON s.order_id = o.id
WHERE s.order_id IS NULL;

A projection with no reconciliation is a second copy of the data that nothing checks, and it will diverge — a missed event, a failed job, a deploy during a write. The check is cheap on an indexed comparison and the alert threshold should be zero, because one mismatch means the mechanism is broken rather than that one row is unlucky.

The first run found forty-one mismatched rows, all from the fortnight before the rename listener was added. That is the expected outcome and is why the reconciliation is built at the same time as the projection rather than after the first incident.

Verifying it worked

$ curl -s -o /dev/null -w '%{time_total}n' /admin/dashboard
0.198        # was 3.412

$ php artisan projection:lag order_summary
max lag: 1.8s   p95: 0.9s   target: 4s

$ php artisan projection:reconcile order_summary
0 mismatched, 0 missing, 412008 compared (2.1s)

$ php artisan projection:rebuild order_summary
412008 rows in 4m12s
$ php artisan projection:reconcile order_summary
0 mismatched

Rehearsing the rebuild and then reconciling is the acceptance test for the whole arrangement, and doing it on a schedule rather than once is what keeps it true — a rebuild that has not run in six months is a rebuild that does not work.

The lag metric with the agreed target on the same dashboard is what makes the staleness budget real rather than a paragraph in a document. When it is exceeded the alert says which promise is being broken.

What this costs

Two models, a projector, a rebuild command, a reconciliation job and a lag metric — five things where there was one query. That is a substantial amount of machinery and it is only worth it because the query was on a page nine people load continuously; the same treatment applied to a monthly report would be indefensible.

The denormalised columns are the part that will cause the next bug. Every duplicated field needs a listener for every event that changes its source, and there is no mechanism that enumerates those — a new event type that changes a customer’s tier will silently not update the summary until somebody notices. The reconciliation catches it, a day later, which is the compromise this design accepts.

It is also worth being clear that this is not event sourcing. The write model remains the source of truth and the events are notifications; the summary can be discarded and rebuilt at any time. Confusing the two leads people to think they have signed up for immutable event storage, schema evolution on written events and a deletion problem, none of which apply here.