Drop 2 unused indexes on the PersonalAccessTokens table

Related to #524159 (closed)

Blocks !193375 (merged)

What does this MR do and why?

Drops 2 unused indexes

• index_expired_and_not_notified_personal_access_tokens (No usage since February)
  • https://dashboards.gitlab.net/goto/o7CYHefNR?orgId=1
• tmp_index_pats_on_notification_columns_and_expires_at (No usage for the past 6 months)
  • https://dashboards.gitlab.net/goto/ae53H6BNR?orgId=1

Database

bin/rails db:migrate

hakeem@habdul-razak--20240918-F45T4 gitlab % bin/rails db:migrate   
main: == [advisory_lock_connection] object_id: 136540, pg_backend_pid: 24540
main: == 20250604162441 DropExpiredAndNotNotifiedPatIndex: migrating ================
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0191s
main: -- indexes(:personal_access_tokens)
main:    -> 0.0030s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- remove_index(:personal_access_tokens, {:algorithm=>:concurrently, :name=>"index_expired_and_not_notified_personal_access_tokens"})
main:    -> 0.0027s
main: -- execute("RESET statement_timeout")
main:    -> 0.0003s
main: == 20250604162441 DropExpiredAndNotNotifiedPatIndex: migrated (0.0374s) =======

main: == [advisory_lock_connection] object_id: 136540, pg_backend_pid: 24540
ci: == [advisory_lock_connection] object_id: 136540, pg_backend_pid: 24541
ci: == 20250604162441 DropExpiredAndNotNotifiedPatIndex: migrating ================
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0004s
ci: -- indexes(:personal_access_tokens)
ci:    -> 0.0055s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0003s
ci: -- remove_index(:personal_access_tokens, {:algorithm=>:concurrently, :name=>"index_expired_and_not_notified_personal_access_tokens"})
ci:    -> 0.0033s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0003s
ci: == 20250604162441 DropExpiredAndNotNotifiedPatIndex: migrated (0.0205s) =======

ci: == [advisory_lock_connection] object_id: 136540, pg_backend_pid: 24541
main: == [advisory_lock_connection] object_id: 136540, pg_backend_pid: 24543
main: == 20250604162618 DropTmpOnNotificationAndExpiredAtOnPatIndex: migrating ======
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0003s
main: -- indexes(:personal_access_tokens)
main:    -> 0.0029s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- remove_index(:personal_access_tokens, {:algorithm=>:concurrently, :name=>"tmp_index_pats_on_notification_columns_and_expires_at"})
main:    -> 0.0031s
main: -- execute("RESET statement_timeout")
main:    -> 0.0003s
main: == 20250604162618 DropTmpOnNotificationAndExpiredAtOnPatIndex: migrated (0.0138s) 

main: == [advisory_lock_connection] object_id: 136540, pg_backend_pid: 24543
ci: == [advisory_lock_connection] object_id: 136540, pg_backend_pid: 24544
ci: == 20250604162618 DropTmpOnNotificationAndExpiredAtOnPatIndex: migrating ======
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0003s
ci: -- indexes(:personal_access_tokens)
ci:    -> 0.0038s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0003s
ci: -- remove_index(:personal_access_tokens, {:algorithm=>:concurrently, :name=>"tmp_index_pats_on_notification_columns_and_expires_at"})
ci:    -> 0.0034s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0003s
ci: == 20250604162618 DropTmpOnNotificationAndExpiredAtOnPatIndex: migrated (0.0180s) 

ci: == [advisory_lock_connection] object_id: 136540, pg_backend_pid: 24544

VERSION=20250604162441 bin/rails db:migrate:down:main && VERSION=20250604162618 bin/rails db:migrate:down:main

hakeem@habdul-razak--20240918-F45T4 gitlab % VERSION=20250604162441 bin/rails db:migrate:down:main && bin/rails db:migrate:down:ci

VERSION=20250604162618 bin/rails db:migrate:down:main && bin/rails db:migrate:down:ci
main: == [advisory_lock_connection] object_id: 136240, pg_backend_pid: 25039
main: == 20250604162441 DropExpiredAndNotNotifiedPatIndex: reverting ================
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0230s
main: -- index_exists?(:personal_access_tokens, [:id, :expires_at], {:where=>"impersonation = false AND revoked = false AND expire_notification_delivered = false", :name=>"index_expired_and_not_notified_personal_access_tokens", :algorithm=>:concurrently})
main:    -> 0.0040s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0011s
main: -- add_index(:personal_access_tokens, [:id, :expires_at], {:where=>"impersonation = false AND revoked = false AND expire_notification_delivered = false", :name=>"index_expired_and_not_notified_personal_access_tokens", :algorithm=>:concurrently})
main:    -> 0.0055s
main: -- execute("RESET statement_timeout")
main:    -> 0.0005s
main: == 20250604162441 DropExpiredAndNotNotifiedPatIndex: reverted (0.0515s) =======

main: == [advisory_lock_connection] object_id: 136240, pg_backend_pid: 25039
bin/rails aborted!
VERSION is required

Tasks: TOP => db:migrate:down:ci
(See full trace by running task with --trace)
main: == [advisory_lock_connection] object_id: 136240, pg_backend_pid: 25163
main: == 20250604162618 DropTmpOnNotificationAndExpiredAtOnPatIndex: reverting ======
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0219s
main: -- index_exists?(:personal_access_tokens, [:id], {:where=>"expire_notification_delivered IS TRUE AND seven_days_notification_sent_at IS NULL AND expires_at IS NOT NULL", :name=>"tmp_index_pats_on_notification_columns_and_expires_at", :algorithm=>:concurrently})
main:    -> 0.0036s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0005s
main: -- add_index(:personal_access_tokens, [:id], {:where=>"expire_notification_delivered IS TRUE AND seven_days_notification_sent_at IS NULL AND expires_at IS NOT NULL", :name=>"tmp_index_pats_on_notification_columns_and_expires_at", :algorithm=>:concurrently})
main:    -> 0.0047s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: == 20250604162618 DropTmpOnNotificationAndExpiredAtOnPatIndex: reverted (0.0456s) 

main: == [advisory_lock_connection] object_id: 136240, pg_backend_pid: 25163

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.