As discussed in https://gitlab.com/gitlab-org/gitlab/-/merge_requests/182451#note_2372325700, we should create a new table to deduplicate `project_authorizations` and then swap the new table with the old table. ## Implementation plan 1. Create the following table: ``` CREATE TABLE project_authorizations_for_migration ( user_id bigint NOT NULL, project_id bigint NOT NULL, access_level smallint NOT NULL # changed from integer to smallint to save storage ); ``` 2. The primary key should be: ``` ALTER TABLE ONLY project_authorizations_for_migration ADD CONSTRAINT project_authorizations_for_migration_pkey PRIMARY KEY (user_id, project_id); # Dropped access level drop primary key as it will be unique ``` 3. Index: ``` CREATE INDEX index_project_authorizations_for_migration_on_project_user_access_level ON project_authorizations_for_migration USING btree (project_id, user_id); # (user_id, project_id) is unique because of primary key so we don't need a unique index. ``` 4. Foreign key: ``` FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE; ``` 5. Loose foreign key: ``` project_authorizations_for_migration: - table: users column: user_id on_delete: async_delete ``` 6. Add trigger(s) to keep the data in sync between `project_authorizations_for_migration` and `project_authorizations`.