I got a recent ClickHouse and played with it, and looked around GitHub some; replying to this dead thread to update outdated bits of the above.
In general: given a huge hash join, you can shrink it (reorder, etc. so fewer rows would need hashed), sort (take advantage of sort order for an efficient join), and/or spill it to disk. ClickHouse's improvements from 2024.12 forward have made it much better at the first of the three; it could get much further with the other two.
1) Shrinking -- "Can we make it a small join?" They've done a lot to solve this one! They'll swap table order, propagate WHERE conditions, and end up using projections, so quite often if the join can be small, it will be, without special effort on the query writer's part. To be clear, this is a huge win for many use cases, and I'm not writing more because all I can say is "great."
2) Sorting -- "Can we take advantage of an existing sort order?" Huge joins can be efficient and not run out of RAM if the data is already ordered by the join key and all you have to do is walk through part of it in order. ClickHouse doesn't automatically do this; it defaults to hashing even when the tables' natural row ordering would make sort-based joins fast. Docs suggest users consider full_sorting_merge when tables are already ordered by the join key. Perhaps a future setting could make the optimizer consider it.
Joins with coinciding ORDER BYs seem like they could occur naturally in "might-have" situations, e.g. joining pageviews to conversions on a shared key.
2a) Sorting with projections -- "Can we use sort orders if we include projections?" If rows aren't already on disk in the correct order to make a sort join fast, the ClickHouse administrator could add a projection with a different ORDER BY to one or both tables to fix that! Right now ClickHouse will pick projections to aid in filtering, but won't look for projections that allow a fast sort join.
Combining 2 and 2a, ClickHouse admins would gain a pretty big hammer to use on problem joins: create a projection or two allowing a fast sort-join, and now the former problem query is efficient with no effort on query authors' part. This is valuable when the admins are willing to understand query and execution patterns precisely, but not everyone (or everything, when some queries are generated) can write queries with optimization in mind.
Query authors can force use of a projection with the mergeTreeProjection function, and force join_algorithm = 'full_sorting_merge', but that's the user acting as query optimizer, not the desired end state.
3) Spilling -- "Will we automatically spill to disk instead of having an out-of-memory error?" Looks like this is still a "no". Docs show options to stop joins at a certain size, but I don't see a way to e.g. trigger a retry with a more spill-friendly join_algorithm when a join gets too big (or if the planner expects it will be big). There is a very interesting patch in https://github.com/ClickHouse/ClickHouse/pull/72728 which introduces the idea of query-wide RAM accounting that can trigger spill to disk, but it's only used in grace_hash as of that PR.
--
I haven't really looked at DISTINCT or aggregation's RAM use or whether they take advantage of sorts. And maybe the better path is to make transparent spill-to-disk something you can rely on then reduce how often you need to spill as an optimization. Either way, ClickHouse could get from its current state--where joins that can fit in RAM are efficient but larger ones need some hand-holding--to a state where you really can throw all sorts of queries at it and reliably get results without really having to guide it.