Fix SyncPolicyWorker timeout on linked root namespaces

What does this MR do and why?

Improves the query used by ComplianceFrameworks::SyncService#linked_source_root_namespaces to resolve the set of root namespaces for all OrchestrationPolicyConfigurations that share a security policy management project. The previous implementation did not scale with the number of linked configurations and could exceed the PostgreSQL statement timeout for management projects with many linked configurations.

Root cause

linked_source_root_namespaces was:

Security::OrchestrationPolicyConfiguration
  .for_management_project(management_project_id)
  .with_project_and_namespace                      # includes(:project, :namespace)
  .filter_map { |c| c.source&.root_ancestor }      # N+1 root_ancestor lookup
  .uniq

Two compounding problems at scale:

1. Bulk preload with massive IN (...) list — includes(:project, :namespace) on 19K configs generates SELECT projects.* WHERE id IN ($1 .. $19000) and the equivalent for namespaces. Postgres struggles with very large IN lists on wide tables; this is the query that hits the 15s timeout.
2. N+1 root_ancestor — for each of the 19K configs, source.root_ancestor issues a separate Namespace.find_by(id: traversal_ids.first) because the namespace's parent isn't preloaded. The successful-but-slow population in the issue logs (22,000–23,000 DB queries in 14–122s) is exactly this fan-out.

Fix

Replace the Ruby-side loop with a single SQL query anchored on security_orchestration_policy_configurations that resolves each config to its root namespace via traversal_ids[1] directly in the database:

def self.linked_source_root_namespace_ids_for_management_project(management_project_id)
  for_management_project(management_project_id)
    .joins(<<~SQL.squish)
      LEFT JOIN projects
        ON projects.id = security_orchestration_policy_configurations.project_id
      INNER JOIN namespaces
        ON namespaces.id = COALESCE(
          projects.namespace_id,
          security_orchestration_policy_configurations.namespace_id
        )
    SQL
    .pluck(Arel.sql('DISTINCT namespaces.traversal_ids[1]'))
end

The DB CHECK constraint cop_configs_project_or_namespace_existence guarantees exactly one of project_id / namespace_id is set on every configuration, so the COALESCE picks whichever is present (logically equivalent to OR-ing the two source paths, expressed as a single per-row choice).

Anchoring on security_orchestration_policy_configurations (filtered by the existing index index_sop_configurations_project_id_policy_project_id) keeps the driving set bounded to configurations linked to this management project. The subsequent JOINs into projects and namespaces resolve via PK seeks, avoiding any scan of the namespaces table.

Result set is bounded by the distinct root namespaces (typically 1–10), not the config count. The service materialises the IDs once per service instance via strong_memoize_attr and adds the CSP namespace in Ruby before passing the array to Framework.with_namespaces(...).

Query

SELECT DISTINCT
   namespaces.traversal_ids[1] 
FROM
   "security_orchestration_policy_configurations" 
   LEFT JOIN
      projects 
      ON projects.id = security_orchestration_policy_configurations.project_id 
   INNER JOIN
      namespaces 
      ON namespaces.id = COALESCE( projects.namespace_id, security_orchestration_policy_configurations.namespace_id ) 
WHERE
   "security_orchestration_policy_configurations"."security_policy_management_project_id" = $1

Query plan

References

#599018 (closed)

Screenshots or screen recordings

Before	After

How to set up and validate locally

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.

Related to #599018 (closed)