From 207b8b454b5fbefa1c545ff58cf580c5fc91fdf5 Mon Sep 17 00:00:00 2001
From: Bogdan Denkovych <bdenkovych@gitlab.com>
Date: Mon, 20 Oct 2025 16:08:08 +0300
Subject: [PATCH] Remove personal_access_tokens indexes added by MR 193375

Related to https://gitlab.com/gitlab-org/gitlab/-/merge_requests/193375
and https://gitlab.com/gitlab-org/gitlab/-/issues/575909

Changelog: removed
---
 ...ts_on_user_id_and_created_at_and_pat_id.rb | 22 ++++++++++++++++++
 ...ts_on_user_id_and_expires_at_and_pat_id.rb | 23 +++++++++++++++++++
 ..._on_user_id_and_last_used_at_and_pat_id.rb | 22 ++++++++++++++++++
 db/schema_migrations/20251020130449           |  1 +
 db/schema_migrations/20251020134120           |  1 +
 db/schema_migrations/20251020134409           |  1 +
 db/structure.sql                              |  6 -----
 spec/db/schema_spec.rb                        |  1 -
 8 files changed, 70 insertions(+), 7 deletions(-)
 create mode 100644 db/migrate/20251020130449_drop_index_pats_on_user_id_and_created_at_and_pat_id.rb
 create mode 100644 db/migrate/20251020134120_drop_index_pats_on_user_id_and_expires_at_and_pat_id.rb
 create mode 100644 db/migrate/20251020134409_drop_index_pats_on_user_id_and_last_used_at_and_pat_id.rb
 create mode 100644 db/schema_migrations/20251020130449
 create mode 100644 db/schema_migrations/20251020134120
 create mode 100644 db/schema_migrations/20251020134409

diff --git a/db/migrate/20251020130449_drop_index_pats_on_user_id_and_created_at_and_pat_id.rb b/db/migrate/20251020130449_drop_index_pats_on_user_id_and_created_at_and_pat_id.rb
new file mode 100644
index 00000000000000..fd4c949fd3ab98
--- /dev/null
+++ b/db/migrate/20251020130449_drop_index_pats_on_user_id_and_created_at_and_pat_id.rb
@@ -0,0 +1,22 @@
+# frozen_string_literal: true
+
+class DropIndexPatsOnUserIdAndCreatedAtAndPatId < Gitlab::Database::Migration[2.3]
+  disable_ddl_transaction!
+
+  milestone '18.6'
+
+  INDEX_NAME = 'index_pats_on_user_id_and_created_at_and_pat_id'
+
+  def up
+    remove_concurrent_index_by_name(:personal_access_tokens, INDEX_NAME)
+  end
+
+  def down
+    add_concurrent_index(
+      :personal_access_tokens,
+      [:user_id, :created_at, :id],
+      where: 'impersonation = false',
+      name: INDEX_NAME
+    )
+  end
+end
diff --git a/db/migrate/20251020134120_drop_index_pats_on_user_id_and_expires_at_and_pat_id.rb b/db/migrate/20251020134120_drop_index_pats_on_user_id_and_expires_at_and_pat_id.rb
new file mode 100644
index 00000000000000..15d4d411f5fb91
--- /dev/null
+++ b/db/migrate/20251020134120_drop_index_pats_on_user_id_and_expires_at_and_pat_id.rb
@@ -0,0 +1,23 @@
+# frozen_string_literal: true
+
+class DropIndexPatsOnUserIdAndExpiresAtAndPatId < Gitlab::Database::Migration[2.3]
+  disable_ddl_transaction!
+
+  milestone '18.6'
+
+  INDEX_NAME = 'index_pats_on_user_id_and_expires_at_and_pat_id'
+
+  def up
+    remove_concurrent_index_by_name(:personal_access_tokens, INDEX_NAME)
+  end
+
+  def down
+    add_concurrent_index(
+      :personal_access_tokens,
+      [:user_id, :expires_at, :id],
+      order: { id: :desc },
+      where: 'impersonation = false',
+      name: INDEX_NAME
+    )
+  end
+end
diff --git a/db/migrate/20251020134409_drop_index_pats_on_user_id_and_last_used_at_and_pat_id.rb b/db/migrate/20251020134409_drop_index_pats_on_user_id_and_last_used_at_and_pat_id.rb
new file mode 100644
index 00000000000000..03ca454fb1c761
--- /dev/null
+++ b/db/migrate/20251020134409_drop_index_pats_on_user_id_and_last_used_at_and_pat_id.rb
@@ -0,0 +1,22 @@
+# frozen_string_literal: true
+
+class DropIndexPatsOnUserIdAndLastUsedAtAndPatId < Gitlab::Database::Migration[2.3]
+  disable_ddl_transaction!
+
+  milestone '18.6'
+
+  INDEX_NAME = 'index_pats_on_user_id_and_last_used_at_and_pat_id'
+
+  def up
+    remove_concurrent_index_by_name(:personal_access_tokens, INDEX_NAME)
+  end
+
+  def down
+    add_concurrent_index(
+      :personal_access_tokens,
+      [:user_id, :last_used_at, :id],
+      where: 'impersonation = false',
+      name: INDEX_NAME
+    )
+  end
+end
diff --git a/db/schema_migrations/20251020130449 b/db/schema_migrations/20251020130449
new file mode 100644
index 00000000000000..a587898febcc4d
--- /dev/null
+++ b/db/schema_migrations/20251020130449
@@ -0,0 +1 @@
+6791eefb3a27f526252d47c7c97535df8d7e9c4b20a057d25b4526c1c661ae81
\ No newline at end of file
diff --git a/db/schema_migrations/20251020134120 b/db/schema_migrations/20251020134120
new file mode 100644
index 00000000000000..d3e42e41ddf3ef
--- /dev/null
+++ b/db/schema_migrations/20251020134120
@@ -0,0 +1 @@
+f80814bf21a57fc515458fce941e7b23772a625f7cb72a386bab62ca8ea32412
\ No newline at end of file
diff --git a/db/schema_migrations/20251020134409 b/db/schema_migrations/20251020134409
new file mode 100644
index 00000000000000..31e80f03c9186e
--- /dev/null
+++ b/db/schema_migrations/20251020134409
@@ -0,0 +1 @@
+005a583f8d70a025a3624e07e4876b69ae1fea057840de5d8806fbaf4865eea2
\ No newline at end of file
diff --git a/db/structure.sql b/db/structure.sql
index 55ee5d864ce91a..46b3c8aac0fec3 100644
--- a/db/structure.sql
+++ b/db/structure.sql
@@ -41580,12 +41580,6 @@ CREATE INDEX index_pats_on_group_id_and_user_type_and_expires_at_and_id ON perso
 
 CREATE INDEX index_pats_on_group_id_and_user_type_and_last_used_at_and_id ON personal_access_tokens USING btree (group_id, user_type, last_used_at, id) WHERE (impersonation = false);
 
-CREATE INDEX index_pats_on_user_id_and_created_at_and_pat_id ON personal_access_tokens USING btree (user_id, created_at, id) WHERE (impersonation = false);
-
-CREATE INDEX index_pats_on_user_id_and_expires_at_and_pat_id ON personal_access_tokens USING btree (user_id, expires_at, id DESC) WHERE (impersonation = false);
-
-CREATE INDEX index_pats_on_user_id_and_last_used_at_and_pat_id ON personal_access_tokens USING btree (user_id, last_used_at, id) WHERE (impersonation = false);
-
 CREATE INDEX index_pe_approval_rules_on_required_approvals_and_created_at ON protected_environment_approval_rules USING btree (required_approvals, created_at);
 
 CREATE INDEX index_pep_policy_config_links_security_policy_id ON security_pipeline_execution_policy_config_links USING btree (security_policy_id);
diff --git a/spec/db/schema_spec.rb b/spec/db/schema_spec.rb
index 37f22536ad4cf3..6fa6bc2a8c37e7 100644
--- a/spec/db/schema_spec.rb
+++ b/spec/db/schema_spec.rb
@@ -330,7 +330,6 @@
       packages_package_files: 16,
       packages_packages: 27,
       project_type_ci_runners: 16,
-      personal_access_tokens: 17, # We will be able to remove 3 indexes added in https://gitlab.com/gitlab-org/gitlab/-/merge_requests/193375 after https://gitlab.com/gitlab-org/gitlab/-/issues/561260 is complete.
       projects: 55,
       sbom_occurrences: 25,
       users: 34, # Decrement by 1 after the removal of a temporary index https://gitlab.com/gitlab-org/gitlab/-/merge_requests/184848
-- 
GitLab