Add queueing history projection to siphon_p_ci_builds

Narendranrequested to merge
narendran-kannan/607847/siphon-builds-queueing-projection into master

What does this MR do and why?

This MR adds the projection by_started_hour_runner_id to siphon_p_ci_builds in a ClickHouse post-deployment migration. It is inert until a follow-up MR switches Ci::Runners::CollectQueueingHistoryService to read Siphon, behind a feature flag. There are no application code changes.

Why

Ci::Runners::CollectQueueingHistoryService backs the wait time charts on the admin and group runner fleet dashboards. It reads tables fed by materialized views on the legacy ci_finished_builds table. Those views cannot move to Siphon: Siphon writes a new row version for every Postgres update, and quantileState aggregates cannot be deduplicated afterwards. The replacement aggregates at query time over siphon_p_ci_builds instead.

The existing by_traversal_path_started_at projection has no queued_at or runner_id, so that query would scan the whole table.

Why this sort key

Both dashboards filter by a set of runners: instance runners on the admin chart, the group's own runners on the group chart. Hour first, then runner_id, lets ClickHouse skip other runners' rows inside each hour. started_at third prunes partial hours at the window edges.

Granules read compared with (started_at, id, partition_id), the key first proposed in the issue, on an unaligned 3-hour window (08:17–11:20):

Runner filter This key (started_at, id, partition_id)
Group runner set 36 133
Instance runner set 110 133
None (API only; the UI always filters) 170 133

Readers must also filter on toStartOfHour(started_at). Verified on ClickHouse 26.3: when a bare started_at column is also in the key, a started_at range predicate binds to that later key column and the hour prefix is never derived, so nothing prunes (18,955/24,330 granules read without it). Nullable is not the cause: a non-Nullable DateTime64 and a DateTime behave the same. The follow-up finder will emit the hour predicate, and its spec will pin it.

Rejected alternative: ORDER BY (toStartOfHour(started_at), runner_id, id, partition_id). ClickHouse then derives the hour prefix itself. But at production-like density (50k rows per runner-hour, synthetic) on the unaligned window, it read 103 vs 80 granules for one runner and 490 vs 383 with no runner filter (about +28%). The UI default window (last 3 hours ending now) is almost never hour-aligned. A toStartOfFiveMinutes(started_at) third column behaved like the alternative (103/490).

This key has no traversal_path, so the follow-up will scope the group chart by the group's own runners, as the legacy query does today.

Query plans

Setup:

  • Local ClickHouse 26.3. Isolated database built by running all ClickHouse migrations from scratch on this branch, so it has exactly the post-deploy schema: only by_traversal_path_started_at and the new projection.
  • Synthetic data: 30M builds over 60 days = 49.8M siphon_p_ci_builds rows, and 263k siphon_ci_runners rows. Multiple CDC versions per build are kept unmerged for the last 2 days (about 4 versions per build in the window). The 3-hour window 09:00–12:05 holds 62.5k builds.
  • Before: optimize_use_projections = 0. This matches production today, because by_traversal_path_started_at cannot serve these queries (verified: on the current schema ClickHouse reads the base table).
  • After: default settings. ClickHouse picked by_started_hour_runner_id by itself.
  • 5 warm runs each, use_query_condition_cache = 0. Results were byte-identical before and after for every query.
Query Variant p50 Rows read Granules Peak memory
Instance (admin dashboard, instance runners) before 529 ms 50.07M 24,499 88.6 MiB
Instance after 66 ms 567k 278 56.9 MiB
Group (owner's runners, ids from Rails) before 152 ms 49.81M 24,370 14.8 MiB
Group after 63 ms 222k 109 12.7 MiB
Group (owner's runners via siphon_ci_runner_namespaces) before 448 ms 49.81M 24,372 15.4 MiB
Group (via siphon_ci_runner_namespaces) after 37 ms 225k 111 13.0 MiB

Instance rows and granules include a full read of siphon_ci_runners (263k rows, 129 granules) for the runner_type semi-join.

Instance query (planned shape; the follow-up finder will generate it with placeholders) 
WITH finished_builds AS (
    SELECT
        id, partition_id,
        any(toStartOfInterval(siphon_p_ci_builds.started_at, INTERVAL 5 MINUTE)) AS time,
        any(if(siphon_p_ci_builds.queued_at IS NOT NULL AND siphon_p_ci_builds.started_at > siphon_p_ci_builds.queued_at,
               age('ms', siphon_p_ci_builds.queued_at, siphon_p_ci_builds.started_at), 0)) AS queueing_ms,
        argMax(siphon_p_ci_builds.status IN ('success', 'failed'), _siphon_replicated_at) AS is_reported_status,
        argMax(siphon_p_ci_builds._siphon_deleted, _siphon_replicated_at) AS is_deleted
    FROM siphon_p_ci_builds
    WHERE siphon_p_ci_builds.started_at >= toDateTime64('2026-10-05 09:00:00', 6, 'UTC')
      AND siphon_p_ci_builds.started_at < toDateTime64('2026-10-05 12:05:00', 6, 'UTC')
      AND toStartOfHour(siphon_p_ci_builds.started_at) BETWEEN toDateTime('2026-10-05 09:00:00', 'UTC') AND toDateTime('2026-10-05 12:00:00', 'UTC')
      AND siphon_p_ci_builds.type = 'Ci::Build'
      AND siphon_p_ci_builds.runner_id IN (SELECT id FROM siphon_ci_runners WHERE runner_type = 1)
    GROUP BY id, partition_id
)
SELECT time, count() AS n, quantiles(0.5, 0.75, 0.9, 0.99)(queueing_ms) AS q
FROM finished_builds
WHERE is_deleted = 0 AND is_reported_status
GROUP BY time
ORDER BY time

The group query is the same, with the runner predicate replaced by AND siphon_p_ci_builds.runner_id IN (1001, 1002, ..., 1039) (the 39 ids that Ci::Runner.belonging_to_group(group.id) would return), or by AND siphon_p_ci_builds.runner_id IN (SELECT runner_id FROM siphon_ci_runner_namespaces WHERE traversal_path = '1/9970/' AND namespace_id = 9970).

EXPLAIN indexes = 1, projections = 1 output

Instance, before:

ReadFromMergeTree (siphon_p_ci_builds)
Indexes:
  PrimaryKey
    Condition: true
    Parts: 79/79
    Granules: 24370/24370

Instance, after:

ReadFromMergeTree (by_started_hour_runner_id)
Indexes:
  PrimaryKey
    Keys:
      toStartOfHour(started_at)
      runner_id
      started_at
    Condition: and((toStartOfHour(started_at) in (-Inf, 1791201600]), and((toStartOfHour(started_at) in [1791190800, +Inf)), and((started_at in (-Inf, '1791201900')), and((started_at in ['1791190800', +Inf)), (runner_id in 50-element set)))))
    Parts: 64/79
    Granules: 149/24370
    Search Algorithm: generic exclusion search

Group (ids from Rails), before:

ReadFromMergeTree (siphon_p_ci_builds)
Indexes:
  PrimaryKey
    Condition: true
    Parts: 79/79
    Granules: 24370/24370

Group, after:

ReadFromMergeTree (by_started_hour_runner_id)
Indexes:
  PrimaryKey
    Keys:
      toStartOfHour(started_at)
      runner_id
      started_at
    Condition: and((toStartOfHour(started_at) in (-Inf, 1791201600]), and((toStartOfHour(started_at) in [1791190800, +Inf)), and((started_at in (-Inf, '1791201900')), and((started_at in ['1791190800', +Inf)), (runner_id in 39-element set)))))
    Parts: 64/79
    Granules: 109/24370
    Search Algorithm: generic exclusion search

Scale caveat: production siphon_p_ci_builds is roughly 280x this table (about 6.76M granules in a production EXPLAIN). Base-table scans grow with table size. Projection reads grow with the number of builds in the 3-hour window. We will measure that on production after this migration finishes and before enabling the follow-up flag.

Database changes

ClickHouse only. No PostgreSQL changes.

  • New post-deployment migration db/click_house/post_migrate/main/20261005101356_add_started_hour_runner_id_projection_to_siphon_p_ci_builds.rb, plus the db/click_house/main.sql dump hunk and the schema version marker db/click_house/schema_migrations/main/20261005101356.
  • The projection stores 9 columns, listed explicitly: id, partition_id, started_at, queued_at, runner_id, status, type, _siphon_replicated_at, _siphon_deleted. SELECT * breaks projection rebuilds when columns are added later, see !225458 (merged).
  • up adds the projection and runs MATERIALIZE PROJECTION ... SETTINGS mutations_sync = 0 to backfill existing parts asynchronously. down runs DROP PROJECTION IF EXISTS ... SETTINGS mutations_sync = 0.
  • Same pattern as the earlier by_traversal_path_started_at projection on this table (migration 20260612111824) and !244535 (merged) (siphon_p_ci_stages).

Storage (local benchmark, compressed): the new projection is 1.07 GiB, the existing by_traversal_path_started_at is 1.62 GiB (about 0.66x), and the base table is 2.79 GiB. Production estimate: about 0.66x the production size of by_traversal_path_started_at, to confirm with system.projection_parts.

Merge cost, for ClickHouse and database reviewers: the table sets deduplicate_merge_projection_mode = 'rebuild', so every merge now also rebuilds this projection (two projections instead of one). Please weigh this cost.

Post-deploy check: the MATERIALIZE runs asynchronously. Confirm completion with:

SELECT command, is_done, latest_fail_reason FROM system.mutations WHERE table = 'siphon_p_ci_builds' AND command LIKE '%by_started_hour_runner_id%'

References

Screenshots or screen recordings

Not applicable. No UI change.

How to set up and validate locally

  1. Start ClickHouse:

    gdk start clickhouse

  2. Run the ClickHouse migrations:

    bundle exec rake gitlab:clickhouse:migrate

  3. Check the projection. The output shows by_started_hour_runner_id.

    clickhouse client --port 9001 --query "SELECT name, sorting_key FROM system.projections WHERE database = 'gitlab_clickhouse_development' AND table = 'siphon_p_ci_builds'"

  4. Roll back and re-run. rollback:main reverts the most recently applied ClickHouse migration, which is this one unless something newer is applied. Passing VERSION=20261005101356 would keep this migration, because the rollback target itself is not reverted.

    bundle exec rake gitlab:clickhouse:rollback:main
bundle exec rake gitlab:clickhouse:migrate

  5. Optional: run EXPLAIN indexes = 1, projections = 1 on the instance query above against your dev database and look for ReadFromMergeTree (by_started_hour_runner_id).

Author validation: I ran every ClickHouse migration from scratch, rolled back, and migrated again; after rollback db/click_house/main.sql was byte-identical to master, and the unrelated ENGINE = Null quoting hunks from local ClickHouse 26.3 were not committed.

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Merge request reports