Deduplicate existing pipeline iids across partitions

Leaminn Marequested to merge
deduplicate-pipeline-iids into master

What does this MR do and why?

Context

The unique index (project_id, iid, partition_id) on p_ci_pipelines only enforces pipeline iid uniqueness within a partition, so a workflow race could persist duplicate iids for the same project across partitions. Going forward this is now prevented by the p_ci_pipeline_iids tracking table and its uniqueness triggers, together with a backfill of existing iids. This MR is the next step: a batched background migration that fixes the existing duplicates.

This step was delayed for a few milestones because our CI BBMs were occupied with ci_builds_metadata but only one of those BBMs now remain, so this MR can proceed.

The affected data on GitLab.com is small and frozen since the triggers prevent any new duplicates:

  • 20 projects affected
  • 42,942 duplicated (project_id, iid) pairs (85,884 rows)
  • at most 2 copies of any (project_id, iid)
  • all duplicates are confined to partitions 100–107
Queries

Totals:

SELECT
    COUNT(DISTINCT project_id) AS projects_count,
    COUNT(*) AS distinct_dup_count,
    SUM(dup_count) AS total_dup_count,
    MAX(dup_count) AS max_dup_count_per_distinct_dup
FROM (
    SELECT project_id, iid, COUNT(*) AS dup_count
    FROM p_ci_pipelines
    WHERE iid IS NOT NULL
    GROUP BY project_id, iid
    HAVING COUNT(*) > 1
) duplicates;
 projects_count | distinct_dup_count | total_dup_count | max_dup_count_per_distinct_dup
----------------+--------------------+-----------------+--------------------------------
             20 |              42942 |           85884 |                              2

Distribution across partitions:

SELECT
    partitions,
    COUNT(*)        AS dup_groups,
    SUM(dup_count)  AS dup_rows
FROM (
    SELECT project_id, iid,
           COUNT(*) AS dup_count,
           array_agg(DISTINCT partition_id ORDER BY partition_id) AS partitions
    FROM p_ci_pipelines
    WHERE iid IS NOT NULL
    GROUP BY project_id, iid
    HAVING COUNT(*) > 1
) d
GROUP BY partitions
ORDER BY dup_groups DESC;
 partitions | dup_groups | dup_rows
------------+------------+----------
 {101,102}  |      12651 |    25302
 {101,107}  |       6527 |    13054
 {101,104}  |       5882 |    11764
 {101,103}  |       5464 |    10928
 {100,102}  |       4337 |     8674
 {101,105}  |       4147 |     8294
 {101,106}  |       3082 |     6164
 {102,106}  |        289 |      578
 {102,104}  |        200 |      400
 {102,105}  |        102 |      204
 {102,107}  |         79 |      158
 {100,105}  |         73 |      146
 {100,106}  |         38 |       76
 {100,104}  |         37 |       74
 {100,103}  |         27 |       54
 {103,104}  |          4 |        8
 {102,103}  |          3 |        6

This MR

This implementation replaces the old draft from !224616 (closed). It implements iid generation in bulk instead of updating them one at a time.

Adds the DeduplicatePipelineIids batched background migration. The queueing migration enqueues one BBM instance per p_ci_pipelines partition (skipping empty partitions, and — on GitLab.com only — partitions newer than the duplicates, partition_id > 107). Each instance:

  • batches by id within its single partition (so the heavy scan stays partition-local),
  • detects (project_id, iid) values that also exist in a lower partition, so each duplicate group is handled once by its highest-partition copy,
  • reserves the iids it needs in bulk from internal_ids (mirroring InternalId.generate, flushing and retrying if a stale last_value collides), and
  • reassigns every copy of the duplicate via a partition-pruned bulk UPDATE. All copies must be renumbered because the uniqueness trigger deletes the shared p_ci_pipeline_iids row whenever any copy's iid changes.

References

Database queries

See MR comments.

How to set up and validate locally

  1. Run bundle exec rails db:migrate.
  2. Confirm the BBMs were enqueued (one per non-empty partition): 
    SELECT table_name, job_arguments, min_value, max_value
FROM batched_background_migrations
WHERE job_class_name = 'DeduplicatePipelineIids';
  3. Migrate down and confirm they're removed: 
    VERSION=20260611192449 bundle exec rails db:migrate:down:ci
VERSION=20260611192449 bundle exec rails db:migrate:down:main

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.

Edited by Leaminn Ma

Merge request reports