v4 backfill (M3): per-part chunking does not bound memory — full_sorting_merge sorts input/output

What happens

20260701_v4_step_3_backfill_events_full_from_observations fails with Code: 241 MEMORY_LIMIT_EXCEEDED on parts of the observations_pid_tid_sorting scratch table, within seconds of starting. Retrying does not converge, at any chunk granularity.

Why

enumerateChunks documents part-level chunking as the OOM mitigation:

One todo per part keeps each INSERT bounded to a single ClickHouse part (single-digit GBs in most cases, capped well below an entire monthly partition), which is the only granularity that's safe to assume self-hoster hardware can chew through without OOM or memory-limit failures.

That bound only helps if memory scales with chunk size, and join_algorithm = 'full_sorting_merge' is what makes it scale. The algorithm sorts both inputs unconditionally, so every observation column flows through a sort, input and output included.

The two sides of this join are structurally asymmetric:

• right — one partition of traces, deduped by LIMIT 1 BY project_id, id, metadata columns only. Size tracks trace count.
• left — the observation payload. Size tracks how much text the instrumented application logged.

So the side that is bounded gets sorted along with the side that is not.

Measurements

One production part: 15.1M rows, 42.8 GiB on disk, 172 GiB of input+output text. Query memory limit 18.63 GiB.

Same part, same statement, read-only (FORMAT Null), only join_algorithm changed:

Flat memory against rising rows is the streaming signature; 1.28 GiB leaves 14.5x headroom against the same limit that the sorting algorithm blew through. Narrowing the chunk further (down to one project) did not help, because the sort is what consumes the memory, not the chunk size.

Also worth knowing

EXPLAIN PLAN reports Sort Left before JOIN even though the scratch table is ORDER BY (project_id, trace_id, id) and the join key is that key's prefix. Reading the local table directly, max_threads = 1, and optimize_read_in_order = 1 each leave it in place.

So the scratch table's reordered sort key does not buy M3 a sort-free join — at least on 25.8, the only version I tested. It still earns its place for co-location; it just is not saving a sort.

Patch

--- a/worker/src/backgroundMigrations/backfillEventsFullFromObservations.ts
+++ b/worker/src/backgroundMigrations/backfillEventsFullFromObservations.ts
@@ -366,7 +366,19 @@
           SELECT project_id, trace_id FROM dataset_run_items_rmt
         )
       SETTINGS
-        join_algorithm = 'full_sorting_merge',
+        -- 'hash', not a sorting algorithm, because the two sides of this join
+        -- are asymmetric. The right side is one partition of traces, deduped to
+        -- one row per (project_id, id) and carrying metadata columns only, so
+        -- its size tracks trace count. The left side carries the observation
+        -- payload, `input` and `output` included, and its size tracks how much
+        -- text the instrumented application logged.
+        --
+        -- 'full_sorting_merge' sorts both inputs unconditionally, so it pulls
+        -- that payload through a sort and makes this step's memory scale with
+        -- part size -- which is what `enumerateChunks` is trying to bound by
+        -- keeping one part per todo. Hashing the small side instead lets the
+        -- large side stream, and the bound stops being load-bearing.
+        join_algorithm = 'hash',
         type_json_skip_duplicated_paths = 1
     `;

hash is what I measured. auto would be the more conservative choice if you would rather degrade than risk the right side on an install where traces is unusually large — I have not tested it.

Environment

• ClickHouse 25.8
• Self-hosted, v4 historic backfill chain M1→M5