Draft: BBM to fix existing duplicate iids in p_ci_pipelines

Leaminn Marequested to merge
fix-existing-dup-pipeline-iids into master

What does this MR do and why?

Background

We currently have the unique index (project_id, iid, partition_id) on the table p_ci_pipelines. This ensures uniqueness within the same partition_id but not across partitions. To prevent duplicates on the database level, we created a new table p_ci_pipeline_iids to track all pipeline iids per project.

As of 2026-02-24 on GitLab.com:

  • There are 85884 duplicate records.
  • Each (project_id, iid) pair has at most 2 records with the same values.
  • There are 20 projects that have duplicates.
See query & results 
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

This MR

In !213722 (merged), we backfilled the existing iids in p_ci_pipelines to p_ci_pipeline_iids. Now in this MR, we implement the next step: fix existing duplicate iids.

The BBM logic is the following:

  1. We create one BBM instance per p_ci_pipelines partition. This enables us to iterate through the data more efficiently.

  2. For each partition, we batch through by id and check if there are any duplicate iids.

  3. For each duplicate, we generate a new iid using the same algorithm as we do in application code with InternalId.

References

Database query plans

See MR comments.

How to set up and validate locally

  1. Run bundle exec rails db:migrate.

  2. Check that the batched_background_migrations table has correctly queued BBMs for the relevant partitions. Example output:

Query 
SELECT
    id,
    column_name,
    table_name,
    job_arguments,
    min_value,
    max_value,
    total_tuple_count
FROM
    batched_background_migrations
WHERE
    job_class_name = 'DeduplicatePipelineIids';
 id | column_name |                 table_name                 |   job_arguments   | min_value | max_value | total_tuple_count 
----+-------------+--------------------------------------------+-------------------+-----------+-----------+-------------------
 48 | id          | gitlab_partitions_dynamic.ci_pipelines_105 | [[105]]           |         1 |       999 |                13
 49 | id          | gitlab_partitions_dynamic.ci_pipelines_104 | [[104]]           |         1 |      1005 |                 5
 50 | id          | gitlab_partitions_dynamic.ci_pipelines     | [[100, 101, 102]] |         1 |       985 |               913
  1. Migrate down:
VERSION=20260219192504 bundle exec rails db:migrate:down:ci
VERSION=20260219192504 bundle exec rails db:migrate:down:main
VERSION=20260219192504 bundle exec rails db:migrate:down:sec
  1. Confirm that the batched_background_migrations table no longer has any migrations with job class name 'DeduplicatePipelineIids'.
 id | column_name | table_name | job_arguments | min_value | max_value | total_tuple_count 
----+-------------+------------+---------------+-----------+-----------+-------------------
(0 rows)

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 #582339 (closed)

Edited by Leaminn Ma

Merge request reports