Remove pages_deployments[project_id, path_prefix] unique index

What does this MR do and why?

Remove pages_deployments[project_id, path_prefix] unique index

Currently we keep pages deployments for about 30m after creating a new one. This is to avoid racing conditions between the time the new deployment is created and GitLab Pages is updates its caches with the latest deployment.

Therefore, while we use this mechanism, we cannot enforce uniqueness on pages_deployments for project_id and path_prefix, as more than one deploy for given path_prefix might exist.

The path_prefix is not being used yet, this work is being done in Add `pages_path_prefix` job option (!129534 - merged)

This is part of the introduction of the GitLab Pages Multiple Versions Support.

Related to:

• Epic:
  • &10914
• Issue that introduced the index:
  • #416488 (closed)
• Problem was found while working on
  • !129534 (merged)
  • #416492 (closed)

Changelog: added

Migrations

bundle exec rails db:migrate:up:main VERSION=20230823194111

main: == [advisory_lock_connection] object_id: 224740, pg_backend_pid: 73650
main: == 20230823194111 RemovePagesDeploymentsProjectIdPathPrefixUniqueIndex: migrating
main: -- transaction_open?()
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0828s
main: -- indexes(:pages_deployments)
main:    -> 0.0044s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0001s
main: -- remove_index(:pages_deployments, {:algorithm=>:concurrently, :name=>"index_pages_deployments_unique_path_prefix_by_project"})
main:    -> 0.0043s
main: -- execute("RESET statement_timeout")
main:    -> 0.0003s
main: == 20230823194111 RemovePagesDeploymentsProjectIdPathPrefixUniqueIndex: migrated (0.1039s)

main: == [advisory_lock_connection] object_id: 224740, pg_backend_pid: 73650

bundle exec rails db:migrate:up:ci VERSION=20230823194111

ci: == [advisory_lock_connection] object_id: 224800, pg_backend_pid: 74318
ci: == 20230823194111 RemovePagesDeploymentsProjectIdPathPrefixUniqueIndex: migrating
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.1842s
ci: -- indexes(:pages_deployments)
ci:    -> 0.0109s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0026s
ci: -- remove_index(:pages_deployments, {:algorithm=>:concurrently, :name=>"index_pages_deployments_unique_path_prefix_by_project"})
ci:    -> 0.0032s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0025s
ci: == 20230823194111 RemovePagesDeploymentsProjectIdPathPrefixUniqueIndex: migrated (0.2548s)

ci: == [advisory_lock_connection] object_id: 224800, pg_backend_pid: 74318

bundle exec rails db:migrate:down:main VERSION=20230823194111

main: == [advisory_lock_connection] object_id: 224800, pg_backend_pid: 75053
main: == 20230823194111 RemovePagesDeploymentsProjectIdPathPrefixUniqueIndex: reverting
main: == 20230823194111 RemovePagesDeploymentsProjectIdPathPrefixUniqueIndex: reverted (0.0179s)

main: == [advisory_lock_connection] object_id: 224800, pg_backend_pid: 75053

bundle exec rails db:migrate:down:ci VERSION=20230823194111

ci: == [advisory_lock_connection] object_id: 224740, pg_backend_pid: 75638
ci: == 20230823194111 RemovePagesDeploymentsProjectIdPathPrefixUniqueIndex: reverting
ci: == 20230823194111 RemovePagesDeploymentsProjectIdPathPrefixUniqueIndex: reverted (0.0082s)

ci: == [advisory_lock_connection] object_id: 224740, pg_backend_pid: 75638

Screenshots or screen recordings

Screenshots are required for UI changes, and strongly recommended for all other merge requests.

Before	After

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

• I have evaluated the MR acceptance checklist for this MR.