A teammate ran a report one afternoon. A few minutes later, our ClickHouse server restarted itself.

One query did it. And the tables weren’t even that big — 6 million and 20 million rows, the kind of thing ClickHouse normally eats for breakfast.

That’s the part that surprised people on the team, so it’s worth explaining: the problem was never the row count. It was how much data we forced into memory before filtering any of it.

What actually happened

The query joined the two tables with no filtering up front, and threw in a couple of extra joins we didn’t even need for the final result. Roughly:

-- BEFORE: the query that restarted the server
SELECT c.segment, sum(s.amount) AS revenue
FROM customers AS c
JOIN sales    AS s ON s.customer_id = c.customer_id   -- 20M-row table on the RIGHT
JOIN products AS p ON p.product_id  = s.product_id    -- not needed for this result
WHERE s.sale_date >= today() - 30
  AND c.city = 'Hanoi'
GROUP BY c.segment

Here’s the trap most people don’t know about:

In ClickHouse, the right-hand table of a JOIN is loaded into memory to build a hash table.

So when you put a big, unfiltered table on the right, you’re asking ClickHouse to hold millions of rows in RAM before it does anything useful. The WHERE clause that would have shrunk everything down runs too late to save you. Pile an unnecessary products join on top of that, and memory keeps climbing until the OS OOM-killer steps in and restarts clickhouse-server.

It was never “20 million rows is a lot.” ClickHouse handles billions. It was “we loaded 20 million unfiltered rows into memory for no reason.”

The fix

Three small changes, no new hardware, no config tuning:

-- AFTER: filter first, then join
SELECT c.segment, sum(s.amount) AS revenue
FROM
(
    SELECT customer_id, amount
    FROM sales
    WHERE sale_date >= today() - 30      -- shrink the big table FIRST
) AS s
INNER JOIN
(
    SELECT customer_id, segment
    FROM customers
    WHERE city = 'Hanoi'                 -- shrink the small table too
) AS c USING (customer_id)
GROUP BY c.segment

What changed:

1. Filter early, in subqueries. sales drops from 20M rows to whatever the last 30 days actually is — often a small fraction. Don’t wait for the optimizer to do this for you; do it explicitly.
2. Select only the columns you need before the join. A narrower table means a smaller hash table in memory.
3. Put the smaller (filtered) table on the right, since the right side is what lands in RAM.
4. Drop the joins you don’t need. The products join added memory and bought us nothing for this result.

The report came back in seconds, and — more to the point — it stopped taking the server down with it.

The sneakier cousin: the join you’re not even using

The story above is about when you filter. There’s a second version of the same mistake that’s even easier to miss: joining a table whose columns you never actually use.

I recently cleaned up a reporting query shaped like this (simplified):

-- BEFORE: a dead join, running on every page load
SELECT
    p.category_id,
    sum(s.quantity)           AS units,
    sum(s.quantity * p.price) AS revenue
FROM sales AS s FINAL
INNER JOIN products  AS p   FINAL ON p.variant_id = s.variant_id
LEFT  JOIN suppliers AS sup FINAL ON sup.id = p.supplier_id   -- never referenced anywhere
WHERE s.branch_id IN (1, 2, 3)
GROUP BY p.category_id

Look at sup. It isn’t in the SELECT, the WHERE, or the GROUP BY. The result is byte-for-byte identical whether that join is there or not — it’s pure dead weight. And it’s FINAL, which in ClickHouse forces a merge of the table’s parts at query time. So we were paying to merge and join a whole table just to throw the result away — on every single request.

That query backs a listing page. It returns twenty rows.

The fix is the delete key:

-- AFTER
SELECT
    p.category_id,
    sum(s.quantity)           AS units,
    sum(s.quantity * p.price) AS revenue
FROM sales AS s FINAL
INNER JOIN products AS p FINAL ON p.variant_id = s.variant_id
WHERE s.branch_id IN (1, 2, 3)
GROUP BY p.category_id

Deleting two lines did this:

	Before	After
Query time	19,620 ms	180 ms	109x faster
Peak memory	2.70 GiB	113 MiB	24x less
Rows read	59.8 M	26.3 M	2.3x fewer

(average of alternating A/B runs on the same cluster, measured from system.query_log — method at the end of this post)

Twenty seconds to a fifth of a second, from deleting code. No index, no schema change, no bigger box.

Two lessons fall out of this:

• Audit every join against the columns you output. If a joined table shows up in neither SELECT, WHERE, GROUP BY nor HAVING, it’s dead — delete it. A FINAL join you don’t use is double waste: you pay to merge parts and to build a hash table, then throw the result away.
• Only join what a given request needs. A join that exists to support an optional filter shouldn’t run when that filter is empty. Make it conditional when you build the SQL:

FROM sales AS s FINAL
INNER JOIN products AS p FINAL ON p.variant_id = s.variant_id
{{ statusFilter ? "LEFT JOIN product_status ps ON ps.product_id = p.id AND ps.branch_id = s.branch_id" : "" }}

That one is easy to get wrong, because the join is needed — just not every time. On a page that re-fires the query on every filter change, most requests were paying for a join that only a minority of them used.

The rule of thumb

Shrink first, join second. In ClickHouse, the cheapest work is the work you never do.

A quick checklist I now run through whenever a JOIN gets heavy:

• Is each table filtered down before it reaches the JOIN?
• Am I selecting only the columns I actually use?
• Is the smaller table on the right (the side that goes into memory)?
• Is every join necessary for the final result?

For joins that are genuinely too large to fit in memory even after filtering, it’s worth looking at join_algorithm = 'grace_hash' or 'partial_merge' — but honestly, most of the time, filtering first is the whole fix.

How I measured this

Don’t time queries with a stopwatch, and don’t trust the number your client prints — that includes network and formatting. ClickHouse already records the truth in system.query_log:

SELECT
    query_duration_ms,
    formatReadableSize(memory_usage)  AS peak_mem,
    formatReadableQuantity(read_rows) AS rows_read
FROM system.query_log
WHERE query_id = 'my-tagged-query' AND type = 'QueryFinish'

Two things worth copying from how I ran it:

1. Alternate the variants — run before, after, before, after — instead of all the “before” runs then all the “after” runs. Otherwise the page cache quietly hands the second variant a win it didn’t earn.
2. Diff the result sets first. If the two versions don’t return byte-identical rows, you didn’t optimise a query — you changed one, and the speedup is meaningless. This is especially worth checking when you remove a LEFT JOIN: if that join ever matched more than one row, it was quietly multiplying your aggregates, and deleting it doesn’t just make the query faster, it changes the numbers.

---

These are the patterns I reach for constantly while tuning ClickHouse for retail reporting. I’m collecting the ones that keep coming up into a short, practical cookbook. If unfiltered joins have ever bitten you, I’d like to hear your war story.