Simplify instance-level integration propagation batching

Rezrequested to merge
reza/631695-simplify-instance-integration-propagation-batching into master

What does this MR do and why?

Instance-level propagation passes the without_integration anti-join straight into each_batch, so every boundary probe re-runs NOT EXISTS until it finds 10k matches. For projects in the Default organization that's about 5.2 s per probe.

Behind simplified_instance_propagation_batching, this batches over in_organization and lets the workers re-filter each id range, like !234568 (merged) did for group hierarchies. With the flag off the batching is unchanged.

The workers now load the batch once instead of running a LIMIT 1 probe first and then to_a. That's one query less per job, and empty windows can't land on a fast-start LIMIT 1 plan. This part isn't behind the flag.

Trade-off: re-saving an instance integration now enqueues one job per 10k projects/groups in the organization, even if they already have it (about 5,340 + 1,113 for org 1). Most of them find nothing to create and exit after one bounded query.

References

Screenshots or screen recordings

N/A, backend only.

How to set up and validate locally

  1. In the Rails console, run Feature.enable(:simplified_instance_propagation_batching).
  2. Create an instance-level Jira integration in the Admin area.
  3. Check that integrations are created only for projects/groups that didn't have one.

Database

gitlab-production-main, organization 1. All new queries are Index Only Scans on index_projects_on_organization_id_and_id / index_namespaces_on_organization_id_and_id_for_groups. The worker load is a Nested Loop Anti Join with one unique index probe per row, so it never scans Jira rows from the start of the integrations index.

Query Before After
Projects boundary probe ~5.2 s 484 ms (mostly cold reads, 324 heap fetches for 10k rows)
Groups boundary probe (start id 71835418) 3,553 ms 143 ms (75 heap fetches for 10k rows)
Start query, projects / groups not measured 4 ms / 5 ms
MIN/MAX pick, projects / groups not measured 0.2 ms / 0.07 ms
Worker load, projects / groups LIMIT 1 probe + this same query 12.2 s / 3.0 s, this query only

Before numbers are from #631695 (closed). The worker load is the same query BulkCreateService#to_a runs today, and almost all of its time is cold-cache reads.

Start query, projects 
SELECT "projects"."id" FROM "projects" WHERE "projects"."organization_id" = 1 ORDER BY "projects"."id" ASC LIMIT 1;

https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/62841/commands/168053

 Limit  (cost=0.56..0.59 rows=1 width=4) (actual time=3.827..3.828 rows=1 loops=1)
   Buffers: shared hit=3 read=5
   I/O Timings: read=3.749 write=0.000
   ->  Index Only Scan using index_projects_on_organization_id_and_id on public.projects  (cost=0.56..1294733.35 rows=53398676 width=4) (actual time=3.826..3.826 rows=1 loops=1)
         Index Cond: (projects.organization_id = 1)
         Heap Fetches: 0
         Buffers: shared hit=3 read=5
         I/O Timings: read=3.749 write=0.000
Settings: effective_cache_size = '472585MB', work_mem = '230MB', jit = 'off', random_page_cost = '1.5', seq_page_cost = '4'
Query ID: -3906590343970485723
Start query, groups 
SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."organization_id" = 1 ORDER BY "namespaces"."id" ASC LIMIT 1;

https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/62841/commands/168054

 Limit  (cost=0.56..0.59 rows=1 width=4) (actual time=5.119..5.120 rows=1 loops=1)
   Buffers: shared hit=3 read=5
   I/O Timings: read=5.034 write=0.000
   ->  Index Only Scan using index_namespaces_on_organization_id_and_id_for_groups on public.namespaces  (cost=0.56..376795.76 rows=11127717 width=4) (actual time=5.118..5.118 rows=1 loops=1)
         Index Cond: (namespaces.organization_id = 1)
         Heap Fetches: 0
         Buffers: shared hit=3 read=5
         I/O Timings: read=5.034 write=0.000
Settings: seq_page_cost = '4', effective_cache_size = '472585MB', work_mem = '230MB', jit = 'off', random_page_cost = '1.5'
Query ID: -8255785235287734268
Boundary probe, projects 
SELECT "projects"."id" FROM "projects" WHERE "projects"."organization_id" = 1 AND (projects.id >= 40000000) ORDER BY "projects"."id" ASC LIMIT 1 OFFSET 10000;

https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/62841/commands/168055

 Limit  (cost=269.07..269.10 rows=1 width=4) (actual time=483.702..483.704 rows=1 loops=1)
   Buffers: shared hit=9639 read=429 dirtied=134
   WAL: records=142 fpi=134 bytes=1023680
   I/O Timings: read=473.194 write=0.000
   ->  Index Only Scan using index_projects_on_organization_id_and_id on public.projects  (cost=0.56..744325.22 rows=27720962 width=4) (actual time=7.253..483.239 rows=10001 loops=1)
         Index Cond: ((projects.organization_id = 1) AND (projects.id >= 40000000))
         Heap Fetches: 324
         Buffers: shared hit=9639 read=429 dirtied=134
         WAL: records=142 fpi=134 bytes=1023680
         I/O Timings: read=473.194 write=0.000
Settings: seq_page_cost = '4', effective_cache_size = '472585MB', work_mem = '230MB', jit = 'off', random_page_cost = '1.5'
Query ID: -6805742817194606375
Boundary probe, groups 
SELECT "namespaces"."id" FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."organization_id" = 1 AND (namespaces.id >= 71835418) ORDER BY "namespaces"."id" ASC LIMIT 1 OFFSET 10000;

https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/62841/commands/168056

 Limit  (cost=387.69..387.73 rows=1 width=4) (actual time=143.267..143.268 rows=1 loops=1)
   Buffers: shared hit=4082 read=186 dirtied=18
   WAL: records=20 fpi=18 bytes=144122
   I/O Timings: read=139.972 write=0.000
   ->  Index Only Scan using index_namespaces_on_organization_id_and_id_for_groups on public.namespaces  (cost=0.56..200024.24 rows=5166876 width=4) (actual time=11.552..142.843 rows=10001 loops=1)
         Index Cond: ((namespaces.organization_id = 1) AND (namespaces.id >= 71835418))
         Heap Fetches: 75
         Buffers: shared hit=4082 read=186 dirtied=18
         WAL: records=20 fpi=18 bytes=144122
         I/O Timings: read=139.972 write=0.000
Settings: seq_page_cost = '4', effective_cache_size = '472585MB', work_mem = '230MB', jit = 'off', random_page_cost = '1.5'
Query ID: -8004001202366326083
MIN/MAX pick, projects 
SELECT MIN(projects.id), MAX(projects.id) FROM "projects" WHERE "projects"."organization_id" = 1 AND (projects.id >= 40000000) AND (projects.id < 40020000);

https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/62841/commands/168057

 Result  (cost=1.19..1.20 rows=1 width=8) (actual time=0.245..0.246 rows=1 loops=1)
   Buffers: shared hit=13 read=1
   I/O Timings: read=0.139 write=0.000
   InitPlan 1
     ->  Limit  (cost=0.56..0.60 rows=1 width=4) (actual time=0.068..0.068 rows=1 loops=1)
           Buffers: shared hit=9
           I/O Timings: read=0.000 write=0.000
           ->  Index Only Scan using index_projects_on_organization_id_and_id on public.projects  (cost=0.56..375.32 rows=11889 width=4) (actual time=0.067..0.067 rows=1 loops=1)
                 Index Cond: ((projects.organization_id = 1) AND (projects.id >= 40000000) AND (projects.id < 40020000))
                 Heap Fetches: 1
                 Buffers: shared hit=9
                 I/O Timings: read=0.000 write=0.000
   InitPlan 2
     ->  Limit  (cost=0.56..0.60 rows=1 width=4) (actual time=0.174..0.174 rows=1 loops=1)
           Buffers: shared hit=4 read=1
           I/O Timings: read=0.139 write=0.000
           ->  Index Only Scan Backward using index_projects_on_organization_id_and_id on public.projects projects_1  (cost=0.56..375.32 rows=11889 width=4) (actual time=0.174..0.174 rows=1 loops=1)
                 Index Cond: ((projects_1.organization_id = 1) AND (projects_1.id >= 40000000) AND (projects_1.id < 40020000))
                 Heap Fetches: 0
                 Buffers: shared hit=4 read=1
                 I/O Timings: read=0.139 write=0.000
Settings: seq_page_cost = '4', effective_cache_size = '472585MB', work_mem = '230MB', jit = 'off', random_page_cost = '1.5'
Query ID: -3799318338413263752
MIN/MAX pick, groups 
SELECT MIN(namespaces.id), MAX(namespaces.id) FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."organization_id" = 1 AND (namespaces.id >= 71835418) AND (namespaces.id < 71935418);

https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/62841/commands/168058

 Result  (cost=1.22..1.23 rows=1 width=8) (actual time=0.073..0.074 rows=1 loops=1)
   Buffers: shared hit=13
   I/O Timings: read=0.000 write=0.000
   InitPlan 1
     ->  Limit  (cost=0.56..0.61 rows=1 width=4) (actual time=0.049..0.049 rows=1 loops=1)
           Buffers: shared hit=8
           I/O Timings: read=0.000 write=0.000
           ->  Index Only Scan using index_namespaces_on_organization_id_and_id_for_groups on public.namespaces  (cost=0.56..341.24 rows=7108 width=4) (actual time=0.048..0.048 rows=1 loops=1)
                 Index Cond: ((namespaces.organization_id = 1) AND (namespaces.id >= 71835418) AND (namespaces.id < 71935418))
                 Heap Fetches: 0
                 Buffers: shared hit=8
                 I/O Timings: read=0.000 write=0.000
   InitPlan 2
     ->  Limit  (cost=0.56..0.61 rows=1 width=4) (actual time=0.022..0.022 rows=1 loops=1)
           Buffers: shared hit=5
           I/O Timings: read=0.000 write=0.000
           ->  Index Only Scan Backward using index_namespaces_on_organization_id_and_id_for_groups on public.namespaces namespaces_1  (cost=0.56..341.24 rows=7108 width=4) (actual time=0.022..0.022 rows=1 loops=1)
                 Index Cond: ((namespaces_1.organization_id = 1) AND (namespaces_1.id >= 71835418) AND (namespaces_1.id < 71935418))
                 Heap Fetches: 0
                 Buffers: shared hit=5
                 I/O Timings: read=0.000 write=0.000
Settings: seq_page_cost = '4', effective_cache_size = '472585MB', work_mem = '230MB', jit = 'off', random_page_cost = '1.5'
Query ID: -1671751587520921056
Worker load, projects 
SELECT "projects".* FROM "projects" LEFT OUTER JOIN "namespaces" ON "namespaces"."type" = 'Group' AND "namespaces"."id" = "projects"."namespace_id" AND "namespaces"."type" = 'Group' WHERE "projects"."id" BETWEEN 40000000 AND 40020000 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;

https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/62841/commands/168064

 Gather  (cost=1001.98..58755.49 rows=10857 width=745) (actual time=28.926..12233.709 rows=9858 loops=1)
   Workers Planned: 1
   Workers Launched: 1
   Buffers: shared hit=101939 read=23336 dirtied=62
   WAL: records=65 fpi=62 bytes=471886
   I/O Timings: read=23987.650 write=0.000
   ->  Nested Loop Anti Join  (cost=1.98..56669.79 rows=6386 width=745) (actual time=24.069..12178.187 rows=4929 loops=2)
         Buffers: shared hit=101939 read=23336 dirtied=62
         WAL: records=65 fpi=62 bytes=471886
         I/O Timings: read=23987.650 write=0.000
         ->  Nested Loop Left Join  (cost=1.69..52800.21 rows=6434 width=773) (actual time=22.742..12106.087 rows=4960 loops=2)
               Buffers: shared hit=85211 read=23228 dirtied=62
               WAL: records=65 fpi=62 bytes=471886
               I/O Timings: read=23901.721 write=0.000
               ->  Nested Loop Anti Join  (cost=1.12..29907.95 rows=6434 width=745) (actual time=13.715..6325.206 rows=4960 loops=2)
                     Buffers: shared hit=47149 read=11687 dirtied=40
                     WAL: records=41 fpi=40 bytes=295780
                     I/O Timings: read=12471.073 write=0.000
                     ->  Parallel Index Scan using idx_projects_on_repository_storage_last_repository_updated_at on public.projects  (cost=0.56..16334.39 rows=6763 width=745) (actual time=5.301..6240.640 rows=5737 loops=2)
                           Index Cond: ((projects.id >= 40000000) AND (projects.id <= 40020000))
                           Filter: ((NOT projects.pending_delete) AND (NOT projects.archived) AND (projects.organization_id = 1))
                           Rows Removed by Filter: 305
                           Buffers: shared hit=640 read=11626 dirtied=38
                           WAL: records=39 fpi=38 bytes=279646
                           I/O Timings: read=12399.233 write=0.000
                     ->  Index Only Scan using index_integrations_on_project_id_and_type_new_unique on public.integrations  (cost=0.56..2.01 rows=1 width=4) (actual time=0.012..0.012 rows=0 loops=11474)
                           Index Cond: ((integrations.project_id = projects.id) AND (integrations.type_new = 'Integrations::Jira'::text))
                           Heap Fetches: 18
                           Buffers: shared hit=46509 read=61 dirtied=2
                           WAL: records=2 fpi=2 bytes=16134
                           I/O Timings: read=71.839 write=0.000
               ->  Index Scan using namespaces_pkey on public.namespaces  (cost=0.57..3.56 rows=1 width=32) (actual time=1.164..1.164 rows=1 loops=9920)
                     Index Cond: (namespaces.id = projects.namespace_id)
                     Filter: ((namespaces.type)::text = 'Group'::text)
                     Buffers: shared hit=38061 read=11541 dirtied=21
                     WAL: records=23 fpi=21 bytes=168637
                     I/O Timings: read=11430.648 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.013..0.013 rows=0 loops=9920)
               Index Cond: (namespace_settings.namespace_id = ANY (namespaces.traversal_ids))
               Heap Fetches: 2
               Buffers: shared hit=16728 read=108
               I/O Timings: read=85.929 write=0.000
Settings: seq_page_cost = '4', effective_cache_size = '472585MB', work_mem = '230MB', jit = 'off', random_page_cost = '1.5'
Query ID: -6054932044437869409
Worker load, groups 
SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" BETWEEN 71835418 AND 71935418 AND (NOT EXISTS (SELECT 1 FROM "integrations" WHERE (integrations.group_id = namespaces.id) AND "integrations"."type_new" = 'Integrations::Jira')) AND "namespaces"."organization_id" = 1;

https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/62841/commands/168065

 Gather  (cost=1001.12..15086.77 rows=7074 width=366) (actual time=8.497..3024.935 rows=8292 loops=1)
   Workers Planned: 1
   Workers Launched: 1
   Buffers: shared hit=37362 read=6721 dirtied=4
   WAL: records=6 fpi=4 bytes=32044
   I/O Timings: read=5934.635 write=0.000
   ->  Nested Loop Anti Join  (cost=1.12..13379.37 rows=4161 width=366) (actual time=5.533..3008.845 rows=4146 loops=2)
         Buffers: shared hit=37362 read=6721 dirtied=4
         WAL: records=6 fpi=4 bytes=32044
         I/O Timings: read=5934.635 write=0.000
         ->  Parallel Index Scan using index_namespaces_on_organization_id_and_id_for_groups on public.namespaces  (cost=0.56..5240.32 rows=4181 width=366) (actual time=0.807..2959.833 rows=4392 loops=2)
               Index Cond: ((namespaces.organization_id = 1) AND (namespaces.id >= 71835418) AND (namespaces.id <= 71935418))
               Buffers: shared hit=1903 read=6692
               I/O Timings: read=5882.257 write=0.000
         ->  Index Only Scan using index_integrations_on_unique_group_id_and_type_new on public.integrations  (cost=0.56..1.95 rows=1 width=8) (actual time=0.010..0.010 rows=0 loops=8784)
               Index Cond: ((integrations.group_id = namespaces.id) AND (integrations.type_new = 'Integrations::Jira'::text))
               Heap Fetches: 11
               Buffers: shared hit=35459 read=29 dirtied=4
               WAL: records=6 fpi=4 bytes=32044
               I/O Timings: read=52.379 write=0.000
Settings: work_mem = '230MB', jit = 'off', random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB'
Query ID: -6374369254462350497

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist.

Related to #631695 (closed)

Edited by Rez

Merge request reports