The problem
Perform[cb] is a performance-marketing network. Every ad click that passes through the platform is an event, and a service aggregated those events hourly across a set of predefined dimensions — location, browser, site, category — writing the results into MySQL. Reporting read from that table.
It worked until it didn't. The clicks table reached roughly 1.5 billion rows, and reporting degraded the way these things always do: gradually, then all at once. By the end, the heaviest client reports took up to 30 minutes to generate. Some combinations of filters never returned at all.
The usual moves had been made. Indexes existed. Queries had been tuned. The instance had been scaled up, and then scaled up again. We were paying for large RDS instances to make a fundamentally unsuitable access pattern slightly less painful.
That last point is the one that mattered. This was not a slow query problem. It was the wrong kind of database for the job.
Why ClickHouse
MySQL is row-oriented. It is built to fetch and modify whole records efficiently, which is exactly right for transactional work. Our reporting workload was the opposite shape:
- Aggregate-heavy — almost every query was a
SUMorCOUNTover a date range, grouped by a handful of dimensions. - Narrow — reports touched a few columns out of many, but scanned enormous numbers of rows.
- Append-only — the aggregation service inserted. We never updated a historical stats row, and we never deleted one.
That is a description of a column store. A column-oriented engine reads only the columns a query asks for, compresses each column far better because the values are homogeneous, and is designed around exactly the bulk-insert, no-update pattern we already had. ClickHouse was built for this workload specifically.
The absence of updates was the detail that made the decision straightforward. Column stores are poor at mutation, and that is usually the objection to them. Here it cost us nothing.
Making the case
I did not want to argue this from first principles in a meeting. Proposing a new database is proposing that other people take on risk, and reasoning is easier to dismiss than numbers.
So I set ClickHouse up locally, loaded it with representative test data, and rebuilt several of the worst-performing reports against it. Then I brought the timings.
The difference was not incremental, and it did not need interpreting. That is what got the decision made.
The team lead approved it and I was asked to define how it would actually work in our infrastructure.
Architecture
At the time there was no managed ClickHouse offering on AWS — no equivalent of RDS to click through. It had to be provisioned and operated ourselves, which I worked out together with our system administrator: he owned the infrastructure, I owned how the application would use it.
The decision that shaped everything else was not replacing MySQL. MySQL stayed as the system of record for the entire platform, and the aggregation service kept writing stats into it. ClickHouse was added alongside, as a second destination for the same data, and reporting reads were pointed at it.
That choice cost some duplicated storage. In exchange, it meant the migration was reversible at every point, and it gave us something to verify against later — which turned out to matter more than the storage cost.
Schema
The ClickHouse tables could not mirror the MySQL ones. Column stores want different things: a sort key chosen around how the data is actually queried, partitioning that lets whole chunks be skipped, and types picked for compression rather than convenience.
I designed the target structure around the access pattern we already knew from the reports — date range first, then the dimensions the reports grouped by. The result was a schema shaped like our questions rather than like our writes.
Migrating 1.5 billion rows
I handled moving the historical data across. Meanwhile the team that owned the aggregation service built the second write path, so new stats landed in both databases going forward.
Nothing about this half was clever. It was batching, checking, and re-running the parts that fell over — which is most of what a migration actually is.
The tooling that didn't exist
This is the part I had not fully anticipated when I proposed the change.
In 2020 there was no usable Laravel integration for ClickHouse. So before any report could move, I had to build:
A connector
A client the application could actually use — connection handling, query execution, result mapping — sitting in roughly the place a database driver sits, so the rest of the codebase did not have to learn a new idiom.
A query translation layer
The bigger piece. We had a substantial body of existing reporting queries, and rewriting all of them by hand would have made the migration take far longer and introduced a long tail of subtle behavioural differences.
Instead I built an intermediary that translated most of our existing queries into ClickHouse-compatible form automatically. Where a query could not be translated cleanly — or where a direct translation would have been slow — I rewrote it by hand to take advantage of the engine rather than fight it.
Most of the work in a database migration is not the database. It is everything that assumed the old one.
Trusting it
A reporting system that is fast and subtly wrong is worse than one that is slow and correct. Clients were making spending decisions from these numbers.
So I wrote reconciliation scripts that compared ClickHouse against MySQL — the same aggregations, run against both, differences surfaced for investigation. We ran them continuously for months after the cutover, not just during it.
This is why MySQL stayed. Keeping the second write path was not redundancy for its own sake. It gave us a reference implementation to check against, for as long as we wanted the reassurance. Verification was a property of the design, not a phase of the project.
Results
| Report latency | Reports that took up to 30 minutes returned in 2–3 seconds. Reports that previously could not complete at all now did. |
| Infrastructure cost | The oversized RDS instances existed to make reporting survivable. Once reporting moved, they were no longer needed. |
| New capability | Several campaign-performance features were built afterwards that had not been feasible before — not because nobody had thought of them, but because the query cost made them impossible. |
| Scope | All stats reads moved to ClickHouse. MySQL remained the system of record for everything else. |
That last row is the one I would point at. Speed was the goal, but the more interesting outcome was that a category of feature became possible. When a query costs half an hour, you do not build things that depend on it — and you stop noticing the ideas you are not having.
What I would do differently
I would have built the translation layer first. I discovered how much of the work it represented after committing to the approach. The migration was never at risk, but my estimate was, and I would now treat "what assumes the current database?" as part of the proposal rather than something to find out during it.
I would push harder for a defined end state on the dual writes. Keeping MySQL as a second source of truth was the right call and I would make it again. But "we will decide later when to stop" is not a decision, and infrastructure kept for reassurance has a way of becoming permanent.