Linear queries roll out plan

1. Merge the MR to keep the namespaces.traversal_ids up to date. !52854 (merged)
2. Merge the MR to index namespaces.traversal_ids. !57207 (merged)

The order within each phase is very important:

1. Ensure the traversal_ids column is synchronized:
   1. Enable sync_traversal_ids FF for the appropriate groups.
   2. Run a migration to backfill the namespaces.traversal_ids for those groups.
2. The namespaces.traversal_ids column can be used in queries for those groups now.
   1. Enable use_traversal_ids FF for the appropriate groups to replace recursive queries with linear queries.

Phase 1: Proof of concept - gitlab-org on Staging

These steps to be performed on staging.

1. #321947 (closed) Enable sync_traversal_ids FF for gitlab-org
2. #233431 (closed) Migration to sync namespaces.traversal_ids values for gitlab-org
3. #301142 (closed) Merge Namespace#self_and_descendants MR query to prove this is all worthwhile! Behind a use_traversal_ids feature flag
4. https://gitlab.com/gitlab-org/gitlab/-/issues/321948 Ensure previous migration from #233431 (closed) has completed, enable use_traversal_ids FF for gitlab-org
5. #323312 (comment 535282654) Confirm the query works and there's performance improvement.
   • There was not enough volume on staging to determine anything conclusive.
   • We were further able to prove theoretical performance improvements from the changes being pushed through.

Phase 2: Proof of concept - gitlab-org on Production

These steps to be performed on production.

1. #321947 (closed) Enable sync_traversal_ids FF for gitlab-org
2. #325509 (closed) Migration to sync namespaces.traversal_ids values for gitlab-org
   • ~~This has been databaseapproved and is waiting for the previous step to be completed before we merge.~~
   • This has been merged. Waiting for deployment.
   • Currently marked as failed to deploy to production.
   • @cmiskell was able to confirm the migration successfully completed on production
3. https://gitlab.com/gitlab-org/gitlab/-/issues/321948 After previous migrate has completed, enable use_traversal_ids FF for gitlab-org
   • 6 April APAC - Currently disabled after an incident. Looks unrelated.
   • 6 April EMEA - Active incident.
   • 7 April APAC - Production locked for deployments.
   • 7 April EMEA - Active incidents.
   • 8 April APAC - Enabled, disabled 45mins after.
   • 14 April APAC - Enabled, disabled 30mins after.
   • 22 April APAC - Enabled

Phase 3: Proof of concept - top 10 groups by DB usage on Production

• Enable sync_traversal_ids FF globally
• gitlab-com/gl-infra/production#4650 (closed) Set traversal_ids for the top 10 groups by DB usage
• Enable use_traversal_ids FF for gitlab-qa-sandbox-group group on staging
• Enable use_traversal_ids FF for gitlab-qa-sandbox-group group on production
• Enable use_traversal_ids FF for the largest group by DB usage
• Verify there are no slow linear traversal queries. Issues identified so far:
  • #331429 (closed)
• Enable use_traversal_ids FF for the next 4 groups
• Verify there are no slow linear traversal queries.
• Enable use_traversal_ids FF for the rest of the top 10 groups by DB usage
• Verify there are no slow linear traversal queries.

Phase 4: Global traversal_ids synchronization

By the completion of this phase, all environments will have up to date namespaces.traversal_ids columns which are also kept in sync with any parenting changes.

• !61329 (merged) Default enable sync_traversal_ids for all environments.
• !57318 (merged) Set traversal_ids for every namespace
• #331477 (closed) Synchronize traversal_ids

Phase 5: Verify #self_and_descendants

1. https://gitlab.com/gitlab-org/gitlab/-/issues/321948 Enable use_traversal_ids FF globally on staging
2. Enable use_traversal_ids feature flag for the gitlab-qa-sandbox-group group on Production

Phase 6: Finalize #self_and_descendants

These steps to be performed on production.

1. #325500 (closed) Merge namespaces.traversal_ids check constraints
2. https://gitlab.com/gitlab-org/gitlab/-/issues/321948 Enable use_traversal_ids FF globally

Phase 7: self managed

The below plan is a work in progress. To be completed in %14.0 or later (after Default enable sync_traversal_ids for all environments).

1. Remove self_and_descendants from use_traversal_ids feature flag

Phase 8: clean up

1. &6308 (closed) traversal_ids migration cleanup

---

There is a similar abbreviated rollout for #ancestor methods through the use_traversal_ids_for_ancestors feature flag.

---

As for roll out of the feature flags, a recommendation was made:

1. Once this MR lands in staging, enable the flag for gitlab-org
2. Keep an eye on the QA pipelines that are executed in staging.
   • These pipelines are executed after each deployment to staging
   • You can keep an eye on these pipelines in the #qa-staging Slack channel
   • At this point, it might be a good idea to coordinate and inform the QA stable counterpart of your team, so they're also aware of this change in case any problem arises.
3. If nothing outside the ordinary was present in staging, proceed to enable to prod.
4. Enable the flag for a beta group only, it might be a test group or perhaps gitlab-com
5. If nothing outside the ordinary was present, proceed to enable the flag for gitlab-org

---