The finance summary aggregated six months of orders on every page load, took 4.1 seconds, and was opened about two hundred times a day by four people. Somebody had created a database view to tidy it up, which made the SQL shorter and the query exactly as slow, because a view is a stored query rather than stored data.
The symptom
mysql> SHOW CREATE VIEW v_monthly_revenueG
CREATE VIEW v_monthly_revenue AS SELECT ... GROUP BY ...
mysql> EXPLAIN ANALYZE SELECT * FROM v_monthly_revenue;
-> Table scan on <temporary>
(actual time=4088..4091 rows=6 loops=1)
-> Aggregate using temporary table
(actual time=4088..4088 rows=6 loops=1)
-> Filter: (o.placed_at > <cache>(...))
(actual rows=2841004 loops=1)
-- 2.8 million rows scanned to produce six.Six rows out of 2.8 million scanned, two hundred times a day. The view had made the application code neater and had changed nothing about the work.
Why it happens
PostgreSQL has materialised views and MySQL does not, so the natural move — store the result — has no syntax. The nearest available thing is a regular view, which sounds similar and is the opposite.
The fix
A summary table, built by hand
CREATE TABLE monthly_revenue (
month DATE NOT NULL,
currency CHAR(3) NOT NULL,
order_count INT UNSIGNED NOT NULL,
net_cents BIGINT NOT NULL,
vat_cents BIGINT NOT NULL,
refunded_cents BIGINT NOT NULL,
refreshed_at DATETIME(6) NOT NULL,
PRIMARY KEY (month, currency)
) ENGINE=InnoDB;The primary key on the grain of the report is what makes an idempotent refresh possible — every write is an upsert on the same key, so a refresh that runs twice produces the same table. Choosing the grain is the design decision and it is the thing to get right first.
Incremental refresh, on a watermark
INSERT INTO monthly_revenue
(month, currency, order_count, net_cents, vat_cents,
refunded_cents, refreshed_at)
SELECT DATE_FORMAT(o.placed_at, '%Y-%m-01'), o.currency,
COUNT(*), SUM(o.net_cents), SUM(o.vat_cents),
SUM(o.refunded_cents), NOW(6)
FROM orders o
WHERE o.placed_at >= ? -- the watermark
GROUP BY 1, 2
ON DUPLICATE KEY UPDATE
order_count = VALUES(order_count),
net_cents = VALUES(net_cents),
vat_cents = VALUES(vat_cents),
refunded_cents = VALUES(refunded_cents),
refreshed_at = VALUES(refreshed_at);Recomputing whole months rather than adding deltas is what makes this robust: a month either has the right total or is rebuilt, and there is no accumulated drift. The watermark selects which months to touch, not which rows to add.
Rounding the watermark back to the start of its month is essential and easy to miss — a watermark at the fifteenth would recompute a month from half its rows and write a total that is half correct. The GROUP BY is over whatever the WHERE selected, and the WHERE has to select complete groups.
-- so the watermark is always a month boundary
SELECT DATE_FORMAT(
COALESCE(MAX(watermark), '2010-01-01'), '%Y-%m-01'
) FROM summary_state WHERE name = 'monthly_revenue';The backdated edit, which breaks every incremental refresh
the failure, in one sentence: a refund applied today
against an order placed in March does not move the
watermark, so March is never recomputed.
the options:
watch updated_at as well as placed_at
→ catches it, and now the watermark is over a column
that moves backwards, which is not a watermark
a dirty-months table, written by a trigger or an
observer on the order model
→ correct, and one more thing to maintain
full rebuild on a schedule
→ correct, simple, and 6 minutes of load nightly
what shipped: incremental every 5 minutes, full rebuild
nightly, and reconciliation after the rebuild.Backdated edits are the failure case for every incremental refresh and there is no clever answer — either something records which groups are dirty, or the whole thing is rebuilt periodically. Combining a frequent incremental pass with a nightly full rebuild is the arrangement that is both fast and eventually correct.
// the dirty-months version, when nightly is not enough
Order::updated(function (Order $order): void {
if ($order->wasChanged(['net_cents', 'vat_cents', 'refunded_cents'])) {
DirtySummaryPeriod::mark(
'monthly_revenue',
$order->placed_at->startOfMonth(),
);
}
});The observer has to fire on every path that changes the underlying values, which means a bulk UPDATE that bypasses the model silently misses it. That is the specific hole in this approach and the reason the nightly rebuild stays even after the observer exists.
Reconciliation, which is not optional
-- run after every full rebuild, alert on any row
SELECT s.month, s.currency,
s.net_cents AS summary, t.net_cents AS truth
FROM monthly_revenue s
JOIN (
SELECT DATE_FORMAT(placed_at, '%Y-%m-01') AS month, currency,
SUM(net_cents) AS net_cents
FROM orders GROUP BY 1, 2
) t ON t.month = s.month AND t.currency = s.currency
WHERE s.net_cents <> t.net_cents;A summary with no reconciliation is a second copy of the data that nothing checks, and it will diverge. The alert threshold is zero rather than a tolerance, because one mismatched row means the mechanism is broken rather than that one month is unlucky.
The first run found two months wrong, both from before the dirty-months observer existed, both caused by refunds applied to old orders. That is the expected outcome and is why the reconciliation is built at the same time as the summary rather than after somebody queries it in a meeting.
Stating the staleness where it is read
// the interface says how old the number is, always
return [
'rows' => $summary,
'refreshed_at' => $summary->max('refreshed_at'),
'stale_by' => now()->diffInSeconds($summary->max('refreshed_at')),
];Putting the refresh time on the page removes an entire category of support conversation, because the question “is this current” is answered before it is asked. It also makes a stalled refresh visible to the people who care most, which is more reliable than an alert going to a channel they do not read.
Verifying it worked
$ curl -s -o /dev/null -w '%{time_total}n' /admin/reports/revenue
0.041 # was 4.102
$ php artisan summary:reconcile monthly_revenue
0 mismatched, 72 periods compared (0.8s)
$ php artisan summary:rebuild monthly_revenue
72 periods rebuilt in 6m11s
$ php artisan summary:reconcile monthly_revenue
0 mismatched
# and the lag, on the dashboard next to the number:
# refreshed 2 minutes agoRebuilding and then reconciling is the acceptance test for the whole arrangement, and running 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.
What this costs
A table that can silently diverge from its source, plus a refresh job, a rebuild command and a reconciliation query. That is four things where there was one view, and it is only worth it because the report is loaded two hundred times a day — the same treatment applied to a monthly export would be indefensible.
The dirty-months observer is the piece most likely to break, because it depends on every write going through the model. A data migration, a bulk update or a fix applied in a database client all bypass it, and the only thing that catches those is the nightly rebuild. Anybody who removes the nightly rebuild because “the incremental refresh handles it” will be right for about three weeks.