diff options
author | GitLab Bot <gitlab-bot@gitlab.com> | 2022-06-07 03:09:08 +0300 |
---|---|---|
committer | GitLab Bot <gitlab-bot@gitlab.com> | 2022-06-07 03:09:08 +0300 |
commit | 83cd5db43517cfd977b0d288b179f794677ba1a7 (patch) | |
tree | 102dc755ceddb8aebd4cbc81cd4720ef6da90314 /doc | |
parent | 1f563de51470eaf2aa44e71046398127c00bcfe3 (diff) |
Add latest changes from gitlab-org/gitlab@master
Diffstat (limited to 'doc')
-rw-r--r-- | doc/.vale/gitlab/Uppercase.yml | 3 | ||||
-rw-r--r-- | doc/administration/postgresql/index.md | 2 | ||||
-rw-r--r-- | doc/development/database/avoiding_downtime_in_migrations.md | 11 | ||||
-rw-r--r-- | doc/development/database/database_reviewer_guidelines.md | 2 | ||||
-rw-r--r-- | doc/development/database/efficient_in_operator_queries.md | 38 | ||||
-rw-r--r-- | doc/development/database/not_null_constraints.md | 16 | ||||
-rw-r--r-- | doc/development/database/table_partitioning.md | 29 | ||||
-rw-r--r-- | doc/development/sql.md | 30 |
8 files changed, 67 insertions, 64 deletions
diff --git a/doc/.vale/gitlab/Uppercase.yml b/doc/.vale/gitlab/Uppercase.yml index c2c565bacac..c9053c20d0e 100644 --- a/doc/.vale/gitlab/Uppercase.yml +++ b/doc/.vale/gitlab/Uppercase.yml @@ -16,6 +16,7 @@ second: '(?:\b[A-Z][a-z]+ )+\(([A-Z]{3,5})\)' exceptions: - AJAX - ANSI + - APAC - API - APM - ARM @@ -40,6 +41,7 @@ exceptions: - CSRF - CSS - CSV + - CTE - CWE - CVE - CVS @@ -211,6 +213,7 @@ exceptions: - TODO - TOML - TOTP + - TPS - TTL - UBI - UDP diff --git a/doc/administration/postgresql/index.md b/doc/administration/postgresql/index.md index a8e69041f89..8b811deee16 100644 --- a/doc/administration/postgresql/index.md +++ b/doc/administration/postgresql/index.md @@ -6,7 +6,7 @@ info: To determine the technical writer assigned to the Stage/Group associated w # Configuring PostgreSQL for scaling **(FREE SELF)** -In this section, you'll be guided through configuring a PostgreSQL database to +In this section, you are guided through configuring a PostgreSQL database to be used with GitLab in one of our [reference architectures](../reference_architectures/index.md). There are essentially three setups to choose from. diff --git a/doc/development/database/avoiding_downtime_in_migrations.md b/doc/development/database/avoiding_downtime_in_migrations.md index fbf25e4fdb6..91254d5c88f 100644 --- a/doc/development/database/avoiding_downtime_in_migrations.md +++ b/doc/development/database/avoiding_downtime_in_migrations.md @@ -15,7 +15,7 @@ requiring downtime. ## Dropping Columns Removing columns is tricky because running GitLab processes may still be using -the columns. To work around this safely, you will need three steps in three releases: +the columns. To work around this safely, you need three steps in three releases: 1. Ignoring the column (release M) 1. Dropping the column (release M+1) @@ -174,7 +174,7 @@ This will take care of renaming the column, ensuring data stays in sync, and copying over indexes and foreign keys. If a column contains one or more indexes that don't contain the name of the -original column, the previously described procedure will fail. In that case, +original column, the previously described procedure fails. In that case, you need to rename these indexes. ### Step 2: Add A Post-Deployment Migration @@ -291,8 +291,9 @@ They can also produce a lot of pressure on the database due to it rapidly updating many rows in sequence. To reduce database pressure you should instead use a background migration -when migrating a column in a large table (for example, `issues`). This will -spread the work / load over a longer time period, without slowing down deployments. +when migrating a column in a large table (for example, `issues`). Background +migrations spread the work / load over a longer time period, without slowing +down deployments. For more information, see [the documentation on cleaning up background migrations](background_migrations.md#cleaning-up). @@ -533,7 +534,7 @@ step approach: Usually this works, but not always. For example, if a field's format is to be changed from JSON to something else we have a bit of a problem. If we were to -change existing data before deploying application code we'll most likely run +change existing data before deploying application code we would most likely run into errors. On the other hand, if we were to migrate after deploying the application code we could run into the same problems. diff --git a/doc/development/database/database_reviewer_guidelines.md b/doc/development/database/database_reviewer_guidelines.md index c8ab1e7353a..b6bbfe690c1 100644 --- a/doc/development/database/database_reviewer_guidelines.md +++ b/doc/development/database/database_reviewer_guidelines.md @@ -47,7 +47,7 @@ As a database reviewer, join the internal `#database` Slack channel and ask ques database related issues with other database reviewers and maintainers. There is also an optional database office hours call held bi-weekly, alternating between -European/US and APAC friendly hours. You can join the office hours call and bring topics +European/US and Asia-Pacific (APAC) friendly hours. You can join the office hours call and bring topics that require a more in-depth discussion between the database reviewers and maintainers: - [Database Office Hours Agenda](https://docs.google.com/document/d/1wgfmVL30F8SdMg-9yY6Y8djPSxWNvKmhR5XmsvYX1EI/edit). diff --git a/doc/development/database/efficient_in_operator_queries.md b/doc/development/database/efficient_in_operator_queries.md index a8104c3d7e9..4f18c937198 100644 --- a/doc/development/database/efficient_in_operator_queries.md +++ b/doc/development/database/efficient_in_operator_queries.md @@ -163,7 +163,7 @@ The technique can only optimize `IN` queries that satisfy the following requirem (the combination of the columns uniquely identifies one particular column in the table). WARNING: -This technique will not improve the performance of the `COUNT(*)` queries. +This technique does not improve the performance of the `COUNT(*)` queries. ## The `InOperatorOptimization` module @@ -183,7 +183,7 @@ in `Gitlab::Pagination::Keyset::InOperatorOptimization`. ### Basic usage of `QueryBuilder` -To illustrate a basic usage, we will build a query that +To illustrate a basic usage, we build a query that fetches 20 issues with the oldest `created_at` from the group `gitlab-org`. The following ActiveRecord query would produce a query similar to @@ -226,10 +226,10 @@ Gitlab::Pagination::Keyset::InOperatorOptimization::QueryBuilder.new( the order by column expressions is available for locating the record. In this example, the yielded values are `created_at` and `id` SQL expressions. Finding a record is very fast via the primary key, so we don't use the `created_at` value. Providing the `finder_query` lambda is optional. - If it's not given, the IN operator optimization will only make the ORDER BY columns available to + If it's not given, the `IN` operator optimization only makes the `ORDER BY` columns available to the end-user and not the full database row. - If it's not given, the IN operator optimization will only make the ORDER BY columns available to + If it's not given, the `IN` operator optimization only makes the `ORDER BY` columns available to the end-user and not the full database row. The following database index on the `issues` table must be present @@ -416,7 +416,7 @@ scope = Issue .limit(20) ``` -To construct the array scope, we'll need to take the Cartesian product of the `project_id IN` and +To construct the array scope, we need to take the Cartesian product of the `project_id IN` and the `issue_type IN` queries. `issue_type` is an ActiveRecord enum, so we need to construct the following table: @@ -589,7 +589,7 @@ LIMIT 20 NOTE: To make the query efficient, the following columns need to be covered with an index: `project_id`, `issue_type`, `created_at`, and `id`. -#### Using calculated ORDER BY expression +#### Using calculated `ORDER BY` expression The following example orders epic records by the duration between the creation time and closed time. It is calculated with the following formula: @@ -766,7 +766,7 @@ using the generalized `IN` optimization technique. ### Array CTE -As the first step, we use a common table expression (CTE) for collecting the `projects.id` values. +As the first step, we use a Common Table Expression (CTE) for collecting the `projects.id` values. This is done by wrapping the incoming `array_scope` ActiveRecord relation parameter with a CTE. ```sql @@ -792,7 +792,7 @@ This query produces the following result set with only one column (`projects.id` ### Array mapping For each project (that is, each record storing a project ID in `array_cte`), -we will fetch the cursor value identifying the first issue respecting the `ORDER BY` clause. +we fetch the cursor value identifying the first issue respecting the `ORDER BY` clause. As an example, let's pick the first record `ID=9` from `array_cte`. The following query should fetch the cursor value `(created_at, id)` identifying @@ -805,7 +805,7 @@ ORDER BY "issues"."created_at" ASC, "issues"."id" ASC LIMIT 1; ``` -We will use `LATERAL JOIN` to loop over the records in the `array_cte` and find the +We use `LATERAL JOIN` to loop over the records in the `array_cte` and find the cursor value for each project. The query would be built using the `array_mapping_scope` lambda function. @@ -854,11 +854,11 @@ The table shows the cursor values (`created_at, id`) of the first record for eac respecting the `ORDER BY` clause. At this point, we have the initial data. To start collecting the actual records from the database, -we'll use a recursive CTE query where each recursion locates one row until +we use a recursive CTE query where each recursion locates one row until the `LIMIT` is reached or no more data can be found. -Here's an outline of the steps we will take in the recursive CTE query -(expressing the steps in SQL is non-trivial but will be explained next): +Here's an outline of the steps we take in the recursive CTE query +(expressing the steps in SQL is non-trivial but is explained next): 1. Sort the initial resultset according to the `ORDER BY` clause. 1. Pick the top cursor to fetch the record, this is our first record. In the example, @@ -994,7 +994,7 @@ After this, the recursion starts again by finding the next lowest cursor value. ### Finalizing the query -For producing the final `issues` rows, we're going to wrap the query with another `SELECT` statement: +For producing the final `issues` rows, we wrap the query with another `SELECT` statement: ```sql SELECT "issues".* @@ -1034,14 +1034,14 @@ The group and project queries are not using sorting, the necessary columns are r indexes. These values are accessed frequently so it's very likely that most of the data will be in the PostgreSQL's buffer cache. -The optimized `IN` query will read maximum 519 entries (cursor values) from the index: +The optimized `IN` query reads maximum 519 entries (cursor values) from the index: - 500 index-only scans for populating the arrays for each project. The cursor values of the first -record will be here. +record is here. - Maximum 19 additional index-only scans for the consecutive records. -The optimized `IN` query will sort the array (cursor values per project array) 20 times, which -means we'll sort 20 x 500 rows. However, this might be a less memory-intensive task than +The optimized `IN` query sorts the array (cursor values per project array) 20 times, which +means we sort 20 x 500 rows. However, this might be a less memory-intensive task than sorting 10 000 rows at once. Performance comparison for the `gitlab-org` group: @@ -1053,5 +1053,5 @@ Performance comparison for the `gitlab-org` group: NOTE: Before taking measurements, the group lookup query was executed separately in order to make -the group data available in the buffer cache. Since it's a frequently called query, it's going to -hit many shared buffers during the query execution in the production environment. +the group data available in the buffer cache. Since it's a frequently called query, it +hits many shared buffers during the query execution in the production environment. diff --git a/doc/development/database/not_null_constraints.md b/doc/development/database/not_null_constraints.md index 947ac6cd875..3962307f80d 100644 --- a/doc/development/database/not_null_constraints.md +++ b/doc/development/database/not_null_constraints.md @@ -99,8 +99,8 @@ such records, so we would follow the same process either way. We first add the `NOT NULL` constraint with a `NOT VALID` parameter, which enforces consistency when new records are inserted or current records are updated. -In the example above, the existing epics with a `NULL` description will not be affected and you'll -still be able to update records in the `epics` table. However, when you try to update or insert +In the example above, the existing epics with a `NULL` description are not affected and you are +still able to update records in the `epics` table. However, when you try to update or insert an epic without providing a description, the constraint causes a database error. Adding or removing a `NOT NULL` clause requires that any application changes are deployed _first_. @@ -129,7 +129,7 @@ end #### Data migration to fix existing records (current release) The approach here depends on the data volume and the cleanup strategy. The number of records that -must be fixed on GitLab.com is a nice indicator that will help us decide whether to use a +must be fixed on GitLab.com is a nice indicator that helps us decide whether to use a post-deployment migration or a background data migration: - If the data volume is less than `1000` records, then the data migration can be executed within the post-migration. @@ -138,7 +138,7 @@ post-deployment migration or a background data migration: When unsure about which option to use, please contact the Database team for advice. Back to our example, the epics table is not considerably large nor frequently accessed, -so we are going to add a post-deployment migration for the 13.0 milestone (current), +so we add a post-deployment migration for the 13.0 milestone (current), `db/post_migrate/20200501000002_cleanup_epics_with_null_description.rb`: ```ruby @@ -173,7 +173,7 @@ end #### Validate the `NOT NULL` constraint (next release) -Validating the `NOT NULL` constraint will scan the whole table and make sure that each record is correct. +Validating the `NOT NULL` constraint scans the whole table and make sure that each record is correct. Still in our example, for the 13.1 milestone (next), we run the `validate_not_null_constraint` migration helper in a final post-deployment migration, @@ -196,11 +196,11 @@ end ## `NOT NULL` constraints on large tables If you have to clean up a nullable column for a [high-traffic table](../migration_style_guide.md#high-traffic-tables) -(for example, the `artifacts` in `ci_builds`), your background migration will go on for a while and -it will need an additional [background migration cleaning up](background_migrations.md#cleaning-up) +(for example, the `artifacts` in `ci_builds`), your background migration goes on for a while and +it needs an additional [background migration cleaning up](background_migrations.md#cleaning-up) in the release after adding the data migration. -In that rare case you will need 3 releases end-to-end: +In that rare case you need 3 releases end-to-end: 1. Release `N.M` - Add the `NOT NULL` constraint and the background-migration to fix the existing records. 1. Release `N.M+1` - Cleanup the background migration. diff --git a/doc/development/database/table_partitioning.md b/doc/development/database/table_partitioning.md index 51f2c443047..c16e6486f99 100644 --- a/doc/development/database/table_partitioning.md +++ b/doc/development/database/table_partitioning.md @@ -36,19 +36,19 @@ before attempting to leverage this feature. 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 +partitioning scheme. There are a few details you 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 +set of columns which determine how the data is split across the partitions. The partition key is used by the database when reading or writing data, to decide which partitions 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 +Second, it's necessary to understand the strategy the database uses +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 @@ -117,7 +117,7 @@ partition key falls in the specified range. For example, the partition 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 +use the `WHERE` to recognize that all matching rows are 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 @@ -164,9 +164,9 @@ 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 +the original table. This migration creates 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 +trigger that syncs writes from the original table into the partitioned copy. An example migration of partitioning the `audit_events` table by its @@ -186,15 +186,15 @@ class PartitionAuditEvents < Gitlab::Database::Migration[1.0] 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 +After this has executed, any inserts, updates, or deletes in the +original table are also duplicated in the new table. For updates and +deletes, the operation only has 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 +The second step is to add a post-deployment migration that schedules +the background jobs that backfill existing data from the original table into the partitioned copy. Continuing the above example, the migration would look like: @@ -225,7 +225,7 @@ partitioning migration. 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 +add another post-deployment migration that cleans up 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. @@ -248,8 +248,7 @@ 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. +original table guarantees that the data remains in sync going forward. ### Step 4: Swap the partitioned and non-partitioned tables (Release N+1) diff --git a/doc/development/sql.md b/doc/development/sql.md index 17250992588..8553e2a5500 100644 --- a/doc/development/sql.md +++ b/doc/development/sql.md @@ -9,7 +9,7 @@ info: To determine the technical writer assigned to the Stage/Group associated w This document describes various guidelines to follow when writing SQL queries, either using ActiveRecord/Arel or raw SQL queries. -## Using LIKE Statements +## Using `LIKE` Statements The most common way to search for data is using the `LIKE` statement. For example, to get all issues with a title starting with "Draft:" you'd write the @@ -56,10 +56,10 @@ FROM issues WHERE (title ILIKE 'Draft:%' OR foo ILIKE 'Draft:%') ``` -## LIKE & Indexes +## `LIKE` & Indexes -PostgreSQL won't use any indexes when using `LIKE` / `ILIKE` with a wildcard at -the start. For example, this will not use any indexes: +PostgreSQL does not use any indexes when using `LIKE` / `ILIKE` with a wildcard at +the start. For example, this does not use any indexes: ```sql SELECT * @@ -145,7 +145,7 @@ The query: Project.select("path, user_id").joins(:merge_requests) # SELECT path, user_id FROM "projects" ... ``` -Later on, a new feature adds an extra column to the `projects` table: `user_id`. During deployment there might be a short time window where the database migration is already executed, but the new version of the application code is not deployed yet. When the query mentioned above executes during this period, the query will fail with the following error message: `PG::AmbiguousColumn: ERROR: column reference "user_id" is ambiguous` +Later on, a new feature adds an extra column to the `projects` table: `user_id`. During deployment there might be a short time window where the database migration is already executed, but the new version of the application code is not deployed yet. When the query mentioned above executes during this period, the query fails with the following error message: `PG::AmbiguousColumn: ERROR: column reference "user_id" is ambiguous` The problem is caused by the way the attributes are selected from the database. The `user_id` column is present in both the `users` and `merge_requests` tables. The query planner cannot decide which table to use when looking up the `user_id` column. @@ -210,7 +210,7 @@ Project.select(:path, :user_id).joins(:merge_requests) # SELECT "projects"."path", "user_id" FROM "projects" ... ``` -When a column list is given, ActiveRecord tries to match the arguments against the columns defined in the `projects` table and prepend the table name automatically. In this case, the `id` column is not going to be a problem, but the `user_id` column could return unexpected data: +When a column list is given, ActiveRecord tries to match the arguments against the columns defined in the `projects` table and prepend the table name automatically. In this case, the `id` column is not a problem, but the `user_id` column could return unexpected data: ```ruby Project.select(:id, :user_id).joins(:merge_requests) @@ -225,7 +225,7 @@ Project.select(:id, :user_id).joins(:merge_requests) ## Plucking IDs Never use ActiveRecord's `pluck` to pluck a set of values into memory only to -use them as an argument for another query. For example, this will execute an +use them as an argument for another query. For example, this executes an extra unnecessary database query and load a lot of unnecessary data into memory: ```ruby @@ -314,10 +314,10 @@ union = Gitlab::SQL::Union.new([projects, more_projects, ...]) Project.from("(#{union.to_sql}) projects") ``` -### Uneven columns in the UNION sub-queries +### Uneven columns in the `UNION` sub-queries -When the UNION query has uneven columns in the SELECT clauses, the database returns an error. -Consider the following UNION query: +When the `UNION` query has uneven columns in the `SELECT` clauses, the database returns an error. +Consider the following `UNION` query: ```sql SELECT id FROM users WHERE id = 1 @@ -333,7 +333,7 @@ each UNION query must have the same number of columns ``` This problem is apparent and it can be easily fixed during development. One edge-case is when -UNION queries are combined with explicit column listing where the list comes from the +`UNION` queries are combined with explicit column listing where the list comes from the `ActiveRecord` schema cache. Example (bad, avoid it): @@ -387,17 +387,17 @@ User.connection.execute(Gitlab::SQL::Union.new([scope1, scope2]).to_sql) When ordering records based on the time they were created, you can order by the `id` column instead of ordering by `created_at`. Because IDs are always -unique and incremented in the order that rows are created, doing so will produce the +unique and incremented in the order that rows are created, doing so produces the exact same results. This also means there's no need to add an index on `created_at` to ensure consistent performance as `id` is already indexed by default. -## Use WHERE EXISTS instead of WHERE IN +## Use `WHERE EXISTS` instead of `WHERE IN` While `WHERE IN` and `WHERE EXISTS` can be used to produce the same data it is recommended to use `WHERE EXISTS` whenever possible. While in many cases PostgreSQL can optimise `WHERE IN` quite well there are also many cases where -`WHERE EXISTS` will perform (much) better. +`WHERE EXISTS` performs (much) better. In Rails you have to use this by creating SQL fragments: @@ -446,7 +446,7 @@ method. This method differs from our `.safe_find_or_create_by` methods because it performs the `INSERT`, and then performs the `SELECT` commands only if that call fails. -If the `INSERT` fails, it will leave a dead tuple around and +If the `INSERT` fails, it leaves a dead tuple around and increment the primary key sequence (if any), among [other downsides](https://api.rubyonrails.org/classes/ActiveRecord/Relation.html#method-i-create_or_find_by). We prefer `.safe_find_or_create_by` if the common path is that we |