fix(server): route reliability-rate rolling alerts through bucketed MVs and cap ClickHouse query memory

Draft. Fix #1 (bucketed reliability MV) + Fix #2 (per-query memory cap) from the ClickHouse OOM investigation. The remaining follow-up (isolating AlertEvaluationWorker onto its own low-concurrency Oban queue) is intentionally left out of this PR.

What

Two changes that together stop reliability-rate flaky-test alerts from exhausting the production ClickHouse instance’s memory:

1. Bucketed fast path for reliability_rate rolling evaluations. Adds a parallel recent_successful_runs groupArraySorted(N) aggregate to the existing test_case_runs_recent_{100,250,500,750}_per_case bucket tables (new per-bucket MVs + partition-chunked backfill of the historical 100/250 buckets, mirroring the flakiness bucket migration), and routes the reliability rolling path through the smallest sufficient bucket — reading the success column via groupArraySortedMerge(bucket) with no re-sort, exactly like flakiness/count. Windows above 750 keep the existing full-aggregate fallback.

2. Per-query max_memory_usage cap on the ClickHouseRepo read path (default 6 GiB, overridable via the clickhouse.max_memory_usage_bytes secret).

Why (root cause)

Production ClickHouse hit its (total) process memory ceiling (~18 GiB) and killed unrelated queries with Code: 241 MEMORY_LIMIT_EXCEEDED. The Sentry issues that fired (build_total_count, build_percentile_durations, flaky-run lookups) were collateral — ordinary sub-GiB queries that happened to request the next allocation once the server was already at the ceiling.

The query_log for the incident window showed the real driver: the reliability_rate rolling query, run ~13×/minute concurrently, each peaking at ~4–5 GiB:

The reliability path always read the 1000-entry test_case_runs_recent_per_case aggregate and did groupArrayLastMerge(1000)(recent_successful_runs) + arrayReverseSort over every active test case in the project (all 76 runs had no test_case_id filter — full-project scans), even for the default 100-run window. AlertEvaluationWorker runs 10-wide per pod on the shared :default Oban queue, so a handful of concurrent full-project reliability scans summed past 18 GiB. Flakiness and flaky-run-count monitors already avoid this by reading the smaller pre-sorted buckets (~0.2 GiB); reliability simply never got that fast path.

Why this solution

• Extending the existing bucket tables (rather than a new structure) reuses the proven two-MV-into-one-AggregatingMergeTree pattern the full test_case_runs_recent_per_case table already uses for recent_runs + recent_successful_runs. It turns the ~4–5 GiB query into ~0.2 GiB at the source.
• The per-query cap is defense-in-depth and independent of #1: it converts any future runaway read into a self-contained (for query) failure the caller retries, instead of a server-wide (total) kill that takes out unrelated dashboards. It’s applied only to the read repo — writes/backfills go through IngestRepo, which sets its own per-query limits.

User / developer impact

• Reliability-rate alert evaluations drop from multi-GiB to sub-GiB, removing the concurrency spike that saturated ClickHouse.
• Dashboards and analytics stop failing intermittently with MEMORY_LIMIT_EXCEEDED.
• Reliability rolling windows ≤750 now read the bucket MVs; windows >750 are unchanged (full-aggregate fallback).

Validation

• mix format + syntax checks on all changed files.
• Added reliability rolling tests: mid-size bucket (asserts runs older than the window are ignored) and above-cap fallback, alongside the existing 100-bucket happy path.
• Migration reuses the partition-chunked backfill machinery (per-project memory fallback, throttling, transient-error retry) already exercised by 20260515100000 and 20260609121000.

How to test locally

1. mix ecto.migrate (ingest repo) to create the bucket success MVs.
2. mix test test/tuist/automations/monitors/flaky_tests_monitor_test.exs — the evaluate_by_reliability_rate/1 with rolling window describe block exercises the 100-bucket, mid-bucket, and above-cap paths end-to-end against ClickHouse.

Not in this PR (follow-up)

• Moving AlertEvaluationWorker off the shared :default queue onto a low-concurrency queue, so a burst of heavy evaluations can’t run 10-wide per pod.
• Optional: a ClickHouse MemoryTracking vs max_server_memory_usage alert.

No GitHub comments yet.