Skip to content
GitLab Next
Closed
Created by Yannis Roussos@iroussos🌴Contributor

Convert ci_builds.id to bigint - Step 1: Add new columns and sync data

The ci_builds table is one of the largest tables in GitLab's database that still uses an integer (int4) Primary Key. It has a high Primary Key Overflow risk as can be seen on its tracking issue.

The first step to address the problem is to create a new column of type bigint, load all the data by using background jobs from the current id column to it and keep them in sync with a trigger.

In addition, the ci_builds table references (has a Foreign Key towards) the ci_stages table, which is also one of the tables with a Primary Key Overflow risk and we'll have to convert its id column to bigint at some point, so it would be optimal to do so in one pass while converting its Primary Key.

The overall process for the ci_builds table will be as follows:

  • Create new column ci_builds.new_id for the Primary Key, with bigint NOT NULL DEFAULT 0
  • Create new columns ci_builds.new_stage_id for the Foreign Key that references ci_stages, with bigint NOT NULL DEFAULT 0
  • Install sync triggers for both columns to keep them updated while new records are inserted or existing ones are updated or deleted.
  • Start background jobs that will batch through the whole table and copy the id and stage_id values to the new columns.

We'll follow with a cleanup migration in the next milestone than the one that the aforementioned migrations are deployed, which will add the necessary indexes, swap the PK (and its sequence) and the FKs and finally drop the old columns.

Finally, the ci_builds table is at the center of the CI tables design, with 17 tables referencing it:

  • TABLE ci_build_trace_sections - FOREIGN KEY build_id
  • TABLE ci_builds_metadata - FOREIGN KEY build_id - (Covered in Address the PK Overflow risk for ci_builds_metadata)
  • TABLE ci_build_trace_chunks - FOREIGN KEY build_id
  • TABLE ci_build_needs - FOREIGN KEY build_id !59467 (merged)
  • TABLE ci_builds_runner_session - FOREIGN KEY build_id
  • TABLE ci_job_artifacts - FOREIGN KEY job_id - (Covered in Address the PK Overflow risk for ci_job_artifacts)
  • TABLE ci_sources_pipelines - FOREIGN KEY source_job_id
  • TABLE ci_job_variables - FOREIGN KEY job_id (already bigint)
  • TABLE ci_build_pending_states - FOREIGN KEY build_id (already bigint)
  • TABLE ci_build_report_results - FOREIGN KEY build_id (already bigint)
  • TABLE ci_resources - FOREIGN KEY build_id (already bigint)
  • TABLE ci_test_case_failures - FOREIGN KEY build_id (already bigint)
  • TABLE ci_unit_test_failures - FOREIGN KEY build_id (already bigint)
  • TABLE pages_deployments - FOREIGN KEY ci_build_id (already bigint)
  • TABLE requirements_management_test_reports - FOREIGN KEY build_id (already bigint)
  • TABLE security_scans - FOREIGN KEY build_id (already bigint)
  • TABLE terraform_state_versions - FOREIGN KEY ci_build_id (already bigint)

So, we have to also follow the same approach for the 15 additional tables that reference it and are not covered by other tables' Primary Key conversion migrations.

Related Tracking Issue: #215017 (closed)

Edited by Yannis Roussos