Add latest changes from gitlab-org/gitlab@13-5-stable-eev13.5.0-rc42

8 files changed, 460 insertions, 2 deletions

diff --git a/doc/development/database/add_foreign_key_to_existing_column.md b/doc/development/database/add_foreign_key_to_existing_column.md
index 1b41a52c95e..85411ff9aa7 100644
--- a/doc/development/database/add_foreign_key_to_existing_column.md
+++ b/doc/development/database/add_foreign_key_to_existing_column.md
@@ -1,3 +1,9 @@
+---
+stage: Enablement
+group: Database
+info: To determine the technical writer assigned to the Stage/Group associated with this page, see https://about.gitlab.com/handbook/engineering/ux/technical-writing/#designated-technical-writers
+---
+
 # Adding foreign key constraint to an existing column
 
 Foreign keys help ensure consistency between related database tables. The current database review process **always** encourages you to add [foreign keys](../foreign_keys.md) when creating tables that reference records from other tables.
@@ -103,7 +109,7 @@ class RemoveRecordsWithoutUserFromEmailsTable < ActiveRecord::Migration[5.2]
   end
 
   def down
-    # Can be a no-op when data inconsistency is not affecting the pre and post deploymnet version of the application.
+    # Can be a no-op when data inconsistency is not affecting the pre and post deployment version of the application.
     # In this case we might have records in the `emails` table where the associated record in the `users` table is not there anymore.
   end
 end
diff --git a/doc/development/database/client_side_connection_pool.md b/doc/development/database/client_side_connection_pool.md
new file mode 100644
index 00000000000..1a30d2d73a3
--- /dev/null
+++ b/doc/development/database/client_side_connection_pool.md
@@ -0,0 +1,63 @@
+---
+type: dev, reference
+stage: none
+group: Development
+info: "See the Technical Writers assigned to Development Guidelines: https://about.gitlab.com/handbook/engineering/ux/technical-writing/#assignments-to-development-guidelines"
+---
+
+# Client-side connection-pool
+
+Ruby processes accessing the database through
+ActiveRecord, automatically calculate the connection-pool size for the
+process based on the concurrency.
+
+Because of the way [Ruby on Rails manages database
+connections](#connection-lifecycle), it is important that we have at
+least as many connections as we have threads. While there is a 'pool'
+setting in [`database.yml`](https://gitlab.com/gitlab-org/gitlab/-/blob/master/config/database.yml.postgresql), it is not very practical because you need to
+maintain it in tandem with the number of application threads. For this
+reason, we override the number of allowed connections in the database
+connection-pool based on the configured number of application threads.
+
+`Gitlab::Runtime.max_threads` is the number of user-facing
+application threads the process has been configured with. We also have
+auxiliary threads that use database connections. As it isn't
+straightforward to keep an accurate count of the number of auxiliary threads as
+the application evolves over time, we just add a fixed headroom to the
+number of user-facing threads. It is OK if this number is too large
+because connections are instantiated lazily.
+
+## Troubleshooting connection-pool issues
+
+The connection-pool usage can be seen per environment in the [connection-pool
+saturation
+dashboard](https://dashboards.gitlab.net/d/alerts-sat_rails_db_connection_pool/alerts-rails_db_connection_pool-saturation-detail?orgId=1).
+
+If the connection-pool is too small, this would manifest in
+`ActiveRecord::ConnectionTimeoutError`s from the application. Because we alert
+when almost all connections are used, we should know this before
+timeouts occur. If this happens we can remediate by setting the
+`DB_POOL_HEADROOM` environment variable to something bigger than the
+hardcoded value (10).
+
+At this point, we need to investigate what is using more connections
+than we anticipated. To do that, we can use the
+`gitlab_ruby_threads_running_threads` metric. For example, [this
+graph](https://thanos-query.ops.gitlab.net/graph?g0.range_input=1h&g0.max_source_resolution=0s&g0.expr=sum%20by%20(thread_name)%20(%20gitlab_ruby_threads_running_threads%7Buses_db_connection%3D%22yes%22%7D%20)&g0.tab=0)
+shows all running threads that connect to the database by their
+name. Threads labeled `puma worker` or `sidekiq_worker_thread` are
+the threads that define `Gitlab::Runtime.max_threads` so those are
+accounted for. If there's more than 10 other threads running, we could
+consider raising the default headroom.
+
+## Connection lifecycle
+
+For web requests, a connection is obtained from the pool at the first
+time a database query is made. The connection is returned to the pool
+after the request completes.
+
+For background jobs, the behavior is very similar. The thread obtains
+a connection for the first query, and returns it after the job is
+finished.
+
+This is managed by Rails internally.
diff --git a/doc/development/database/database_reviewer_guidelines.md b/doc/development/database/database_reviewer_guidelines.md
index 6cb061f9959..3345df8b46b 100644
--- a/doc/development/database/database_reviewer_guidelines.md
+++ b/doc/development/database/database_reviewer_guidelines.md
@@ -1,3 +1,9 @@
+---
+stage: Enablement
+group: Database
+info: To determine the technical writer assigned to the Stage/Group associated with this page, see https://about.gitlab.com/handbook/engineering/ux/technical-writing/#designated-technical-writers
+---
+
 # Database Reviewer Guidelines
 
 This page includes introductory material for new database reviewers.
diff --git a/doc/development/database/index.md b/doc/development/database/index.md
index 9ea5b6fcaac..4bcefefe7a7 100644
--- a/doc/development/database/index.md
+++ b/doc/development/database/index.md
@@ -1,3 +1,9 @@
+---
+stage: Enablement
+group: Database
+info: To determine the technical writer assigned to the Stage/Group associated with this page, see https://about.gitlab.com/handbook/engineering/ux/technical-writing/#designated-technical-writers
+---
+
 # Database guides
 
 ## Database Reviews
@@ -24,6 +30,7 @@
 - [Background migrations](../background_migrations.md)
 - [Swapping tables](../swapping_tables.md)
 - [Deleting migrations](../deleting_migrations.md)
+- [Partitioning tables](table_partitioning.md)
 
 ## Debugging
 
@@ -49,6 +56,8 @@
 - [Database Debugging and Troubleshooting](../database_debugging.md)
 - [Query Count Limits](../query_count_limits.md)
 - [Creating enums](../creating_enums.md)
+- [Client-side connection-pool](client_side_connection_pool.md)
+- [Updating multiple values](./setting_multiple_values.md)
 
 ## Case studies
 
diff --git a/doc/development/database/not_null_constraints.md b/doc/development/database/not_null_constraints.md
index e4dec2afa10..96271863d94 100644
--- a/doc/development/database/not_null_constraints.md
+++ b/doc/development/database/not_null_constraints.md
@@ -1,3 +1,9 @@
+---
+stage: Enablement
+group: Database
+info: To determine the technical writer assigned to the Stage/Group associated with this page, see https://about.gitlab.com/handbook/engineering/ux/technical-writing/#designated-technical-writers
+---
+
 # `NOT NULL` constraints
 
 > [Introduced](https://gitlab.com/gitlab-org/gitlab/-/issues/38358) in GitLab 13.0.
@@ -33,7 +39,7 @@ end
 
 ## Add a `NOT NULL` column to an existing table
 
-With PostgreSQL 11 being the minimum version since GitLab 13.0, adding columns with `NULL` and/or
+With PostgreSQL 11 being the minimum version in GitLab 13.0 and later, adding columns with `NULL` and/or
 default values has become much easier and the standard `add_column` helper should be used in all cases.
 
 For example, consider a migration that adds a new `NOT NULL` column `active` to table `db_guides`,
diff --git a/doc/development/database/setting_multiple_values.md b/doc/development/database/setting_multiple_values.md
new file mode 100644
index 00000000000..5569a0e10b7
--- /dev/null
+++ b/doc/development/database/setting_multiple_values.md
@@ -0,0 +1,103 @@
+# Setting Multiple Values
+
+> [Introduced](https://gitlab.com/gitlab-org/gitlab/-/issues/32921) in GitLab 13.5.
+
+Frequently, we will want to update multiple objects with new values for one
+or more columns. The obvious way to do this is using `Relation#update_all`:
+
+```ruby
+user.issues.open.update_all(due_date: 7.days.from_now) # (1)
+user.issues.update_all('relative_position = relative_position + 1') # (2)
+```
+
+But what do you do if you cannot express the update as either a static value (1)
+or as a calculation (2)?
+
+Thankfully we can use `UPDATE FROM` to express the need to update multiple rows
+with distinct values in a single query. One can either use a temporary table, or
+a Common Table Expression (CTE), and then use that as the source of the updates:
+
+```sql
+with updates(obj_id, new_title, new_weight) as (
+  values (1 :: integer, 'Very difficult issue' :: text, 8 :: integer),
+         (2, 'Very easy issue', 1)
+)
+update issues
+  set title = new_title, weight = new_weight
+  from updates
+  where id = obj_id
+```
+
+The bad news: There is no way to express this in ActiveRecord or even dropping
+down to ARel - the `UpdateManager` just does not support `update from`, so this
+is not expressible.
+
+The good news: We supply an abstraction to help you generate these kinds of
+updates, called `Gitlab::Database::BulkUpdate`. This constructs queries such as the
+above, and uses binding parameters to avoid SQL injection.
+
+## Usage
+
+To use this, we need:
+
+- the list of columns to update
+- a mapping from object/ID to the new values to set for that object
+- a way to determine the table for each object
+
+So for example, we can express the query above as:
+
+```ruby
+issue_a = Issue.find(..)
+issue_b = Issue.find(..)
+
+# Issues a single query:
+::Gitlab::Database::BulkUpdate.execute(%i[title weight], {
+  issue_a => { title: 'Very difficult issue', weight: 8 },
+  issue_b => { title: 'Very easy issue', weight: 1 }
+})
+```
+
+Here the table can be determined automatically, from calling
+`object.class.table_name`, so we don't need to provide anything.
+
+We can even pass heterogeneous sets of objects, if the updates all make sense
+for them:
+
+```ruby
+issue_a = Issue.find(..)
+issue_b = Issue.find(..)
+merge_request = MergeRequest.find(..)
+
+# Issues two queries
+::Gitlab::Database::BulkUpdate.execute(%i[title], {
+  issue_a => { title: 'A' },
+  issue_b => { title: 'B' },
+  merge_request => { title: 'B' }
+})
+```
+
+If your objects do not return the correct model class (perhaps because they are
+part of a union), then we need to specify this explicitly in a block:
+
+```ruby
+bazzes = params
+objects = Foo.from_union([
+    Foo.select("id, 'foo' as object_type").where(quux: true),
+    Bar.select("id, 'bar' as object_type").where(wibble: true)
+    ])
+# At this point, all the objects are instances of Foo, even the ones from the
+# Bar table
+mapping = objects.to_h { |obj| [obj, bazzes[obj.id] }
+
+# Issues at most 2 queries
+::Gitlab::Database::BulkUpdate.execute(%i[baz], mapping) do |obj|
+  obj.object_type.constantize
+end
+```
+
+## Caveats
+
+Note that this is a **very low level** tool, and operates on the raw column
+values. Enumerations and state fields must be translated into their underlying
+representations, for example, and nested associations are not supported. No
+validations or hooks will be called.
diff --git a/doc/development/database/strings_and_the_text_data_type.md b/doc/development/database/strings_and_the_text_data_type.md
index b73dfa859fb..fe8cfa5cd22 100644
--- a/doc/development/database/strings_and_the_text_data_type.md
+++ b/doc/development/database/strings_and_the_text_data_type.md
@@ -1,3 +1,9 @@
+---
+stage: Enablement
+group: Database
+info: To determine the technical writer assigned to the Stage/Group associated with this page, see https://about.gitlab.com/handbook/engineering/ux/technical-writing/#designated-technical-writers
+---
+
 # Strings and the Text data type
 
 > [Introduced](https://gitlab.com/gitlab-org/gitlab/-/issues/30453) in GitLab 13.0.
diff --git a/doc/development/database/table_partitioning.md b/doc/development/database/table_partitioning.md
new file mode 100644
index 00000000000..30d0b0a2f5b
--- /dev/null
+++ b/doc/development/database/table_partitioning.md
@@ -0,0 +1,259 @@
+---
+stage: Enablement
+group: Database
+info: To determine the technical writer assigned to the Stage/Group associated with this page, see https://about.gitlab.com/handbook/engineering/ux/technical-writing/#designated-technical-writers
+---
+
+# Database table partitioning
+
+Table partitioning is a powerful database feature that allows a table's
+data to be split into smaller physical tables that act as a single large
+table. If the application is designed to work with partitioning in mind,
+there can be multiple benefits, such as:
+
+- Query performance can be improved greatly, because the database can
+cheaply eliminate much of the data from the search space, while still
+providing full SQL capabilities.
+
+- Bulk deletes can be achieved with minimal impact on the database by
+dropping entire partitions. This is a natural fit for features that need
+to periodically delete data that falls outside the retention window.
+
+- Administrative tasks like `VACUUM` and index rebuilds can operate on
+individual partitions, rather than across a single massive table.
+
+Unfortunately, not all models fit a partitioning scheme, and there are
+significant drawbacks if implemented incorrectly. Additionally, tables
+can only be partitioned at their creation, making it nontrivial to apply
+partitioning to a busy database. A suite of migration tools are available
+to enable backend developers to partition existing tables, but the
+migration process is rather heavy, taking multiple steps split across
+several releases. Due to the limitations of partitioning and the related
+migrations, you should understand how partitioning fits your use case
+before attempting to leverage this feature.
+
+## Determining when to use partitioning
+
+While partitioning can be very useful when properly applied, it's
+imperative to identify if the data and workload of a table naturally fit a
+partitioning scheme. There are a few details you'll have to understand
+in order to decide if partitioning is a good fit for your particular
+problem.
+
+First, a table is partitioned on a partition key, which is a column or
+set of columns which determine how the data will be split across the
+partitions. The partition key is used by the database when reading or
+writing data, to decide which partition(s) need to be accessed. The
+partition key should be a column that would be included in a `WHERE`
+clause on almost all queries accessing that table.
+
+Second, it's necessary to understand the strategy the database will
+use to split the data across the partitions. The scheme supported by the
+GitLab migration helpers is date-range partitioning, where each partition
+in the table contains data for a single month. In this case, the partitioning
+key would need to be a timestamp or date column. In order for this type of
+partitioning to work well, most queries would need to access data within a
+certain date range.
+
+For a more concrete example, the `audit_events` table can be used, which
+was the first table to be partitioned in the application database
+(scheduled for deployment with the GitLab 13.5 release). This
+table tracks audit entries of security events that happen in the
+application. In almost all cases, users want to see audit activity that
+occurs in a certain timeframe. As a result, date-range partitioning
+was a natural fit for how the data would be accessed.
+
+To look at this in more detail, imagine a simplified `audit_events` schema:
+
+```sql
+CREATE TABLE audit_events (
+  id SERIAL NOT NULL PRIMARY KEY,
+  author_id INT NOT NULL,
+  details jsonb NOT NULL,
+  created_at timestamptz NOT NULL);
+```
+
+Now imagine typical queries in the UI would display the data within a
+certain date range, like a single week:
+
+```sql
+SELECT *
+FROM audit_events
+WHERE created_at >= '2020-01-01 00:00:00'
+  AND created_at < '2020-01-08 00:00:00'
+ORDER BY created_at DESC
+LIMIT 100
+```
+
+If the table is partitioned on the `created_at` column the base table would
+look like:
+
+```sql
+CREATE TABLE audit_events (
+  id SERIAL NOT NULL,
+  author_id INT NOT NULL,
+  details jsonb NOT NULL,
+  created_at timestamptz NOT NULL,
+  PRIMARY KEY (id, created_at))
+PARTITION BY RANGE(created_at);
+```
+
+NOTE: **Note:**
+The primary key of a partitioned table must include the partition key as
+part of the primary key definition.
+
+And we might have a list of partitions for the table, such as:
+
+```sql
+audit_events_202001 FOR VALUES FROM ('2020-01-01') TO ('2020-02-01')
+audit_events_202002 FOR VALUES FROM ('2020-02-01') TO ('2020-03-01')
+audit_events_202003 FOR VALUES FROM ('2020-03-01') TO ('2020-04-01')
+```
+
+Each partition is a separate physical table, with the same structure as
+the base `audit_events` table, but contains only data for rows where the
+partition key falls in the specified range. For example, the partition
+`audit_events_202001` contains rows where the `created_at` column is
+greater than or equal to `2020-01-01` and less than `2020-02-01`.
+
+Now, if we look at the previous example query again, the database can
+use the `WHERE` to recognize that all matching rows will be in the
+`audit_events_202001` partition. Rather than searching all of the data
+in all of the partitions, it can search only the single month's worth
+of data in the appropriate partition. In a large table, this can
+dramatically reduce the amount of data the database needs to access.
+However, imagine a query that does not filter based on the partitioning
+key, such as:
+
+```sql
+SELECT *
+FROM audit_events
+WHERE author_id = 123
+ORDER BY created_at DESC
+LIMIT 100
+```
+
+In this example, the database can't prune any partitions from the search,
+because matching data could exist in any of them. As a result, it has to
+query each partition individually, and aggregate the rows into a single result
+set. Since `author_id` would be indexed, the performance impact could
+likely be acceptable, but on more complex queries the overhead can be
+substantial. Partitioning should only be leveraged if the access patterns
+of the data support the partitioning strategy, otherwise performance will
+suffer.
+
+## Partitioning a table
+
+Unfortunately, tables can only be partitioned at their creation, making
+it nontrivial to apply to a busy database. A suite of migration
+tools have been developed to enable backend developers to partition
+existing tables. This migration process takes multiple steps which must
+be split across several releases.
+
+### Caveats
+
+The partitioning migration helpers work by creating a partitioned duplicate
+of the original table and using a combination of a trigger and a background
+migration to copy data into the new table. Changes to the original table
+schema can be made in parallel with the partitioning migration, but they
+must take care to not break the underlying mechanism that makes the migration
+work. For example, if a column is added to the table that is being
+partitioned, both the partitioned table and the trigger definition need to
+be updated to match.
+
+### Step 1: Creating the partitioned copy (Release N)
+
+The first step is to add a migration to create the partitioned copy of
+the original table. This migration will also create the appropriate
+partitions based on the data in the original table, and install a
+trigger that will sync writes from the original table into the
+partitioned copy.
+
+An example migration of partitioning the `audit_events` table by its
+`created_at` column would look like:
+
+```ruby
+class PartitionAuditEvents < ActiveRecord::Migration[6.0]
+  include Gitlab::Database::PartitioningMigrationHelpers
+
+  def up
+    partition_table_by_date :audit_events, :created_at
+  end
+
+  def down
+    drop_partitioned_table_for :audit_events
+  end
+end
+```
+
+Once this has executed, any inserts, updates or deletes in the
+original table will also be duplicated in the new table. For updates and
+deletes, the operation will only have an effect if the corresponding row
+exists in the partitioned table.
+
+### Step 2: Backfill the partitioned copy (Release N)
+
+The second step is to add a post-deployment migration that will schedule
+the background jobs that will backfill existing data from the original table
+into the partitioned copy.
+
+Continuing the above example, the migration would look like:
+
+```ruby
+class BackfillPartitionAuditEvents < ActiveRecord::Migration[6.0]
+  include Gitlab::Database::PartitioningMigrationHelpers
+
+  def up
+    enqueue_partitioning_data_migration :audit_events
+  end
+
+  def down
+    cleanup_partitioning_data_migration :audit_events
+  end
+end
+```
+
+This step uses the same mechanism as any background migration, so you
+may want to read the [Background Migration](../background_migrations.md)
+guide for details on that process. Background jobs are scheduled every
+2 minutes and copy `50_000` records at a time, which can be used to
+estimate the timing of the background migration portion of the
+partitioning migration.
+
+### Step 3: Post-backfill cleanup (Release N+1)
+
+The third step must occur at least one release after the release that
+includes the background migration. This gives time for the background
+migration to execute properly in self-managed installations. In this step,
+add another post-deployment migration that will cleanup after the
+background migration. This includes forcing any remaining jobs to
+execute, and copying data that may have been missed, due to dropped or
+failed jobs.
+
+Once again, continuing the example, this migration would look like:
+
+```ruby
+class CleanupPartitionedAuditEventsBackfill < ActiveRecord::Migration[6.0]
+  include Gitlab::Database::PartitioningMigrationHelpers
+
+  def up
+    finalize_backfilling_partitioned_table :audit_events
+  end
+
+  def down
+    # no op
+  end
+end
+```
+
+After this migration has completed, the original table and partitioned
+table should contain identical data. The trigger installed on the
+original table guarantees that the data will remain in sync going
+forward.
+
+### Step 4: Swap the partitioned and non-partitioned tables (Release N+1)
+
+The final step of the migration will make the partitioned table ready
+for use by the application. This section will be updated when the
+migration helper is ready, for now development can be followed in the
+[Tracking Issue](https://gitlab.com/gitlab-org/gitlab/-/issues/241267).