Reduce ClickHouse merge memory and ingestion fan-out for rolling test automations

Context

Rolling test automations currently maintain five global ClickHouse aggregate tables containing the latest 100, 250, 500, 750, and 1,000 physical run rows for every project and test case. Each table has separate flaky and successful materialized views.

Every test_case_runs insert therefore executes ten materialized views and writes up to ten aggregate states, even when no automation reads most of them.

A background merge of test_case_runs_recent_750_per_case recently exhausted the production ClickHouse memory limit for roughly 13 hours. The runner autoscaling query reported in Sentry was a victim of global memory pressure, not the source.

Impact

During the incident:

• A merge of only 100 to 130 mebibytes of compressed input expanded to 8 to 9 gibibytes in memory.
• The merge failed 1,971 times across three compute replicas.
• 1,370 foreground ClickHouse queries failed while the merge kept the service near its 18-gibibyte memory limit.
• Sentry recorded 220 errors in 14 days whose event text explicitly names the 750-run table, including ingestion buffer failures and unrelated reads. Sentry query

The large merge eventually completed on one compute replica, but the table shape has not changed, so this can recur as new parts accumulate.

Root cause

The rolling tables use unpartitioned AggregatingMergeTree storage with large groupArraySorted or groupArrayLast states.

During a merge, ClickHouse has to deserialize and combine those arrays. Memory therefore scales with the number of logical keys in the merge, the retained array capacity, and the number of aggregate columns. Compressed source-part size is a poor predictor of peak memory for this data shape.

All five tables are structurally exposed because no partition boundary limits how many project and test-case keys a merge may combine. The 1,000-run table is currently the largest at 15.86 gibibytes uncompressed.

The replacement proposed here creates a fixed number of independently growing merge domains. It reduces the blast radius substantially, but it is not a mathematical upper bound on merge memory because the number of keys inside each partition can continue growing. The 10-times and 100-times load replays are therefore release gates, not optional validation.

The current implementation is in:

• Rolling window routing and query construction
• Bucket table and materialized-view creation
• Parallel reliability materialized views

ClickHouse documents that parts never merge across partitions and that every incremental materialized view executes on every source insert and creates target parts: ClickHouse guidance.

Current usage

Production currently has three enabled rolling automations:

• All three belong to one project.
• All three use a trigger window of 75.
• Only the 100-run table is read.
• Their recovery window of 500 reads raw runs and does not use the 750-run table.

The 250, 500, 750, and 1,000 tables are therefore maintained globally without serving any currently enabled rolling trigger.

There is also a separate baseline scaling problem. Established automations evaluate changed test cases in chunks of at most 2,000, but a new automation passes no identifiers and scans aggregate state for the whole project. That whole-project query already exceeds its one-gibibyte query limit against the 100-run table.

The largest relevant project currently has 140,593 test cases, which is about 71 bounded chunks.

Required rolling-window semantics

For a configured window N, the result must mean the latest N distinct logical run identifiers ordered by (ran_at, run_identifier), with the corrected flaky value and the original successful value for each run.

The run identifier is part of the ordering and deduplication key. Two distinct runs with the same timestamp must both remain in the window. A flaky correction for an existing run must update that run and must not consume an additional logical slot.

The current writer can reinsert the same historical failure more than once because test_case_runs_by_commit does not expose the latest is_flaky value. This makes physical multiplicity unbounded and means deduplicating only after groupArraySorted(capacity) cannot provide exact logical windows.

Before the new rolling tables are enabled:

• Extend the commit-scoped lookup with the latest is_flaky value and exclude failures that are already flaky.
• Make the correction operation durable and idempotent by logical run identifier, including concurrent processing and buffer retries.
• Give a run at most one original tuple and one correction tuple in rolling state.
• Add a metric for physical tuple multiplicity per logical run and alert if the maximum exceeds two.

With that invariant, a capacity of 2 * N is sufficient for exact post-capacity deduplication even when every logical run has a correction.

Proposed storage and routing

Replace the five tables and ten materialized views with two tables and two materialized views:

• Capacity 250 serves windows from 1 through 125.
• Capacity 1,000 serves windows from 126 through 500.
• New or modified rolling trigger windows above 500 are rejected until exact pre-capacity run resolution is available.
• Each tuple contains the ordering timestamp, stable run identifier, correction rank, flaky flag, and successful flag.
• Read-side deduplication groups by run identifier, selects the corrected version, then slices to the configured logical window.

This temporarily lowers the supported maximum from 1,000 to 500. No enabled production automation currently uses a value above 75. Supporting 501 through 1,000 with post-capacity deduplication would require capacity 2,000, which works against the scaling goal. That range should return only after either pre-capacity per-run resolution or a demand-gated large-window tier passes the same merge and read gates.

Use a fixed hybrid partition key:

PARTITION BY (
  cityHash64(project_id) % 128,
  cityHash64(test_case_id) % 4
)
ORDER BY (project_id, test_case_id)

This creates at most 512 partitions per table. Every project touches only four partitions, so project queries remain local while large projects are split four ways.

Using the complete current 1,000-run key distribution:

• The current largest proposed partition contains 35,328 logical keys.
• At 100 times the current customer distribution, the simulated average is 268,950 keys per partition.
• The simulated 95th percentile is 361,662.
• The simulated maximum is 390,569.
• Project-only hashing into 512 partitions produced a maximum of 656,964.

Production five-second insert windows currently contain a median of one project, a 95th percentile of two, and a maximum of four. The four-way test-case salt adds predictable part fan-out while protecting against one large tenant dominating a partition.

The 80 percent reduction applies to materialized-view executions and aggregate-state writes, from ten to two. It does not imply an 80 percent reduction in target parts:

• One project can touch four partitions in each of two target tables, for up to eight target parts per source block.
• The production 95th percentile of two projects can touch up to sixteen target parts.
• The observed maximum of four projects can touch up to thirty-two target parts.

Actual part creation may be lower when projects share hash partitions. Production-shaped replay must measure it directly.

Read measurements

The requested routes were measured in production on a fixed contiguous range of 1,000 test cases from the largest project. The query used two threads and the application’s one-gibibyte memory limit. These numbers use the current tuple shape, so they are a lower bound for the proposed tuple that also carries a stable run identifier.

Requested window	Capacity read	Database duration	Peak memory	Bytes read
75	250	364 milliseconds	159 mebibytes	3.8 mebibytes
249	250	393 milliseconds	172 mebibytes	3.8 mebibytes
250	1,000	91 milliseconds	49 mebibytes	27.9 mebibytes
500	1,000	55 milliseconds	60 mebibytes	27.9 mebibytes
750	1,000	55 milliseconds	51 mebibytes	27.9 mebibytes
1,000	1,000	58 milliseconds	49 mebibytes	27.9 mebibytes

The read trade-off is acceptable on the current state shape, but it is not yet an approval for the replacement schema. Repeat this matrix against fully populated candidate tables with the wider tuple, cold storage caches, 2,000 identifiers, and concurrent ingestion before cutover.

Exact baseline cursor and retry semantics

Baseline evaluation must use a durable attempt record in PostgreSQL rather than advancing the cursor to completion time.

1. Create or resume a baseline attempt and persist a cursor captured before identifier enumeration.
2. Enumerate project test-case identifiers and evaluate them in chunks of at most 2,000 without emitting events or actions.
3. Persist the completed triggered-identifier set on the attempt before publishing any ClickHouse events.
4. Publish baseline events in bounded batches with deterministic event identifiers derived from the attempt and test-case identifier.
5. Make event reads deduplicate by event identifier so a successful ClickHouse insert followed by a PostgreSQL failure is safe to retry.
6. Only after every event batch succeeds, atomically mark the attempt committed, mark the alert baseline established, and set the scoped cursor to the cursor captured in step 1.
7. The first incremental evaluation uses the normal flush lookback around that captured cursor, so changes that arrive while the baseline runs are evaluated.

Retry behavior depends on the durable attempt state:

• An evaluating attempt reruns chunks using the original cursor.
• A publishing attempt reuses the persisted triggered-identifier set and deterministic event identifiers.
• A committed attempt is a no-op.

This avoids skipped changes, duplicate baseline events, and a partially published baseline becoming active.

Application changes

• Group rolling alerts by window size rather than by aggregate column.
• Read flaky and successful counts in one ClickHouse query per chunk.
• Evaluate individual thresholds in Elixir.
• Deduplicate by stable run identifier, not timestamp.
• Enforce the one-original-plus-one-correction invariant before routing reads to the new tables.
• Reject unsupported windows above 500 at validation and at execution.
• Implement the durable baseline attempt workflow described above.

Immediate containment

Before the replacement tables are ready:

• Gate creation or modification of unsupported rolling windows.
• Recheck all saved production configurations, including disabled alerts.
• Disable the unused 250, 500, 750, and 1,000 materialized views.
• Disable merges on the unused old 750 table so existing parts cannot trigger another large merge.
• Keep the current 100-run table operational for the existing window-75 automations.
• Add monitoring for background merge memory, failed merges, aggregate-table part counts, physical run multiplicity, and ingestion buffer failures.

max_bytes_to_merge_at_max_space_in_pool may be used as containment, including setting it to zero on an unused table to disable merges, but it is not the durable fix. The setting limits compressed source bytes and cannot reliably bound deserialized aggregate-state memory. ClickHouse setting documentation

Migration and rollback

• Deploy idempotent flaky correction and tuple-multiplicity monitoring first.
• Deploy the durable chunked baseline workflow.
• Create the two new partitioned tables and consolidated materialized views.
• Keep the old 100-run materialized views active for rollback.
• Backfill by project and bounded test-case ranges, starting with the largest project.
• Compare the new results against a sampled raw query that deduplicates by run identifier before applying the logical window.
• Use the old tables only as a regression comparison, not as the correctness oracle.
• Switch reads behind a runtime flag.
• Soak for at least seven days, longer than the incident duration.
• Stop old materialized views only after the soak succeeds.
• Retain old tables for an additional rollback period before removing them.

Old materialized views must remain active during the rollback window. Keeping only the old tables would leave rollback data stale.

Alternatives considered

One capacity-1,000 table

A scoped 1,000-identifier query was 3.6 times slower and read 3.2 times more data than the capacity-100 table in an earlier production comparison. One tier simplifies writes but makes common small-window reads deserialize more state.

Capacity 2,000 for window 1,000

This makes post-capacity deduplication exact under the two-physical-tuples invariant, but doubles the largest retained state and directly conflicts with the merge-memory goal. It should not be maintained globally without demonstrated demand and a successful 100-times load replay.

Direct project partitioning

There are currently 498 projects in the complete rolling state, but customer growth would create tens of thousands of partitions. A fixed hash keeps the partition count fixed.

Pure test-case hashing

This protects large tenants but makes every insert and project read touch many partitions. The proposed four-way salt limits that trade-off.

Plain raw history with query-time latest runs

An exact current-state query for 1,000 identifiers exceeded one gibibyte. A query using FINAL completed but took 5.9 seconds for 1,000 identifiers, compared with 146 milliseconds for the current capacity-100 aggregate. It would approach or exceed the five-minute cadence for the largest project.

Demand-gated aggregation

Maintaining rolling state only for projects with enabled automations could reduce work substantially and may be the right way to restore windows above 500. It requires a reliable activation registry, race-free backfill, and repair logic across PostgreSQL and ClickHouse.

Acceptance criteria

• No ClickHouse memory-limit errors or test-case ingestion buffer failures for seven days.
• Peak memory for one background merge remains below 4.5 gibibytes during a 100-times load replay.
• Concurrent background merge memory remains below 9 gibibytes, leaving half the service limit for reads and ingestion.
• Actual target-part creation is measured for source blocks containing one, two, and four projects, and active parts per partition reach a steady state below ClickHouse’s insert-delay threshold.
• A 2,000-identifier rolling query remains below 512 mebibytes for every supported route using fully populated candidate states.
• The largest baseline completes within two minutes and below its five-minute evaluation cadence.
• A change written during baseline evaluation is observed by the first incremental evaluation.
• Retrying after any baseline chunk or event batch produces one committed baseline and no duplicate visible event.
• Exact boundary tests cover windows 125, 126, 249, 250, and 500 with every run corrected once.
• Validation and execution reject windows 501, 999, and 1,000 until an exact larger-window design is enabled.
• Two distinct run identifiers sharing the same timestamp remain distinct and are ordered deterministically.
• Sampled comparisons against a raw query that resolves the latest row per run identifier return identical triggered identifiers.
• Load tests cover 10 times and 100 times the current customer distribution, 10 times the largest tenant, corrections, retries, out-of-order arrivals, and fully populated aggregate states.

Related work

• #11978 fixes test-report ingestion reverting muted test-case state.
• #11979 removes legacy test-case state-column writes.

Neither pull request changes these ClickHouse tables, materialized views, partitioning, merge behavior, rolling correction multiplicity, or baseline query shape.

F

fortmarek Jul 23, 2026

Overall, I like the proposal. It materially reduces the blast radius and ingestion work. I’d approve the direction, but I’d want the exactness issue, baseline cursor semantics, and partition part fan-out nailed down before implementation.

Area	Current implementation	Proposed implementation
Storage capacities	100, 250, 500, 750, 1,000	250 and 1,000
Partitioning	Unpartitioned; any merge can span all keys	Fixed 128 project hashes × 4 test-case salts
Aggregate shape	Separate flaky and successful arrays	One tuple containing timestamp and both flags
Materialized views	Two per capacity: 10 total	One per capacity: 2 total
Routing	1–99→100, 100–249→250, 250–499→500, 500–749→750, 750+→1000	<250→250, ≥250→1000
Alert query grouping	By window and aggregate column	By window only
New baseline	One whole-project query	Enumerate IDs and process chunks of ≤2,000
Rollout	Current tables directly serve reads	Backfill, shadow-read, runtime switch, soak, rollback

What I particularly like:

• Partitioning attacks the actual failure mode. A background merge no longer combines every project/test-case key in the table.
• Ten incremental MV executions becoming two is a large reduction in aggregation and serialization work.
• The combined tuple keeps flaky and successful measurements aligned and allows one query to serve reliability, flakiness rate, and flaky count.
• The migration plan is unusually solid: bounded backfill, shadow comparison, feature flag, old MV kept current for rollback, and a meaningful soak period.
• Chunking baseline evaluation fixes an independent failure mode: currently new alerts scan the entire project, while only established alerts use bounded ranges.

The main things I would challenge are:

1. “Bounded” is slightly overstated

The number of partitions is bounded, but the keys inside each partition continue growing. This changes an unbounded global merge into 512 independently growing merge domains—which is much better—but it is not a hard memory bound. The 10×/100× testing and merge-memory acceptance criteria are therefore essential.

2. The part-count reduction may be much smaller than 80%

Aggregate writes/MV executions go from ten to two. But one project can touch four partitions in each target table, so one source insert can still create roughly eight target partition-parts. At the production p95 of two projects, it could create up to sixteen, depending on hash distribution.

That does not invalidate the design—the states are smaller and merges isolated—but I would avoid implying target parts also drop 80%. The existing “active parts per partition reaches steady state” acceptance criterion is the right test.

3. Some reads become more expensive

A window of 75 currently reads capacity 100; the proposal reads 250. A window of 250 currently reads 500; the proposal reads 1,000. That is an intentional write/read trade-off, and likely correct because ingestion is continuous while only three automations currently read the data. Still, I would benchmark the important routes explicitly:

• 75 from capacity 250
• 249 from capacity 250
• 250 and 500 from capacity 1,000
• 750/1,000 from capacity 1,000

4. The chunked baseline needs cursor and retry semantics

Currently establishing the baseline also sets the scoped cursor to “now.” If a chunked baseline takes two minutes, a test evaluated in the first chunk could change during the remaining work; setting the cursor to completion time would skip that change.

I would capture a cursor before baseline enumeration and use that as the first incremental cursor. Partial chunks must also be idempotent so retrying after chunk 60 of 71 does not duplicate baseline events.

5. Exact rolling-window semantics remain the largest semantic gap

The configured window value can remain exact at query time by slicing the capacity state to N. But unless deduplication happens before capacity truncation, the result remains “last N subject to retained physical tuples,” not rigorously “last N logical runs.”

For capacity C and requested window N, only C−N redundant entries can be tolerated. For example:

Window N	Capacity C	Redundant entries tolerated
75	250	175
249	250	1
999	1,000	1
1,000	1,000	0

The combined tuple correctly solves alignment, but merely including a run ID while still deduplicating after groupArraySorted(C) would not solve slot loss. Exact semantics require per-run correction resolution before the capacity bound.

I would add boundary acceptance tests for windows 249, 250, 999, and 1,000 with more than C−N reinserts, plus two distinct run IDs sharing the same timestamp. Shadow-reading against the old tables is insufficient as the sole correctness oracle because the old implementation has the same approximation; a sampled raw, deduplicated query should be used as the oracle.

Verdict: strong operational proposal and significantly better than the current implementation. I’d proceed with it, but treat exact per-run semantics and baseline cursor handling as required design work, while part amplification and larger read buckets should be validated by the planned load tests.

P

pepicrft Jul 23, 2026

Thanks, I updated the issue to make these constraints explicit.

The main correction is that the current writer can reinsert the same historical failure repeatedly because the commit-scoped lookup does not expose the latest is_flaky value. The revised design now requires an idempotent correction operation and a strict maximum of one original tuple plus one correction tuple per logical run before cutover. With that invariant, the two capacities route exact windows as 1...125 -> 250 and 126...500 -> 1,000. New or modified windows above 500 are rejected rather than pretending that a capacity of 1,000 can return 1,000 logical runs. Restoring the larger range requires pre-capacity run resolution or a demand-gated tier.

I also changed the correctness oracle. The old tables remain useful for regression comparison, but sampled raw queries that resolve the latest row per run identifier before applying the window are now required. The boundary cases cover 125, 126, 249, 250, and 500 with every run corrected once, rejection of 501, 999, and 1,000, plus distinct run identifiers sharing a timestamp.

The baseline section now specifies a durable PostgreSQL attempt with a cursor captured before enumeration. Chunk evaluation does not publish events. The completed triggered set is persisted first, event identifiers are deterministic, and only a fully published attempt can establish the baseline and advance to the captured cursor. Retries resume from the durable attempt state.

I corrected the write claim as well. The 80 percent reduction applies to materialized-view executions and aggregate-state writes, not target parts. The issue now records upper bounds of 8 target parts for one project, 16 at the production 95th percentile of two projects, and 32 at the observed maximum of four projects.

Finally, I ran the requested production read matrix over a fixed 1,000-test-case range. Database execution ranged from 55 to 393 milliseconds and peak memory from 49 to 172 mebibytes with the current tuple shape. I called these a lower bound and added a release gate to repeat the matrix against fully populated candidate tables with the wider run-identifier tuple, cold caches, 2,000 identifiers, and concurrent ingestion.

I also removed the implication of a hard bound. The partition count is fixed, but keys per partition continue growing, so the 10-times and 100-times replay remains a required release gate.

P

pepicrft Jul 26, 2026

Implementation and rollout update

The performance work is now represented as one ordered stack:

1. #12070 constrains rolling automation triggers.
2. #12071 retires unused rolling aggregates after stopping future writes safely.
3. #12082 bounds stale test-run resolution.
4. #12083 bounds command-event reads while preserving exact cache-hit calculations and ordering.
5. #12084 bounds test-case-state filtering while retaining the correctness fallback.
6. #12085 evaluates automation in resumable 15-minute windows and only advances after successful evaluation.
7. #12086 adds an identifier-ordered test-case-run project lookup. Its materialized view is created before the partition-wise backfill so concurrent writes are captured, and reruns are idempotent.
8. #12087 bounds modern test attachment retention and introduces the global legacy sweep in dry-run mode.
9. #12088 enables legacy object deletion. This is the final production-gated activation step.

#11977, #11978, and #11979 are already merged. They fixed the muted-test-case correctness race and removed the legacy state writes. The stack above addresses the independent scaling failures exposed by the same workload.

Correctness safeguards

The implementation was tested specifically against feature regressions:

• Modern and legacy attachments are separated by the nullable test_run_id relationship, preserving both storage-key formats.
• Retention uses an inclusive lower time boundary and exclusive upper time boundary, with tests at the exact microsecond boundary.
• Page cursors follow each ClickHouse table’s sorting key.
• Object deletion finishes before cursor persistence.
• Cursor writes lock and compare the expected persisted state, so a late retry cannot move progress backward.
• Missing ClickHouse ownership stops advancement. A PostgreSQL project that was already deleted is reported and skipped so it cannot wedge the global sweep forever.
• Account ownership is resolved before deletion and every account’s object keys are deleted with that account.
• Tests cover retained versus expired rows, cross-account isolation, failed deletion, mid-band continuation, completed-band restart, stale retries, missing ownership, deleted projects, dry runs, disabled self-hosted retention, and exact-multiple pagination.

The retention suite passes 33 tests against the repository-pinned ClickHouse version. The lookup migration integration test passes backfill, concurrent live ingestion, and rerun cases. An independent Claude Opus review returned SHIP after its original two blockers were addressed.

Production gate for #12088

Do not merge #12088 until #12086 and #12087 have fully deployed and one complete dry run confirms:

• zero missing test-case-run ownership lookups;
• deleted-project counts are understood;
• page-level rows and bytes are bounded;
• resolved object and account counts are plausible;
• the sweep reaches the end of its frozen time band without repeated failures.

A dry-run job already in progress retains dry_run: true. Only a newly scheduled job after #12088 deploys can delete legacy objects. Rolling back #12088 stops future destructive sweeps, but objects already deleted from storage are not recoverable.

ClickHouse and Tinybird design references

The approach follows the published guidance to filter early, select only required columns, align pagination and filtering with sorting keys, and avoid repeatedly building large joins:

• ClickHouse primary-key guidance
• ClickHouse guidance on joins and denormalization
• ClickHouse query optimization guide
• ClickHouse projections and secondary indices
• Tinybird rules for faster Structured Query Language queries
• Tinybird materialized-view guidance

The lookup table in #12086 is intentionally narrow. It pays one write-time materialization cost to prevent thousands of repeated broad reads during the legacy sweep. Other queries were fixed with bounded predicates and existing sort keys instead of adding more materialized state.