Deployments::UpdateEnvironmentWorker: slow unindexed UPDATE on merge_request_metrics

Deployments::UpdateEnvironmentWorker is running a slow, unindexed update query on every successful production deployment. In a 10,000-query sample of slow queries (each over ~1s) from gitlab.com, this worker was the single biggest contributor: 3,571 slow queries totaling about 6,066 seconds of database time. Mean 1.70s, p95 3.31s, max 14.49s.

Top slow-query endpoints in the sample

Ranked by total database time (count times cost), the biggest contributors (https://log.gprd.gitlab.net/app/r/s/30Ixc):

Endpoint Slow queries Total s Mean s p95 Max
Deployments::UpdateEnvironmentWorker 3571 6066 1.70 3.31 14.5
Deployments::LinkMergeRequestWorker 1606 5529 3.44 9.19 14.9
Projects::ImportExport::RelationExportWorker 1099 1687 1.54 2.35 11.4
UpdateProjectStatisticsWorker 468 1135 2.43 5.18 14.1
RepositoryUpdateRemoteMirrorWorker 541 1032 1.91 4.58 11.2
WorkItems::RolledupDates::UpdateRolledupDatesEventHandler 97 593 6.11 13.1 14.9

Query

Generated by Deployment#update_merge_request_metrics! in app/models/deployment.rb (around line 329), called from Deployments::UpdateEnvironmentService on each successful production deployment.

UPDATE merge_request_metrics SET first_deployed_to_production_at = $1
WHERE merge_request_id IN (
  SELECT merge_requests.id FROM merge_requests
  JOIN merge_request_metrics ON merge_request_metrics.merge_request_id = merge_requests.id
  WHERE merge_requests.target_project_id = $2 AND merge_requests.target_branch = $3
    AND merge_request_metrics.first_deployed_to_production_at IS NULL
    AND merge_request_metrics.merged_at <= $4 AND merge_request_metrics.merged_at >= $5
) AND merge_request_metrics.first_deployed_to_production_at IS NULL

Ruby source:

def update_merge_request_metrics!
  return unless environment.production? && success?

  merge_requests = project.merge_requests
                   .joins(:metrics)
                   .where(target_branch: ref, merge_request_metrics: { first_deployed_to_production_at: nil })
                   .where("merge_request_metrics.merged_at <= ?", finished_at)

  if previous_deployment
    merge_requests = merge_requests.where("merge_request_metrics.merged_at >= ?", previous_deployment.finished_at)
  end

  MergeRequest::Metrics
    .where(merge_request_id: merge_requests.select(:id), first_deployed_to_production_at: nil)
    .update_all(first_deployed_to_production_at: finished_at)
end

Why it's slow

Two separate problems:

  1. Missing lower bound in some cases. The merged_at >= $5 clause only gets added when previous_deployment exists. On the first production deployment, or when the previous deployment record is missing, that clause is dropped and the range covers every merge request ever merged into the branch. On a busy default branch, that is the entire history.

  2. No index supports the selective part of the query. The set we actually want is "metrics in this project, merged in the range, not yet deployed to production." That not-yet-deployed set stays small per project, because a row leaves it permanently once deployed. But the existing index, index_merge_request_metrics_on_first_deployed_to_production_at, is a plain single-column index on a column that is NULL for almost every row, so the planner can't use it usefully. Instead, the plan drives off a target_project_id index on merge_requests, filters target_branch as a residual, then probes metrics per merge request. On a large branch, that's a lot of probes.

Proposed fix

Add a partial composite index, and consider dropping the near-useless single-column one:

CREATE INDEX index_mr_metrics_on_project_merged_at_not_deployed
  ON merge_request_metrics (target_project_id, merged_at)
  WHERE first_deployed_to_production_at IS NULL;

merge_request_metrics already carries target_project_id, so this lets the planner start from the metrics side, land on the small not-yet-deployed set in the merged_at range, then join merge_requests only to check target_branch. It also helps the no-lower-bound case, since the partial predicate keeps the scanned set small regardless of range.

Note for implementer: this needs a standard GitLab async index migration (add_concurrent_index, post-deploy), following the database migration style guide. The numbers above come from a 10k sample, so they are a lower bound on real impact.

Update: the fix was the query, not a new index (!256809 (merged))

The slow query does not need a new index. The problem is that it filtered only merge_requests.target_project_id, so Postgres had no predicate on merge_request_metrics.target_project_id and could not seek any (target_project_id, merged_at) index on the metrics table. It drove the plan from merge_requests and probed metrics once per MR in the project.

Adding merge_request_metrics.target_project_id to the query lets the planner use the existing index_mr_metrics_on_target_project_id_merged_at_nulls_last. On gitlab-org/gitlab this took the query from ~31s to ~5ms. !256809 (merged) does this by scoping through the existing MergeRequest.join_metrics(project.id) scope. The partial index prepared in !254915 (merged) turns out to be unnecessary; the fixed query does not use it.

Before/after, verified with rails runner (only the one target_project_id predicate differs):

Before !256809 (merged) 
UPDATE merge_request_metrics SET first_deployed_to_production_at = $1
WHERE merge_request_id IN (
  SELECT merge_requests.id FROM merge_requests
  INNER JOIN merge_request_metrics ON merge_request_metrics.merge_request_id = merge_requests.id
  WHERE merge_requests.target_project_id = $2
    AND merge_requests.target_branch = $3
    AND merge_request_metrics.first_deployed_to_production_at IS NULL
    AND merge_request_metrics.merged_at <= $4
    AND merge_request_metrics.merged_at >= $5
) AND merge_request_metrics.first_deployed_to_production_at IS NULL

Plan ~31s: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/58244/commands/162915

After !256809 (merged) 
UPDATE merge_request_metrics SET first_deployed_to_production_at = $1
WHERE merge_request_id IN (
  SELECT merge_requests.id FROM merge_requests
  INNER JOIN merge_request_metrics ON merge_request_metrics.merge_request_id = merge_requests.id
  WHERE merge_requests.target_project_id = $2
    AND merge_request_metrics.target_project_id = $2
    AND merge_requests.target_branch = $3
    AND merge_request_metrics.first_deployed_to_production_at IS NULL
    AND merge_request_metrics.merged_at <= $4
    AND merge_request_metrics.merged_at >= $5
) AND merge_request_metrics.first_deployed_to_production_at IS NULL

Plan ~5ms, uses index_mr_metrics_on_target_project_id_merged_at_nulls_last: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/58244/commands/162917

Table is over the size limit

The merge_request_metrics table is past the large-table size threshold. The database dictionary (db/docs/merge_request_metrics.yml) records table_size: over_limit.

There is an open alert issue tracking this: https://gitlab.com/gitlab-org/gitlab/-/issues/521859

There has been prior work to cut the number of indexes on this table, because it is oversized: #331930 (closed) (closed, "Reduce the number of indexes used by merge_request_metrics").

No open issue proposes partitioning or re-sharding this table. The Cells sharding key is already set (target_project_id maps to projects), and the table lives in the gitlab_main_org schema.

Because the table is over the limit, and there is a standing goal to cut indexes, adding a new index needs justification. Two points support this one:

  1. The partial predicate WHERE first_deployed_to_production_at IS NULL keeps the index small. It only covers merge requests not yet deployed to production, and a row leaves that set permanently once deployed.
  2. We can keep the table's index count flat by dropping an existing near-useless index in the same effort: index_merge_request_metrics_on_first_deployed_to_production_at. It is a plain single-column index on a column that is NULL for almost every row.

A code check supports the drop. first_deployed_to_production_at is referenced in only three places, and none needs a standalone index on that column:

  • Deployment#update_merge_request_metrics!, the query this issue fixes, uses it as an IS NULL filter combined with target_project_id, target_branch, and a merged_at range. The new partial index serves this.
  • Value Stream Analytics stage events (merge_request_first_deployed_to_production, issue_deployed_to_production) use it as a stage timestamp inside project- or namespace-scoped queries. Those use the target_project_id-leading composite indexes, not a standalone one. There is no ORDER BY on the column anywhere.
  • A read-only API field (lib/api/entities/merge_request.rb) reads it per record, with no query on the column.

Recommendation: drop index_merge_request_metrics_on_first_deployed_to_production_at as part of the synchronous follow-up MR. First confirm its scan count is effectively zero on GitLab.com via pg_stat_user_indexes, to be safe before removing it.

Related follow-up (not part of this fix)

Deployments::LinkMergeRequestWorker is the second biggest contributor in the same sample: 1,606 slow queries, about 5,529 seconds total. Its main queries already have good indexes (index_merge_requests_for_latest_diffs_with_state_merged and index_on_merge_request_diffs_head_commit_sha), so this looks like a volume and data-skew problem rather than a missing index. Now tracked separately in #628637, with production query plans and a proposed fix.

Checklist

  • Prepare the async index with prepare_async_index (this MR)
  • After the async index is built on GitLab.com, add the synchronous add_concurrent_index migration in a follow-up MR
  • Verify the new plan uses the partial index via Postgres.ai on a large project
  • Confirm index_merge_request_metrics_on_first_deployed_to_production_at scan count is near zero on GitLab.com via pg_stat_user_indexes
  • Drop index_merge_request_metrics_on_first_deployed_to_production_at in the synchronous follow-up MR to keep the table's index count flat
Edited by Stan Hu