Add partial index on oauth_access_tokens for cleanup worker

What does this MR do and why?

Add partial index on oauth_access_tokens for cleanup worker

Adds partial index on (id) WHERE revoked_at IS NOT NULL to optimize the upcoming OauthAccessTokenArchiveWorker.

Relates: https://gitlab.com/gitlab-org/gitlab/-/issues/521855
Changelog: performance

References

Main: https://gitlab.com/gitlab-org/gitlab/-/issues/521855
Exception: https://gitlab.com/gitlab-org/database-team/team-tasks/-/issues/545
Worker: !202767 (merged)

Details

Table size: 125 GB (bloated - actual data ~10 GB)
Estimated storage cost: ~445 MB

Create INDEX

Create INDEX Concurrently

Database

Migration

>>> Executing: bin/rails db:migrate:up:main VERSION=20251003143132
main: == [advisory_lock_connection] object_id: 129000, pg_backend_pid: 32883
main: == 20251003143132 AddIndexToOauthAccessTokensIdWhereRevoked: migrating ========
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0146s
main: -- index_exists?(:oauth_access_tokens, [:id], {:where=>"revoked_at IS NOT NULL", :name=>"index_oauth_access_tokens_on_id_where_revoked", :algorithm=>:concurrently})
main:    -> 0.0024s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- add_index(:oauth_access_tokens, [:id], {:where=>"revoked_at IS NOT NULL", :name=>"index_oauth_access_tokens_on_id_where_revoked", :algorithm=>:concurrently})
main:    -> 0.2940s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: == 20251003143132 AddIndexToOauthAccessTokensIdWhereRevoked: migrated (0.3582s) 

main: == [advisory_lock_connection] object_id: 129000, pg_backend_pid: 32883
>>> Executing: bin/rails db:migrate:up:ci VERSION=20251003143132
ci: == [advisory_lock_connection] object_id: 129000, pg_backend_pid: 32942
ci: == 20251003143132 AddIndexToOauthAccessTokensIdWhereRevoked: migrating ========
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0193s
ci: -- index_exists?(:oauth_access_tokens, [:id], {:where=>"revoked_at IS NOT NULL", :name=>"index_oauth_access_tokens_on_id_where_revoked", :algorithm=>:concurrently})
ci:    -> 0.0060s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0003s
ci: -- add_index(:oauth_access_tokens, [:id], {:where=>"revoked_at IS NOT NULL", :name=>"index_oauth_access_tokens_on_id_where_revoked", :algorithm=>:concurrently})
ci:    -> 0.0050s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0003s
ci: == 20251003143132 AddIndexToOauthAccessTokensIdWhereRevoked: migrated (0.0824s) 

ci: == [advisory_lock_connection] object_id: 129000, pg_backend_pid: 32942

Rollback

>>> Executing: bin/rails db:migrate:down:main VERSION=20251003143132
main: == [advisory_lock_connection] object_id: 129000, pg_backend_pid: 35042
main: == 20251003143132 AddIndexToOauthAccessTokensIdWhereRevoked: reverting ========
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0139s
main: -- indexes(:oauth_access_tokens)
main:    -> 0.0045s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- remove_index(:oauth_access_tokens, {:algorithm=>:concurrently, :name=>"index_oauth_access_tokens_on_id_where_revoked"})
main:    -> 0.0042s
main: -- execute("RESET statement_timeout")
main:    -> 0.0003s
main: == 20251003143132 AddIndexToOauthAccessTokensIdWhereRevoked: reverted (0.0712s) 

main: == [advisory_lock_connection] object_id: 129000, pg_backend_pid: 35042
>>> Executing: bin/rails db:migrate:down:ci VERSION=20251003143132
ci: == [advisory_lock_connection] object_id: 129000, pg_backend_pid: 35105
ci: == 20251003143132 AddIndexToOauthAccessTokensIdWhereRevoked: reverting ========
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0158s
ci: -- indexes(:oauth_access_tokens)
ci:    -> 0.0041s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0005s
ci: -- remove_index(:oauth_access_tokens, {:algorithm=>:concurrently, :name=>"index_oauth_access_tokens_on_id_where_revoked"})
ci:    -> 0.0091s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0004s
ci: == 20251003143132 AddIndexToOauthAccessTokensIdWhereRevoked: reverted (0.0978s) 

ci: == [advisory_lock_connection] object_id: 129000, pg_backend_pid: 35105

MR acceptance checklist

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