Skip to content

Adds a new keep file to remove duplicated indexes

Leonardo da Rosa requested to merge 440213-use-a-keep-to-clean-up-duplicate-indexes-automatically into master

What does this MR do and why?

Adds a new keep file to drop duplicated indexes

This keep uses the test database to look for duplicated indexes and creates each respective MRs.

Here are some examples:

MR acceptance checklist

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

How to set up and validate locally

  1. Checkout this branch

  2. Run bundle exec gitlab-housekeeper -k Keeps::RemoveDuplicatedIndexes -d -m 3

    • The -d just prints the contents of the merge request. Removing this will actually create merge requests and requires setting a few environment variables.
    • The -m option is the limit of MRs to create. Defaults to 1 if not set.
  1. Check the output
Output 
=> RemoveDuplicatedIndexes: board_group_recent_visits
=> Title:
Remove duplicated index from board_group_recent_visits

=> Description:
## What does this MR do and why?

Remove duplicated index from `board_group_recent_visits` table.

### It removes:

Drop `index_board_group_recent_visits_on_user_id` as it's already covered by:
| Index | Columns |
| ----- | ------ |
| `index_board_group_recent_visits_on_user_group_and_board` | `user_id asc`, `group_id asc`, `board_id asc` |

It is possible that this MR will still need some changes to drop the index from the database.
Currently, the `gitlab-housekeeper` is not always capable of removing all references, so you must check the diff and pipeline failures to confirm if there are any issues.
Ensure that the index exists in the production database by checking Joe Bot trough https://console.postgres.ai/gitlab.
If the index was already removed or if the index it's being removed in another merge request, consider closing this merge request.

=> Attributes:
Labels: group::database, maintenance::scalability, type::maintenance, Category:Database, automation:gitlab-housekeeper-authored
Reviewers: engwan

=> Diff:
diff --git a/db/migrate/20240404154505_drop_index_board_group_recent_visits_on_user_id.rb b/db/migrate/20240404154505_drop_index_board_group_recent_visits_on_user_id.rb
new file mode 100644
index 000000000000..501f0c663f64
--- /dev/null
+++ b/db/migrate/20240404154505_drop_index_board_group_recent_visits_on_user_id.rb
@@ -0,0 +1,19 @@
+# frozen_string_literal: true
+
+class DropIndexBoardGroupRecentVisitsOnUserId < Gitlab::Database::Migration[2.2]
+  milestone '16.11'
+
+  disable_ddl_transaction!
+
+  TABLE_NAME = :board_group_recent_visits
+  INDEX_NAME = :index_board_group_recent_visits_on_user_id
+  COLUMN_NAMES = [:user_id]
+
+  def up
+    remove_concurrent_index_by_name(TABLE_NAME, INDEX_NAME)
+  end
+
+  def down
+    add_concurrent_index(TABLE_NAME, COLUMN_NAMES, name: INDEX_NAME)
+  end
+end
diff --git a/db/schema_migrations/20240404154505 b/db/schema_migrations/20240404154505
new file mode 100644
index 000000000000..c2a5baf8c7af
--- /dev/null
+++ b/db/schema_migrations/20240404154505
@@ -0,0 +1 @@
+efeeaa89317df99a89efa48328de90a746ffe01de4ac782c52e185d7d05c5211
\ No newline at end of file
diff --git a/db/structure.sql b/db/structure.sql
index 756634a95736..8e2bb641e82b 100644
--- a/db/structure.sql
+++ b/db/structure.sql
@@ -24317,8 +24317,6 @@ CREATE INDEX index_board_group_recent_visits_on_group_id ON board_group_recent_v

 CREATE UNIQUE INDEX index_board_group_recent_visits_on_user_group_and_board ON board_group_recent_visits USING btree (user_id, group_id, board_id);

-CREATE INDEX index_board_group_recent_visits_on_user_id ON board_group_recent_visits USING btree (user_id);
-
 CREATE UNIQUE INDEX index_board_labels_on_board_id_and_label_id ON board_labels USING btree (board_id, label_id);

 CREATE INDEX index_board_labels_on_label_id ON board_labels USING btree (label_id);

If you want to create the actual MRs

  1. Create a fork of https://gitlab.com/gitlab-org/gitlab
  2. Checkout this branch
  3. Run git remote add housekeeper git@gitlab.com:<fork-path>.git
  4. Run HOUSEKEEPER_TARGET_PROJECT_ID=<FORK_ID> HOUSEKEEPER_GITLAB_API_TOKEN=<PAT_TOKEN> bundle exec gitlab-housekeeper -k Keeps::RemoveDuplicatedIndexes -m 1

NOTE Please keep in mind that this will trigger the person responsible for reviewing the MR.

Related to #440213 (closed)

Edited by Leonardo da Rosa

Merge request reports