For Cells 1.0 we need a way to make sure that every entity in Organization 1 doesn't have links to any other Organization. To do that, we need to add sharding keys, see more in <https://docs.gitlab.com/ee/architecture/blueprints/organization/isolation.html> `ci_runners` table creates a challenge here: 1. it doesn't have a sharding key yet 2. it can belong to project/group 3. the sharding key would be **nullable** because runner can be an instance runner and not belong to anything 4. we allow users to **link** project runners **to any other project with no restrictions** 5. There are tables which depend on the runner, e.g. `runner_machines` which will inherit all the same limitation, **including sharding key being nullable** ### Proposed solution 1. [x] !166308: [Add partition prefix](https://gitlab.com/gitlab-org/gitlab/-/blob/df0d6654bb540cf7321e297554acd8adb3e2e3a4/app/models/ci/build.rb#L234-236) to runner tokens. 1. [x] https://gitlab.com/gitlab-org/gitlab/-/issues/493256: Add `sharding_key_id` column to `ci_runners` table 1. [x] %17.5: Backfill `sharding_key_id` - Update the app to populate `sharding_key_id` for new runners 1. [x] %17.6: [Post-backfill cleanup (Release N+1)](https://gitlab.com/gitlab-org/gitlab/-/issues/493384) 2. ~~_Optional_ %17.6: if we take the approach of only copying valid records to the partitioned table.~~ 1. [ ] ~~[Delete invalid records](https://gitlab.com/gitlab-org/gitlab/-/issues/500566).~~ 2. [ ] ~~https://gitlab.com/gitlab-org/gitlab/-/issues/498019+~~ 3. [ ] ~~Validate sharding key constraints.~~ 1. [x] #500447: Create routing table and partitions 1. [x] Create `ci_runners_e59bb2812d` routing table (exempt from sharding), and its 3 partitions: - `instance_type_ci_runners_e59bb2812d` will be attached to partition 1, and will be exempt from sharding. - `group_type_ci_runners_e59bb2812d` will be attached to partition 2, and will be sharded by `namespace: namespace_id`. - `project_type_ci_runners_e59bb2812d` will be attached to partition 3, and will be sharded by `project: project_id`. 1. [x] Mimic LFK definitions from `ci_runners` in `config/gitlab_loose_foreign_keys.yml` for the new `ci_runners_e59bb2812d`. 1. [x] Add missing FK definition between `ci_runners_e59bb2812d` and `ci_runner_projects` (resolves https://gitlab.com/gitlab-org/gitlab/-/issues/369084 for the partitioned table) 1. [x] Create triggers to keep `ci_runners` and each of the partitioned tables in sync. 1. [x] See if we want to enforce unique encrypted tokens across partitions with a trigger: <http://blog.ioguix.net/postgresql/2015/02/05/Partitionning-and-constraints-part-1.html>. 1. [x] Copy data to partitioned table (`enqueue_partitioning_data_migration`). - %17.6: 1. [x] !166520+. This is estimated to take just over 2 hours for .com. - after next required stop (%17.9): 1. [x] https://gitlab.com/gitlab-org/gitlab/-/issues/504277+ 2. [x] https://gitlab.com/gitlab-org/gitlab/-/issues/369084+ 2. [x] Ensure `sharding_key_id` values are correctly maintained: - [x] #511343+ - [x] https://gitlab.com/gitlab-org/gitlab/-/issues/512075+ - [x] https://gitlab.com/gitlab-org/gitlab/-/issues/517114+ - [x] https://gitlab.com/gitlab-org/gitlab/-/issues/517523+ 1. [ ] Adapt FKs: - [x] `ci_running_builds.runner_id` will need to point to `ci_runners_e59bb2812d` - Instance runners: - [x] Make `ci_cost_settings`'s `fk_rails_6a70651f75` reference `instance_type_ci_runners_e59bb2812d` table, since this table only applies to instance runners. - Group runners: - [ ] Add an LFK pointing to `namespaces`. - [x] Make `ci_runner_namespaces`'s `fk_rails_8767676b7a` reference `group_type_ci_runners_e59bb2812d` table, since this table only applies to group runners. - Project runners: - [ ] Add an LFK pointing to `projects`. - [x] `ci_runner_projects` [doesn't have a FK pointing to `ci_runners`](https://gitlab.com/gitlab-org/gitlab/-/issues/369084). We can add a new FK pointing to `project_type_ci_runners_e59bb2812d`, after - [x] https://gitlab.com/gitlab-org/gitlab/-/issues/504963: Call `replace_with_partitioned_table` and set `Ci::Runner.primary_key = [:id, :runner_type]`. ensuring that no records exist that point to missing project runners. 2. [x] https://gitlab.com/gitlab-org/gitlab/-/issues/504963+ 2. [x] https://gitlab.com/gitlab-org/gitlab/-/issues/504965+ 3. [ ] Optimize code that can take advantage of runner_type PK, such as `Ci::Runner.find` when we know the runner type in addition to the ID. 3. [x] #516060: Once everything is verified to be running as expected, drop `ci_runners_archived` table. 3. [x] !171172: Change `Ci::Runner#owner_project` to return `project_id`, instead of relying on a join with `ci_project_runners`. 4. [x] https://gitlab.com/gitlab-org/gitlab/-/issues/523694+ 5. [x] https://gitlab.com/gitlab-org/gitlab/-/issues/548139+ 6. [x] https://gitlab.com/gitlab-org/gitlab/-/issues/523850+ 7. [x] https://gitlab.com/gitlab-org/gitlab/-/issues/523851+ 8. [x] https://gitlab.com/gitlab-org/gitlab/-/issues/557226+ 9. [x] https://gitlab.com/gitlab-org/gitlab/-/issues/523852+ 10. [x] https://gitlab.com/gitlab-org/gitlab/-/issues/525293+ 11. [x] https://gitlab.com/gitlab-org/gitlab/-/issues/547654+ 12. [x] https://gitlab.com/gitlab-org/gitlab/-/issues/547650+ 13. [ ] Bonus: We could drop `ci_runner_namespaces` if we don't plan on allowing sharing group runners across multiple groups. 14. [x] Mark `db/docs/ci_runners.yml` as `exempt_from_sharding: true`. This will affect Org mover as it is planned to skip `exempt_from_sharding` tables from org mover. 15. [x] Open a follow-up issue to address impact on Org Mover