Support transferring security export tables (dep list + vuln) on org group transfer

tim mccarthyrequested to merge
feature/transfer-security-export-tables into master

What does this MR do and why?

Add organization transfer support for 8 tables related to dependency list exports and vulnerability exports, and move vulnerability_exports transfer from UsersService auto-discovery to a dedicated SecurityExportsService.

dependency_list_exports requires no transfer work — its num_nonnulls(group_id, organization_id, project_id) = 1 constraint means the organization is derived through the group or project sharding key. vulnerability_exports was previously auto-discovered by UsersService; this MR explicitly skips it there and transfers it via SecurityExportsService instead, keeping it co-located with its child tables on the gitlab_sec database.

Architecture

Phase 1 (synchronous, gitlab_main_org transaction):

  • Updates 3 upload partition tables scoped by model_id to only affect records belonging to the transferring group hierarchy
  • Nullifies organization_id on 3 state tables via batched update_all to fire BEFORE UPDATE triggers, which re-read organization_id from their now-updated parent upload
  • State table updates are scoped to the exact upload IDs just transferred (via transfer_uploads_and_fire_trigger helper), preventing unintended updates to pre-existing records in the target organization

Phase 2 (async, gitlab_sec via SecurityExportsWorker):

  • Updates vulnerability_exports, dependency_list_export_parts, and vulnerability_export_parts via SecurityExportsService
  • Batches group and project IDs from gitlab_main into Ruby arrays to avoid cross-database subqueries with gitlab_sec
  • For each group/project batch: transfers vulnerability_exports directly, then iterates parent dependency_list_exports and vulnerability_exports to collect export IDs and transfer their parts
  • Worker uses concurrency_limit -> { 1 }, deduplicate :until_executed, and defer_on_database_health_signal :gitlab_sec

Tables changed to supported

Table Schema Phase
dependency_list_export_parts gitlab_sec Phase 2 (async)
dependency_list_export_part_uploads gitlab_main_org Phase 1 (sync)
dependency_list_export_part_upload_states gitlab_main_org Phase 1 (trigger)
vulnerability_export_parts gitlab_sec Phase 2 (async)
vulnerability_export_uploads gitlab_main_org Phase 1 (sync)
vulnerability_export_upload_states gitlab_main_org Phase 1 (trigger)
vulnerability_export_part_uploads gitlab_main_org Phase 1 (sync)
vulnerability_export_part_upload_states gitlab_main_org Phase 1 (trigger)

Query plans

ee/app/services/ee/organizations/transfer/groups_service.rb

Phase 1 — ID lookups (gitlab_sec)

.where(group_id: descendant_group_ids).or(::Dependencies::DependencyListExport.where(project_id: descendant_project_ids))

SELECT dependency_list_exports pluck (cold: 1.0 ms / hot: 0.8 ms) 
SELECT "dependency_list_exports"."id"
FROM "dependency_list_exports"
WHERE ("dependency_list_exports"."group_id" IN (9970)
    OR "dependency_list_exports"."project_id" = 278964)

cold

hot

.where(dependency_list_export_id: dep_list_export_ids).pluck(:id)

SELECT dependency_list_export_parts pluck (cold: 0.7 ms / hot: 0.8 ms) 
SELECT "dependency_list_export_parts"."id"
FROM "dependency_list_export_parts"
WHERE "dependency_list_export_parts"."dependency_list_export_id" IN (1, 2, 3, 4, 5)

cold

hot

.where(group_id: descendant_group_ids).or(::Vulnerabilities::Export.where(project_id: descendant_project_ids))

SELECT vulnerability_exports pluck (cold: 2.9 ms / hot: 0.8 ms) 
SELECT "vulnerability_exports"."id"
FROM "vulnerability_exports"
WHERE ("vulnerability_exports"."group_id" IN (9970)
    OR "vulnerability_exports"."project_id" = 278964)

cold

hot

.where(vulnerability_export_id: vuln_export_ids).pluck(:id)

SELECT vulnerability_export_parts pluck (cold: 0.9 ms / hot: 0.7 ms) 
SELECT "vulnerability_export_parts"."id"
FROM "vulnerability_export_parts"
WHERE "vulnerability_export_parts"."vulnerability_export_id" IN (1, 2, 3, 4, 5)

cold

hot

Phase 1 — Upload transfers (gitlab_main_org)

upload_class.where(model_id: model_id_batch, organization_id: old_organization.id)

upload SELECT: dependency_list_export_part_uploads (cold: 1.0 ms / hot: 1.0 ms) 
SELECT "dependency_list_export_part_uploads"."id"
FROM "dependency_list_export_part_uploads"
WHERE "dependency_list_export_part_uploads"."model_id" = 278964
    AND "dependency_list_export_part_uploads"."organization_id" = 1
LIMIT 1000

cold

hot

upload_class.where(id: upload_ids).update_all(organization_id: new_organization.id)

upload UPDATE: dependency_list_export_part_uploads (cold: 2.3 ms) 
UPDATE "dependency_list_export_part_uploads"
SET "organization_id" = 2000258
WHERE "dependency_list_export_part_uploads"."id" IN (
    SELECT "dependency_list_export_part_uploads"."id"
    FROM "dependency_list_export_part_uploads"
    WHERE "dependency_list_export_part_uploads"."model_id" = 278964
        AND "dependency_list_export_part_uploads"."organization_id" = 1
    LIMIT 1000)

cold

upload_class.where(model_id: model_id_batch, organization_id: old_organization.id)

upload SELECT: vulnerability_export_uploads (cold: 1.0 ms / hot: 0.8 ms) 
SELECT "vulnerability_export_uploads"."id"
FROM "vulnerability_export_uploads"
WHERE "vulnerability_export_uploads"."model_id" IN (278964)
    AND "vulnerability_export_uploads"."organization_id" = 1
LIMIT 1000

cold

hot

upload_class.where(id: upload_ids).update_all(organization_id: new_organization.id)

upload UPDATE: vulnerability_export_uploads (cold: 1.4 ms) 
UPDATE "vulnerability_export_uploads"
SET "organization_id" = 2000258
WHERE "vulnerability_export_uploads"."id" IN (
    SELECT "vulnerability_export_uploads"."id"
    FROM "vulnerability_export_uploads"
    WHERE "vulnerability_export_uploads"."model_id" IN (278964)
        AND "vulnerability_export_uploads"."organization_id" = 1
    LIMIT 1000)

cold

upload_class.where(model_id: model_id_batch, organization_id: old_organization.id)

upload SELECT: vulnerability_export_part_uploads (cold: 1.0 ms / hot: 0.9 ms) 
SELECT "vulnerability_export_part_uploads"."id"
FROM "vulnerability_export_part_uploads"
WHERE "vulnerability_export_part_uploads"."model_id" = 278964
    AND "vulnerability_export_part_uploads"."organization_id" = 1
LIMIT 1000

cold

hot

upload_class.where(id: upload_ids).update_all(organization_id: new_organization.id)

upload UPDATE: vulnerability_export_part_uploads (cold: 1.8 ms) 
UPDATE "vulnerability_export_part_uploads"
SET "organization_id" = 2000258
WHERE "vulnerability_export_part_uploads"."id" IN (
    SELECT "vulnerability_export_part_uploads"."id"
    FROM "vulnerability_export_part_uploads"
    WHERE "vulnerability_export_part_uploads"."model_id" = 278964
        AND "vulnerability_export_part_uploads"."organization_id" = 1
    LIMIT 1000)

cold

Phase 1 — Trigger-fire state tables (gitlab_main_org)

state_class.where(parent_fk => upload_ids).where(organization_id: old_organization.id).update_all(organization_id: nil)

trigger fire UPDATE: dependency_list_export_part_upload_states (cold: 2.1 ms) 
UPDATE "dependency_list_export_part_upload_states"
SET "organization_id" = NULL
WHERE "dependency_list_export_part_upload_states"."dependency_list_export_part_upload_id" IN (
    SELECT "dependency_list_export_part_uploads"."id"
    FROM "dependency_list_export_part_uploads"
    WHERE "dependency_list_export_part_uploads"."model_id" = 278964)
AND "dependency_list_export_part_upload_states"."organization_id" = 1

cold

trigger fire UPDATE: vulnerability_export_upload_states (cold: 2.3 ms) 
UPDATE "vulnerability_export_upload_states"
SET "organization_id" = NULL
WHERE "vulnerability_export_upload_states"."vulnerability_export_upload_id" IN (
    SELECT "vulnerability_export_uploads"."id"
    FROM "vulnerability_export_uploads"
    WHERE "vulnerability_export_uploads"."model_id" = 278964)
AND "vulnerability_export_upload_states"."organization_id" = 1

cold

trigger fire UPDATE: vulnerability_export_part_upload_states (cold: 1.9 ms) 
UPDATE "vulnerability_export_part_upload_states"
SET "organization_id" = NULL
WHERE "vulnerability_export_part_upload_states"."vulnerability_export_part_upload_id" IN (
    SELECT "vulnerability_export_part_uploads"."id"
    FROM "vulnerability_export_part_uploads"
    WHERE "vulnerability_export_part_uploads"."model_id" = 278964)
AND "vulnerability_export_part_upload_states"."organization_id" = 1

cold

ee/app/services/organizations/transfer/security_exports_service.rb

Phase 2 — each_export_batch iteration queries (gitlab_sec)

export_class.where(scope).each_batch — iterates parent exports by group_id

each_export_batch cursor: dependency_list_exports by group_id (cold: 3.3 ms / hot: 3.3 ms) 
SELECT "dependency_list_exports"."id"
FROM "dependency_list_exports"
WHERE "dependency_list_exports"."group_id" IN (9970)
ORDER BY "dependency_list_exports"."id" ASC
LIMIT 1

cold

hot

each_export_batch boundary: dependency_list_exports by group_id (cold: 3.3 ms / hot: 3.3 ms) 
SELECT "dependency_list_exports"."id"
FROM "dependency_list_exports"
WHERE "dependency_list_exports"."group_id" IN (9970)
    AND "dependency_list_exports"."id" >= 1
ORDER BY "dependency_list_exports"."id" ASC
LIMIT 1 OFFSET 1000

cold

hot

export_class.where(scope).each_batch — iterates parent exports by project_id

each_export_batch cursor: dependency_list_exports by project_id (cold: 3.3 ms / hot: 3.3 ms) 
SELECT "dependency_list_exports"."id"
FROM "dependency_list_exports"
WHERE "dependency_list_exports"."project_id" = 278964
ORDER BY "dependency_list_exports"."id" ASC
LIMIT 1

cold

hot

each_export_batch boundary: dependency_list_exports by project_id (cold: 3.3 ms / hot: 3.3 ms) 
SELECT "dependency_list_exports"."id"
FROM "dependency_list_exports"
WHERE "dependency_list_exports"."project_id" = 278964
    AND "dependency_list_exports"."id" >= 1
ORDER BY "dependency_list_exports"."id" ASC
LIMIT 1 OFFSET 1000

cold

hot

each_export_batch(::Vulnerabilities::Export, scope) — iterates parent exports by group_id

each_export_batch cursor: vulnerability_exports by group_id (cold: 0.8 ms / hot: 0.8 ms) 
SELECT "vulnerability_exports"."id"
FROM "vulnerability_exports"
WHERE "vulnerability_exports"."group_id" IN (9970)
ORDER BY "vulnerability_exports"."id" ASC
LIMIT 1

cold

hot

each_export_batch boundary: vulnerability_exports by group_id (cold: 0.8 ms / hot: 0.8 ms) 
SELECT "vulnerability_exports"."id"
FROM "vulnerability_exports"
WHERE "vulnerability_exports"."group_id" IN (9970)
    AND "vulnerability_exports"."id" >= 1
ORDER BY "vulnerability_exports"."id" ASC
LIMIT 1 OFFSET 1000

cold

hot

each_export_batch(::Vulnerabilities::Export, scope) — iterates parent exports by project_id

each_export_batch cursor: vulnerability_exports by project_id (cold: 0.8 ms / hot: 0.8 ms) 
SELECT "vulnerability_exports"."id"
FROM "vulnerability_exports"
WHERE "vulnerability_exports"."project_id" = 278964
ORDER BY "vulnerability_exports"."id" ASC
LIMIT 1

cold

hot

each_export_batch boundary: vulnerability_exports by project_id (cold: 0.8 ms / hot: 0.8 ms) 
SELECT "vulnerability_exports"."id"
FROM "vulnerability_exports"
WHERE "vulnerability_exports"."project_id" = 278964
    AND "vulnerability_exports"."id" >= 1
ORDER BY "vulnerability_exports"."id" ASC
LIMIT 1 OFFSET 1000

cold

hot

Phase 2 — update_organization_id_for each_batch queries (gitlab_sec)

update_organization_id_for(::Dependencies::DependencyListExport::Part) { |r| r.where(dependency_list_export_id: export_ids) }

each_batch cursor: dependency_list_export_parts (cold: 12.7 ms / hot: 3.4 ms) 
SELECT "dependency_list_export_parts"."id"
FROM "dependency_list_export_parts"
WHERE "dependency_list_export_parts"."organization_id" = 1
    AND "dependency_list_export_parts"."dependency_list_export_id" IN (
        SELECT "dependency_list_exports"."id"
        FROM "dependency_list_exports"
        WHERE "dependency_list_exports"."group_id" IN (9970))
ORDER BY "dependency_list_export_parts"."id" ASC
LIMIT 1

cold

hot

each_batch boundary: dependency_list_export_parts (cold: 7.3 ms / hot: 3.7 ms) 
SELECT "dependency_list_export_parts"."id"
FROM "dependency_list_export_parts"
WHERE "dependency_list_export_parts"."organization_id" = 1
    AND "dependency_list_export_parts"."dependency_list_export_id" IN (
        SELECT "dependency_list_exports"."id"
        FROM "dependency_list_exports"
        WHERE "dependency_list_exports"."group_id" IN (9970))
    AND "dependency_list_export_parts"."id" >= 1
ORDER BY "dependency_list_export_parts"."id" ASC
LIMIT 1 OFFSET 1000

cold

hot

each_batch UPDATE: dependency_list_export_parts (cold: 1.4 ms) 
UPDATE "dependency_list_export_parts"
SET "organization_id" = 2000258
WHERE "dependency_list_export_parts"."organization_id" = 1
    AND "dependency_list_export_parts"."dependency_list_export_id" IN (
        SELECT "dependency_list_exports"."id"
        FROM "dependency_list_exports"
        WHERE "dependency_list_exports"."group_id" IN (9970))
    AND "dependency_list_export_parts"."id" >= 1
    AND "dependency_list_export_parts"."id" < 1001

cold

update_organization_id_for(::Vulnerabilities::Export) { |r| r.where(group_id: batch) }

each_batch cursor: vulnerability_exports by group_id (cold: 0.8 ms / hot: 0.8 ms) 
SELECT "vulnerability_exports"."id"
FROM "vulnerability_exports"
WHERE "vulnerability_exports"."organization_id" = 1
    AND "vulnerability_exports"."group_id" IN (9970)
ORDER BY "vulnerability_exports"."id" ASC
LIMIT 1

cold

hot

each_batch boundary: vulnerability_exports by group_id (cold: 0.8 ms / hot: 0.9 ms) 
SELECT "vulnerability_exports"."id"
FROM "vulnerability_exports"
WHERE "vulnerability_exports"."organization_id" = 1
    AND "vulnerability_exports"."group_id" IN (9970)
    AND "vulnerability_exports"."id" >= 1
ORDER BY "vulnerability_exports"."id" ASC
LIMIT 1 OFFSET 1000

cold

hot

each_batch UPDATE: vulnerability_exports by group_id (cold: 0.8 ms) 
UPDATE "vulnerability_exports"
SET "organization_id" = 2000258
WHERE "vulnerability_exports"."organization_id" = 1
    AND "vulnerability_exports"."group_id" IN (9970)
    AND "vulnerability_exports"."id" >= 1
    AND "vulnerability_exports"."id" < 1001

cold

update_organization_id_for(::Vulnerabilities::Export) { |r| r.where(project_id: batch) }

each_batch cursor: vulnerability_exports by project_id (cold: 0.8 ms / hot: 0.8 ms) 
SELECT "vulnerability_exports"."id"
FROM "vulnerability_exports"
WHERE "vulnerability_exports"."organization_id" = 1
    AND "vulnerability_exports"."project_id" = 278964
ORDER BY "vulnerability_exports"."id" ASC
LIMIT 1

cold

hot

each_batch boundary: vulnerability_exports by project_id (cold: 0.8 ms / hot: 0.8 ms) 
SELECT "vulnerability_exports"."id"
FROM "vulnerability_exports"
WHERE "vulnerability_exports"."organization_id" = 1
    AND "vulnerability_exports"."project_id" = 278964
    AND "vulnerability_exports"."id" >= 1
ORDER BY "vulnerability_exports"."id" ASC
LIMIT 1 OFFSET 1000

cold

hot

each_batch UPDATE: vulnerability_exports by project_id (cold: 0.8 ms) 
UPDATE "vulnerability_exports"
SET "organization_id" = 2000258
WHERE "vulnerability_exports"."organization_id" = 1
    AND "vulnerability_exports"."project_id" = 278964
    AND "vulnerability_exports"."id" >= 1
    AND "vulnerability_exports"."id" < 1001

cold

update_organization_id_for(::Vulnerabilities::Export::Part) { |r| r.where(vulnerability_export_id: export_ids) }

each_batch cursor: vulnerability_export_parts (cold: 2.7 ms / hot: 1.4 ms) 
SELECT "vulnerability_export_parts"."id"
FROM "vulnerability_export_parts"
WHERE "vulnerability_export_parts"."organization_id" = 1
    AND "vulnerability_export_parts"."vulnerability_export_id" IN (
        SELECT "vulnerability_exports"."id"
        FROM "vulnerability_exports"
        WHERE "vulnerability_exports"."group_id" IN (9970))
ORDER BY "vulnerability_export_parts"."id" ASC
LIMIT 1

cold

hot

each_batch boundary: vulnerability_export_parts (cold: 1.8 ms / hot: 1.7 ms) 
SELECT "vulnerability_export_parts"."id"
FROM "vulnerability_export_parts"
WHERE "vulnerability_export_parts"."organization_id" = 1
    AND "vulnerability_export_parts"."vulnerability_export_id" IN (
        SELECT "vulnerability_exports"."id"
        FROM "vulnerability_exports"
        WHERE "vulnerability_exports"."group_id" IN (9970))
    AND "vulnerability_export_parts"."id" >= 1
ORDER BY "vulnerability_export_parts"."id" ASC
LIMIT 1 OFFSET 1000

cold

hot

each_batch UPDATE: vulnerability_export_parts (cold: 1.4 ms) 
UPDATE "vulnerability_export_parts"
SET "organization_id" = 2000258
WHERE "vulnerability_export_parts"."organization_id" = 1
    AND "vulnerability_export_parts"."vulnerability_export_id" IN (
        SELECT "vulnerability_exports"."id"
        FROM "vulnerability_exports"
        WHERE "vulnerability_exports"."group_id" IN (9970))
    AND "vulnerability_export_parts"."id" >= 1
    AND "vulnerability_export_parts"."id" < 1001

cold

References

Edited by tim mccarthy

Merge request reports