Add async index destruction

Ref: #367506 (closed)

What does this MR do and why?

This MR allows to drop index asynchronously to prevent further incident as experienced in gitlab-com/gl-infra/production#6821 (closed).

This is useful when dropping an index an a huge table with a lot of data over the weekend when we have less traffic.

This new feature is behind a feature flag called database_async_index_destruction.

How does it work?

The implementation is heavily inspired from our prepare_async_index helper method introduced in !66478 (merged).

1. Creates a migration to schedule index destruction
2. Index is added to postgres_async_indexes table
3. Over the weekend a job is scheduled to drop this index concurrently as we do for creating index

# frozen_string_literal: true
class PrepareIndexRemovalCiBuilds < Gitlab::Database::Migration[2.0]
  def up
    prepare_async_index_removal :ci_builds, :queued_at, name: :index_ci_builds_on_queued_at
  end

  def down
    unprepare_async_index_by_name :ci_builds, :index_ci_builds_on_queued_at
  end
end

It will creates a new record in Gitlab::Database::AsyncIndexes::PostgresAsyncIndex 👇

pry(main)> Gitlab::Database::AsyncIndexes::PostgresAsyncIndex.to_drop
  Gitlab::Database::AsyncIndexes::PostgresAsyncIndex Load (1.1ms)  SELECT "postgres_async_indexes".* FROM "postgres_async_indexes" WHERE (definition ILIKE 'DROP%')
=> [#<Gitlab::Database::AsyncIndexes::PostgresAsyncIndex:0x000000011508d570
  id: 17,
  created_at: Thu, 14 Jul 2022 13:34:39.304745000 UTC +00:00,
  updated_at: Thu, 14 Jul 2022 13:34:39.304745000 UTC +00:00,
  name: "index_ci_builds_on_queued_at",
  definition: "DROP INDEX CONCURRENTLY \"index_ci_builds_on_queued_at\"",
  table_name: "ci_builds">]

How to set up and validate locally

1. Verify the index does exist

gitlabhq_development=# \di+ index_ci_builds_on_queued_at;
                                   List of relations
Schema |             Name             | Type  |  Owner   |   Table   | Size  | Description
--------+------------------------------+-------+----------+-----------+-------+-------------
public | index_ci_builds_on_queued_at | index | morefice | ci_builds | 40 kB |
(1 row)

2. Enable feature flag

Feature.enable(:database_reindexing)
Feature.enable(:database_async_index_destruction)

3. Creates new migration using prepare_async_index_removal
4. Run migration with bundle exec rails db:migrate
5. Run bundle exec rails gitlab:db:reindex so the index is destroyed asynchronously

[5] pry(main)> Gitlab::Database::AsyncIndexes::PostgresAsyncIndex.to_drop
Gitlab::Database::AsyncIndexes::PostgresAsyncIndex Load (0.6ms)  SELECT "postgres_async_indexes".* FROM "postgres_async_indexes" WHERE (definition ILIKE 'DROP%') /*application:console,db_config_name:main,line:bin/rails:4:in `<main>'*/
=> []

1. Verify the index no longer exist

gitlabhq_development=# \di+ index_ci_builds_on_queued_at;
Did not find any relation named "index_ci_builds_on_queued_at".

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

• I have evaluated the MR acceptance checklist for this MR.