[BE] Validate performance with CSP

Why are we doing this work

Using the CSP is technically the same as having a single top-level group with policies that are propagated to all sub-groups and sub-projects. However, it's also easier to overlook the performance implications when a group is designated as a CSP on the instance. We should ensure to not introduce any performance degradation that could impact the whole instance.

1. Whenever we access entities belonging to security_orchestration_policy_configuration, we should check that existing indexes cover the number of records matching the base condition
   • For example, in AddApproversToRulesService we call configuration.approval_project_rules & configuration.approval_merge_request_rules which would return a huge number of records.
2. We need to identify all the queries that we currently do and try to batch them and check/create indexes to improve the performance of the query. If we can estimate a rough number on maximum number of top-level groups, projects, MRs with the projects in an existing instance, we can run some benchmarks. Nevertheless, we should document it explicitly that the policy sync would be eventually consistent and there will be a delay
3. Perform testing on staging not possible to enable CSP on staging
4. We should aim to support 100 groups, each with 100 projects, and 100 MRs in each project as a starting point

Relevant links

Non-functional requirements

• Documentation:
• Feature flag:
• Performance:
• Testing:

Implementation plan

Look into:

• https://docs.gitlab.com/administration/reference_architectures/#how-we-perform-the-tests
• https://gitlab.com/gitlab-org/gitlab-environment-toolkit

Verification steps