Finish list partitioning ci_job_artifact_states (steps 6 to 11)

Everyone can contribute. Help move this issue forward while earning points, leveling up and collecting rewards.

Goal

Finish converting ci_job_artifact_states into a table that is list partitioned by partition_id, following the list partitioning guide. Steps 1 to 5 are done. Steps 6 to 11 remain. For why, see the parent epic Partition table ci_job_artifact_states (gitlab-org#16755).

Context

  • The table stores Geo verification state (checksum, state, retries) for each CI job artifact. The model is Geo::JobArtifactState.
  • It has one row per row in p_ci_job_artifacts, which is already list partitioned by partition_id. CI tables must use the same strategy.
  • Rows exist only on instances that run Geo, so its size depends on the instance. On self-managed and Dedicated Geo sites it can be large.

Steps

Step in the guide Issue State
1. Add partition key Done while partitioning ci_job_artifacts Done
2. Create required indexes #525447 (closed) Done
3. Enforce unique constraint #525449 (closed) Done
4. Enforce foreign key constraint #525450 (closed) Done
5. Swap primary key #525451 (closed) Done
6. Create parent table and attach the existing table #525452 Next. !190290 (closed) is a starting point. It closed unmerged when the work was deprioritized.
7. Re-point foreign keys to the parent table #525453 Open
8. Ensure ID uniqueness across partitions #525454 Open
9. Analyze and create new partitions #525455 Open
10. Update the application to use the partitioned table #525456 Open
11. Move the first partition into the dynamic schema No issue yet Open

Watch out for

  • Initial partition values (step 6). The open review question on !190290 (closed) was how to choose the initial partition_id values so the migration works on GitLab.com, Dedicated and self-managed. Values must match the partitions in p_ci_job_artifacts on each instance, so a static list won't work. Settle this first.
  • Large tables outside GitLab.com. The migrations run on every instance. Constraint validation may need an asynchronous approach where the table is big.
  • Geo writes to this table. The step 5 key change broke Geo::VerificationStateBackfillService on Geo primaries, a severity 2 bug: Geo VerificationStateBackfillService errors on ... (#557340 - closed). At each step, test Geo verification backfill and upserts against the new keys.
  • Triggers. trigger_a465de38164e and trigger_mark_geo_ci_job_artifact_summary_dirty must still fire after the conversion.
Generated by Claude Opus 5.5 high in Claude Code

Session a324da6e-c3e3-425a-bbb8-92214e040060

Edited by Michael Kozono