Permissions query causing LWLock contention and CPU Microbursts

Research issue found here: gitlab-com/gl-infra/scalability#3803

Essentially, this query is being called roughly 2k times per second, with bursts of up to 10k:

Click to expand

SELECT
    project_ids.project_id,
    bool_or(custom_permissions.admin_cicd_variables) AS admin_cicd_variables,
    bool_or(custom_permissions.admin_compliance_framework) AS admin_compliance_framework,
    bool_or(custom_permissions.admin_integrations) AS admin_integrations,
    bool_or(custom_permissions.admin_merge_request) AS admin_merge_request,
    bool_or(custom_permissions.admin_protected_branch) AS admin_protected_branch,
    bool_or(custom_permissions.admin_push_rules) AS admin_push_rules,
    bool_or(custom_permissions.admin_runners) AS admin_runners,
    bool_or(custom_permissions.admin_terraform_state) AS admin_terraform_state,
    bool_or(custom_permissions.admin_vulnerability) AS admin_vulnerability,
    bool_or(custom_permissions.admin_web_hook) AS admin_web_hook,
    bool_or(custom_permissions.archive_project) AS archive_project,
    bool_or(custom_permissions.manage_deploy_tokens) AS manage_deploy_tokens,
    bool_or(custom_permissions.manage_merge_request_settings) AS manage_merge_request_settings,
    bool_or(custom_permissions.manage_project_access_tokens) AS manage_project_access_tokens,
    bool_or(custom_permissions.manage_security_policy_link) AS manage_security_policy_link,
    bool_or(custom_permissions.read_code) AS read_code,
    bool_or(custom_permissions.read_dependency) AS read_dependency,
    bool_or(custom_permissions.read_runners) AS read_runners,
    bool_or(custom_permissions.read_vulnerability) AS read_vulnerability,
    bool_or(custom_permissions.remove_project) AS remove_project
FROM (
    VALUES (362, ARRAY[827])) AS project_ids (project_id, namespace_ids),
    LATERAL (
        SELECT
            (member_roles.permissions -> 'admin_cicd_variables')::boolean AS admin_cicd_variables, (member_roles.permissions -> 'admin_compliance_framework')::boolean AS admin_compliance_framework, (member_roles.permissions -> 'admin_integrations')::boolean AS admin_integrations, (member_roles.permissions -> 'admin_merge_request')::boolean AS admin_merge_request, (member_roles.permissions -> 'admin_protected_branch')::boolean AS admin_protected_branch, (member_roles.permissions -> 'admin_push_rules')::boolean AS admin_push_rules, (member_roles.permissions -> 'admin_runners')::boolean AS admin_runners, (member_roles.permissions -> 'admin_terraform_state')::boolean AS admin_terraform_state, (member_roles.permissions -> 'admin_vulnerability')::boolean AS admin_vulnerability, (member_roles.permissions -> 'admin_web_hook')::boolean AS admin_web_hook, (member_roles.permissions -> 'archive_project')::boolean AS archive_project, (member_roles.permissions -> 'manage_deploy_tokens')::boolean AS manage_deploy_tokens, (member_roles.permissions -> 'manage_merge_request_settings')::boolean AS manage_merge_request_settings, (member_roles.permissions -> 'manage_project_access_tokens')::boolean AS manage_project_access_tokens, (member_roles.permissions -> 'manage_security_policy_link')::boolean AS manage_security_policy_link, (member_roles.permissions -> 'read_code')::boolean AS read_code, (member_roles.permissions -> 'read_dependency')::boolean AS read_dependency, (member_roles.permissions -> 'read_runners')::boolean AS read_runners, (member_roles.permissions -> 'read_vulnerability')::boolean AS read_vulnerability, (member_roles.permissions -> 'remove_project')::boolean AS remove_project
        FROM
            "members"
            JOIN group_group_links ON members.source_id = group_group_links.shared_with_group_id
            JOIN member_roles ON member_roles.id = group_group_links.member_role_id
        WHERE
            "members"."user_id" = 730
            AND (member_roles.permissions @> ('{"admin_cicd_variables":true}')::jsonb
                OR member_roles.permissions @> ('{"admin_compliance_framework":true}')::jsonb
                OR member_roles.permissions @> ('{"admin_integrations":true}')::jsonb
                OR member_roles.permissions @> ('{"admin_merge_request":true}')::jsonb
                OR member_roles.permissions @> ('{"admin_protected_branch":true}')::jsonb
                OR member_roles.permissions @> ('{"admin_push_rules":true}')::jsonb
                OR member_roles.permissions @> ('{"admin_runners":true}')::jsonb
                OR member_roles.permissions @> ('{"admin_terraform_state":true}')::jsonb
                OR member_roles.permissions @> ('{"admin_vulnerability":true}')::jsonb
                OR member_roles.permissions @> ('{"admin_web_hook":true}')::jsonb
                OR member_roles.permissions @> ('{"archive_project":true}')::jsonb
                OR member_roles.permissions @> ('{"manage_deploy_tokens":true}')::jsonb
                OR member_roles.permissions @> ('{"manage_merge_request_settings":true}')::jsonb
                OR member_roles.permissions @> ('{"manage_project_access_tokens":true}')::jsonb
                OR member_roles.permissions @> ('{"manage_security_policy_link":true}')::jsonb
                OR member_roles.permissions @> ('{"read_code":true}')::jsonb
                OR member_roles.permissions @> ('{"read_dependency":true}')::jsonb
                OR member_roles.permissions @> ('{"read_runners":true}')::jsonb
                OR member_roles.permissions @> ('{"read_vulnerability":true}')::jsonb
                OR member_roles.permissions @> ('{"remove_project":true}')::jsonb)
            AND (group_group_links.shared_group_id IN (
                    SELECT
                        UNNEST(project_ids.namespace_ids) AS ids))
                AND (access_level > group_access)
            UNION ALL
            SELECT
                (member_roles.permissions -> 'admin_cicd_variables')::boolean AS admin_cicd_variables,
                (member_roles.permissions -> 'admin_compliance_framework')::boolean AS admin_compliance_framework,
                (member_roles.permissions -> 'admin_integrations')::boolean AS admin_integrations,
                (member_roles.permissions -> 'admin_merge_request')::boolean AS admin_merge_request,
                (member_roles.permissions -> 'admin_protected_branch')::boolean AS admin_protected_branch,
                (member_roles.permissions -> 'admin_push_rules')::boolean AS admin_push_rules,
                (member_roles.permissions -> 'admin_runners')::boolean AS admin_runners,
                (member_roles.permissions -> 'admin_terraform_state')::boolean AS admin_terraform_state,
                (member_roles.permissions -> 'admin_vulnerability')::boolean AS admin_vulnerability,
                (member_roles.permissions -> 'admin_web_hook')::boolean AS admin_web_hook,
                (member_roles.permissions -> 'archive_project')::boolean AS archive_project,
                (member_roles.permissions -> 'manage_deploy_tokens')::boolean AS manage_deploy_tokens,
                (member_roles.permissions -> 'manage_merge_request_settings')::boolean AS manage_merge_request_settings,
                (member_roles.permissions -> 'manage_project_access_tokens')::boolean AS manage_project_access_tokens,
                (member_roles.permissions -> 'manage_security_policy_link')::boolean AS manage_security_policy_link,
                (member_roles.permissions -> 'read_code')::boolean AS read_code,
                (member_roles.permissions -> 'read_dependency')::boolean AS read_dependency,
                (member_roles.permissions -> 'read_runners')::boolean AS read_runners,
                (member_roles.permissions -> 'read_vulnerability')::boolean AS read_vulnerability,
                (member_roles.permissions -> 'remove_project')::boolean AS remove_project
            FROM
                "members"
                JOIN group_group_links ON members.source_id = group_group_links.shared_with_group_id
                JOIN member_roles ON member_roles.id = members.member_role_id
            WHERE
                "members"."user_id" = 730
                AND (member_roles.permissions @> ('{"admin_cicd_variables":true}')::jsonb
                    OR member_roles.permissions @> ('{"admin_compliance_framework":true}')::jsonb
                    OR member_roles.permissions @> ('{"admin_integrations":true}')::jsonb
                    OR member_roles.permissions @> ('{"admin_merge_request":true}')::jsonb
                    OR member_roles.permissions @> ('{"admin_protected_branch":true}')::jsonb
                    OR member_roles.permissions @> ('{"admin_push_rules":true}')::jsonb
                    OR member_roles.permissions @> ('{"admin_runners":true}')::jsonb
                    OR member_roles.permissions @> ('{"admin_terraform_state":true}')::jsonb
                    OR member_roles.permissions @> ('{"admin_vulnerability":true}')::jsonb
                    OR member_roles.permissions @> ('{"admin_web_hook":true}')::jsonb
                    OR member_roles.permissions @> ('{"archive_project":true}')::jsonb
                    OR member_roles.permissions @> ('{"manage_deploy_tokens":true}')::jsonb
                    OR member_roles.permissions @> ('{"manage_merge_request_settings":true}')::jsonb
                    OR member_roles.permissions @> ('{"manage_project_access_tokens":true}')::jsonb
                    OR member_roles.permissions @> ('{"manage_security_policy_link":true}')::jsonb
                    OR member_roles.permissions @> ('{"read_code":true}')::jsonb
                    OR member_roles.permissions @> ('{"read_dependency":true}')::jsonb
                    OR member_roles.permissions @> ('{"read_runners":true}')::jsonb
                    OR member_roles.permissions @> ('{"read_vulnerability":true}')::jsonb
                    OR member_roles.permissions @> ('{"remove_project":true}')::jsonb)
                AND (group_group_links.shared_group_id IN (
                        SELECT
                            UNNEST(project_ids.namespace_ids) AS ids))
                    AND ((access_level < group_access)
                        OR (access_level = group_access
                            AND group_group_links.member_role_id IS NOT NULL))
                UNION ALL
                SELECT
                    (member_roles.permissions -> 'admin_cicd_variables')::boolean AS admin_cicd_variables,
                    (member_roles.permissions -> 'admin_compliance_framework')::boolean AS admin_compliance_framework,
                    (member_roles.permissions -> 'admin_integrations')::boolean AS admin_integrations,
                    (member_roles.permissions -> 'admin_merge_request')::boolean AS admin_merge_request,
                    (member_roles.permissions -> 'admin_protected_branch')::boolean AS admin_protected_branch,
                    (member_roles.permissions -> 'admin_push_rules')::boolean AS admin_push_rules,
                    (member_roles.permissions -> 'admin_runners')::boolean AS admin_runners,
                    (member_roles.permissions -> 'admin_terraform_state')::boolean AS admin_terraform_state,
                    (member_roles.permissions -> 'admin_vulnerability')::boolean AS admin_vulnerability,
                    (member_roles.permissions -> 'admin_web_hook')::boolean AS admin_web_hook,
                    (member_roles.permissions -> 'archive_project')::boolean AS archive_project,
                    (member_roles.permissions -> 'manage_deploy_tokens')::boolean AS manage_deploy_tokens,
                    (member_roles.permissions -> 'manage_merge_request_settings')::boolean AS manage_merge_request_settings,
                    (member_roles.permissions -> 'manage_project_access_tokens')::boolean AS manage_project_access_tokens,
                    (member_roles.permissions -> 'manage_security_policy_link')::boolean AS manage_security_policy_link,
                    (member_roles.permissions -> 'read_code')::boolean AS read_code,
                    (member_roles.permissions -> 'read_dependency')::boolean AS read_dependency,
                    (member_roles.permissions -> 'read_runners')::boolean AS read_runners,
                    (member_roles.permissions -> 'read_vulnerability')::boolean AS read_vulnerability,
                    (member_roles.permissions -> 'remove_project')::boolean AS remove_project
                FROM
                    "members"
                    INNER JOIN "member_roles" ON "member_roles"."id" = "members"."member_role_id"
                WHERE
                    "members"."user_id" = 730
                    AND (member_roles.permissions @> ('{"admin_cicd_variables":true}')::jsonb
                        OR member_roles.permissions @> ('{"admin_compliance_framework":true}')::jsonb
                        OR member_roles.permissions @> ('{"admin_integrations":true}')::jsonb
                        OR member_roles.permissions @> ('{"admin_merge_request":true}')::jsonb
                        OR member_roles.permissions @> ('{"admin_protected_branch":true}')::jsonb
                        OR member_roles.permissions @> ('{"admin_push_rules":true}')::jsonb
                        OR member_roles.permissions @> ('{"admin_runners":true}')::jsonb
                        OR member_roles.permissions @> ('{"admin_terraform_state":true}')::jsonb
                        OR member_roles.permissions @> ('{"admin_vulnerability":true}')::jsonb
                        OR member_roles.permissions @> ('{"admin_web_hook":true}')::jsonb
                        OR member_roles.permissions @> ('{"archive_project":true}')::jsonb
                        OR member_roles.permissions @> ('{"manage_deploy_tokens":true}')::jsonb
                        OR member_roles.permissions @> ('{"manage_merge_request_settings":true}')::jsonb
                        OR member_roles.permissions @> ('{"manage_project_access_tokens":true}')::jsonb
                        OR member_roles.permissions @> ('{"manage_security_policy_link":true}')::jsonb
                        OR member_roles.permissions @> ('{"read_code":true}')::jsonb
                        OR member_roles.permissions @> ('{"read_dependency":true}')::jsonb
                        OR member_roles.permissions @> ('{"read_runners":true}')::jsonb
                        OR member_roles.permissions @> ('{"read_vulnerability":true}')::jsonb
                        OR member_roles.permissions @> ('{"remove_project":true}')::jsonb)
                    AND "members"."source_type" = 'Project'
                    AND (members.source_id = project_ids.project_id)
                UNION ALL
                SELECT
                    (member_roles.permissions -> 'admin_cicd_variables')::boolean AS admin_cicd_variables,
                    (member_roles.permissions -> 'admin_compliance_framework')::boolean AS admin_compliance_framework,
                    (member_roles.permissions -> 'admin_integrations')::boolean AS admin_integrations,
                    (member_roles.permissions -> 'admin_merge_request')::boolean AS admin_merge_request,
                    (member_roles.permissions -> 'admin_protected_branch')::boolean AS admin_protected_branch,
                    (member_roles.permissions -> 'admin_push_rules')::boolean AS admin_push_rules,
                    (member_roles.permissions -> 'admin_runners')::boolean AS admin_runners,
                    (member_roles.permissions -> 'admin_terraform_state')::boolean AS admin_terraform_state,
                    (member_roles.permissions -> 'admin_vulnerability')::boolean AS admin_vulnerability,
                    (member_roles.permissions -> 'admin_web_hook')::boolean AS admin_web_hook,
                    (member_roles.permissions -> 'archive_project')::boolean AS archive_project,
                    (member_roles.permissions -> 'manage_deploy_tokens')::boolean AS manage_deploy_tokens,
                    (member_roles.permissions -> 'manage_merge_request_settings')::boolean AS manage_merge_request_settings,
                    (member_roles.permissions -> 'manage_project_access_tokens')::boolean AS manage_project_access_tokens,
                    (member_roles.permissions -> 'manage_security_policy_link')::boolean AS manage_security_policy_link,
                    (member_roles.permissions -> 'read_code')::boolean AS read_code,
                    (member_roles.permissions -> 'read_dependency')::boolean AS read_dependency,
                    (member_roles.permissions -> 'read_runners')::boolean AS read_runners,
                    (member_roles.permissions -> 'read_vulnerability')::boolean AS read_vulnerability,
                    (member_roles.permissions -> 'remove_project')::boolean AS remove_project
                FROM
                    "members"
                    INNER JOIN "member_roles" ON "member_roles"."id" = "members"."member_role_id"
                WHERE
                    "members"."user_id" = 730
                    AND (member_roles.permissions @> ('{"admin_cicd_variables":true}')::jsonb
                        OR member_roles.permissions @> ('{"admin_compliance_framework":true}')::jsonb
                        OR member_roles.permissions @> ('{"admin_integrations":true}')::jsonb
                        OR member_roles.permissions @> ('{"admin_merge_request":true}')::jsonb
                        OR member_roles.permissions @> ('{"admin_protected_branch":true}')::jsonb
                        OR member_roles.permissions @> ('{"admin_push_rules":true}')::jsonb
                        OR member_roles.permissions @> ('{"admin_runners":true}')::jsonb
                        OR member_roles.permissions @> ('{"admin_terraform_state":true}')::jsonb
                        OR member_roles.permissions @> ('{"admin_vulnerability":true}')::jsonb
                        OR member_roles.permissions @> ('{"admin_web_hook":true}')::jsonb
                        OR member_roles.permissions @> ('{"archive_project":true}')::jsonb
                        OR member_roles.permissions @> ('{"manage_deploy_tokens":true}')::jsonb
                        OR member_roles.permissions @> ('{"manage_merge_request_settings":true}')::jsonb
                        OR member_roles.permissions @> ('{"manage_project_access_tokens":true}')::jsonb
                        OR member_roles.permissions @> ('{"manage_security_policy_link":true}')::jsonb
                        OR member_roles.permissions @> ('{"read_code":true}')::jsonb
                        OR member_roles.permissions @> ('{"read_dependency":true}')::jsonb
                        OR member_roles.permissions @> ('{"read_runners":true}')::jsonb
                        OR member_roles.permissions @> ('{"read_vulnerability":true}')::jsonb
                        OR member_roles.permissions @> ('{"remove_project":true}')::jsonb)
                    AND "members"."source_type" = 'Namespace'
                    AND (members.source_id IN (
                            SELECT
                                UNNEST(project_ids.namespace_ids) AS ids))
                    UNION ALL
                    SELECT
                        FALSE AS admin_cicd_variables,
                        FALSE AS admin_compliance_framework,
                        FALSE AS admin_integrations,
                        FALSE AS admin_merge_request,
                        FALSE AS admin_protected_branch,
                        FALSE AS admin_push_rules,
                        FALSE AS admin_runners,
                        FALSE AS admin_terraform_state,
                        FALSE AS admin_vulnerability,
                        FALSE AS admin_web_hook,
                        FALSE AS archive_project,
                        FALSE AS manage_deploy_tokens,
                        FALSE AS manage_merge_request_settings,
                        FALSE AS manage_project_access_tokens,
                        FALSE AS manage_security_policy_link,
                        FALSE AS read_code,
                        FALSE AS read_dependency,
                        FALSE AS read_runners,
                        FALSE AS read_vulnerability,
                        FALSE AS remove_project) AS custom_permissions
            GROUP BY
                project_ids.project_id;

The main TL;DR is that this one query can hold all of the LW locks, and it is computationally inefficient because planning requires postgres to read a histogram of JSON objects with every request because it does a filter operating against a JSONB column.

My preliminary ideas to mitigate this:

1. Normalize all of these JSON permissions into a standard many-to-many permissions table. The amount of data involved is not a lot, and the backfill to achieve 100% compatibility would be fast, not even requiring a background migration.
2. Use a prepared statement for this query, and enable prepared statement caching in PgBouncer. This is probably easier, but also much scarier.