Aggregate DORA metrics from ClickHouse behind a flag
What does this MR do and why?
DORA metrics for large groups time out: Dora::AggregateMetricsService reads dora_daily_metrics for the environments of every project in the hierarchy. This MR is step 2 of the plan in https://gitlab.com/gitlab-org/gitlab/-/work_items/636172. Behind the new dora_metrics_clickhouse_aggregation flag (gitlab_com_derisk, off by default, checked against the root namespace), it aggregates the same dora_daily_metrics rows in ClickHouse instead, scoped by traversal_path.
Builds on !268292 (merged) (merged), which replicates dora_daily_metrics to ClickHouse through Siphon, and !266291 (merged) (merged), which added the result cache.
Analytics::DoraMetricsAggregatorchooses the source. GraphQLdoraMetrics, RESTdora/metrics, VSA and Insights all go through it, and the result cache wraps both paths.Analytics::DoraMetricsAggregator::ClickHouseQueryreads the latest version of eachsiphon_dora_daily_metricsrow, joinssiphon_environmentsfor the tier and project filters, and aggregates the rows the same wayDora::DailyMetrics.aggregate_for!does.- The
AggregateMetricsServicecache key includes the source the aggregator actually uses, so results from the two backends never share an entry.
ClickHouse is used only when all of these hold. Otherwise the request stays on PostgreSQL:
- the caller passes the container.
DoraPerformanceScoreCalculatordoesn't, so performance scores stay on PostgreSQL. - ClickHouse is enabled for analytics and the flag is on for the root namespace.
Gitlab::ClickHouse.siphon_enabled?is true forenvironmentsanddora_daily_metrics.
If the ClickHouse query fails, the error goes to error tracking and the request falls back to PostgreSQL. The checks above can't catch an outage, because siphon_enabled? caches a positive result for the life of the process.
Because ClickHouse aggregates the stored rows, the results match PostgreSQL exactly, including the dora_configurations branch filter and how incidents are stored per environment. Both are applied when PostgreSQL refreshes the rows.
Database
PostgreSQL
SELECT
date AS interval_date,
SUM(deployment_frequency) AS metric_deployment_frequency,
(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY lead_time_for_changes_in_seconds)) AS metric_lead_time_for_changes,
(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY time_to_restore_service_in_seconds)) AS metric_time_to_restore_service,
SUM(incidents_count)::float / GREATEST(SUM(deployment_frequency), 1) AS metric_change_failure_rate
FROM "dora_daily_metrics"
WHERE "dora_daily_metrics"."environment_id" IN (
SELECT "environments"."id"
FROM "environments"
WHERE "environments"."project_id" IN (
SELECT "projects"."id"
FROM "projects"
WHERE "projects"."namespace_id" IN (
SELECT "namespaces"."id"
FROM UNNEST(
COALESCE(
(SELECT ids FROM (
SELECT "namespace_descendants"."self_and_descendant_group_ids" AS ids
FROM "namespace_descendants"
WHERE "namespace_descendants"."outdated_at" IS NULL
AND "namespace_descendants"."namespace_id" = 9970
) cached_query),
(SELECT ids FROM (
SELECT ARRAY_AGG("namespaces"."id") AS ids
FROM (
SELECT namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND (traversal_ids @> ('{9970}'))
) namespaces
) consistent_query)
)
) AS namespaces(id)
)
)
AND "environments"."tier" = 0
AND "environments"."tier" IS NOT NULL
)
AND "dora_daily_metrics"."date" BETWEEN '2026-05-10' AND '2026-11-06'
GROUP BY "dora_daily_metrics"."date"
ORDER BY interval_date ASC;ClickHouse
SELECT
toDate(daily_metrics.date) AS interval_date,
sumOrNull(daily_metrics.deployment_frequency) AS deployment_frequency,
quantileExactInclusiveOrNull(0.5)(daily_metrics.lead_time_for_changes_in_seconds) AS lead_time_for_changes,
quantileExactInclusiveOrNull(0.5)(daily_metrics.time_to_restore_service_in_seconds) AS time_to_restore_service,
sumOrNull(daily_metrics.incidents_count) / greatest(ifNull(sumOrNull(daily_metrics.deployment_frequency), 1), 1) AS change_failure_rate
FROM
(
SELECT
siphon_dora_daily_metrics.id AS id,
tupleElement(argMax(tuple(siphon_dora_daily_metrics.environment_id), siphon_dora_daily_metrics._siphon_replicated_at), 1) AS environment_id,
tupleElement(argMax(tuple(siphon_dora_daily_metrics.date), siphon_dora_daily_metrics._siphon_replicated_at), 1) AS date,
tupleElement(argMax(tuple(siphon_dora_daily_metrics.deployment_frequency), siphon_dora_daily_metrics._siphon_replicated_at), 1) AS deployment_frequency,
tupleElement(argMax(tuple(siphon_dora_daily_metrics.lead_time_for_changes_in_seconds), siphon_dora_daily_metrics._siphon_replicated_at), 1) AS lead_time_for_changes_in_seconds,
tupleElement(argMax(tuple(siphon_dora_daily_metrics.time_to_restore_service_in_seconds), siphon_dora_daily_metrics._siphon_replicated_at), 1) AS time_to_restore_service_in_seconds,
tupleElement(argMax(tuple(siphon_dora_daily_metrics.incidents_count), siphon_dora_daily_metrics._siphon_replicated_at), 1) AS incidents_count
FROM siphon_dora_daily_metrics
WHERE startsWith(siphon_dora_daily_metrics.traversal_path, '1/9970/') AND (siphon_dora_daily_metrics.date >= toDate32('2026-05-10')) AND (siphon_dora_daily_metrics.date <= toDate32('2026-11-06'))
GROUP BY siphon_dora_daily_metrics.id
HAVING argMax(siphon_dora_daily_metrics._siphon_deleted, siphon_dora_daily_metrics._siphon_replicated_at) = 0
) AS daily_metrics
INNER JOIN
(
SELECT *
FROM
(
SELECT
siphon_environments.id AS id,
tupleElement(argMax(tuple(siphon_environments.project_id), siphon_environments._siphon_replicated_at), 1) AS project_id,
tupleElement(argMax(tuple(siphon_environments.tier), siphon_environments._siphon_replicated_at), 1) AS tier
FROM siphon_environments
WHERE startsWith(siphon_environments.traversal_path, '1/9970/')
GROUP BY siphon_environments.id
HAVING argMax(siphon_environments._siphon_deleted, siphon_environments._siphon_replicated_at) = 0
) AS environments
WHERE environments.tier IN (0)
) AS environments ON environments.id = daily_metrics.environment_id
GROUP BY interval_date
ORDER BY interval_date ASCHow the query matches dora_daily_metrics
- Scope: PostgreSQL filters
dora_daily_metricsby the environments of the projects in scope and their tier. The query does the same withsiphon_environments, scoped bytraversal_pathand filtered by tier andgroup_project_ids. - Range:
dateis in the ClickHouse key and never changes, so the date range is applied before the dedup, inclusive on both ends likeBETWEEN. - Aggregates:
sumOrNullfor deployment frequency and incidents,quantileExactInclusiveOrNull(0.5)for the medians, which matchesPERCENTILE_CONT(0.5), andSUM(incidents) / GREATEST(SUM(deployments), 1)for change failure rate. - Interval
all: with no data, PostgreSQL still returns one row ofNULLs, and so does the query. - Replicated rows: rows are deduplicated with
argMax(..., _siphon_replicated_at), and deleted rows are dropped. Nullable columns go throughtuple(), because plainargMaxskipsNULLvalues.
Verified on GitLab.com
- Siphon replication of
dora_daily_metricsis complete. - Query cost on gitlab-org: the ClickHouse query above ran in 592 ms and read 573,742 rows (35.19 MB), compared with the 23 M rows the earlier source-table version read.
No changelog entry: the change is behind a flag that is off by default.
References
- https://gitlab.com/gitlab-org/gitlab/-/work_items/636172
- Previous step, the result cache: !266291 (merged)
- Customer-facing timeouts: #591394
- Replication of
dora_daily_metrics: !268292 (merged)
Screenshots or screen recordings
https://gitlab.com/gitlab-org/gitlab/-/work_items/636172#note_4159743344
How to set up and validate locally
-
Make sure ClickHouse and Siphon run in your GDK, and Admin > Settings > General > Analytics > Use ClickHouse for analytics is on.
-
Run
bundle exec rake gitlab:clickhouse:migrate, thengdk reconfigureandgdk restart siphon, sodora_daily_metricsreplicates (from !268292 (merged)). -
Pick a group with DORA data, and note its numbers on Analyze > Analytics dashboards > DORA metrics.
-
In the Rails console, enable the flag for the group's root namespace:
group = Group.find_by_full_path('your-group') Feature.enable(:dora_metrics_clickhouse_aggregation, group.root_ancestor) -
Compare both sources for the same range:
params = { metrics: Dora::DailyMetrics::AVAILABLE_METRICS, interval: 'daily', start_date: 90.days.ago.to_date, end_date: Date.current, environment_tiers: %w[production] } pg = Analytics::DoraMetricsAggregator.aggregate_for(projects: group.all_projects, **params) ch = Analytics::DoraMetricsAggregator.aggregate_for(container: group, project_ids: [], projects: group.all_projects, **params) pg == ch -
Reload the dashboard. The numbers are unchanged, and
log/development.logshows nodora_daily_metricsquery.
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.