Remove personal_access_tokens indexes added by MR 193375

What does this MR do and why?

This MR removes index_pats_on_user_id_and_created_at_and_pat_id, index_pats_on_user_id_and_expires_at_and_pat_id, and index_pats_on_user_id_and_last_used_at_and_pat_id indexes. We added those indexes in !193375 (merged) for implementation that is behind the credentials_inventory_pat_finder FF.

That implementation is disabled by another optimize_credentials_inventory FF and in !209392 we are about to delete that implementation entirely.

Database

bin/rails db:migrate RAILS_ENV=test

main: == [advisory_lock_connection] object_id: 170380, pg_backend_pid: 33608
main: == 20251020130449 DropIndexPatsOnUserIdAndCreatedAtAndPatId: migrating ========
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0039s
main: -- indexes(:personal_access_tokens)
main:    -> 0.0069s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0004s
main: -- remove_index(:personal_access_tokens, {:algorithm=>:concurrently, :name=>"index_pats_on_user_id_and_created_at_and_pat_id"})
main:    -> 0.0018s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: == 20251020130449 DropIndexPatsOnUserIdAndCreatedAtAndPatId: migrated (0.0289s)

main: == [advisory_lock_connection] object_id: 170380, pg_backend_pid: 33608
ci: == [advisory_lock_connection] object_id: 170380, pg_backend_pid: 33609
ci: == 20251020130449 DropIndexPatsOnUserIdAndCreatedAtAndPatId: migrating ========
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0006s
ci: -- indexes(:personal_access_tokens)
ci:    -> 0.0057s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0004s
ci: -- remove_index(:personal_access_tokens, {:algorithm=>:concurrently, :name=>"index_pats_on_user_id_and_created_at_and_pat_id"})
ci:    -> 0.0019s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0004s
ci: == 20251020130449 DropIndexPatsOnUserIdAndCreatedAtAndPatId: migrated (0.0296s)

ci: == [advisory_lock_connection] object_id: 170380, pg_backend_pid: 33609
main: == [advisory_lock_connection] object_id: 170380, pg_backend_pid: 33611
main: == 20251020134120 DropIndexPatsOnUserIdAndExpiresAtAndPatId: migrating ========
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0007s
main: -- indexes(:personal_access_tokens)
main:    -> 0.0060s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0005s
main: -- remove_index(:personal_access_tokens, {:algorithm=>:concurrently, :name=>"index_pats_on_user_id_and_expires_at_and_pat_id"})
main:    -> 0.0022s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: == 20251020134120 DropIndexPatsOnUserIdAndExpiresAtAndPatId: migrated (0.0214s)

main: == [advisory_lock_connection] object_id: 170380, pg_backend_pid: 33611
ci: == [advisory_lock_connection] object_id: 170380, pg_backend_pid: 33612
ci: == 20251020134120 DropIndexPatsOnUserIdAndExpiresAtAndPatId: migrating ========
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0007s
ci: -- indexes(:personal_access_tokens)
ci:    -> 0.0078s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0005s
ci: -- remove_index(:personal_access_tokens, {:algorithm=>:concurrently, :name=>"index_pats_on_user_id_and_expires_at_and_pat_id"})
ci:    -> 0.0018s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0004s
ci: == 20251020134120 DropIndexPatsOnUserIdAndExpiresAtAndPatId: migrated (0.0321s)

ci: == [advisory_lock_connection] object_id: 170380, pg_backend_pid: 33612
main: == [advisory_lock_connection] object_id: 170380, pg_backend_pid: 33614
main: == 20251020134409 DropIndexPatsOnUserIdAndLastUsedAtAndPatId: migrating =======
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0009s
main: -- indexes(:personal_access_tokens)
main:    -> 0.0056s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0004s
main: -- remove_index(:personal_access_tokens, {:algorithm=>:concurrently, :name=>"index_pats_on_user_id_and_last_used_at_and_pat_id"})
main:    -> 0.0019s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: == 20251020134409 DropIndexPatsOnUserIdAndLastUsedAtAndPatId: migrated (0.0208s)

main: == [advisory_lock_connection] object_id: 170380, pg_backend_pid: 33614
ci: == [advisory_lock_connection] object_id: 170380, pg_backend_pid: 33615
ci: == 20251020134409 DropIndexPatsOnUserIdAndLastUsedAtAndPatId: migrating =======
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0008s
ci: -- indexes(:personal_access_tokens)
ci:    -> 0.0071s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0005s
ci: -- remove_index(:personal_access_tokens, {:algorithm=>:concurrently, :name=>"index_pats_on_user_id_and_last_used_at_and_pat_id"})
ci:    -> 0.0019s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0004s
ci: == 20251020134409 DropIndexPatsOnUserIdAndLastUsedAtAndPatId: migrated (0.0336s)

ci: == [advisory_lock_connection] object_id: 170380, pg_backend_pid: 33615

bin/rails db:migrate:down:main VERSION=20251020130449 RAILS_ENV=test

main: == [advisory_lock_connection] object_id: 127020, pg_backend_pid: 34008
main: == 20251020130449 DropIndexPatsOnUserIdAndCreatedAtAndPatId: reverting ========
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0037s
main: -- index_exists?(:personal_access_tokens, [:user_id, :created_at, :id], {:where=>"impersonation = false", :name=>"index_pats_on_user_id_and_created_at_and_pat_id", :algorithm=>:concurrently})
main:    -> 0.0070s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0005s
main: -- add_index(:personal_access_tokens, [:user_id, :created_at, :id], {:where=>"impersonation = false", :name=>"index_pats_on_user_id_and_created_at_and_pat_id", :algorithm=>:concurrently})
main:    -> 0.0030s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: == 20251020130449 DropIndexPatsOnUserIdAndCreatedAtAndPatId: reverted (0.0440s)

main: == [advisory_lock_connection] object_id: 127020, pg_backend_pid: 34008

bin/rails db:migrate:down:main VERSION=20251020134120 RAILS_ENV=test

main: == [advisory_lock_connection] object_id: 126500, pg_backend_pid: 34383
main: == 20251020134120 DropIndexPatsOnUserIdAndExpiresAtAndPatId: reverting ========
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0045s
main: -- index_exists?(:personal_access_tokens, [:user_id, :expires_at, :id], {:order=>{:id=>:desc}, :where=>"impersonation = false", :name=>"index_pats_on_user_id_and_expires_at_and_pat_id", :algorithm=>:concurrently})
main:    -> 0.0047s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0006s
main: -- add_index(:personal_access_tokens, [:user_id, :expires_at, :id], {:order=>{:id=>:desc}, :where=>"impersonation = false", :name=>"index_pats_on_user_id_and_expires_at_and_pat_id", :algorithm=>:concurrently})
main:    -> 0.0032s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: == 20251020134120 DropIndexPatsOnUserIdAndExpiresAtAndPatId: reverted (0.0509s) 

main: == [advisory_lock_connection] object_id: 126500, pg_backend_pid: 34383

bin/rails db:migrate:down:main VERSION=20251020134409 RAILS_ENV=test

main: == [advisory_lock_connection] object_id: 126500, pg_backend_pid: 34535
main: == 20251020134409 DropIndexPatsOnUserIdAndLastUsedAtAndPatId: reverting =======
main: -- transaction_open?(nil)                                                          
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)                                                                                                                                       main:    -> 0.0052s
main: -- index_exists?(:personal_access_tokens, [:user_id, :last_used_at, :id], {:where=>"impersonation = false", :name=>"index_pats_on_user_id_and_last_used_at_and_pat_id", :alg
orithm=>:concurrently})                    
main:    -> 0.0050s
main: -- execute("SET statement_timeout TO 0")                                         
main:    -> 0.0005s
main: -- add_index(:personal_access_tokens, [:user_id, :last_used_at, :id], {:where=>"impersonation = false", :name=>"index_pats_on_user_id_and_last_used_at_and_pat_id", :algorit
hm=>:concurrently})     
main:    -> 0.0038s     
main: -- execute("RESET statement_timeout")
main:    -> 0.0019s     
main: == 20251020134409 DropIndexPatsOnUserIdAndLastUsedAtAndPatId: reverted (0.0495s) 
                                            
main: == [advisory_lock_connection] object_id: 126500, pg_backend_pid: 34535

References

• [Feature flag] Cleanup optimize_credentials_inv... (#575909)
• Increase the performance of the PersonalAccessT... (!193375 - merged)

Screenshots or screen recordings

Before	After

How to set up and validate locally

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.