Add latest changes from gitlab-org/gitlab@master

3 files changed, 54 insertions, 4 deletions

diff --git a/doc/.vale/gitlab/spelling-exceptions.txt b/doc/.vale/gitlab/spelling-exceptions.txt
index 5403b80141e..2ffd42c9390 100644
--- a/doc/.vale/gitlab/spelling-exceptions.txt
+++ b/doc/.vale/gitlab/spelling-exceptions.txt
@@ -65,6 +65,7 @@ autoscaler
 autoscalers
 autoscales
 autoscaling
+autovacuum
 awardable
 awardables
 Axios
diff --git a/doc/development/database/adding_database_indexes.md b/doc/development/database/adding_database_indexes.md
index e1d5a7af6d9..d909f66d6c8 100644
--- a/doc/development/database/adding_database_indexes.md
+++ b/doc/development/database/adding_database_indexes.md
@@ -214,6 +214,45 @@ def down
 end
 ```
 
+## Analyzing a new index before a batched background migration
+
+Sometimes it is necessary to add an index to support a [batched background migration](batched_background_migrations.md).
+It is commonly done by creating two [post deployment migrations](post_deployment_migrations.md):
+
+1. Add the new index, often a [temporary index](#temporary-indexes).
+1. [Queue the batched background migration](batched_background_migrations.md#queueing).
+
+In most cases, no additional work is needed. The new index is created and is used
+as expected when queuing and executing the batched background migration.
+
+[Expression indexes](https://www.postgresql.org/docs/current/indexes-expressional.html),
+however, do not generate statistics for the new index on creation. Autovacuum
+eventually runs `ANALYZE`, and updates the statistics so the new index is used.
+Run `ANALYZE` explicitly only if it is needed right after the index
+is created, such as in the background migration scenario described above.
+
+To trigger `ANALYZE` after the index is created, update the index creation migration
+to analyze the table:
+
+```ruby
+# in db/post_migrate/
+
+INDEX_NAME = 'tmp_index_projects_on_owner_and_lower_name_where_emails_disabled'
+TABLE = :projects
+
+disable_ddl_transaction!
+
+def up
+  add_concurrent_index TABLE, '(creator_id, lower(name))', where: 'emails_disabled = false', name: INDEX_NAME
+
+  connection.execute("ANALYZE #{TABLE}")
+end
+```
+
+`ANALYZE` should only be run in post deployment migrations and should not target
+[large tables](https://gitlab.com/gitlab-org/gitlab/-/blob/master/rubocop/rubocop-migrations.yml#L3).
+If this behavior is needed on a larger table, ask for assistance in the `#database` Slack channel.
+
 ## Indexes for partitioned tables
 
 Indexes [cannot be created](https://www.postgresql.org/docs/15/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE-MAINTENANCE)
@@ -254,7 +293,7 @@ end
 
 For very large tables, index creation can be a challenge to manage.
 While `add_concurrent_index` creates indexes in a way that does not block
-normal traffic, it can still be problematic when index creation runs for
+ordinary traffic, it can still be problematic when index creation runs for
 many hours. Necessary database operations like `autovacuum` cannot run, and
 on GitLab.com, the deployment process is blocked waiting for index
 creation to finish.
@@ -351,7 +390,7 @@ Use the asynchronous index helpers on your local environment to test changes for
 
 For very large tables, index destruction can be a challenge to manage.
 While `remove_concurrent_index` removes indexes in a way that does not block
-normal traffic, it can still be problematic if index destruction runs for
+ordinary traffic, it can still be problematic if index destruction runs for
 during `autovacuum`. Necessary database operations like `autovacuum` cannot run, and
 the deployment process on GitLab.com is blocked while waiting for index
 destruction to finish.
diff --git a/doc/development/database/batched_background_migrations.md b/doc/development/database/batched_background_migrations.md
index 88fdfab9828..ea6bdbdab8b 100644
--- a/doc/development/database/batched_background_migrations.md
+++ b/doc/development/database/batched_background_migrations.md
@@ -309,7 +309,7 @@ In the second (filtered) example, we know exactly 100 will be updated with each
 
 NOTE:
 When applying additional filters, it is important to ensure they are properly covered by an index to optimize `EachBatch` performance.
-In the example above we need an index on `(type, id)` to support the filters. See [the `EachBatch` docs for more information](iterating_tables_in_batches.md).
+In the example above we need an index on `(type, id)` to support the filters. See [the `EachBatch` documentation for more information](iterating_tables_in_batches.md).
 
 ## Example
 
@@ -502,7 +502,7 @@ end
 ```
 
 NOTE:
-[Additional filters](#additional-filters) defined with `scope_to` will be ignored by `LooseIndexScanBatchingStrategy` and `distinct_each_batch`.
+[Additional filters](#additional-filters) defined with `scope_to` are ignored by `LooseIndexScanBatchingStrategy` and `distinct_each_batch`.
 
 ## Testing
 
@@ -686,6 +686,16 @@ You can view failures in two ways:
       WHERE transition_logs.next_status = '2' AND migration.job_class_name = "CLASS_NAME";
      ```
 
+### Adding indexes to support batched background migrations
+
+Sometimes it is necessary to add a new or temporary index to support a batched background migration.
+To do this, create the index in a post-deployment migration that precedes the post-deployment
+migration that queues the background migration.
+
+See the documentation for [adding database indexes](adding_database_indexes.md#analyzing-a-new-index-before-a-batched-background-migration)
+for additional information about some cases that require special attention to allow the index to be used directly after
+creation.
+
 ## Legacy background migrations
 
 Batched background migrations replaced the [legacy background migrations framework](background_migrations.md).