## Summary This issue is to roll out [query change](https://gitlab.com/gitlab-org/gitlab/-/merge_requests/239187) on production, that is currently behind the `commit_shas_metadata_lateral_join` feature flag. The flag switches `MergeRequestDiffCommit.commit_shas_from_metadata` from a plain `LEFT JOIN` against `merge_request_commits_metadata` to a fenced `LEFT JOIN LATERAL (... LIMIT 1)`. During the commits-metadata backfill the planner badly misestimates the row count for a given `merge_request_diff_id` (~111k estimated vs. 2 actual) and picks a hash join that scans every metadata row for the project (~63k rows, ~13s). The `LATERAL` + `LIMIT 1` fence blocks subquery pull-up and forces a per-row primary-key lookup on `merge_request_commits_metadata_pkey (id, project_id)`. It is a **derisk** flag — both paths return identical data; only the join strategy changes. Execution plans (Database Lab): - Before (plain `LEFT JOIN` — Hash Left Join, ~13.3s, scans 63,517 metadata rows): https://console.postgres.ai/gitlab/gitlab-production-main/sessions/52182/commands/153696 - After (`LATERAL` + `LIMIT 1` — Nested Loop, per-row PK probe): https://console.postgres.ai/gitlab/gitlab-production-main/sessions/52182/commands/153697 Actor: `Feature.current_request` (request-based) — production rollout is a percentage rollout with `--actors`, not project/group/user gating. ## Owners - Most appropriate Slack channel to reach out to: `#g_database_health` - Best individual to reach out to: @krasio ## Expectations ### What are we expecting to happen? The goal is to reduce LWLock/BufferMapping for replicas - https://dashboards.gitlab.net/goto/ffo5pe2d91y4ga?orgId=1. ### What can go wrong and how would we detect it? The forced nested loop could be slower than the hash join if a single `merge_request_diff` references a very large fraction of the project's commit metadata (crossover around ~15k commits). This would show up as increased latency on the `commit_shas_from_metadata` query. - Slow query logs, breakdown by query fingerprint - https://log.gprd.gitlab.net/app/r/s/5tWz7 - Mitigation: disable the flag (see Rollback Steps). ## Rollout Steps Note: Please make sure to run the chatops commands in the Slack channel that gets impacted by the command. ### Rollout on non-production environments - Verify the MR with the feature flag is merged to `master` and has been deployed to non-production environments with `/chatops gitlab run auto_deploy status https://gitlab.com/gitlab-org/gitlab/-/merge_requests/239187` - [x] Deploy the feature flag at a percentage (recommended percentage: 50%) with `/chatops gitlab run feature set commit_shas_metadata_lateral_join 50 --actors --dev --pre --staging --staging-ref` - [x] Monitor that the error rates did not increase (repeat with a different percentage as necessary). - [x] Enable the feature globally on non-production environments with `/chatops gitlab run feature set commit_shas_metadata_lateral_join true --dev --pre --staging --staging-ref` - [x] Verify that the feature works as expected. ### Specific rollout on production For visibility, all `/chatops` commands that target production must be executed in the [`#production` Slack channel](https://gitlab.slack.com/archives/C101F3796) and cross-posted (with the command results) to the responsible team's Slack channel. - Ensure that the feature MRs have been deployed to both production and canary with `/chatops gitlab run auto_deploy status <merge-commit-of-your-feature>` - [ ] This flag uses a **request actor**, so roll out by percentage of actors: - [x] `/chatops gitlab run feature set commit_shas_metadata_lateral_join 5 --actors` - [x] `/chatops gitlab run feature set commit_shas_metadata_lateral_join 25 --actors` - [x] `/chatops gitlab run feature set commit_shas_metadata_lateral_join 50 --actors` - [x] `/chatops gitlab run feature set commit_shas_metadata_lateral_join 75 --actors` - [ ] `/chatops gitlab run feature set commit_shas_metadata_lateral_join true` - [ ] Verify query latency on the Database dashboards. ### Preparation before global rollout - [ ] Set a milestone to this rollout issue to signal for enabling and removing the feature flag when it is stable. - [ ] Ensure that you or a representative in development can be available for at least 2 hours after feature flag updates in production. ### Global rollout on production For visibility, all `/chatops` commands that target production must be executed in the [`#production` Slack channel](https://gitlab.slack.com/archives/C101F3796) and cross-posted (with the command results) to the responsible team's Slack channel. - [ ] [Incrementally roll out](https://docs.gitlab.com/development/feature_flags/controls/#process) the feature on production. - Example: `/chatops gitlab run feature set commit_shas_metadata_lateral_join <rollout-percentage> --actors`. - Between every step wait for at least 15 minutes and monitor the appropriate graphs on https://dashboards.gitlab.net. - [ ] After the feature has been 100% enabled, wait for at least one day before releasing the feature. ### Release the feature After the feature has been deemed stable, the clean up should be done as soon as possible to permanently enable the feature and reduce complexity in the codebase. - [ ] Create a merge request to remove the `commit_shas_metadata_lateral_join` feature flag. The MR should include the following changes: - Remove all references to the feature flag from the codebase (drop the plain-join branch). - Remove the YAML definition `config/feature_flags/gitlab_com_derisk/commit_shas_metadata_lateral_join.yml`. - [ ] Once the cleanup MR has been deployed to production, clean up the feature flag from all environments by running this chatops command in `#production` channel: `/chatops gitlab run feature delete commit_shas_metadata_lateral_join --dev --pre --staging --staging-ref --production` - [ ] Close this rollout issue. ## Rollback Steps - [ ] This feature can be disabled on production by running the following Chatops command: ``` /chatops gitlab run feature set commit_shas_metadata_lateral_join false ``` - [ ] Disable the feature flag on non-production environments: ``` /chatops gitlab run feature set commit_shas_metadata_lateral_join false --dev --pre --staging --staging-ref ``` - [ ] Delete feature flag from all environments: ``` /chatops gitlab run feature delete commit_shas_metadata_lateral_join --dev --pre --staging --staging-ref --production ```