Integration settings not propagated to projects under a large group tree

Problem

After shipping the optimized project propagation query in !220587 (merged), a different bottleneck is still causing statement timeouts in the same code path.

The fix in !220587 (merged) replaced the per-row correlated NOT EXISTS on namespace_settings with the && array overlap operator. That fixed the original bottleneck, but each_batch re-executes the full query on every batch boundary lookup, and the combined query now hits a nested loop on the IN (subquery) join between projects and the namespace subquery.

Where it happens

app/services/integrations/propagate_service.rb:84 - propagate_integrations wraps the relation with each_batch(of: 10_000):

relation.each_batch(of: BATCH_SIZE) do |records|
  min_id, max_id = records.pick(...)
  worker_class.perform_async(integration.id, min_id, max_id)
end

The relation for the project path is:

Project.without_integration_excluding_ancestor_archived_check(integration)
  .in_namespace(integration.group.self_and_descendants.excluding_self_and_ancestors_archived)

This generates:

SELECT projects.id FROM projects
WHERE NOT EXISTS (
  SELECT 1 FROM integrations
  WHERE integrations.project_id = projects.id
    AND integrations.type_new = '<type>'
)
AND projects.pending_delete = false
AND projects.archived = false
AND projects.namespace_id IN (
  SELECT namespaces.id FROM namespaces
  WHERE traversal_ids @> '{<group_id>}'
    AND NOT (namespaces.traversal_ids::bigint[] && ARRAY(
      SELECT namespace_id FROM namespace_settings WHERE archived = true
    )::bigint[])
)
ORDER BY projects.id ASC
OFFSET 10000 LIMIT 1

each_batch fires this boundary query repeatedly. For each iteration the planner evaluates the full IN (subquery) with the traversal_ids + array overlap filter, and the NOT EXISTS on integrations. On large hierarchies (~6,200 projects under ~1,364 groups) this produces a nested loop join that exceeds the 15s statement timeout.

The group propagation path (create_integration_for_groups_without_integration_belonging_to_group) has the same problem with integration.group.descendants.without_integration(integration) wrapped in each_batch.

Related

Edited by 🤖 GitLab Bot 🤖