Optimize foundational flows sync with batched bulk inserts

Alper Akgunrequested to merge
duo-edit-20260203-232832 into master

Summary

This MR optimizes the Ai::Catalog::Flows::CascadeSyncFoundationalFlowsWorker Sidekiq job performance by implementing batch processing for syncing foundational flows to projects in large group hierarchies.

When cascading foundational flow settings in large groups, the worker processes each project individually with separate database queries, causing timeouts before completing the sync operation. This prevents settings from being properly propagated to all subgroups and projects.

Introduces a new SyncBatchFoundationalFlowsService that processes projects in batches with optimized database operations:

  • Adds sync_projects_optimized method behind Feature flag: optimized_foundational_flows_sync (disabled by default and group based)
  • Pre-fetches, Pre-loads as much as possible for performance and and to reduces N+1 queries by loading all required data upfront
  • Uses bulk insert_all for members, item consumers, and flow triggers
  • Tracks existing records in memory to avoid duplicate inserts

Speedup: 20x faster

References

Risks ⏰

insert_all for members and project_authorizations can be risky. I'm still verifying

How to test

  1. Enable all foundational flows and see service accounts, members and flows are in the project
  2. Go to an issue and try "issue to MR" and see if it works http://gdk.test:3000/gitlab-duo/test/-/issues/1
  3. Disable the flows and see they are gone. Also disable only a few and disable only a few and see what happens

Query plans

⌚ We want this MR merged because: By optimizing with pg.ai we came to a batch size of 100. We shall test this on a single very large namespace with 20K projects and observe error logs. If things don't work - we shall revisit query plans and batch size.

SELECTS from project/project_settings.

INSERTs into project_authorizations/members

Acceptance Criteria

[x] All

Edited by Alper Akgun

Merge request reports