Scope instance integration propagation to organization (!258752) · Merge requests · GitLab.org / GitLab

What does this MR do and why?

Saving an instance-level integration default runs Integrations::PropagateService, which calls Project.without_integration and Group.without_integration to find every project/group in the cell that doesn't yet have the integration. Neither scope filtered by organization, so saving a default in organization A created or updated integration rows for every project and group in the cell, including organization B.

This MR scopes both class methods to integration.organization_id when the integration is instance-level:

Project.without_integration (app/models/project.rb)
Group.without_integration (app/models/group.rb)

Group- and project-level integrations are unaffected — they're already scoped correctly via the group hierarchy (integration.group.descendants / self_and_ancestors), which can't cross organizations.

PropagateIntegrationGroupWorker and PropagateIntegrationProjectWorker call these same two methods to re-verify their batch, so they inherit the fix without any code changes — I added a one-line comment at each call site pointing at where the organization scoping actually happens.

No changes were needed in Integrations::Propagation::BulkCreateService / BulkUpdateService: Integration#to_database_hash already excludes organization_id from what it copies onto project/group integration rows (BASE_ATTRIBUTES in app/models/concerns/integrations/base/integration.rb).

New/changed queries

Both changed scopes add a single WHERE organization_id = ? predicate, combined via the existing in_organization scope. Both projects.organization_id and namespaces.organization_id already have covering indexes (index_projects_on_organization_id_and_id, index_namespaces_on_organization_id_and_id_for_groups), so I'd like a second pair of eyes on the plans before/after via postgres.ai. Raw SQL below (substitute <organization_id>, <type>, and the id range for real values).

1. `Project.without_integration` (used directly in `PropagateService`, unbounded id range)

This exact query never executes standalone in production because create_integration_for_projects_without_integration wraps it in each_batch(of: 10_000). See the bounded form shown in query #3 (closed) for a query plan on this.

SELECT "projects".*
FROM "projects"
LEFT OUTER JOIN "namespaces"
  ON "namespaces"."type" = 'Group'
  AND "namespaces"."id" = "projects"."namespace_id"
WHERE (NOT EXISTS (
        SELECT 1 FROM "integrations"
        WHERE integrations.project_id = projects.id
          AND "integrations"."type_new" = 'Integrations::Jira'
      ))
  AND "projects"."pending_delete" = FALSE
  AND "projects"."archived" = FALSE
  AND NOT (EXISTS (
        SELECT 1 FROM "namespace_settings"
        WHERE "namespace_settings"."namespace_id" = ANY (namespaces.traversal_ids)
          AND "namespace_settings"."archived" = TRUE
      ))
  AND "projects"."organization_id" = 1;

Plan:

Gather  (cost=240957.33..24490087.31 rows=48495565 width=756)
  Workers Planned: 2
  ->  Nested Loop Anti Join  (cost=239957.33..19639530.81 rows=20206485 width=756)
        ->  Nested Loop Left Join  (cost=239957.04..7337443.17 rows=20378369 width=784)
              ->  Parallel Hash Anti Join  (cost=239956.46..5871445.88 rows=20378369 width=756)
                    Hash Cond: (projects.id = integrations.project_id)
                    ->  Parallel Index Scan using index_projects_on_namespace_id_and_repository_size_limit on projects  (cost=0.56..5263519.56 rows=21432593 width=756)
                          Filter: ((NOT pending_delete) AND (NOT archived) AND (organization_id = 1))
                    ->  Parallel Hash  (cost=224094.45..224094.45 rows=1268916 width=4)
                          ->  Parallel Index Only Scan using index_integrations_on_project_id_and_type_new_unique on integrations  (cost=0.56..224094.45 rows=1268916 width=4)
                                Index Cond: (type_new = 'Integrations::Jira'::text)
              ->  Memoize  (cost=0.58..0.67 rows=1 width=32)
                    Cache Key: projects.namespace_id
                    Cache Mode: logical
                    ->  Index Scan using namespaces_pkey on namespaces  (cost=0.57..0.66 rows=1 width=32)
                          Index Cond: (id = projects.namespace_id)
                          Filter: ((type)::text = 'Group'::text)
        ->  Index Only Scan using index_namespace_settings_on_namespace_id_where_archived_true on namespace_settings  (cost=0.29..0.60 rows=1 width=4)
              Index Cond: (namespace_id = ANY (namespaces.traversal_ids))

https://postgres.ai/console/gitlab/gitlab-production-main/sessions/60042/commands/165046

2. Group.without_integration (used directly in PropagateService, unbounded id range)

Again, this exact query never executes standalone in production because create_integration_for_groups_without_integration wraps it in each_batch(of: 10_000). See the bounded form shown in query #4 (closed) for a query plan on this.

SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
  AND (NOT EXISTS (
        SELECT 1 FROM "integrations"
        WHERE integrations.group_id = namespaces.id
          AND "integrations"."type_new" = 'Integrations::Jira'
      ))
  AND "namespaces"."organization_id" = 1;

Plan:

Merge Anti Join  (cost=1.12..2285410.85 rows=11097649 width=367)
  Merge Cond: (namespaces.id = integrations.group_id)
  ->  Index Scan using index_namespaces_on_organization_id_and_id_for_groups on namespaces  (cost=0.56..2233349.24 rows=11150180 width=367)
        Index Cond: (organization_id = 1)
  ->  Index Only Scan using index_integrations_on_unique_group_id_and_type_new on integrations  (cost=0.56..160150.79 rows=3045399 width=8)
        Index Cond: (type_new = 'Integrations::Jira'::text)

https://postgres.ai/console/gitlab/gitlab-production-main/sessions/60042/commands/165049

3. PropagateIntegrationProjectWorker#perform batch query (id-bounded)

SELECT "projects".*
FROM "projects"
LEFT OUTER JOIN "namespaces"
  ON "namespaces"."type" = 'Group'
  AND "namespaces"."id" = "projects"."namespace_id"
WHERE "projects"."id" BETWEEN 1 AND 87114989
  AND (NOT EXISTS (
        SELECT 1 FROM "integrations"
        WHERE integrations.project_id = projects.id
          AND "integrations"."type_new" = 'Integrations::Jira'
      ))
  AND "projects"."pending_delete" = FALSE
  AND "projects"."archived" = FALSE
  AND NOT (EXISTS (
        SELECT 1 FROM "namespace_settings"
        WHERE "namespace_settings"."namespace_id" = ANY (namespaces.traversal_ids)
          AND "namespace_settings"."archived" = TRUE
      ))
  AND "projects"."organization_id" = 1;

Plan:

Gather  (cost=246923.89..24425345.51 rows=48457164 width=749)
  Workers Planned: 2
  ->  Nested Loop Anti Join  (cost=245923.89..19578629.11 rows=20190485 width=749)
        ->  Nested Loop Left Join  (cost=245923.60..7359242.76 rows=20362232 width=777)
              ->  Parallel Hash Anti Join  (cost=245923.02..5880271.61 rows=20362232 width=749)
                    Hash Cond: (projects.id = integrations.project_id)
                    ->  Parallel Index Scan using index_projects_on_namespace_id_and_repository_size_limit on projects  (cost=0.56..5373362.68 rows=21405775 width=749)
                          Filter: ((NOT pending_delete) AND (NOT archived) AND (id >= 1) AND (id <= 87114989) AND (organization_id = 1))
                    ->  Parallel Hash  (cost=230181.66..230181.66 rows=1259264 width=4)
                          ->  Parallel Index Only Scan using index_integrations_on_project_id_and_type_new_unique on integrations  (cost=0.56..230181.66 rows=1259264 width=4)
                                Index Cond: (type_new = 'Integrations::Jira'::text)
              ->  Memoize  (cost=0.58..0.67 rows=1 width=32)
                    Cache Key: projects.namespace_id
                    Cache Mode: logical
                    ->  Index Scan using namespaces_pkey on namespaces  (cost=0.57..0.66 rows=1 width=32)
                          Index Cond: (id = projects.namespace_id)
                          Filter: ((type)::text = 'Group'::text)
        ->  Index Only Scan using index_namespace_settings_on_namespace_id_where_archived_true on namespace_settings  (cost=0.29..0.60 rows=1 width=4)
              Index Cond: (namespace_id = ANY (namespaces.traversal_ids))

https://postgres.ai/console/gitlab/gitlab-production-main/sessions/60477/commands/165595

4. PropagateIntegrationGroupWorker#perform batch query, instance-level branch (id-bounded)

SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."id" BETWEEN 2244 AND 143670836
  AND "namespaces"."type" = 'Group'
  AND (NOT EXISTS (
        SELECT 1 FROM "integrations"
        WHERE integrations.group_id = namespaces.id
          AND "integrations"."type_new" = 'Integrations::Jira'
      ))
  AND "namespaces"."organization_id" = 1;

Plan:

Merge Anti Join  (cost=1.12..2341135.89 rows=11097366 width=367)
  Merge Cond: (namespaces.id = integrations.group_id)
  ->  Index Scan using index_namespaces_on_organization_id_and_id_for_groups on namespaces  (cost=0.56..2289075.00 rows=11149896 width=367)
        Index Cond: ((organization_id = 1) AND (id >= 2244) AND (id <= 143670836))
  ->  Index Only Scan using index_integrations_on_unique_group_id_and_type_new on integrations  (cost=0.56..160150.79 rows=3045399 width=8)
        Index Cond: (type_new = 'Integrations::Jira'::text)

https://postgres.ai/console/gitlab/gitlab-production-main/sessions/60042/commands/165048

5. `each_batch` boundary probe for `Project.without_integration` (internal query, runs once per ~10,000-row batch)

This is the each_batch boundary-discovery probe, issued once per ~10,000-row batch while walking Project.without_integration. The plan shows an actual execution time of 5.2 seconds for a single probe; It's roughly 4,850 probes for the organization used (org_id = 1), so potentially multiple hours of cumulative boundary-discovery time alone, before any integration rows are created. This is pre-existing each_batch behavior, not caused by this MR's organization_id predicate. An issue for this is tracked separately in #631695 (closed).

SELECT "projects"."id"
FROM "projects"
LEFT OUTER JOIN "namespaces"
  ON "namespaces"."type" = 'Group'
  AND "namespaces"."id" = "projects"."namespace_id"
WHERE "projects"."id" >= 1
  AND (NOT EXISTS (
        SELECT 1 FROM "integrations"
        WHERE integrations.project_id = projects.id
          AND "integrations"."type_new" = 'Integrations::Jira'
      ))
  AND "projects"."pending_delete" = FALSE
  AND "projects"."archived" = FALSE
  AND NOT (EXISTS (
        SELECT 1 FROM "namespace_settings"
        WHERE "namespace_settings"."namespace_id" = ANY (namespaces.traversal_ids)
          AND "namespace_settings"."archived" = TRUE
      ))
  AND "projects"."organization_id" = 1
ORDER BY "projects"."id" ASC
OFFSET 10000
LIMIT 1;

Plan:

Limit  (cost=6364.04..6364.58 rows=1 width=4) (actual time=5230.449..5239.873 rows=1 loops=1)
   Buffers: shared hit=34876 read=15169 dirtied=3
   WAL: records=3 fpi=3 bytes=22367
   I/O Timings: read=11990.247 write=0.000
   ->  Gather Merge  (cost=1003.00..25999705.42 rows=48495565 width=4) (actual time=778.860..5239.355 rows=10001 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=34876 read=15169 dirtied=3
         WAL: records=3 fpi=3 bytes=22367
         I/O Timings: read=11990.247 write=0.000
         ->  Nested Loop Anti Join  (cost=2.97..20401113.89 rows=20206485 width=4) (actual time=33.555..4096.323 rows=3724 loops=3)
               Buffers: shared hit=34876 read=15169 dirtied=3
               WAL: records=3 fpi=3 bytes=22367
               I/O Timings: read=11990.247 write=0.000
               ->  Nested Loop Left Join  (cost=2.69..8099026.25 rows=20378369 width=32) (actual time=33.541..4088.477 rows=3724 loops=3)
                     Buffers: shared hit=29816 read=15169 dirtied=3
                     WAL: records=3 fpi=3 bytes=22367
                     I/O Timings: read=11990.247 write=0.000
                     ->  Merge Anti Join  (cost=2.11..6633028.97 rows=20378369 width=8) (actual time=6.449..2566.112 rows=3724 loops=3)
                           Merge Cond: (projects.id = integrations.project_id)
                           Buffers: shared hit=1241 read=9727 dirtied=3
                           WAL: records=3 fpi=3 bytes=22367
                           I/O Timings: read=7617.674 write=0.000
                           ->  Parallel Index Scan using index_projects_on_id_and_archived_and_pending_delete on public.projects  (cost=0.56..6354625.73 rows=21432593 width=8) (actual time=4.952..2560.102 rows=3725 loops=3)
                                 Index Cond: (projects.id >= 1)
                                 Filter: (projects.organization_id = 1)
                                 Buffers: shared hit=1156 read=9691 dirtied=3
                                 WAL: records=3 fpi=3 bytes=22367
                                 I/O Timings: read=7608.339 write=0.000
                           ->  Index Only Scan using index_integrations_on_project_id_and_type_new_unique on public.integrations  (cost=0.56..241859.27 rows=3045399 width=4) (actual time=1.494..3.378 rows=4 loops=3)
                                 Index Cond: (integrations.type_new = 'Integrations::Jira'::text)
                                 Heap Fetches: 0
                                 Buffers: shared hit=85 read=36
                                 I/O Timings: read=9.335 write=0.000
                     ->  Memoize  (cost=0.58..0.67 rows=1 width=32) (actual time=0.408..0.408 rows=0 loops=11172)
                           Cache Key: projects.namespace_id
                           Cache Mode: logical
                           Hits: 579  Misses: 1193  Evictions: 0  Overflows: 0  Memory Usage: 91kB
                           Buffers: shared hit=28575 read=5442
                           I/O Timings: read=4372.573 write=0.000
                           ->  Index Scan using namespaces_pkey on public.namespaces  (cost=0.57..0.66 rows=1 width=32) (actual time=0.655..0.655 rows=0 loops=6803)
                                 Index Cond: (namespaces.id = projects.namespace_id)
                                 Filter: ((namespaces.type)::text = 'Group'::text)
                                 Rows Removed by Filter: 1
                                 Buffers: shared hit=28575 read=5442
                                 I/O Timings: read=4372.573 write=0.000
               ->  Index Only Scan using index_namespace_settings_on_namespace_id_where_archived_true on public.namespace_settings  (cost=0.29..0.60 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=11172)
                     Index Cond: (namespace_settings.namespace_id = ANY (namespaces.traversal_ids))
                     Heap Fetches: 0
                     Buffers: shared hit=5060
                     I/O Timings: read=0.000 write=0.000
Settings: jit = 'off', random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB', work_mem = '230MB'
Query ID: 5995477430991377694

https://postgres.ai/console/gitlab/gitlab-production-main/sessions/60042/commands/165111

6. `each_batch` boundary probe for `Group.without_integration` (internal query, runs once per ~10,000-row batch)

Cost per probe depends on where the batch starts. It is cheap at the bottom of the id range (84 ms) and about 3.5 s at the midpoint. This is pre-existing each_batch behavior, not caused by this MR's organization_id predicate. Tracked in #631695 (closed).

SELECT "namespaces"."id"
FROM "namespaces"
WHERE "namespaces"."id" >= 71835418
  AND "namespaces"."type" = 'Group'
  AND (NOT EXISTS (
        SELECT 1 FROM "integrations"
        WHERE integrations.group_id = namespaces.id
          AND "integrations"."type_new" = 'Integrations::Jira'
      ))
  AND "namespaces"."organization_id" = 1
ORDER BY "namespaces"."id" ASC
OFFSET 10000
LIMIT 1;

Plan:

 Limit  (cost=492.13..492.18 rows=1 width=4) (actual time=3553.362..3553.364 rows=1 loops=1)
   Buffers: shared hit=118513 read=6842 dirtied=6
   WAL: records=6 fpi=6 bytes=39342
   I/O Timings: read=3463.264 write=0.000
   ->  Merge Anti Join  (cost=1.12..253129.92 rows=5155251 width=4) (actual time=3450.649..3552.949 rows=10001 loops=1)
         Merge Cond: (namespaces.id = integrations.group_id)
         Buffers: shared hit=118513 read=6842 dirtied=6
         WAL: records=6 fpi=6 bytes=39342
         I/O Timings: read=3463.264 write=0.000
         ->  Index Only Scan using index_namespaces_on_organization_id_and_id_for_groups on public.namespaces  (cost=0.56..215286.12 rows=5179614 width=4) (actual time=3.234..90.462 rows=10616 loops=1)
               Index Cond: ((namespaces.organization_id = 1) AND (namespaces.id >= 71835418))
               Heap Fetches: 157
               Buffers: shared hit=4459 read=179
               I/O Timings: read=87.583 write=0.000
         ->  Index Only Scan using index_integrations_on_unique_group_id_and_type_new on public.integrations  (cost=0.56..166081.48 rows=3022233 width=8) (actual time=0.016..3449.940 rows=173212 loops=1)
               Index Cond: (integrations.type_new = 'Integrations::Jira'::text)
               Heap Fetches: 3512
               Buffers: shared hit=114054 read=6663 dirtied=6
               WAL: records=6 fpi=6 bytes=39342
               I/O Timings: read=3375.681 write=0.000
Settings: random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB', work_mem = '230MB', jit = 'off'
Query ID: 6659879826453780072

https://console.postgres.ai/gitlab/joe-instances/255#:~:text=https%3A//postgres.ai/console/gitlab/gitlab%2Dproduction%2Dmain/sessions/60477/commands/165621

Related to #631288 (closed)

Edited Oct 02, 2026 by Carla Drago

Scope instance integration propagation to organization

What does this MR do and why?

New/changed queries

1. Project.without_integration (used directly in PropagateService, unbounded id range)

2. Group.without_integration (used directly in PropagateService, unbounded id range)

3. PropagateIntegrationProjectWorker#perform batch query (id-bounded)

4. PropagateIntegrationGroupWorker#perform batch query, instance-level branch (id-bounded)

5. each_batch boundary probe for Project.without_integration (internal query, runs once per ~10,000-row batch)

6. each_batch boundary probe for Group.without_integration (internal query, runs once per ~10,000-row batch)

Merge request reports

1. `Project.without_integration` (used directly in `PropagateService`, unbounded id range)

5. `each_batch` boundary probe for `Project.without_integration` (internal query, runs once per ~10,000-row batch)

6. `each_batch` boundary probe for `Group.without_integration` (internal query, runs once per ~10,000-row batch)