Backfill p_ci_pipeline_iids with existing iids from p_ci_pipelines

Leaminn Marequested to merge
backfill-iids-to-ci-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 but not across partitions. Due to a possible workflow bug/race condition, we occasionally save duplicate pipeline iids for the same project, which causes problems such as https://gitlab.com/gitlab-com/request-for-help/-/issues/2855#note_2518831537.

We've explored a couple different possible solutions (https://gitlab.com/gitlab-org/gitlab/-/issues/545167#note_2802709422, https://gitlab.com/gitlab-org/gitlab/-/issues/545167#note_2844491364), and we ultimately decided that the only way to guarantee Pipeline iid uniqueness on the database level is to create a new table to track the iids. (See proposed approach in !210789 (comment 2869213142).)

  • In !213065 (merged), we introduced the static hash partitioned table p_ci_pipeline_iids. It has 64 partitions with the hash on project_id and primary key on (project_id, iid).
  • In !213457 (merged), we added database triggers on p_ci_pipelines so that it keep track of iids in p_ci_pipeline_iids.
    • This means that further duplicates are now prevented for new iids going forward.
  • In !213992 (merged), we are updating the internal ID initial_value logic to read maximum(:iid) from ci_pipeline_iids in addition to ci_pipelines.

This MR

In this MR, we now backfill p_ci_pipeline_iids with existing iids from p_ci_pipelines so that all iids are accounted for. The BBM logic is as follows:

  • Queues a separate BBM per partition from p_ci_pipelines.
  • Copies over p_ci_pipelines.iids to p_ci_pipeline_iids, ignoring duplicates and null iids.

Next steps:

  1. Create a BBM to fix existing duplicate iids.

References

Database query plans

Batching query

SELECT "gitlab_partitions_dynamic"."ci_pipelines_107"."id"
FROM "gitlab_partitions_dynamic"."ci_pipelines_107"
WHERE
    "gitlab_partitions_dynamic"."ci_pipelines_107"."id" BETWEEN 2170076088 AND 2171076088
    AND "gitlab_partitions_dynamic"."ci_pipelines_107"."id" >= 2170076088
ORDER BY
    "gitlab_partitions_dynamic"."ci_pipelines_107"."id" ASC
LIMIT 1 OFFSET 1000

Query link: https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/46118/commands/140960

Project IDs query

sub_batch.select(:project_id, :iid).limit(sub_batch_size).map(&:project_id)
SELECT
    "gitlab_partitions_dynamic"."ci_pipelines_107"."project_id",
    "gitlab_partitions_dynamic"."ci_pipelines_107"."iid"
FROM
    "gitlab_partitions_dynamic"."ci_pipelines_107"
WHERE
    "gitlab_partitions_dynamic"."ci_pipelines_107"."id" BETWEEN 2170076088 AND 2171076088
    AND "gitlab_partitions_dynamic"."ci_pipelines_107"."id" >= 2170076088
    AND "gitlab_partitions_dynamic"."ci_pipelines_107"."id" < 2170077088
LIMIT 100

Query link: https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/46292/commands/141279

Insert query

WITH iids AS MATERIALIZED (
    SELECT
        "gitlab_partitions_dynamic"."ci_pipelines_107"."project_id",
        "gitlab_partitions_dynamic"."ci_pipelines_107"."iid"
    FROM
        "gitlab_partitions_dynamic"."ci_pipelines_107"
    WHERE
        "gitlab_partitions_dynamic"."ci_pipelines_107"."id" BETWEEN 2170076088 AND 2171076088
        AND "gitlab_partitions_dynamic"."ci_pipelines_107"."id" >= 2170076088
        AND "gitlab_partitions_dynamic"."ci_pipelines_107"."id" < 2170077088
    LIMIT 100
), filtered_iids AS MATERIALIZED (
    SELECT project_id, iid
    FROM iids
    WHERE iid IS NOT NULL
    LIMIT 100
)
INSERT INTO p_ci_pipeline_iids (project_id, iid)
SELECT 38375152, iid
FROM filtered_iids
WHERE project_id = 38375152
ON CONFLICT DO NOTHING

Query link: https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/46292/commands/141280

How to set up and validate locally

  1. Before checking out this branch, ensure you have some pipeline data in your local gdk. Clear any data currently in p_ci_pipelines and observe the count of unique iids in each table:
connection = Ci::ApplicationRecord.connection
connection.execute('DELETE FROM p_ci_pipeline_iids')
Ci::Pipeline.select('DISTINCT (project_id, iid)').where.not(iid: nil).count # Should be non-zero
connection.execute('SELECT COUNT(*) FROM p_ci_pipeline_iids').to_a # Should be zero ([{"count"=>0}])
  1. Check out this branch and run the migration: bundle exec rails db:migrate.
  2. Check that a BBM for each partition is queued in the CI database. Example
% gdk psql
gitlabhq_development=# \c gitlabhq_development_ci
gitlabhq_development_ci=# SELECT id, created_at, job_class_name, table_name, job_arguments FROM batched_background_migrations WHERE job_class_name = 'BackfillPCiPipelineIids';
 id |          created_at           |     job_class_name      |               table_name               | job_arguments 
----+-------------------------------+-------------------------+----------------------------------------+---------------
 22 | 2025-12-03 18:40:48.266444+00 | BackfillPCiPipelineIids | gitlab_partitions_dynamic.ci_pipelines | []
(1 row)
  1. After the BBMs are complete (which should be fast on your local gdk), check the counts of both tables and they should now be equal.
Ci::Pipeline.select('DISTINCT (project_id, iid)').where.not(iid: nil).count
connection.execute('SELECT COUNT(*) FROM p_ci_pipeline_iids').to_a

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