Partition ci_job_artifacts table
Description
After the recent incident related to unlocking artifacts. we should consider our next candidate for partitioning to be one the of the biggest CI table: ci_job_artifacts
.
Having smaller tables will allow autovacuum to run more often and in parallel which could avoid database saturation as we've experienced it.
Technical Proposal
-
Prepare ci_job_artifacts
for partitioning-
Add partition_id
to table with referenced FK -
Add partition_id
to all unique indexes -
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_job_artifacts
table -
Revert changes to partitioning tests
partition_id
Unique index needs to include "index_ci_job_artifacts_on_job_id_and_file_type" UNIQUE, btree (job_id, file_type)
partition_id
Foreign keys that needs to include table name | FK index created | FK created | MR | FK valid on .com | FK valid |
---|---|---|---|---|---|
ci_build_trace_metadata |
|||||
ci_job_artifact_states |
All FKs
Referenced by:
TABLE "ci_build_trace_metadata" CONSTRAINT "fk_21d25cac1a" FOREIGN KEY (trace_artifact_id) REFERENCES ci_job_artifacts(id) ON DELETE CASCADE
TABLE "ci_job_artifact_states" CONSTRAINT "fk_rails_80a9cba3b2" FOREIGN KEY (job_artifact_id) REFERENCES ci_job_artifacts(id) ON DELETE CASCADE
partition_id
Primary key include "ci_job_artifacts_pkey" PRIMARY KEY, btree (id)
Learning
We will use this section to document the challenges we faced to partition ci_job_artifacts
table.