## Summary Roll out [the feature](https://gitlab.com/gitlab-org/gitlab/-/work_items/627135) currently behind the `mr_by_commit_sha_use_array_subquery` feature flag. The flag guards a query change to the `MergeRequest.by_commit_sha` scope, introduced in https://gitlab.com/gitlab-org/gitlab/-/merge_requests/254592 (milestone 19.4, flag type `gitlab_com_derisk`, `default_enabled: false`). The scope currently filters with `EXISTS (...)`, which the PostgreSQL planner mis-estimates, producing poor joins and query timeouts — `GET /api/:version/projects/:id/repository/commits/:sha/merge_requests` has been returning 500s as a result ([logs](https://log.gprd.gitlab.net/app/r/s/yinaT)). With the flag enabled, the scope first collects matching diff IDs via `ARRAY(subquery)` and then filters with `latest_merge_request_diff_id = ANY (...)`, forcing the filter on diff IDs. The flag is evaluated per project actor (`Project.actor_from_id(target_project_id)`). - DRI: @Saahmed - Team Slack channel: `#g_code_review` > [!note] > Process and guidance live in the docs — this issue is just the commands and a place to track the rollout. > "Rolling out" means incrementally enabling the flag on GitLab.com to validate stability — it is not the same as releasing the feature, which happens when the flag is removed. > [Feature flag controls](https://docs.gitlab.com/development/feature_flags/controls/) · [Feature flag lifecycle](https://handbook.gitlab.com/handbook/product-development/how-we-work/product-development-flow/feature-flag-lifecycle/#feature-flag-lifecycle) ## Expected Improvement Based on the output of `EXPLAIN` on the new query, I expect the `500s` due to timeouts to cease. Old query: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/56879/commands/161363 New query: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/56879/commands/161366 ## What could go wrong? The change only rewrites how `MergeRequest.by_commit_sha` filters merge requests; the expected result set is identical, and both paths are covered by the specs in https://gitlab.com/gitlab-org/gitlab/-/merge_requests/254592. - The new query shape could still be mis-estimated for some data distributions and end up slower than the old plan for some projects. Watch the [slow-query logs](https://log.gprd.gitlab.net/app/r/s/yinaT) and the 5xx rate on `GET /api/:version/projects/:id/repository/commits/:sha/merge_requests` at each rollout step. - If results ever diverged between paths, the endpoint would return missing or extra merge requests for a commit SHA. - Containment: the flag is `default_enabled: false` and checked per project actor, so it can be rolled out and rolled back per project; disabling it restores the previous `EXISTS` query immediately (see Rollback). ## Events - [Exceptions with mr_by_commit_sha_use_array_subquery:1](https://log.gprd.gitlab.net/app/discover#/?_g=(time:(from:now-1d,to:now))&_a=(index:'7092c4e2-4eb5-46f2-8305-a7da2edad090',query:(language:kuery,query:'json.exception.feature_flag_states.keyword:%22mr_by_commit_sha_use_array_subquery:1%22'),columns:!(json.exception.class,json.exception.message,json.extra.workflow_id,json.meta.caller_id),sort:!(!(json.time,desc)))) Feature Flag events are only logged by default for feature flags marked for the current or future milestones. To enable while the feature flag is active, see https://docs.gitlab.com/development/feature_flags/#logging ## Rollout Run all production `/chatops` in [`#production`](https://gitlab.slack.com/archives/C101F3796) and cross-post the results to `#g_code_review`. Background: [incremental rollout process](https://docs.gitlab.com/development/feature_flags/controls/#process), [feature actors](https://docs.gitlab.com/development/feature_flags/#feature-actors). **Non-production** ``` /chatops gitlab run feature set mr_by_commit_sha_use_array_subquery 50 --actors --dev --pre --staging --staging-ref /chatops gitlab run feature set mr_by_commit_sha_use_array_subquery true --dev --pre --staging --staging-ref ``` **Production** — percentage rollout (wait ≥15 min between steps, watch dashboards): ``` /chatops gitlab run feature set mr_by_commit_sha_use_array_subquery <percentage> --actors ``` Or target specific actors instead: ``` /chatops gitlab run feature set --project=gitlab-org/gitlab,gitlab-org/gitlab-foss mr_by_commit_sha_use_array_subquery true /chatops gitlab run feature set --group=gitlab-org,gitlab-com mr_by_commit_sha_use_array_subquery true /chatops gitlab run feature set --user=Saahmed mr_by_commit_sha_use_array_subquery true ``` ## Before global rollout Confirm the relevant gotchas before going to 100% — see [enabling a feature for GitLab.com](https://docs.gitlab.com/development/feature_flags/controls/#enabling-a-feature-for-gitlabcom): - [Docs + version history](https://docs.gitlab.com/development/documentation/feature_flags/) updated - [Breaking changes](https://docs.gitlab.com/development/documentation/release_notes/#deprecations-removals-and-breaking-changes) announced, if any - [Change management issue](https://handbook.gitlab.com/handbook/engineering/infrastructure-platforms/change-management/#feature-flags-and-the-change-management-process) opened, if required - [External API consumers](https://docs.gitlab.com/development/feature_flags/#do-not-use-feature-flags-in-external-api-consumers) handled with a fail-open mechanism, if applicable ## Cleanup Remove the flag once [deemed stable](https://handbook.gitlab.com/handbook/product-development/how-we-work/product-development-flow/feature-flag-lifecycle/#feature-flag-lifecycle) — see [cleaning up](https://docs.gitlab.com/development/feature_flags/controls/#cleaning-up). Track it here, or open a follow-up [Feature Flag Cleanup issue](https://gitlab.com/gitlab-org/gitlab/-/work_items/new?description_template=Feature%20Flag%20Cleanup). Remove the flag and its YAML definition from the codebase, then: ``` /chatops gitlab run release check <merge-request-url> <milestone> /chatops gitlab run feature delete mr_by_commit_sha_use_array_subquery --dev --pre --staging --staging-ref --production ``` ## Rollback ``` /chatops gitlab run feature set mr_by_commit_sha_use_array_subquery false # production /chatops gitlab run feature set mr_by_commit_sha_use_array_subquery false --dev --pre --staging --staging-ref # non-production /chatops gitlab run feature delete mr_by_commit_sha_use_array_subquery --dev --pre --staging --staging-ref --production # remove entirely ```