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 with rails generate gitlab:click_house:siphon dora_daily_metrics --with-traversal-path. traversal_path is derived from project_id, the table's sharding key.
  • The primary key is (traversal_path, date, environment_id, id): rows are aggregates, and environment_id and date never change after a row is created.
  • siphon_dora_daily_metrics_pg_pkey_ordered and its materialized view: the lookup table Siphon uses to find a row's full ClickHouse key from its PostgreSQL id. It carries date and environment_id as well as traversal_path, so updates and deletes land on the right key.
  • db/siphon/tables/dora_daily_metrics.yml: dedup_by: id, the lookup table, and a reconcile job for traversal_path from project_id.
Key order and the alternative
  • id has 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. date second 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

Screenshots or screen recordings

Not applicable: no UI change.

How to set up and validate locally

  1. Make sure GDK runs ClickHouse, NATS and Siphon (gdk config get siphon.enabled is true).
  2. Run the ClickHouse migrations:
    bundle exec rake gitlab:clickhouse:migrate
  3. Reconfigure GDK so Siphon picks up the new table, then restart Siphon:
    gdk reconfigure
    gdk restart siphon
  4. 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)
  5. 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)
  6. Update and delete the row (metric.update!(deployment_frequency: 5), then metric.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.

Merge request reports

Loading
Loading