Add dynamic concurrency limit for create pipeline worker

What does this MR do and why?

This MR is related to the epic &13997.

This MR is a modified version of MR !158636 (closed) to limit the concurrency of the workers on the policy level.

Additional details from description from the MR !158636 (closed):

Our goal is to provide an way for instance admins to manage the number of jobs executed on behalf of a scheduled scan execution policy, so that pipelines are distributed and do not overburden the runners.

This MR adds a custom concurrency limit for CreatePipelineWorker to improve our previous solution that used the concurrency_limit alone.

Our concerns about using the concurrency_limit for our case are:

• It limits the worker's concurrency, but we want to restrict the CI builds' concurrency. One worker will create one pipeline, but the pipeline can have multiple ci build jobs.

• Sidekiq jobs seem to be much faster than pipeline jobs executed by the runners, so limiting the number of workers with the concurrency_limit might not be enough to reduce the runner's pressure.

This solution has some limitations, but it is an improvement compared to using the worker concurrency_limit attribute alone.

Queries

Get project ids

SELECT
    "projects"."id"
FROM
    "projects"
WHERE
    "projects"."namespace_id" IN (
        SELECT
            namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id
        FROM
            "namespaces"
        WHERE
            "namespaces"."type" = 'Group'
            AND (traversal_ids @> ('{70820400}')))

Group-level policy

SELECT
    COUNT(*)
FROM (
    SELECT
        1 AS one
    FROM
        "p_ci_builds"
        INNER JOIN "ci_pipelines" "pipeline" ON "pipeline"."partition_id" IS NOT NULL
            AND "pipeline"."id" = "p_ci_builds"."commit_id"
            AND "pipeline"."partition_id" = "p_ci_builds"."partition_id"
    WHERE
        "p_ci_builds"."type" = 'Ci::Build'
        AND "pipeline"."source" = 15
        AND "p_ci_builds"."project_id" IN (
            SELECT
                "projects"."id"
            FROM
                "projects"
            WHERE
                "projects"."namespace_id" IN (
                    SELECT
                        namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id
                    FROM
                        "namespaces"
                    WHERE
                        "namespaces"."type" = 'Group'
                        AND (traversal_ids @> ('{70820400}'))))
                AND ("p_ci_builds"."status" IN ('preparing', 'pending', 'running', 'waiting_for_callback', 'waiting_for_resource', 'canceling', 'created'))
                AND "p_ci_builds"."created_at" > '2024-08-13 19:47:54.539319'
                AND "p_ci_builds"."updated_at" > '2024-08-13 19:47:54.539520'
            LIMIT 100) subquery_for_count

https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/30755/commands/95521

Project-level policy

SELECT
    COUNT(*)
FROM (
    SELECT
        1 AS one
    FROM
        "p_ci_builds"
        INNER JOIN "ci_pipelines" "pipeline" ON "pipeline"."partition_id" IS NOT NULL
            AND "pipeline"."id" = "p_ci_builds"."commit_id"
            AND "pipeline"."partition_id" = "p_ci_builds"."partition_id"
    WHERE
        "p_ci_builds"."type" = 'Ci::Build'
        AND "pipeline"."source" = 15
        AND "p_ci_builds"."project_id" = 278964
        AND ("p_ci_builds"."status" IN ('preparing', 'pending', 'running', 'waiting_for_callback', 'waiting_for_resource', 'canceling', 'created'))
        AND "p_ci_builds"."created_at" > '2024-08-13 19:23:01.827195'
        AND "p_ci_builds"."updated_at" > '2024-08-13 19:23:01.828523'
    LIMIT 100) subquery_for_count

https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/30755/commands/95522

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

How to set up and validate locally