Replicate dora_daily_metrics to ClickHouse with Siphon
What does this MR do and why?
Replicates dora_daily_metrics to ClickHouse through Siphon, as agreed in !266754 (comment 4161783223). DORA metrics for large groups time out when PostgreSQL aggregates dora_daily_metrics across every project in the hierarchy. With the table in ClickHouse, the next change to !266754 (merged) can aggregate the same stored rows there, so the numbers match PostgreSQL exactly, instead of rebuilding them from the source tables.
This MR only adds the replication. Nothing reads the table yet.
siphon_dora_daily_metrics: the replicated table, generated withrails generate gitlab:click_house:siphon dora_daily_metrics --with-traversal-path.traversal_pathis derived fromproject_id, the table's sharding key.- The primary key is
(traversal_path, date, environment_id, id): rows are aggregates, andenvironment_idanddatenever change after a row is created. siphon_dora_daily_metrics_pg_pkey_orderedand its materialized view: the lookup table Siphon uses to find a row's full ClickHouse key from its PostgreSQLid. It carriesdateandenvironment_idas well astraversal_path, so updates and deletes land on the right key.db/siphon/tables/dora_daily_metrics.yml:dedup_by: id, the lookup table, and areconcilejob fortraversal_pathfromproject_id.
Key order and the alternative
idhas to be last: Siphon requires the ClickHouse key to end with the PostgreSQL primary key (spec/support/matchers/siphon.rb), even though(environment_id, date)is already unique in PostgreSQL.(traversal_path, environment_id, date, id)would match PostgreSQL's unique index(environment_id, date)and skip non-production environments, such as review apps, without reading them, because DORA queries always filter by tier.datesecond instead favours date ranges. At this table's size the difference is small: a 180-day query for gitlab-org reads environments × 180 rows. I kept the order suggested in the thread.
Backfill
dora_daily_metrics has about 150 million rows on GitLab.com. Siphon backfills them automatically after the table is added. #f_siphon should know before this merges, so the snapshot doesn't surprise anyone.
No changelog entry: nothing user-facing changes.
References
- https://gitlab.com/gitlab-org/gitlab/-/work_items/636172
- !266754 (merged) (will read the new table)
- Discussion: !266754 (comment 4161783223)
Screenshots or screen recordings
Not applicable: no UI change.
How to set up and validate locally
- Make sure GDK runs ClickHouse, NATS and Siphon (
gdk config get siphon.enabledistrue). - Run the ClickHouse migrations:
bundle exec rake gitlab:clickhouse:migrate - Reconfigure GDK so Siphon picks up the new table, then restart Siphon:
gdk reconfigure gdk restart siphon - In the Rails console, create a row for a project that has a production environment:
environment = Environment.find_by(tier: :production) metric = Dora::DailyMetrics.create!(environment: environment, project_id: environment.project_id, date: Date.current, deployment_frequency: 2, incidents_count: 0) - Within a few seconds the row is in ClickHouse, with its
traversal_path:ClickHouse::Client.select('SELECT id, traversal_path, date, environment_id, deployment_frequency FROM siphon_dora_daily_metrics', :main) - Update and delete the row (
metric.update!(deployment_frequency: 5), thenmetric.destroy!). Each change adds a newer version under the same key, and the delete adds a version with_siphon_deleted = true.
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.