Schedule the creation of a unique partial index on lfs_objects_projects

What does this MR do and why?

We have this validation in LfsObjectsProjects: https://gitlab.com/gitlab-org/gitlab/-/blob/cf47572fd486a672f8297779fa7e3ee80790fc89/app/models/lfs_objects_project.rb#L11

validates :lfs_object_id, uniqueness: { scope: [:project_id, :repository_type], message: "already exists in repository" }

An index was created in a previous MR(!177308 (merged)) to enforce this validation:

-- idx_lfs_objects_projects_on_project_id_lfs_object_id_repo_type
CREATE UNIQUE INDEX CONCURRENTLY "idx_lfs_objects_projects_on_project_id_lfs_object_id_repo_type" ON "lfs_objects_projects" ("project_id",
                                                                                                                             "lfs_object_id",
                                                                                                                             "repository_type")

• Since repository_type can be null, that index does not prevent rows with duplicate (project_id,lfs_object_id) and when repository_type is null.
• A separate partial index was created in another MR(!177919 (merged)) to handle those cases.
• The motivation for this MR is to schedule the creation of another smaller more specific partial index for when repository_type is not null.
  • This will make the previously created idx_lfs_objects_projects_on_project_id_lfs_object_id_repo_type redundant and allow us to drop it in a future MR.

References

Please include cross links to any resources that are relevant to this MR. This will give reviewers and future readers helpful context to give an efficient review of the changes introduced.

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

How to set up and validate locally

Try something similar to the steps described in: https://docs.gitlab.com/ee/development/database/adding_database_indexes.html#verify-indexes-created-asynchronously

For example

1. Enable async index operations (Feature.enable(:database_async_index_operations), Feature.enable(:database_async_index_creation) and Feature.enable(:database_reindexing))
2. Run migrations bundle exec rails db:migrate
3. Check that async operation has been scheduled in the postgres_async_indexes table (SELECT definition from postgres_async_indexes)
4. Rollback the migration and check that the async operation is no long scheduled
5. Run the migration again and execute the async operation (bundle exec rails gitlab:db:execute_async_index_operations:all)
6. Check that the unique index has been added to lfs_objects_projects Related to #467119 (closed)