Chart queries build a profiles FINAL CTE over the whole project (no id filter) — 9–22 GiB per chart, OOMs the ClickHouse server on dashboard refresh

从 issue 中生成的 SQL 开始，跟踪使用 profile properties 时图表查询如何构造 profiles CTE。使用提供的 EXPLAIN 或 EXPLAIN ESTIMATE 输出以及 system.query_log query，将当前读取整个项目的方式与事件窗口的 profile IDs 进行比较。完成的标准是，受影响的图表不再 materialise 每个项目 profile，并且 dashboard 刷新能够避免报告的内存故障。

Chart queries build a profiles FINAL CTE over the whole project (no id filter) — 9–22 GiB per chart, OOMs the ClickHouse server on dashboard refresh

Summary

Any chart/insight that references a profile property in a filter or breakdown compiles to a query that wraps the project's entire profiles table in a CTE:

WITH profile AS (
  SELECT id AS "profile.id", properties AS "profile.properties"
  FROM profiles FINAL
  WHERE project_id = '<project-id>'
)
SELECT ... FROM events e LEFT ANY JOIN profile ON profile.id = profile_id
WHERE profile.properties['<key>'] = '<value>' AND ...

There is no id predicate on the CTE and no correlation to the events actually being scanned, so FINAL deduplicates and materialises the properties map for every profile in the project regardless of how narrow the event window is.

On a project with ~18.4M profiles this costs 9–22.6 GiB per chart. Because a dashboard fires all of its charts in parallel on load/refresh, four or more of these land simultaneously and exhaust the server memory budget. The queries that get killed are not only the charts — the API's own small profile point-lookups die too, which takes the API down for the duration of the burst.

We see this roughly once a day, at varying times.

This is a different problem from #382. Evidence for that is in the "Not the same as #382" section below — worth reading before the two get merged.

Environment

• Self-hosted OpenPanel, updated to the current release before the measurements below were taken. API image lindesvard/openpanel-api:latest, @openpanel/api 0.0.4, digest sha256:9353647f96dcfcceecd0ba2efd4c05e77ace3ea3f5d56b2cef55c1c3b550be7c
• ClickHouse 26.2.3.2 (official build)
• max_server_memory_usage = 55.73 GiB
• No per-query max_memory_usage cap (see "Why we can't fix this operator-side" below)

Profiles table:

ENGINE = ReplacingMergeTree(created_at)
PARTITION BY toYYYYMM(created_at)
ORDER BY (project_id, id)

Total rows	~21M
Rows in the affected project	~18.4M (effectively single-tenant)
Active parts	44, across 5 monthly partitions
On disk	~933 MiB
Profiles spanning >1 monthly partition	0

A third and far more common variant — a plain profile-property equality filter over an 8-day window, with no LIKE and no breakdown — peaks at 8.7–9.0 GiB and fires 3–10×/minute near-continuously while a dashboard is open. It is the one that actually drives the outages; we captured only its memory figure, not its read counters.

Note the first row: 80.9 GiB read for 155M rows. That is the properties map being materialised, not event data.

The cost is in properties, not in FINAL's bookkeeping

A plain GROUP BY id over the same project's profiles, selecting no properties at all, already peaks at 4.04 GiB. So an argMax + GROUP BY rewrite of FINAL would not fix the memory problem on its own — the dominant cost is reading and holding 18.4M properties maps that the query will then discard for all but the handful of profiles that actually appear in the event window.

Every OOM exception in our logs points at the same column:

(while reading column properties): (while reading from part
/clickhouse-data/store/…/ in table openpanel.profiles …)

The failure mode

Correlating each OOM against what was in flight at that moment (query at the bottom of this report), across 7 days and 12 distinct OOM moments, the signature is strikingly consistent:

┌────────────oom_time─┬─kind───────────┬─in_flight─┬─mem───────┐
│ 2026-08-04 21:34:07 │ profiles_final │         4 │ 45.44 GiB │
│ 2026-08-04 21:58:53 │ profiles_final │         4 │ 46.09 GiB │
│ 2026-08-04 23:12:04 │ profiles_final │         3 │ 34.40 GiB │
│ 2026-08-04 23:25:12 │ profiles_final │         4 │ 46.74 GiB │
│ 2026-08-05 10:46:07 │ profiles_final │         4 │ 46.50 GiB │
│ 2026-08-05 16:35:02 │ profiles_final │         4 │ 45.82 GiB │
│ 2026-08-06 09:56:31 │ profiles_final │         4 │ 45.88 GiB │
└─────────────────────┴────────────────┴───────────┴───────────┘

in_flight counts only queries that finished — the query that was killed is a fifth one, by definition absent from that count. So the arithmetic of each outage is:

45.88 GiB (4 survivors)  +  11.45 GiB (the one killed)  =  57.33 GiB
                                          server cap  =  55.73 GiB

Four of these charts fit. Five do not.

Two different deaths, and the API is collateral

The exceptions come in two shapes, and the distinction matters:

1. The charts themselves — selected as victims:

Code: 241. DB::Exception: (total) memory limit exceeded: would use 55.77 GiB
(attempt to allocate chunk of 8.40 MiB), current RSS: 54.52 GiB, maximum: 55.73 GiB.
OvercommitTracker decision: Query was selected to stop by OvercommitTracker
… (while reading column properties) … (MEMORY_LIMIT_EXCEEDED)

2. The API's own point-lookups — not selected, just starved out:

Code: 241. DB::Exception: (total) memory limit exceeded: would use 55.73 GiB
(attempt to allocate chunk of 0.00 B), current RSS: 53.86 GiB, maximum: 55.73 GiB.
OvercommitTracker decision: Memory overcommit has not freed enough memory
… (MEMORY_LIMIT_EXCEEDED)

…on queries whose own memory usage was 84 KiB, 250 KiB, 11.7 MiB:

SELECT id, project_id,
       last_value(nullIf(first_name, '')) AS first_name,
       …
       last_value(properties) AS properties,
       last_value(created_at) AS created_at
FROM profiles
WHERE id = '<id>' AND project_id = '<project-id>'
GROUP BY id, project_id
ORDER BY created_at DESC
LIMIT 1

These waited out memory_usage_overcommit_max_wait_microseconds (default 5 s), never got memory because the burst runs 40–100 s, and failed. This is what users experience as "the panel is down" — the API is alive and well under its own memory limit, but the queries it depends on cannot run.

One further observation: at one OOM moment only 6.76 GiB was in flight, and a query still died. Memory is not returned to the OS promptly after a burst clears, so the window of risk is meaningfully wider than the duration of the heavy queries themselves.

Why we can't fix this operator-side

• Per-query max_memory_usage would make charts fail outright. That is a worse product than an occasional short outage, so it isn't an option for us.
• Concurrency limiting (max_concurrent_queries_for_user + queue_max_wait_ms) does fit the constraint — excess charts queue rather than fail. But there is no safe value for the limit: the same family costs anywhere from 9 to 22.6 GiB depending on window width and whether a breakdown is present. A cap tuned for the 9 GiB variant (4–5) does not protect against three 22.6 GiB ones; a cap that does (2) serialises every dashboard.
• Splitting the workloads onto separate ClickHouse users — so the API's point-lookups aren't queued behind 90-second chart queries — appears to be a precondition for any of the above. OpenPanel takes a single ClickHouse connection for everything, so there is currently no way to do this. Being able to give the API and the dashboard different credentials would help a lot on its own.

Suggested fixes

Roughly in order of how much we think they'd help:

1. Correlate the profile CTE to the events being scanned. Restrict profiles to the profile_ids that actually appear in the event window rather than to the whole project. For a one-day chart on a 6-month-old project this is a difference of several orders of magnitude in rows and, more importantly, in properties maps materialised. The table is already sorted ORDER BY (project_id, id), so a project_id = … AND id IN (…) lookup hits the primary index directly — the ingredients for this are in place.
2. Build the CTE only when the report actually references a profile property. We haven't traced the codegen closely enough to say whether it's currently unconditional — if it is, that alone would remove the cost from the majority of charts.
3. do_not_merge_across_partitions_select_final. Where profiles don't span partitions (0 of 18.4M do, in our case), this is safe and cheap. It could reasonably be a documented self-hosting setting even if it isn't made the default.
4. Avoid FINAL for property reads that tolerate it. #423 makes the same observation for a different query: older row versions can only contribute keys that genuinely existed.
5. Bound dashboard chart concurrency client-side. Even with the query cost unchanged, not firing every chart on a dashboard in one parallel burst would convert a hard failure into slower rendering.

Not the same as #382

#382 reports the Paths analytics query at 14–18 GiB. We have that query too, and on our data it costs 15.9–20.7 GiB per run — so #382 reproduces here and is a real problem independently.

But it is not what takes our API down. We checked directly whether Paths queries were in flight at each outage:

WITH
    ooms AS (
        SELECT DISTINCT event_time AS t
        FROM system.query_log
        WHERE event_date >= today() - 7
          AND type = 'ExceptionWhileProcessing'
          AND exception_code = 241
    ),
    heavy AS (
        SELECT query_start_time AS s, event_time AS e, memory_usage AS m,
               multiIf(query LIKE '%session_paths%',  'paths',
                       query LIKE '%profiles FINAL%', 'profiles_final',
                                                      'other') AS kind
        FROM system.query_log
        WHERE event_date >= today() - 7
          AND type = 'QueryFinish'
          AND memory_usage > 1073741824
    )
SELECT t AS oom_time, kind, count() AS in_flight, formatReadableSize(sum(m)) AS mem
FROM ooms, heavy
WHERE s <= t AND e >= t
GROUP BY t, kind
ORDER BY t ASC, sum(m) DESC

Across 12 OOM moments in 7 days, kind = 'paths' appears zero times. Every outage has profiles_final in flight; none has Paths.

(Caveat for anyone re-running this on their own instance: memory_usage on a QueryFinish row is that query's peak, not its consumption at the instant t, so the mem column is an upper bound. And DISTINCT on event_time matters — without it, two exceptions in the same second double-count every in-flight query.)

---

Happy to provide full system.query_log extracts, EXPLAIN / EXPLAIN ESTIMATE output, or to test a patch against this dataset.