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:
- Both workers get
concurrency_limit -> { 10 }, the limit that !264331 (merged) gives the Duo cascade workers. The LLM worker also gets themembershealth deferral the MCP worker already has. Both workers now useif_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. - Behind the
batch_ai_namespace_access_cache_resetflag (gitlab_com_derisk, default off), both workers read members throughAi::HierarchyMemberUserIdsand clear the cache one batch at a time:Gitlab::Database::NamespaceEachBatchwalks the hierarchy 100 namespaces at a time, groups and project namespaces together.- 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. - 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>}'))))) membersIt 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 1Index 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 1001Index 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 100Index 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 100Index 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 1001Index 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 1000Index 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
- https://gitlab.com/gitlab-org/gitlab/-/work_items/635760
- !264331 (merged)
- !264320 (merged) (the rate limit in step 1 of the issue builds on this MR and will follow after it merges)
Screenshots or screen recordings
Not applicable. There is no UI change.
How to set up and validate locally
-
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) -
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.