diff options
Diffstat (limited to 'doc/development/database')
| -rw-r--r-- | doc/development/database/adding_database_indexes.md | 36 | ||||
| -rw-r--r-- | doc/development/database/avoiding_downtime_in_migrations.md | 62 | ||||
| -rw-r--r-- | doc/development/database/batched_background_migrations.md | 2 | ||||
| -rw-r--r-- | doc/development/database/constraint_naming_convention.md | 2 | ||||
| -rw-r--r-- | doc/development/database/database_dictionary.md | 115 | ||||
| -rw-r--r-- | doc/development/database/database_lab.md | 39 | ||||
| -rw-r--r-- | doc/development/database/index.md | 66 | ||||
| -rw-r--r-- | doc/development/database/pagination_guidelines.md | 2 | ||||
| -rw-r--r-- | doc/development/database/query_recorder.md | 8 | ||||
| -rw-r--r-- | doc/development/database/required_stops.md | 41 | ||||
| -rw-r--r-- | doc/development/database/setting_multiple_values.md | 2 | ||||
| -rw-r--r-- | doc/development/database/table_partitioning.md | 10 |
12 files changed, 296 insertions, 89 deletions
diff --git a/doc/development/database/adding_database_indexes.md b/doc/development/database/adding_database_indexes.md index 6a401c804f5..e1d5a7af6d9 100644 --- a/doc/development/database/adding_database_indexes.md +++ b/doc/development/database/adding_database_indexes.md @@ -294,16 +294,14 @@ end ### Verify the MR was deployed and the index exists in production -You can verify if the post-deploy migration was executed on GitLab.com by: - -- Executing `/chatops run auto_deploy status <merge_sha>`. If the output returns `db/gprd`, - the post-deploy migration has been executed in the production database. More details in this - [guide](https://gitlab.com/gitlab-org/release/docs/-/blob/master/general/post_deploy_migration/readme.md#how-to-determine-if-a-post-deploy-migration-has-been-executed-on-gitlabcom). -- Use a meta-command in #database-lab, such as: `\d <index_name>`. - - Ensure that the index is not [`invalid`](https://www.postgresql.org/docs/12/sql-createindex.html#:~:text=The%20psql%20%5Cd%20command%20will%20report%20such%20an%20index%20as%20INVALID). -- Ask someone in #database to check if the index exists. -- With proper access, you can also verify directly on production or in a - production clone. +1. Verify that the post-deploy migration was executed on GitLab.com using ChatOps with + `/chatops run auto_deploy status <merge_sha>`. If the output returns `db/gprd`, + the post-deploy migration has been executed in the production database. For more information, see + [How to determine if a post-deploy migration has been executed on GitLab.com](https://gitlab.com/gitlab-org/release/docs/-/blob/master/general/post_deploy_migration/readme.md#how-to-determine-if-a-post-deploy-migration-has-been-executed-on-gitlabcom). +1. In the case of an [index created asynchronously](#schedule-the-index-to-be-created), wait + until the next week so that the index can be created over a weekend. +1. Use [Database Lab](database_lab.md) to check [if creation was successful](database_lab.md#checking-indexes). + Ensure the output does not indicate the index is `invalid`. ### Add a migration to create the index synchronously @@ -394,15 +392,15 @@ You must test the database index changes locally before creating a merge request ### Verify the MR was deployed and the index no longer exists in production -You can verify if the MR was deployed to GitLab.com with -`/chatops run auto_deploy status <merge_sha>`. To verify the existence of -the index, you can: - -- Use a meta-command in `#database-lab`, for example: `\d <index_name>`. -- Make sure the index no longer exists -- Ask someone in `#database` to check if the index exists. -- If you have access, you can verify directly on production or in a - production clone. +1. Verify that the post-deploy migration was executed on GitLab.com using ChatOps with + `/chatops run auto_deploy status <merge_sha>`. If the output returns `db/gprd`, + the post-deploy migration has been executed in the production database. For more information, see + [How to determine if a post-deploy migration has been executed on GitLab.com](https://gitlab.com/gitlab-org/release/docs/-/blob/master/general/post_deploy_migration/readme.md#how-to-determine-if-a-post-deploy-migration-has-been-executed-on-gitlabcom). +1. In the case of an [index removed asynchronously](#schedule-the-index-to-be-removed), wait + until the next week so that the index can be created over a weekend. +1. Use Database Lab [to check if removal was successful](database_lab.md#checking-indexes). + [Database Lab](database_lab.md) + should report an error when trying to find the removed index. If not, the index may still exist. ### Add a migration to destroy the index synchronously diff --git a/doc/development/database/avoiding_downtime_in_migrations.md b/doc/development/database/avoiding_downtime_in_migrations.md index b34c0bbf728..bb6e13eff53 100644 --- a/doc/development/database/avoiding_downtime_in_migrations.md +++ b/doc/development/database/avoiding_downtime_in_migrations.md @@ -282,6 +282,62 @@ Example migration: end ``` +## Changing column defaults + +Changing column defaults is difficult because of how Rails handles values +that are equal to the default. + +If running code ever explicitly writes the old default value of a column, you must follow a multi-step +process to prevent Rails replacing the old default with the new default in INSERT queries that explicitly +specify the old default. + +Doing this requires steps in two minor releases: + +1. Add the `SafelyChangeColumnDefault` concern to the model and change the default in a post-migration. +1. Clean up the `SafelyChangeColumnDefault` concern in the next minor release. + +We must wait a minor release before cleaning up the `SafelyChangeColumnDefault` because self-managed +releases bundle an entire minor release into a single zero-downtime deployment. + +### Step 1: Add the `SafelyChangeColumnDefault` concern to the model and change the default in a post-migration + +The first step is to mark the column as safe to change in application code. + +```ruby +class Ci::Build < ApplicationRecord + include SafelyChangeColumnDefault + + columns_changing_default :partition_id +end +``` + +Then create a **post-deployment migration** to change the default: + +```shell +bundle exec rails g post_deployment_migration change_ci_builds_default +``` + +```ruby +class ChangeCiBuildsDefault < Gitlab::Database::Migration[2.1] + def up + change_column_default('ci_builds', 'partition_id', from: 100, to: 101) + end + + def down + change_column_default('ci_builds', 'partition_id', from: 101, to: 100) + end +end +``` + +You can consider [enabling lock retries](../migration_style_guide.md#usage-with-transactional-migrations) +when you run a migration on big tables, because it might take some time to +acquire a lock on this table. + +### Step 2: Clean up the `SafelyChangeColumnDefault` concern in the next minor release + +In the next minor release, create a new merge request to remove the `columns_changing_default` call. Also remove the `SafelyChangeColumnDefault` include +if it is not needed for a different column. + ## Changing The Schema For Large Tables While `change_column_type_concurrently` and `rename_column_concurrently` can be @@ -319,10 +375,8 @@ This operation is safe as there's no code using the table just yet. Dropping tables can be done safely using a post-deployment migration, but only if the application no longer uses the table. -Add the table to `DELETED_TABLES` in -[gitlab_schema.rb](https://gitlab.com/gitlab-org/gitlab/-/blob/master/lib/gitlab/database/gitlab_schema.rb), -along with its `gitlab_schema`. Even though the table is deleted, it is still -referenced in database migrations. +Add the table to [`db/docs/deleted_tables`](https://gitlab.com/gitlab-org/gitlab/-/tree/master/db/docs/deleted_tables) using the process described in [database dictionary](database_dictionary.md#dropping-tables). +Even though the table is deleted, it is still referenced in database migrations. ## Renaming Tables diff --git a/doc/development/database/batched_background_migrations.md b/doc/development/database/batched_background_migrations.md index 71df4da59c3..88fdfab9828 100644 --- a/doc/development/database/batched_background_migrations.md +++ b/doc/development/database/batched_background_migrations.md @@ -269,6 +269,7 @@ In the second (filtered) example, we know exactly 100 will be updated with each class BackfillNamespaceType < BatchedMigrationJob scope_to ->(relation) { relation.where(type: nil) } operation_name :update_all + feature_category :source_code_management def perform each_sub_batch do |sub_batch| @@ -330,6 +331,7 @@ background migration. # end operation_name :update_all + feature_category :source_code_management def perform each_sub_batch( diff --git a/doc/development/database/constraint_naming_convention.md b/doc/development/database/constraint_naming_convention.md index e9e130495e6..4ac1cd2a71d 100644 --- a/doc/development/database/constraint_naming_convention.md +++ b/doc/development/database/constraint_naming_convention.md @@ -18,7 +18,7 @@ The intent is not to retroactively change names in existing databases but rather |--------------------------|---------------------------------------------------------------------------------------------------|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------|-------------------------------------------------------------------------------------------------------------------| | **Primary Key** | `pk_<table name>` | | `pk_projects` | | **Foreign Key** | `fk_<table name>_<column name>[_and_<column name>]*_<foreign table name>` | | `fk_projects_group_id_groups` | -| **Index** | `index_<table name>_on_<column name>[_and_<column name>]*[_and_<column name in partial clause>]*` | | `index_repositories_on_group_id` | +| **Index** | `index_<table name>_on_<column name>[_and_<column name>]*[_and_<column name in partial clause>]*` | Index names must be all lowercase. | `index_repositories_on_group_id` | | **Unique Constraint** | `unique_<table name>_<column name>[_and_<column name>]*` | | `unique_projects_group_id_and_name` | | **Check Constraint** | `check_<table name>_<column name>[_and_<column name>]*[_<suffix>]?` | The optional suffix should denote the type of validation, such as `length` and `enum`. It can also be used to disambiguate multiple `CHECK` constraints on the same column. | `check_projects_name_length`<br />`check_projects_type_enum`<br />`check_projects_admin1_id_and_admin2_id_differ` | | **Exclusion Constraint** | `excl_<table name>_<column name>[_and_<column name>]*_[_<suffix>]?` | The optional suffix should denote the type of exclusion being performed. | `excl_reservations_start_at_end_at_no_overlap` | diff --git a/doc/development/database/database_dictionary.md b/doc/development/database/database_dictionary.md index d74d7e77edb..b7e6fa4b5b3 100644 --- a/doc/development/database/database_dictionary.md +++ b/doc/development/database/database_dictionary.md @@ -17,7 +17,7 @@ For the `geo` database, the dictionary files are stored under `ee/db/docs/`. ## Example dictionary file ```yaml ---- +---- table_name: terraform_states classes: - Terraform::State @@ -28,45 +28,110 @@ introduced_by_url: https://gitlab.com/gitlab-org/gitlab/-/merge_requests/26619 milestone: '13.0' ``` -## Schema +## Adding tables -| Attribute | Type | Required | Description | -|----------------------------|---------------|----------|-----------------------------------------------------------------------------------| -| `table_name` / `view_name` | String | yes | Database table name or view name | -| `classes` | Array(String) | no | List of classes that are associated to this table or view. | -| `feature_categories` | Array(String) | yes | List of feature categories using this table or view. | -| `description` | String | no | Text description of the information stored in the table or view, and its purpose. | -| `introduced_by_url` | URL | no | URL to the merge request or commit which introduced this table or view. | -| `milestone` | String | no | The milestone that introduced this table or view. | -| `gitlab_schema` | String | yes | GitLab schema name. | +### Schema -## Adding tables +| Attribute | Type | Required | Description | +|----------------------------|---------------|----------|-------------| +| `table_name` | String | yes | Database table name. | +| `classes` | Array(String) | no | List of classes that are associated to this table. | +| `feature_categories` | Array(String) | yes | List of feature categories using this table. | +| `description` | String | no | Text description of the information stored in the table, and its purpose. | +| `introduced_by_url` | URL | no | URL to the merge request or commit which introduced this table. | +| `milestone` | String | no | The milestone that introduced this table. | +| `gitlab_schema` | String | yes | GitLab schema name. | -When adding a new table, create a new file under `db/docs/` for the `main` and `ci` databases. -For the `geo` database use `ee/db/docs/`. -Name the file as `<table_name>.yml`, containing as much information as you know about the table. +### Process -Include this file in the commit with the migration that creates the table. +When adding a table, you should: + +1. Create a new file for this table in the appropriate directory: + - `gitlab_main` table: `db/docs/` + - `gitlab_ci` table: `db/docs/` + - `gitlab_shared` table: `db/docs/` + - `gitlab_geo` table: `ee/db/docs/` +1. Name the file `<table_name>.yml`, and include as much information as you know about the table. +1. Include this file in the commit with the migration that creates the table. ## Dropping tables -When dropping a table, you must remove the metadata file from `db/docs/` for `main` and `ci` databases. -For the `geo` database, you must remove the file from `ee/db/docs/`. -Use the same commit with the migration that drops the table. +### Schema + +| Attribute | Type | Required | Description | +|----------------------------|---------------|----------|-------------| +| `table_name` | String | yes | Database table name. | +| `classes` | Array(String) | no | List of classes that are associated to this table. | +| `feature_categories` | Array(String) | yes | List of feature categories using this table. | +| `description` | String | no | Text description of the information stored in the table, and its purpose. | +| `introduced_by_url` | URL | no | URL to the merge request or commit which introduced this table. | +| `milestone` | String | no | The milestone that introduced this table. | +| `gitlab_schema` | String | yes | GitLab schema name. | +| `removed_by_url` | String | yes | URL to the merge request or commit which removed this table. | +| `removed_in_milestone` | String | yes | The milestone that removes this table. | + +### Process + +When dropping a table, you should: + +1. Move the dictionary file for this table to the `deleted_tables` directory: + - `gitlab_main` table: `db/docs/deleted_tables/` + - `gitlab_ci` table: `db/docs/deleted_tables/` + - `gitlab_shared` table: `db/docs/deleted_tables/` + - `gitlab_geo` table: `ee/db/docs/deleted_tables/` +1. Add the fields `removed_by_url` and `removed_in_milestone` to the dictionary file. +1. Include this change in the commit with the migration that drops the table. ## Adding views +### Schema + +| Attribute | Type | Required | Description | +|----------------------------|---------------|----------|-------------| +| `table_name` | String | yes | Database view name. | +| `classes` | Array(String) | no | List of classes that are associated to this view. | +| `feature_categories` | Array(String) | yes | List of feature categories using this view. | +| `description` | String | no | Text description of the information stored in the view, and its purpose. | +| `introduced_by_url` | URL | no | URL to the merge request or commit which introduced this view. | +| `milestone` | String | no | The milestone that introduced this view. | +| `gitlab_schema` | String | yes | GitLab schema name. | + +### Process + When adding a new view, you should: 1. Create a new file for this view in the appropriate directory: - - `main` database: `db/docs/views/` - - `ci` database: `db/docs/views/` - - `geo` database: `ee/db/docs/views/` + - `gitlab_main` view: `db/docs/views/` + - `gitlab_ci` view: `db/docs/views/` + - `gitlab_shared` view: `db/docs/views/` + - `gitlab_geo` view: `ee/db/docs/views/` 1. Name the file `<view_name>.yml`, and include as much information as you know about the view. 1. Include this file in the commit with the migration that creates the view. ## Dropping views -When dropping a view, you must remove the metadata file from `db/docs/views/`. -For the `geo` database, you must remove the file from `ee/db/docs/views/`. -Use the same commit with the migration that drops the view. +## Schema + +| Attribute | Type | Required | Description | +|----------------------------|---------------|----------|-------------| +| `view_name` | String | yes | Database view name. | +| `classes` | Array(String) | no | List of classes that are associated to this view. | +| `feature_categories` | Array(String) | yes | List of feature categories using this view. | +| `description` | String | no | Text description of the information stored in the view, and its purpose. | +| `introduced_by_url` | URL | no | URL to the merge request or commit which introduced this view. | +| `milestone` | String | no | The milestone that introduced this view. | +| `gitlab_schema` | String | yes | GitLab schema name. | +| `removed_by_url` | String | yes | URL to the merge request or commit which removed this view. | +| `removed_in_milestone` | String | yes | The milestone that removes this view. | + +### Process + +When dropping a view, you should: + +1. Move the dictionary file for this table to the `deleted_views` directory: + - `gitlab_main` view: `db/docs/deleted_views/` + - `gitlab_ci` view: `db/docs/deleted_views/` + - `gitlab_shared` view: `db/docs/deleted_views/` + - `gitlab_geo` view: `ee/db/docs/deleted_views/` +1. Add the fields `removed_by_url` and `removed_in_milestone` to the dictionary file. +1. Include this change in the commit with the migration that drops the view. diff --git a/doc/development/database/database_lab.md b/doc/development/database/database_lab.md index b60091fa37c..162fc597cc4 100644 --- a/doc/development/database/database_lab.md +++ b/doc/development/database/database_lab.md @@ -75,6 +75,45 @@ the new index. `exec` does not return any results, only the time required to exe After many changes, such as after a destructive query or an ineffective index, you must start over. To reset your designated clone, run `reset`. +#### Checking indexes + +Use Database Lab to check the status of an index with the meta-command `\d <index_name>`. + +Caveats: + +- Indexes are created in both the `main` and `ci` databases, so be sure to use the instance + that matches the table's `gitlab_schema`. For example, if the index is added to + [`ci_builds`](https://gitlab.com/gitlab-org/gitlab/-/blob/master/db/docs/ci_builds.yml#L14), + use `gitlab-production-ci`. +- Database Lab typically has a small delay of a few hours. If more up-to-date information + is required, you can instead request access to a replica [via Teleport](https://gitlab.com/gitlab-com/runbooks/-/blob/master/docs/Teleport/Connect_to_Database_Console_via_Teleport.md) + +For example: `\d index_design_management_designs_on_project_id` produces: + +```plaintext +Index "public.index_design_management_designs_on_project_id" + Column | Type | Key? | Definition +------------+---------+------+------------ + project_id | integer | yes | project_id +btree, for table "public.design_management_designs" +``` + +In the case of an invalid index, the output ends with `invalid`, like: + +```plaintext +Index "public.index_design_management_designs_on_project_id" + Column | Type | Key? | Definition +------------+---------+------+------------ + project_id | integer | yes | project_id +btree, for table "public.design_management_designs", invalid +``` + +If the index doesn't exist, JoeBot throws an error like: + +```plaintext +ERROR: psql error: psql:/tmp/psql-query-932227396:1: error: Did not find any relation named "no_index". +``` + ### Migration testing For information on testing migrations, review our diff --git a/doc/development/database/index.md b/doc/development/database/index.md index c244d784422..5abc7cd3ffa 100644 --- a/doc/development/database/index.md +++ b/doc/development/database/index.md @@ -23,59 +23,59 @@ info: To determine the technical writer assigned to the Stage/Group associated w ## Migrations -- [Different types of migrations](../migration_style_guide.md#choose-an-appropriate-migration-type) -- [Create a regular migration](../migration_style_guide.md#create-a-regular-schema-migration), including creating new models -- [Post-deployment migrations guidelines](post_deployment_migrations.md) and [how to create one](post_deployment_migrations.md#creating-migrations) -- [Legacy Background migrations guidelines](background_migrations.md) +- [Adding required stops](required_stops.md) +- [Avoiding downtime in migrations](avoiding_downtime_in_migrations.md) - [Batched background migrations guidelines](batched_background_migrations.md) +- [Create a regular migration](../migration_style_guide.md#create-a-regular-schema-migration), including creating new models - [Deleting migrations](deleting_migrations.md) -- [Running database migrations](database_debugging.md#migration-wrangling) +- [Different types of migrations](../migration_style_guide.md#choose-an-appropriate-migration-type) +- [Legacy background migrations guidelines](background_migrations.md) - [Migrations for multiple databases](migrations_for_multiple_databases.md) -- [Avoiding downtime in migrations](avoiding_downtime_in_migrations.md) -- [When and how to write Rails migrations tests](../testing_guide/testing_migrations_guide.md) - [Migrations style guide](../migration_style_guide.md) for creating safe SQL migrations -- [Testing Rails migrations](../testing_guide/testing_migrations_guide.md) guide -- [Post deployment migrations](post_deployment_migrations.md) -- [Swapping tables](swapping_tables.md) -- [Deleting migrations](deleting_migrations.md) -- [SQL guidelines](../sql.md) for working with SQL queries - [Partitioning tables](table_partitioning.md) +- [Post-deployment migrations guidelines](post_deployment_migrations.md) and [how to create one](post_deployment_migrations.md#creating-migrations) +- [Running database migrations](database_debugging.md#migration-wrangling) +- [SQL guidelines](../sql.md) for working with SQL queries +- [Swapping tables](swapping_tables.md) +- [Testing Rails migrations](../testing_guide/testing_migrations_guide.md) guide +- [When and how to write Rails migrations tests](../testing_guide/testing_migrations_guide.md) ## Debugging -- [Resetting the database](database_debugging.md#delete-everything-and-start-over) - [Accessing the database](database_debugging.md#manually-access-the-database) +- [Resetting the database](database_debugging.md#delete-everything-and-start-over) - [Troubleshooting and debugging the database](database_debugging.md) -- Tracing the source of an SQL query using query comments with [Marginalia](database_query_comments.md) -- Tracing the source of an SQL query in Rails console using [Verbose Query Logs](https://guides.rubyonrails.org/debugging_rails_applications.html#verbose-query-logs) +- Tracing the source of an SQL query: + - In Rails console using [Verbose Query Logs](https://guides.rubyonrails.org/debugging_rails_applications.html#verbose-query-logs) + - Using query comments with [Marginalia](database_query_comments.md) ## Best practices - [Adding database indexes](adding_database_indexes.md) -- [Foreign keys & associations](foreign_keys.md) - [Adding a foreign key constraint to an existing column](add_foreign_key_to_existing_column.md) -- [`NOT NULL` constraints](not_null_constraints.md) -- [Strings and the Text data type](strings_and_the_text_data_type.md) -- [Single table inheritance](single_table_inheritance.md) -- [Polymorphic associations](polymorphic_associations.md) -- [Serializing data](serializing_data.md) +- [Check for background migrations before upgrading](../../update/background_migrations.md) +- [Client-side connection-pool](client_side_connection_pool.md) +- [Constraints naming conventions](constraint_naming_convention.md) +- [Creating enums](creating_enums.md) +- [Data layout and access patterns](layout_and_access_patterns.md) +- [Efficient `IN` operator queries](efficient_in_operator_queries.md) +- [Foreign keys & associations](foreign_keys.md) - [Hash indexes](hash_indexes.md) -- [Storing SHA1 hashes as binary](sha1_as_binary.md) -- [Iterating tables in batches](iterating_tables_in_batches.md) - [Insert into tables in batches](insert_into_tables_in_batches.md) +- [Iterating tables in batches](iterating_tables_in_batches.md) +- [`NOT NULL` constraints](not_null_constraints.md) - [Ordering table columns](ordering_table_columns.md) -- [Verifying database capabilities](verifying_database_capabilities.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) -- [Constraints naming conventions](constraint_naming_convention.md) -- [Query performance guidelines](query_performance.md) - [Pagination guidelines](pagination_guidelines.md) - [Pagination performance guidelines](pagination_performance_guidelines.md) -- [Efficient `IN` operator queries](efficient_in_operator_queries.md) -- [Data layout and access patterns](layout_and_access_patterns.md) -- [Check for background migrations before upgrading](../../update/background_migrations.md) +- [Polymorphic associations](polymorphic_associations.md) +- [Query count limits](query_count_limits.md) +- [Query performance guidelines](query_performance.md) +- [Serializing data](serializing_data.md) +- [Single table inheritance](single_table_inheritance.md) +- [Storing SHA1 hashes as binary](sha1_as_binary.md) +- [Strings and the Text data type](strings_and_the_text_data_type.md) +- [Updating multiple values](setting_multiple_values.md) +- [Verifying database capabilities](verifying_database_capabilities.md) ## Case studies diff --git a/doc/development/database/pagination_guidelines.md b/doc/development/database/pagination_guidelines.md index 54b315b9dd9..aeab45e2158 100644 --- a/doc/development/database/pagination_guidelines.md +++ b/doc/development/database/pagination_guidelines.md @@ -62,7 +62,7 @@ Offset-based pagination is the easiest way to paginate over records, however, it - Avoid presenting total counts, prefer limit counts. - Example: count maximum 1001 records, and then on the UI show 1000+ if the count is 1001, show the actual number otherwise. - - See the [badge counters approach](../merge_request_performance_guidelines.md#badge-counters) for more information. + - See the [badge counters approach](../merge_request_concepts/performance.md#badge-counters) for more information. - Avoid using page numbers, use next and previous page buttons. - Keyset pagination doesn't support page numbers. - For APIs, advise against building URLs for the next page by "hand". diff --git a/doc/development/database/query_recorder.md b/doc/development/database/query_recorder.md index 84bd0fc938f..dfaaf8afcde 100644 --- a/doc/development/database/query_recorder.md +++ b/doc/development/database/query_recorder.md @@ -10,7 +10,7 @@ QueryRecorder is a tool for detecting the [N+1 queries problem](https://guides.r > Implemented in [spec/support/query_recorder.rb](https://gitlab.com/gitlab-org/gitlab/-/blob/master/spec/support/helpers/query_recorder.rb) via [9c623e3e](https://gitlab.com/gitlab-org/gitlab-foss/commit/9c623e3e5d7434f2e30f7c389d13e5af4ede770a) -As a rule, merge requests [should not increase query counts](../merge_request_performance_guidelines.md#query-counts). If you find yourself adding something like `.includes(:author, :assignee)` to avoid having `N+1` queries, consider using QueryRecorder to enforce this with a test. Without this, a new feature which causes an additional model to be accessed can silently reintroduce the problem. +As a rule, merge requests [should not increase query counts](../merge_request_concepts/performance.md#query-counts). If you find yourself adding something like `.includes(:author, :assignee)` to avoid having `N+1` queries, consider using QueryRecorder to enforce this with a test. Without this, a new feature which causes an additional model to be accessed can silently reintroduce the problem. ## How it works @@ -52,7 +52,7 @@ there are no N+1 queries. Rather than make an extra request to warm the cache, p ## Cached queries -By default, QueryRecorder ignores [cached queries](../merge_request_performance_guidelines.md#cached-queries) in the count. However, it may be better to count +By default, QueryRecorder ignores [cached queries](../merge_request_concepts/performance.md#cached-queries) in the count. However, it may be better to count all queries to avoid introducing an N+1 query that may be masked by the statement cache. To do this, this requires the `:use_sql_query_cache` flag to be set. You should pass the `skip_cached` variable to `QueryRecorder` and use the `exceed_all_query_limit` matcher: @@ -146,5 +146,5 @@ There are multiple ways to find the source of queries. - [Bullet](../profiling.md#bullet) For finding `N+1` query problems - [Performance guidelines](../performance.md) -- [Merge request performance guidelines - Query counts](../merge_request_performance_guidelines.md#query-counts) -- [Merge request performance guidelines - Cached queries](../merge_request_performance_guidelines.md#cached-queries) +- [Merge request performance guidelines - Query counts](../merge_request_concepts/performance.md#query-counts) +- [Merge request performance guidelines - Cached queries](../merge_request_concepts/performance.md#cached-queries) diff --git a/doc/development/database/required_stops.md b/doc/development/database/required_stops.md new file mode 100644 index 00000000000..46fabb5c1b4 --- /dev/null +++ b/doc/development/database/required_stops.md @@ -0,0 +1,41 @@ +--- +stage: Data Stores +group: Database +info: To determine the technical writer assigned to the Stage/Group associated with this page, see https://about.gitlab.com/handbook/product/ux/technical-writing/#assignments +--- + +# Adding required stops + +Required stops should only be added when it is deemed absolutely necessary, due to their +disruptive effect on customers. Before adding a required stop, consider if any +alternative approaches exist to avoid a required stop. Sometimes a required +stop is unavoidable. In those cases, follow the instructions below. + +## Before the required stop is released + +Before releasing a known required stop, complete these steps. If the required stop +is identified after release, the following steps must still be completed: + +1. Update [upgrade paths](../../update/index.md#upgrade-paths) to include the new + required stop. +1. Communicate the changes with the customer Support and Release management teams. +1. File an issue with the Database group to squash migrations to that version in the + next release. Use this template for your issue: + + ```markdown + Title: `Squash migrations to <Required stop version>` + As a result of the required stop added for <required stop version> we should squash + migrations up to that version, and update the minimum schema version. + + Deliverables: + - [ ] Migrations are squashed up to <required stop version> + - [ ] `Gitlab::Database::MIN_SCHEMA_VERSION` matches init_schema version + + /label ~"group::database" ~"section::enablement" ~"devops::data_stores" ~"Category:Database" ~"type::maintenance" + /cc @gitlab-org/database-team/triage + ``` + +## In the release following the required stop + +1. Update `Gitlab::Database::MIN_SCHEMA_GITLAB_VERSION` in `lib/gitlab/database.rb` to the + new required stop versions. Do not change `Gitlab::Database::MIN_SCHEMA_VERSION`. diff --git a/doc/development/database/setting_multiple_values.md b/doc/development/database/setting_multiple_values.md index fb85386785d..48be7ff9f10 100644 --- a/doc/development/database/setting_multiple_values.md +++ b/doc/development/database/setting_multiple_values.md @@ -32,7 +32,7 @@ update issues where id = obj_id ``` -You can't express this in ActiveRecord, or by dropping down to [Arel](https://api.rubyonrails.org/v6.1.0/classes/Arel.html), +You can't express this in ActiveRecord, or by dropping down to [Arel](https://api.rubyonrails.org/classes/Arel.html), because the `UpdateManager` does not support `update from`. However, we supply an abstraction to help you generate these kinds of updates: `Gitlab::Database::BulkUpdate`. This abstraction constructs queries like the previous example, and uses diff --git a/doc/development/database/table_partitioning.md b/doc/development/database/table_partitioning.md index 5f1deb77b6c..30131fc0347 100644 --- a/doc/development/database/table_partitioning.md +++ b/doc/development/database/table_partitioning.md @@ -389,7 +389,8 @@ class PrepareForeignKeyForPartitioning < Gitlab::Database::Migration[2.1] TARGET_TABLE_NAME, column: [PARTITION_COLUMN, COLUMN], target_column: [PARTITION_COLUMN, TARGET_COLUMN], - validate: false + validate: false, + on_update: :cascade, name: CONSTRAINT_NAME ) @@ -402,6 +403,13 @@ class PrepareForeignKeyForPartitioning < Gitlab::Database::Migration[2.1] end ``` +The `on_update: :cascade` option is mandatory if we want the partitioning column +to be updated. This will cascade the update to all dependent rows. Without +specifying it, updating the partition column on the target table we would +result in a `Key is still referenced from table ...` error and updating the +partition column on the source table would raise a +`Key is not present in table ...` error. + ### Step 6 - Create parent table and attach existing table as the initial partition You can now create the parent table attaching the existing table as the initial |