Use NamespaceProjectIdsEachBatch for group level SPP service

Gal Katzrequested to merge
gkatz_improve_group_spp_performance into master

What does this MR do and why?

Replaces Project.for_group_and_its_subgroups with Gitlab::Database::NamespaceProjectIdsEachBatch in SetGroupSecretPushProtectionService for improving the scalability and performance of the service for groups with many projects.

Related issue:

High error rate from 'SetGroupSecretPushProtect... (#510865 - closed) • Gal Katz • 17.10

Validate:

To run the service locally, use setGroupSecretPushProtection GraphQL mutation. Example:

mutation {
  setGroupSecretPushProtection(input: {namespacePath: <PATH_TO_A_GROUP>, secretPushProtectionEnabled: <BOOLEAN>}) {
    errors
  }
}

This should schedule a worker who will load all the child groups under the given group and then load all the projects for each group (see full example with actual SQL here). This process occurs iteratively and outside of a transaction. Previously, the use of the single query for_group_and_its_subgroups for all the nested project_ids of a group caused a query timeouts when trying to run for a group with 34k+ projects. In addition, it was a time consuming action taking place inside a transaction.

Edited by Gal Katz

Merge request reports