Skip to content

Partition ci_builds table

Description

After we successfully partitioned ci_builds_metadata, let's partition one the most used CI table: ci_builds.

Technical Proposal

  • Prepare ci_builds for partitioning
    • Add partition_id to table with referenced FK
    • Add index on each table with (partition_id, id)
    • Rewrite FK to include partition_id
    • Rewrite indexes to include partition_id
    • Rewrite PK to include partition_id (needs to be done after all FK are redefined)
  • Partition ci_builds table
  • Revert changes to partitioning tests
  • Revert !120419 (merged)

Foreign keys that needs to include partition_id

table name FK index created FK created MR FK valid on .com FK valid
ci_unit_test_failures ✅ ✅ !106717 (merged) ✅ ✅
ci_sources_pipelines ✅ ✅ !110367 (merged), !110368 (merged) ✅ ✅
ci_resources ✅ ✅ !109337 (merged) ✅ ✅
p_ci_builds_metadata ✅ ✅ !110388 (merged), !112316 (merged), !112788 (merged), !113655 (merged) ✅ ✅
ci_build_pending_states ✅ ✅ !106717 (merged) ✅ ✅
ci_build_trace_chunks ✅ ✅ !106717 (merged) ✅ ✅
ci_build_report_results ✅ ✅ !107476 (merged), !112461 (merged) ✅ ✅
ci_build_needs ✅ ✅ !107547 (merged), !112462 (merged) ✅ ✅
ci_builds_runner_session ✅ ✅ !109316 (merged) ✅ ✅
ci_pending_builds ✅ ✅ !109311 (merged) ✅ ✅
ci_build_trace_metadata ✅ ✅ !111333 (merged), !110392 (merged), !111163 (merged) ✅ ✅
ci_job_artifacts ✅ ✅ !111333 (merged), !110395 (merged), !111166 (merged) ✅ ✅
ci_running_builds ✅ ✅ !109308 (merged) ✅ ✅
ci_job_variables ✅ ✅ !110397 (merged), !111158 (merged) ✅ ✅
All FKs 
Referenced by:
    TABLE "ci_unit_test_failures" CONSTRAINT "fk_0f09856e1f" FOREIGN KEY (build_id) REFERENCES ci_builds(id) ON DELETE CASCADE
    TABLE "ci_sources_pipelines" CONSTRAINT "fk_be5624bf37" FOREIGN KEY (source_job_id) REFERENCES ci_builds(id) ON DELETE CASCADE
    TABLE "ci_resources" CONSTRAINT "fk_e169a8e3d5" FOREIGN KEY (build_id) REFERENCES ci_builds(id) ON DELETE SET NULL
    TABLE "p_ci_builds_metadata" CONSTRAINT "fk_e20479742e" FOREIGN KEY (build_id) REFERENCES ci_builds(id) ON DELETE CASCADE
    TABLE "ci_build_pending_states" CONSTRAINT "fk_rails_0bbbfeaf9d" FOREIGN KEY (build_id) REFERENCES ci_builds(id) ON DELETE CASCADE
    TABLE "ci_build_trace_chunks" CONSTRAINT "fk_rails_1013b761f2" FOREIGN KEY (build_id) REFERENCES ci_builds(id) ON DELETE CASCADE
    TABLE "ci_build_report_results" CONSTRAINT "fk_rails_16cb1ff064" FOREIGN KEY (build_id) REFERENCES ci_builds(id) ON DELETE CASCADE
    TABLE "ci_build_needs" CONSTRAINT "fk_rails_3cf221d4ed" FOREIGN KEY (build_id) REFERENCES ci_builds(id) ON DELETE CASCADE
    TABLE "ci_builds_runner_session" CONSTRAINT "fk_rails_70707857d3" FOREIGN KEY (build_id) REFERENCES ci_builds(id) ON DELETE CASCADE
    TABLE "ci_pending_builds" CONSTRAINT "fk_rails_725a2644a3" FOREIGN KEY (build_id) REFERENCES ci_builds(id) ON DELETE CASCADE
    TABLE "ci_build_trace_metadata" CONSTRAINT "fk_rails_aebc78111f" FOREIGN KEY (build_id) REFERENCES ci_builds(id) ON DELETE CASCADE
    TABLE "ci_job_artifacts" CONSTRAINT "fk_rails_c5137cb2c1" FOREIGN KEY (job_id) REFERENCES ci_builds(id) ON DELETE CASCADE
    TABLE "ci_running_builds" CONSTRAINT "fk_rails_da45cfa165" FOREIGN KEY (build_id) REFERENCES ci_builds(id) ON DELETE CASCADE
    TABLE "ci_job_variables" CONSTRAINT "fk_rails_fbf3b34792" FOREIGN KEY (job_id) REFERENCES ci_builds(id) ON DELETE CASCADE

Learning

We will use this section to document the challenges we faced to partition ci_builds table.

  • Use ON UPDATE CASCADE introduced in !108535 (merged) to update correctly partition_id with FKs
Edited by Marius Bobin