diff options
Diffstat (limited to 'doc/development/database')
| -rw-r--r-- | doc/development/database/constraint_naming_convention.md | 26 | ||||
| -rw-r--r-- | doc/development/database/index.md | 7 | ||||
| -rw-r--r-- | doc/development/database/maintenance_operations.md | 46 | ||||
| -rw-r--r-- | doc/development/database/setting_multiple_values.md | 22 |
4 files changed, 92 insertions, 9 deletions
diff --git a/doc/development/database/constraint_naming_convention.md b/doc/development/database/constraint_naming_convention.md new file mode 100644 index 00000000000..63a2d607ac5 --- /dev/null +++ b/doc/development/database/constraint_naming_convention.md @@ -0,0 +1,26 @@ +--- +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 +--- + +# Constraints naming conventions + +The most common option is to let Rails pick the name for database constraints and indexes or let PostgreSQL use the defaults (when applicable). However, when needing to define custom names in Rails or working in Go applications where no ORM is used, it is important to follow strict naming conventions to improve consistency and discoverability. + +The table below describes the naming conventions for custom PostgreSQL constraints. +Please note that the intent is not to retroactively change names in existing databases but rather ensure consistency of future changes. + +| Type | Syntax | Notes | Examples | +|--------------------------|---------------------------------------------------------------------------------------------------|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------|-------------------------------------------------------------------------------------------------------------------| +| **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` | +| **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 desambiguate 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` | + +## Observations + +- Prefixes are preferred over suffices because they make it easier to identify the type of a given constraint quickly, as well as group them alphabetically; +- The `_and_` that joins column names can be omitted to keep the identifiers under the 63 characters' length limit defined by PostgreSQL. Additionally, the notation may be abbreviated to the best of our ability if struggling to keep under this limit. diff --git a/doc/development/database/index.md b/doc/development/database/index.md index 4bcefefe7a7..19159c6c0ff 100644 --- a/doc/development/database/index.md +++ b/doc/development/database/index.md @@ -57,9 +57,14 @@ info: To determine the technical writer assigned to the Stage/Group associated w - [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) +- [Updating multiple values](setting_multiple_values.md) +- [Constraints naming conventions](constraint_naming_convention.md) ## Case studies - [Database case study: Filtering by label](../filtering_by_label.md) - [Database case study: Namespaces storage statistics](../namespaces_storage_statistics.md) + +## Miscellaneous + +- [Maintenance operations](maintenance_operations.md) diff --git a/doc/development/database/maintenance_operations.md b/doc/development/database/maintenance_operations.md new file mode 100644 index 00000000000..c84ec31471e --- /dev/null +++ b/doc/development/database/maintenance_operations.md @@ -0,0 +1,46 @@ +--- +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 +--- + +# Maintenance operations + +This page details various database related operations that may relate to development. + +## Disabling an index + +There are certain situations in which you might want to disable an index before removing it: + +- The index is on a large table and rebuilding it in the case of a revert would take a long time. +- It is uncertain whether or not the index is being used in ways that are not fully visible. + +To disable an index before removing it: + +1. Open a [production infrastructure issue](https://gitlab.com/gitlab-com/gl-infra/production/-/issues/new) +and use the "Production Change" template. +1. Inform the database team in the issue `@gl-database` or in Slack `#database`. +1. Add a step to verify the index is used (this would likely be an `EXPLAIN` command known to use the index). +1. Add the step to disable the index: + + ```sql + UPDATE pg_index SET indisvalid = false WHERE indexrelid = 'index_issues_on_foo'::regclass; + ``` + +1. Add a step to verify the index is invalid (this would likely be the same as used to verify before disabling the index). +1. Verify the index is invalid on replicas: + + ```sql + SELECT indisvalid FROM pg_index WHERE indexrelid = 'index_issues_on_foo'::regclass; + ``` + +1. Add steps for rolling back the invalidation: + 1. Rollback the index invalidation + + ```sql + UPDATE pg_index SET indisvalid = true WHERE indexrelid = 'index_issues_on_foo'::regclass; + ``` + + 1. Verify the index is being used again. + +See this [example infrastructure issue](https://gitlab.com/gitlab-com/gl-infra/production/-/issues/2795) for reference. diff --git a/doc/development/database/setting_multiple_values.md b/doc/development/database/setting_multiple_values.md index 5569a0e10b7..c354247a9f8 100644 --- a/doc/development/database/setting_multiple_values.md +++ b/doc/development/database/setting_multiple_values.md @@ -1,9 +1,15 @@ +--- +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 +--- + # 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`: +There's often a need to update multiple objects with new values for one +or more columns. One method of doing this is using `Relation#update_all`: ```ruby user.issues.open.update_all(due_date: 7.days.from_now) # (1) @@ -28,11 +34,11 @@ update issues 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 +The bad news: there is no way to express this in ActiveRecord or even dropping +down to ARel. The `UpdateManager` does not support `update from`, so this is not expressible. -The good news: We supply an abstraction to help you generate these kinds of +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. @@ -44,7 +50,7 @@ To use this, we need: - 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: +For example, we can express the query above as: ```ruby issue_a = Issue.find(..) @@ -87,7 +93,7 @@ objects = Foo.from_union([ ]) # 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] } +mapping = objects.to_h { |obj| [obj, bazzes[obj.id]] } # Issues at most 2 queries ::Gitlab::Database::BulkUpdate.execute(%i[baz], mapping) do |obj| @@ -100,4 +106,4 @@ end 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. +validations or hooks are called. |