Some background on this issue is available:

• Give Jobsets an ID; add jobset_id to builds and jobs. #710 (comment)
• my personal notes: https://www.notion.so/grahamc/Foreign-ID-keys-on-Builds-for-Hydra-6d246851b1b0436eba09e1e00138358b
• part 1 and updates on how part 1's trial deployment is going: jobset_id, #710 Part 1 #711
• discourse announcement thread: https://discourse.nixos.org/t/upcoming-hydra-schema-change/5806

note: each commit here is pretty small and should be very easy to review.

Here is the proposed plan for this migration:

1. Add an id to the Jobsets table: serial, non-null, unique.
   • Fast.
   • Postgresql will automatically backfill, and new writes will automatically have them too.
   • Code changes: None. Fully backwards and forward compatible.
2. Add a jobset_id to the Jobs table: nullable, foreign key to Jobsets
   • Fast.
   • Code changes:
     • All places writing to Jobs should begin writing the jobset_id
       • I believe there is only one place:

       • hydra/src/script/hydra-eval-jobset

         Line 420 in 9da60e3

         my $job = $jobset->jobs->update_or_create({ name => $jobName });

3. Add a jobset_id to the Builds table: nullable, foreign key to Jobsets (3f074388)
   • Fast.
   • Code changes:
     • All places writing to Builds should begin writing the jobset_id
       • I believe there is only one place:

       • hydra/src/script/hydra-eval-jobset

         Line 465 in 9da60e3

         $build = $job->builds->create(

         (52283da9)
4. Backfill Jobs with jobset_id values.
   1. The naive way to backfill has many problems:
      1. Very slow
      2. Huge amount of time with a read lock.
      3. Rewrites the entire table on disk in one shot, causing a full 2x table bloat
   2. Solution:
      • Create a purpose-built tool to incrementally backfill the table:

        1. running this in a loop:

            UPDATE jobs
            SET jobset_id = (
              SELECT jobsets.id
              FROM jobsets
              WHERE jobsets.name = jobs.jobset
                AND jobsets.project = jobs.project
            )
            WHERE (jobs.project, jobs.jobset, jobs.name) in (
              SELECT jobsprime.project, jobsprime.jobset, jobsprime.name
              FROM jobs jobsprime
              WHERE jobsprime.jobset_id IS NULL
              FOR UPDATE SKIP LOCKED
              LIMIT 10000
            );
           

        2. Every N iterations, run VACUUM

      • Hydra can stay fully online during the entire migration
      • The subselect of a specific collection of IDs allows the write lock to only affect those rows.
      • VACUUM will prevent 2x table bloat from happening all at once
5. Backfill Builds with jobset_id values.
   1. The naive way to backfill has many problems:
      1. Very slow
      2. Huge amount of time with a read lock.
      3. Rewrites the entire table on disk in one shot, causing a full 2x table bloat
   2. Solution:
      • Create a purpose-built tool to incrementally backfill the table:

        1. running this in a loop:

            UPDATE builds
            SET jobset_id = (
              SELECT jobsets.id
              FROM jobsets
              WHERE jobsets.name = builds.jobset
                AND jobsets.project = builds.project
            )
            WHERE builds.id in (
              SELECT buildprime.id
              FROM builds buildprime
              WHERE buildprime.jobset_id IS NULL
              ORDER BY buildprime.id
              FOR UPDATE SKIP LOCKED
              LIMIT 10000
            );
           

        2. Every N iterations, run VACUUM

      • Hydra can stay fully online during the entire migration
      • The subselect of a specific collection of IDs allows the write lock to only affect those rows.
      • VACUUM will prevent 2x table bloat from happening all at once
6. Perform an explicit release and put this in to production, and run the backfill tool until all rows are updated.
   1. Monitor to see if new Builds or Jobs rows are added with null jobset_id fields, this would indicate a bug which needs to be fixed.
7. Modify the Builds table, doing two things in one transaction:
   1. Assert that there are no rows with a null jobset_id, any rows with a null jobset_id is likely from a bug we should fix.
   2. Alter the Builds table to make jobset_id not-null. Hopefully not very slow, I think postgresql will only validate no rows have a null jobset_id.
8. Modify the Jobs table, doing two things in one transaction:
   1. Assert that there are no rows with a null jobset_id, any rows with a null jobset_id is likely from a bug we should fix.
   2. Alter the Jobs table to make jobset_id not-null. Hopefully not very slow, I think postgresql will only validate no rows have a null jobset_id.
9. Alter the read paths to read through the jobset_id field for Builds and Jobs.

Under this plan we would:

1. merge the first half of the PR (up until Jobs.jobset_id: make not-null)
2. deploy to hydra.nixos.org
3. run hydra-init to add the nullable columns
4. run hydra-backfill-ids
5. once that is done, give the code a day or so in prod
6. run hydra-backfill-ids to see if any new records have been created with NULL jobset_id columns
7. if any bugs are found, fix & deploy those changes, repeating 5 & 6 until we see no more records with NULL jobset_id values.

THEN: merge the second half (migration making them not-null) and deploy to production.

This becomes a bit more complicated for users other than nixos.org. I have tried to make a clear warning in hydra-init. We might want to create a branch off of master after merging the first half, in case there are bugs found in the code after we have merged the second half.

Currently marking this as a draft, here is my personal to-do list before merging:

• Run the first half against a replica of production's database, and time how long the migration and backfiller takes
• Run the evaluator a bit to add more rows
• Run the second half of the migration and confirm it both works and doesn't take a long time
• Verify that the queries for the latest-finished URL completes quickly, and the EXPLAIN ANALYZE output is improved over the current state.
• Open two PRs, one for the first and and the other for the second half.
• Confirm for both of those PRs that nix-build ./release.nix -A build -A manual -A tests.install -A tests.api -A tests.notifications works a final time.
• Copy the flake migration from the flake branch #713