The ClickHouse server was not just out of memory, it was out of time.

A client came to us with a ClickHouse server that kept running out of memory. Refreshes were dying with MEMORY_LIMIT_EXCEEDED, dashboards were showing stale attribution data, and the box was pinned. They had already tried the obvious lever, which is to give the query planner a bigger memory budget, and it had not helped.

The memory pressure was real, but it was a symptom rather than the disease. The real problem was that the server never had a spare moment: four materialized views recomputed their entire result from scratch every ten to twelve minutes, forever, whether or not anything had changed. Here is how we found that, and what happened when we replaced them with incremental batch loads.

The symptom, and why it was misleading

The server had a hard ceiling of 27.59 GiB. The obvious hypothesis was that one greedy query was blowing through it. We pulled six and a half days of query history to find the culprit and ranked every materialized view by peak memory.

The ranking did not indict anyone:

Not one of those peaks crosses 27.59 GiB. Every single view fits comfortably on its own. If you were hunting for the one query to optimize, there was not one. That is usually the point where a team reaches for a bigger instance.

The number that actually explained it

The metric that broke the case open was duty cycle: not how much memory a view peaks at, but what fraction of wall-clock time it spends running at all.

Three of the four views ran essentially all the time. They were scheduled every twelve minutes, but each refresh took longer than twelve minutes, so the next one started the moment the last finished. Between them they held 19.92 GiB continuously against a 27.59 GiB ceiling. That left about 7.7 GiB for eleven other materialized views and every user-facing query on the system.

So the OOMs were not caused by a greedy query. They were caused by a permanently occupied server. Anything that spiked, from any source, crossed the ceiling. The failures looked random because the thing that killed you was whatever happened to arrive while the floor was already at 20 GiB.

This also explained a detail that had confused everyone: the view with the worst failure rate (the session attribution view, failing 44.9% of the time) was not a meaningful contributor to the memory pressure at all. At a 12% duty cycle it barely ran. It was the most broken view, but it was not the cause. Fixing the loudest thing first would have changed nothing.

The cost nobody was measuring

Once we started measuring I/O instead of memory, the picture got worse. Over that same six-and-a-half-day window, the four views read 117.54 TiB from disk. That is a sustained ~219 MiB/s, continuously, for six and a half days, purely to recompute tables that had mostly not changed.

We got an accidental controlled experiment that proved the point. While seeding a replacement table, one of the old views was still running. We stopped it mid-seed and watched the progress rate:

• Before stopping the view: ~1.8% per minute.
• Immediately after stopping it: ~12.8% per minute.

Seven times faster, instantly, with 19.81 GiB of memory free at that moment. Memory was not the binding constraint. The views were starving everything else of disk and CPU, and every other query on the cluster was paying that tax.

The fix: stop recomputing what has not changed

A refreshable materialized view in ClickHouse is a scheduled full rebuild. Every twelve minutes it re-scanned the entire source table, re-aggregated all of history, and wrote out a result that was almost identical to the one it wrote twelve minutes earlier. Nothing about that work was proportional to how much new data had arrived.

The replacement was a set of plain scheduled scripts that only process a recent window, typically the last 24 hours, and upsert the results. We verified the premise before writing any of them: a 24-hour filter on the source table reads 41 of 8,963 granules, about 0.5% of the data, because the time predicate prunes against the table's sort order. That ~200× reduction in data read is the entire trick.

Making an upsert correct rather than merely fast is where the engine choice matters, and it differed per view:

• Most-recent-wins aggregates (the two attribution views) went to ReplacingMergeTree keyed on the aggregation key. Recomputing a recent window and upserting is trivially correct here, because a later value is supposed to win.
• First-touch aggregates (the session referrer view) could not use that. This view captures the earliest referrer and landing page per session. A recent window only sees part of a session that started before the window, and with ReplacingMergeTree that partial recomputation carries a higher version and silently overwrites the correct row. We moved it to AggregatingMergeTree instead, storing partial aggregate states. A partial recomputation then merges with what is stored rather than replacing it, so the true minimum survives. Correctness stopped depending on an unverified assumption about how long sessions last.
• One view stayed a full rebuild. The consent state view reads a source with no usable time index, so no window can prune. We left it alone deliberately.

We also tried a wrong turn worth mentioning, because it is the intuitive first idea. Our initial approach chunked each rebuild into hash buckets to bound peak memory per query. It worked, and it was slower than what it replaced: 16 minutes against the old 6, because every bucket re-scanned the whole source table. Bounding memory while multiplying I/O solved the wrong problem. That failure is what redirected us from "make the rebuild fit" to "stop rebuilding."

Rolling it out without betting the database

Every new table was built alongside the live one, never on top of it. The old views and their tables kept running untouched while the replacements filled in parallel. A comparison job diffed old against new on a settled slice of data and reported an accuracy percentage, so we could see divergence before committing to anything.

Parity came in clean:

• Visitor attribution: 99.952% match across 3.46M rows, with zero rows present in the new table but absent from the old.
• Session attribution: 78,056,221 rows matched, with only 7 keys out of 78.15M missing.
• Session referrer: 19,598,315 matched and zero missing sessions.

Cutover was then a one-line repoint per view, either flipping a database view or changing a single table reference in application code, with the old table still sitting there as an instant rollback. We did them one at a time with a soak between each.

The parity work paid for itself in an unexpected way. On the session attribution view, the new table had 86,177 rows the old view had never produced, because the old view had not successfully refreshed in over five hours. On the session referrer view, the new table had 13,032 extra rows because the old view grouped by session alone and collapsed any session spanning two sites into one arbitrary row, which the consumer's site filter would then miss. The migration surfaced two silent data bugs that predated it.

The results

These are measured over the 14 hours after cutover, compared against the identical clock hours on the previous day, so normal daily traffic patterns cannot account for the difference.

Per view, the refresh times collapsed:

215 scheduled runs in that window, zero failures, and a slowest single run of 18 seconds. The cleanest apples-to-apples measurement is the same query on the same data, run as a full rebuild and then incrementally: 2,243.6 s versus 6.4 s, 91.02 GiB read versus 282 MiB, 3.13 GiB of memory versus 86 MiB.

Duty cycle, the metric that identified the problem, is also the one that best captures the fix. The visitor attribution view went from occupying the server 99.9% of the time to roughly 0.9%.

Two things worth stealing from this

The business impact was data loss, not slowness. Because the server was permanently saturated, incoming pageview writes were failing during load spikes. Across the week before cutover that was 237 failed inserts out of 5.3 million attempts, 153 of them memory-caused. The failures were bursty rather than steady, which is the signature of resource exhaustion rather than a background error rate. That was real customer data that never landed. In the 14 hours after cutover, across 418,887 inserts, zero failed. Insert latency also dropped 36%, from 128.3 ms to 82.3 ms, because disk latency is shared and halving it makes every query faster, not just the ones we touched.

Fixing three views fixed the fourth. We never touched the consent state view, the view with the highest peak memory of all, which had been failing 133 times a week. After the other three were converted it ran 14 hours without a single failure. That is the clearest possible confirmation of the diagnosis: it was never individually broken, it was just competing for a server that had nothing left to give.

The general lesson is that peak memory is a seductive metric because it is the one in the error message. But MEMORY_LIMIT_EXCEEDED tells you who was standing there when the ceiling was hit, not who put the floor at 20 GiB. If your query history shows lots of failures and no obviously guilty query, stop ranking by peak and start ranking by how much of the time each thing is running. And before you optimize a scheduled job, ask whether it should be doing the work at all. The fastest full-table aggregation is still slower than not running one.

If your data infrastructure is fighting you, whether that is a warehouse that keeps falling over, a pipeline that costs more every month, or queries that got slow and nobody knows why, we can help you find the actual cause rather than the loudest symptom. Get in touch and tell us what is in the way.