Update pipeline iid init value logic to read from new p_ci_pipeline_iids table

Leaminn Marequested to merge
update-pipeline-iid-init-value-logic 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. 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 MR

In this MR, we now update the Ci::Pipeline model's init function so that it checks the max iid value in the new table p_ci_pipeline_iids. This init function is called by InternalId#initial_value to recalculate last_value when the record doesn't already exist (which can happen when we run InternalId.flush_records! or when a new project is created.) The following changes were made in the new logic:

  • Refactors the existing logic a bit so that it always falls back to the pipelines count if a maximum(:iid) value cannot be obtained.
  • Cleans up the code so it uses .where(**scope) instead of a long chain of associations (e.g. pipeline.project&.all_pipelines&.count). The existing SQLs remain the same.
  • Gets the maximum iid value from p_ci_pipeline_iids, falls back to the max(iid) of p_ci_pipelines if the latter is greater.

This change is made behind a feature flag: update_init_iid_to_read_from_ci_pipeline_iids. Roll-out issue: #581989 (closed)

Next steps:

  1. Create a BBM to backfill existing iids into p_ci_pipeline_iids.
  2. Create a BBM to fix existing duplicate iids.

References

Database query plans

Maximum iid query

Before (existing) 

pipeline.project&.all_pipelines&.maximum(:iid) 

::Ci::Pipeline.where(**scope).maximum(:iid)
SELECT MAX("p_ci_pipelines"."iid")
FROM "p_ci_pipelines"
WHERE "p_ci_pipelines"."project_id" = 278964

Query plan: https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/45850/commands/140331

  • Query plan reads from every partition.

After (added query) 

::Ci::PipelineIid.where(**scope).maximum(:iid)
SELECT MAX("p_ci_pipeline_iids"."iid")
FROM "p_ci_pipeline_iids"
WHERE "p_ci_pipeline_iids"."project_id" =278964

Query plan: https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/45850/commands/140332

  • Query plan only needs to read from one partition.

Pipeline count query

  • This query has not changed from before. Included here for completeness.
::Ci::Pipeline.where(**scope).count
SELECT COUNT(*)
FROM "p_ci_pipelines"
WHERE "p_ci_pipelines"."project_id" = 68540063;

Query plan: https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/45850/commands/140330

How to set up and validate locally

  1. In a new project, you can verify that creating a new pipeline multiple times succeeds as usual both before and after enabling the feature flag:
Feature.enable(:update_init_iid_to_read_from_ci_pipeline_iids)
  1. You can also confirm the behaviour when a duplicate iid is attempted to be inserted into a different partition. Follow the steps in !213457 (merged).

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