Batch the member scan in AI namespace access cache resets

What does this MR do and why?

Mcp::NamespaceAccessCacheResetWorker and Llm::NamespaceAccessCacheResetWorker run when mcp_server_enabled or experiment_features_enabled changes on a group. Each job reads the user ID of every member of the group hierarchy in one query, then clears those users' access cache keys.

The MR makes two changes:

  1. Both workers get concurrency_limit -> { 10 }, the limit that !264331 (merged) gives the Duo cascade workers. The LLM worker also gets the members health deferral the MCP worker already has. Both workers now use if_deduplicated: :reschedule_once, so a setting change that arrives while a job is running runs again after it instead of being dropped. This part is not behind a flag.
  2. Behind the batch_ai_namespace_access_cache_reset flag (gitlab_com_derisk, default off), both workers read members through Ai::HierarchyMemberUserIds and clear the cache one batch at a time:
    1. Gitlab::Database::NamespaceEachBatch walks the hierarchy 100 namespaces at a time, groups and project namespaces together.
    2. For each slice, one query reads up to 1,001 member user IDs by member_namespace_id. If it gets 1,000 or fewer, that is the whole slice.
    3. If it gets 1,001, the slice falls back to reading each group's and project's members on their own. Each source is read with one query of up to 1,001 rows, and only a source with more than 1,000 members goes through each_batch(of: 1000).

With the flag on, the workers also clear the cache of blocked and minimal access group members, which the old query skipped. Clearing a cache key for those users only means their next request recomputes it.

The LLM worker handles Members::AddedCloudEvent the same way as before. Those jobs now share the concurrency limit and the health deferral.

Why the fallback reads each source on its own

each_batch over member_namespace_id IN (...) can't use an index for id order, because index_members_on_member_namespace_id_compound has type and requested_at before id. Every batch boundary query then sorts all members of the slice. For a namespace with M members, that is about M²/1,000 rows read.

Filtering one source by source_id, source_type and type uses index_members_on_source_and_type_and_id, which returns rows in id order. Each boundary query then reads at most 1,001 rows.

Database

Plans are from gitlab-production-main on Database Lab, using the gitlab-org hierarchy (about 10,800 namespaces).

Before, the old union read every member of the hierarchy in one query:

Old union query
SELECT "members"."user_id" FROM ((SELECT "members"."user_id" FROM "members" WHERE "members"."type" = 'ProjectMember' AND "members"."source_type" = 'Project' AND "members"."source_id" IN (SELECT "projects"."id" FROM "projects" WHERE "projects"."namespace_id" IN (SELECT "namespaces"."id" FROM UNNEST(
  COALESCE(
    (SELECT ids FROM (SELECT "namespace_descendants"."self_and_descendant_group_ids" AS ids FROM "namespace_descendants" WHERE "namespace_descendants"."outdated_at" IS NULL AND "namespace_descendants"."namespace_id" = <group id>) cached_query),
    (SELECT ids FROM (SELECT ARRAY_AGG("namespaces"."id") AS ids FROM (SELECT namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)] AS id FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{<group id>}'))) namespaces) consistent_query))
) AS namespaces(id)
)) AND "members"."requested_at" IS NULL AND "members"."invite_token" IS NULL)
UNION
(SELECT "members"."user_id" FROM "members" LEFT OUTER JOIN "users" ON "users"."id" = "members"."user_id" WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "users"."state" = 'active' AND "members"."state" = 0 AND "members"."requested_at" IS NULL AND "members"."invite_token" IS NULL AND (members.access_level > 5) AND "members"."source_id" IN (SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND (traversal_ids @> ('{<group id>}'))))) members

It sorted 152,352 member rows down to 13,838 users, with about 226,500 buffers (1.7 GiB) and 56 s on the clone. A second run by the database reviewer took 84 s.

Plan: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/77358/commands/185104

Namespace walk, from Gitlab::Database::NamespaceEachBatch, run once per 100 namespaces:

Walk query
SELECT current_id, depth, ids, count, index FROM (WITH RECURSIVE "result" AS ((SELECT <group id>::bigint AS current_id, ARRAY[<group id>]::bigint[] AS depth, ARRAY[<group id>]::bigint[] AS ids, 1::bigint AS count, 0::bigint AS index FROM (VALUES (1)) AS initializer_row WHERE (EXISTS (SELECT 1 FROM "namespaces" WHERE "namespaces"."id" = <group id>)))
UNION ALL
(WITH "cte" AS MATERIALIZED (SELECT result.* FROM result) SELECT current_id, depth, ids, count, index FROM ((SELECT "namespaces"."id" AS current_id, cte.depth || "namespaces".id::bigint AS depth, cte.ids || "namespaces".id::bigint AS ids, cte.count + 1 AS count, 1::bigint AS index FROM cte, LATERAL (SELECT "namespaces"."id" FROM "namespaces" WHERE (parent_id = cte.current_id) ORDER BY "namespaces"."id" ASC LIMIT 1) "namespaces")
UNION ALL
(SELECT "namespaces"."id" AS current_id, cte.depth[:array_length(cte.depth, 1) - 1] || "namespaces".id::bigint AS depth, cte.ids || "namespaces".id::bigint AS ids, cte.count + 1 AS count, 2::bigint AS index FROM cte, LATERAL (SELECT "namespaces"."id" FROM "namespaces" WHERE ("namespaces".parent_id = cte.depth[array_length(cte.depth, 1) - 1]) AND ("namespaces".id > cte.depth[array_length(cte.depth, 1)]) ORDER BY "namespaces"."id" ASC LIMIT 1) "namespaces")
UNION ALL
(SELECT cte.current_id AS current_id, cte.depth[:array_length(cte.depth, 1) - 1] AS depth, cte.ids AS ids, cte.count + 1 AS count, 3::bigint AS index FROM cte WHERE (cte.depth <> '{}') LIMIT 1)) namespaces ORDER BY "namespaces"."index" ASC LIMIT 1)) SELECT current_id, depth, ids, count, index FROM "result" AS "namespaces" LIMIT 101) namespaces ORDER BY "count" DESC LIMIT 1

Index Only Scan on namespaces_pkey and index_namespaces_on_parent_id_and_id, no sequential scans, 874 buffers, 180 ms for the first 100 namespaces of gitlab-org.

Plan: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/77358/commands/185105

Slice query:

SELECT "members"."user_id" FROM "members"
WHERE "members"."member_namespace_id" IN (<100 namespace ids>)
  AND "members"."requested_at" IS NULL
  AND "members"."invite_token" IS NULL
LIMIT 1001

Index Scan using index_members_on_member_namespace_id_compound, stops at 1,001 rows, 987 buffers, 1.5 s cold and 52 ms warm. The plan used gitlab-org, gitlab-org/gitlab, gitlab-org/gitlab-foss and 97 gitlab-org subgroups as the 100 namespaces.

Plan: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/77358/commands/185106

When a slice goes over 1,000 members, two lookups find the groups and projects in it:

SELECT "namespaces"."id" FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
  AND "namespaces"."id" IN (<100 namespace ids>)
LIMIT 100

Index Only Scan using index_namespaces_on_type_and_id, 100 rows, 427 buffers, 111 ms.

Plan: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/77358/commands/185107

SELECT "projects"."id" FROM "projects"
WHERE "projects"."project_namespace_id" IN (<100 namespace ids>)
LIMIT 100

Index Scan using index_projects_on_project_namespace_id (unique), 100 rows, 217 buffers, 210 ms. Project namespace IDs aren't exposed in the API, so the plan takes the namespace IDs of 100 gitlab-org projects from a subquery (project_namespace_id = ANY(ARRAY(SELECT project_namespace_id FROM projects WHERE namespace_id = <group id> ORDER BY id LIMIT 100))). The outer scan is the same as in the query above.

Plan: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/77358/commands/185108

Per-source probe, run for each group and project in a slice that went over 1,000 members:

SELECT "members"."user_id" FROM "members"
WHERE "members"."type" = 'GroupMember'
  AND "members"."source_type" = 'Namespace'
  AND "members"."source_id" = <group id>
  AND "members"."requested_at" IS NULL
  AND "members"."invite_token" IS NULL
LIMIT 1001

Index Only Scan using index_members_on_source_state_type_access_level_and_user_id, stops at 1,001 rows, 913 buffers, 69 ms.

Plan: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/77358/commands/185109

Fallback batch boundary for a source with more than 1,000 members (the each_batch query):

SELECT "members"."id" FROM "members"
WHERE "members"."type" = 'GroupMember'
  AND "members"."source_type" = 'Namespace'
  AND "members"."source_id" = <group id>
  AND "members"."requested_at" IS NULL
  AND "members"."invite_token" IS NULL
  AND "members"."id" >= <member id>
ORDER BY "members"."id" ASC LIMIT 1 OFFSET 1000

Index Scan using index_members_on_source_and_type_and_id, no sort, 1,001 rows, 988 buffers, 11 ms warm and 64 ms cold. The ProjectMember version uses the same index. It only runs for a project with more than 1,000 direct members, and gitlab-org/gitlab has fewer, so its plan returns no row (264 buffers, 402 ms cold).

Plan: https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/77358/commands/185110

For comparison, the same boundary query by member_namespace_id sorted 2,774 rows for one group. That is the cost that grows with the size of the namespace.

References

Screenshots or screen recordings

Not applicable. There is no UI change.

How to set up and validate locally

  1. In the Rails console, enable the flag and seed a cache key for a member of a group:

    Feature.enable(:batch_ai_namespace_access_cache_reset)
    group = Group.find_by_full_path('flightjs')
    user = group.members.first.user
    key = ['users', user.id, User::GROUP_WITH_MCP_SERVER_ENABLED_CACHE_KEY]
    Rails.cache.write(key, true)
  2. Run the worker and check that the key is gone:

    Mcp::NamespaceAccessCacheResetWorker.new.handle_event(
      Mcp::ServerSettingsChangedEvent.new(data: { group_id: group.id })
    )
    Rails.cache.read(key) # => nil

MR acceptance checklist

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

Edited by Raounak Sharma

Merge request reports

Loading
Loading