Skip to content

Convert ci_builds.id to bigint - Step 2: Finalize background migrations and swap columns

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.

With #325618 (closed) we have started the process of converting it to bigint (int8) type.

In this issue we'll address the second step of the process:

  • Clean up the background migrations

  • Drop the DEFAULT 0 from the affected columns

  • Concurrently add a Unique index for the PK (ci_builds_pkey)

  • Concurrently add any additional indexes that include the converted columns

    id:
  "index_ci_builds_on_commit_id_artifacts_expired_at_and_id" btree (commit_id, artifacts_expire_at, id) 
  "index_ci_builds_on_project_id_and_id" btree (project_id, id)
  "index_ci_builds_on_runner_id_and_id_desc" btree (runner_id, id DESC)
  "index_for_resource_group" btree (resource_group_id, id)
  "index_security_ci_builds_on_name_and_id_parser_features" btree (name, id)
  
 stage_id:
  "index_ci_builds_on_stage_id" btree (stage_id)

 stage_idx (if it is related to stage_id and requires conversion):
  "index_ci_builds_on_commit_id_and_stage_idx_and_created_at" btree (commit_id, stage_idx, created_at)

  • Single Transaction: Drop old PK, define new PK, swap sequence, swap columns

  • Single Transaction: Drop old FK, define new FK (as invalid), swap columns, validate FK

  • Drop the old columns

Same for the 17 tables that reference the ci_builds table:

  • 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_pending_states - FOREIGN KEY build_id
  • TABLE ci_build_trace_chunks - FOREIGN KEY build_id
  • TABLE ci_build_report_results - FOREIGN KEY build_id
  • TABLE ci_build_needs - FOREIGN KEY build_id
  • 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_job_variables - FOREIGN KEY job_id
  • TABLE ci_resources - FOREIGN KEY build_id
  • TABLE ci_sources_pipelines - FOREIGN KEY source_job_id
  • TABLE ci_test_case_failures - FOREIGN KEY build_id
  • TABLE ci_unit_test_failures - FOREIGN KEY build_id
  • TABLE pages_deployments - FOREIGN KEY ci_build_id
  • TABLE requirements_management_test_reports - FOREIGN KEY build_id
  • TABLE security_scans - FOREIGN KEY build_id
  • TABLE terraform_state_versions - FOREIGN KEY ci_build_id

Previous Implementation Issue: #325618 (closed)

Related Tracking Issue: #215017 (closed)