A betting platform with several brands across Latin America. Slow reports all over the back office: the bets screen needed 20 to 50 seconds to open, operational customer-service tables hovered around 30, and a few business-critical reports took minutes to run. We went through them one at a time, six in all, and the same root problem sat under every one.
The pattern that kept repeating
Every report was a self-contained query against the raw tables that mirror the
transactional system. And on every render it did all the work again:
deduplicate the current state of each row (in ClickHouse, FINAL or argMax
over 250 million bets), join five or six tables live to resolve which brand
each thing belongs to, and only then filter.
The numbers were embarrassing. The user-acquisition listing read 254 million rows to return a page of 25. The operational dashboard fired 41 queries per page load, about 2.5 billion rows in total, peaking at 5 GB of RAM for a single widget, and all of that multiplied by every connected user.
One detail gave the problem away better than any trace: the business summary took ~16 seconds every time. One day, one month, any brand: 16 seconds. A flat cost means no filter is pruning anything, and the query reads the whole history whether it needs it or not.
The work was well written and badly placed: paid in full on every open, with someone watching the screen. That is the thesis of why adding layers makes reports faster, so this article covers what that one leaves out: what happens when you apply it for real, and in what order it pays.
First, what already exists
Looking at this, the temptation is to go build infrastructure, and that is the wrong order. The first report we optimized had left a layer behind: a denormalized fact per brand, sorted so that filtering one brand reads only its slice. The second slow report, the business summary, scanned the entire transactions table twice per render (926 million rows) to add up bonuses. Those two subqueries needed nothing the existing layer did not already have.
We rewrote two CTEs to read from it. Zero new objects, zero application changes, and from a flat 16 seconds to sub-second in the typical case, up to 60×. Since then the first question we ask is no longer what to build, but what the previous report already built that this one can read.
When something is missing, keep it minimal
The margin-by-provider report could not reuse anything: its bonus exclusion needed a column no layer carried. The measurement that settled the design was this: out of 460 million transactions, only 271 thousand were bonus transactions. That is 0.06 %. The other 99.94 % was read on every render just to be discarded.
So the new layer was a table of 271 thousand rows: only the bonus transactions, with the join chains already resolved into flat columns, refreshed every five minutes. The report went from 25–35 seconds cold to 0.13–0.45 seconds, and became flat in the good sense: asking for one day or the whole history costs the same, because it no longer reads what it will throw away.
Sometimes the problem is the shape of the query
The transactions listing already read from a layer sorted by
(tenant_id, created_at, id), and with one brand it flew: the engine walks
the index in order and stops when the page is full. With several brands it
took 9–13 seconds. The difference was an IN.
-- This kills the early stop: the index prefix is not
-- pinned, so the whole range of both brands gets read.
where tenant_id in (1, 2)
order by id desc
limit 25;
-- This brings it back: equality per brand, each branch
-- stops at its own page, a light combiner cuts the final one.
select * from (
select * from events where tenant_id = 1
order by id desc limit 25
union all
select * from events where tenant_id = 2
order by id desc limit 25
)
order by id desc
limit 25;
This is simplified pseudo-SQL (the real one deduplicates and pages with an offset), but the whole idea is there: same data, same layer, different shape. Pagination counts dropped from 41–84 seconds to 0.3–2. No new table would have helped here, because the data was already stored well; what failed was how it was being asked for.
The dashboard: pre-aggregate, because the page is the query
With 41 widgets per load it is not enough for each query to be reasonable: the unit of cost is the page. The fix was to pre-aggregate per hour and per brand (about 98 thousand buckets in total) so each widget sums a few thousand buckets instead of scanning hundreds of millions of rows. Per hour, not per day, because the widgets mix UTC windows with local-timezone windows, and the hourly bucket serves both exactly.
The heavy widgets went from 3.8–36 seconds to 5–386 milliseconds. A full page load dropped from 2.5 billion rows read to under 300 thousand, so ten users staring at the dashboard stopped being a cluster problem.
The gate: the same output, byte for byte
None of this could have shipped without one fixed rule: every new version is compared against the original with the same parameters, and the output has to be identical byte for byte, verified in two independent runs, with the raw results kept under version control. The query templates keep their exact tokens, so rolling back any step means restoring one file.
That rigor had a side effect we were not looking for: reading the queries that closely uncovered a widget whose filter could never be true, and which had been showing zero in production for years. That story is worth an article of its own, and we will write it.
The numbers, side by side
| Report | Before | After |
|---|---|---|
| Business summary | ~16 s always (926 M rows) | 0.3–4 s depending on slice |
| Margin by provider | 25–35 s cold (465 M rows) | 0.13–0.45 s, flat |
| Multi-brand txn listing | 9–13 s; counts 41–84 s | 0.14–1.9 s; counts 0.3–2 s |
| Bets screen | 22–52 s | 0.2–0.8 s |
| User acquisition | 8–30 s (254 M rows per page) | 0.02–0.46 s |
| Dashboard (20 widgets) | 3.8–36 s per widget | 5–386 ms per widget |
All byte-identical against the originals, measured server-side in the same QA environment.
When none of this is needed
- If you have one slow report, fix that query. A program of layers pays when the pattern repeats; for a single case it is over-engineering.
- If your volume is tens of millions of rows, well-indexed brute force is probably enough and simpler to operate.
- If the data has to be current to the second, a five-minute refresh does not serve you and the conversation is a different one (streaming, or reading the source).
- If nobody will own the refresh jobs. A stale layer answers fast and wrong, which is worse than answering slow.
What stays open
The layers are running and the reports are fast, but the work left a deliberate queue: a couple of dead conditions found in one widget were reported and not fixed, because changing a number the business has watched for years is not a technical decision. And the next slow report on that platform no longer starts from zero, because it inherits the layers, the parity scripts and the order of questions. To us that is worth more than any row in the table above, even if it is less flashy.