Loading
Add pipeline_id projection to siphon_p_ci_stages
What does this MR do and why?
Adds a by_traversal_path_pipeline_id projection to siphon_p_ci_stages, ordered (traversal_path, pipeline_id, id, partition_id), so a pipeline_id IN (...) predicate can prune granules when resolving stage names for CI job analytics.
The base table is ordered (traversal_path, id, partition_id), so the job analytics stage_name join currently scans the container's entire stage history, ~15M of the ~17.2M rows a 7-day query reads on gitlab-org/gitlab (measured after !243923 (merged)).
A pipeline_id filter without this projection cannot prune: measured on production, granules stayed ~7,5xx and the query regressed.
With the projection (validated with a test projection on production ClickHouse, 90-day window):
| Metric | Before | After |
|---|---|---|
| Stages granules | 7,514 / 2.66M | 780 / 2.67M (~10×) |
| Total rows_read | 36.8M | 23.1M (−37%) |
Local prod-scale benchmark (25M rows): 72/12,208 granules, ~68× fewer stages rows read, identical results (row count + content hash) vs the unpruned path.
The consuming finder change is in the follow-up MR !244536 (merged); the projection is inert until that lands. Resolves the projection half of #605208 (closed).
Migration follows the by_traversal_path_started_at precedent (!in siphon_p_ci_builds): post-deployment, explicit column list (see !225458 (merged)), MATERIALIZE ... SETTINGS mutations_sync = 0 backfill, DROP IF EXISTS down.
References
- Issue: #605208 (closed) (design, measurements, EXPLAIN plans)
- Feature issue: #601321 (closed)
- Follow-up (finder change): !244536 (merged)
Database changes
ClickHouse only. New projection on siphon_p_ci_stages (7 narrow columns) + MATERIALIZE PROJECTION backfill mutation.
Storage cost: roughly the size of the 7 projected columns across the table (local benchmark showed ~2× table disk for a same-column-set table; production siphon_p_ci_stages is comparatively small).
The table already sets deduplicate_merge_projection_mode = 'rebuild'.
How to set up and validate locally
-
gdk start clickhouse -
bundle exec rake gitlab:clickhouse:migrate -
Verify the projection exists:
clickhouse client --port 9001 --query \ "SHOW CREATE TABLE gitlab_clickhouse_development.siphon_p_ci_stages" | grep by_traversal_path_pipeline_id -
Rollback and re-run to verify both directions:
bundle exec rake gitlab:clickhouse:rollback:main VERSION=20260708074154 bundle exec rake gitlab:clickhouse:migrate
Both directions validated locally (migrate -> rollback -> migrate).
MR acceptance checklist
Evaluate this MR against the MR acceptance checklist.
Edited by Narendran