## Summary This issue is to roll out [the feature](https://gitlab.com/gitlab-org/gitlab/-/work_items/577552) on production, that is currently behind the `loose_foreign_keys_lateral_query` feature flag. Introduced in: https://gitlab.com/gitlab-org/gitlab/-/merge_requests/235721 ## Owners - Most appropriate Slack channel to reach out to: `#g_database_frameworks` - Best individual to reach out to: @krasio ## Expectations ### What are we expecting to happen? When the flag is enabled, `LooseForeignKeys::CleanerService` will issue child-row lookups via a `LATERAL` join over a `(VALUES ...)` list of parent IDs instead of a single `IN (...)` predicate. This forces PostgreSQL to do one index seek per parent ID, eliminating the planner's option to fall back to a sequential scan when its row-count estimate for the `IN` list is mis-costed. Expected effects: - LFK cleanup queries that previously regressed to seq scans (e.g. `ci_build_names`, see https://gitlab.com/gitlab-com/request-for-help/-/issues/3588#note_2826467364) return to index-seek plans. - Worker throughput and SLI (apdex / error rate via `Gitlab::Metrics::LooseForeignKeysSlis`) should be unchanged or improved. - No change to functional behavior — the same set of child rows is deleted/updated per batch. ### What can go wrong and how would we detect it? - A different plan choice could regress queries on tables where the old `IN` form was already producing a good plan. Detection: LFK apdex/error dashboards and per-table cleanup metrics (`loose_foreign_key_deletions`, `loose_foreign_key_updates`, `loose_foreign_key_processed_deleted_records`). - Database load increase (more queries / more locks). Detection: PgBouncer/Patroni dashboards on `main` and `ci` clusters. - Lock contention: `FOR UPDATE SKIP LOCKED` lives inside the lateral so leaf-row locking semantics are preserved, but unexpected interactions should surface in lock-wait graphs. Relevant dashboards on https://dashboards.gitlab.net: - [sidekiq: LFK Worker Detail](https://dashboards.gitlab.net/goto/dfp9dhiduascgf?orgId=1) - [sidekiq: Loose Foreign Keys Processing](https://dashboards.gitlab.net/goto/efp9dfxy6dvr4a?orgId=1) - Patroni overview - [main](https://dashboards.gitlab.net/goto/dfp9do1d6fklcc?orgId=1) - [ci](https://dashboards.gitlab.net/goto/afp9dp2nt57uof?orgId=1) - [sec](https://dashboards.gitlab.net/goto/efp9dphqts5j4c?orgId=1) - PgBouncer overview - [main](https://dashboards.gitlab.net/goto/bfp9dsjzq8kjka?orgId=1) - [ci](https://dashboards.gitlab.net/goto/cfp9dtkipa9kwd?orgId=1) - [sec](https://dashboards.gitlab.net/goto/dfp9dtvcwc7b4a?orgId=1) - [LFK deletions and updates totals](https://dashboards.gitlab.net/goto/ffp9e71ss1v5sc?orgId=1) ## 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 <merge-commit-of-your-feature>` - [x] Deploy the feature flag at a percentage (recommended percentage: 50%) with `/chatops gitlab run feature set loose_foreign_keys_lateral_query 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 loose_foreign_keys_lateral_query true --dev --pre --staging --staging-ref` - [x] Verify that the feature works as expected. The best environment to validate the feature in is [`staging-canary`](https://about.gitlab.com/handbook/engineering/infrastructure/environments/#staging-canary) as this is the first environment deployed to. Make sure you are [configured to use canary](https://next.gitlab.com/). - [ ] If the feature flag causes end-to-end tests to fail, disable the feature flag on staging to avoid blocking [deployments](https://about.gitlab.com/handbook/engineering/deployments-and-releases/deployments/). ### Before production rollout - [ ] If the change is significant and you wanted to announce in [#whats-happening-at-gitlab](https://gitlab.enterprise.slack.com/archives/C0259241C), it best to do it before rollout to `gitlab-org/gitlab-com`. ### Preparation before global rollout - [x] Set a milestone to this rollout issue to signal for enabling and removing the feature flag when it is stable. - [x] Check if the feature flag change needs to be accompanied with a [change management issue](https://about.gitlab.com/handbook/engineering/infrastructure-platforms/change-management/#feature-flags-and-the-change-management-process). Cross link the issue here if it does. - [x] Ensure that you or a representative in development can be available for at least 2 hours after feature flag updates in production. If a different developer will be covering, or an exception is needed, please inform the oncall SRE by using the `@sre-oncall` Slack alias. - [x] Notify the [`#support_gitlab-com` Slack channel](https://gitlab.slack.com/archives/C4XFU81LG) and your team channel ([more guidance when this is necessary in the dev docs](https://docs.gitlab.com/development/feature_flags/controls/#communicate-the-change)). ### 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. - [x] `/chatops gitlab run feature set loose_foreign_keys_lateral_query 25 --actors` - [x] `/chatops gitlab run feature set loose_foreign_keys_lateral_query 50 --actors` - [x] `/chatops gitlab run feature set loose_foreign_keys_lateral_query 75 --actors` - [x] `/chatops gitlab run feature set loose_foreign_keys_lateral_query true` - 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). ### Release the feature After the feature has been [deemed stable](https://about.gitlab.com/handbook/product-development-flow/feature-flag-lifecycle/#including-a-feature-behind-feature-flag-in-the-final-release), the [clean up](https://docs.gitlab.com/development/feature_flags/controls/#cleaning-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 `loose_foreign_keys_lateral_query` feature flag. The MR should include: - Remove the dispatcher in `LooseForeignKeys::CleanerService#in_query_with_limit` and the `legacy_in_query_with_limit` method. - Drop the safety-check fallback for the `IN (` form in `build_query`. - Remove the corresponding `flag disabled` spec contexts in `spec/services/loose_foreign_keys/cleaner_service_spec.rb`. - Remove the YAML definition `config/feature_flags/gitlab_com_derisk/loose_foreign_keys_lateral_query.yml`. - [ ] Ensure that the cleanup MR has been included in the release package. - [ ] Close [the feature issue](https://gitlab.com/gitlab-org/gitlab/-/work_items/577552) to indicate the feature will be released in the current milestone. - [ ] Once the cleanup MR has been deployed to production, clean up the feature flag from all environments by running these chatops command in `#production` channel: `/chatops gitlab run feature delete loose_foreign_keys_lateral_query --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 loose_foreign_keys_lateral_query false ``` - [ ] Disable the feature flag on non-production environments: ``` /chatops gitlab run feature set loose_foreign_keys_lateral_query false --dev --pre --staging --staging-ref ``` - [ ] Delete feature flag from all environments: ``` /chatops gitlab run feature delete loose_foreign_keys_lateral_query --dev --pre --staging --staging-ref --production ```