blob: fcdf132aa094f6ea4e8c51d514e645453e67ee1a (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
|
---
stage: Data Stores
group: Database
info: Any user with at least the Maintainer role can merge updates to this content. For details, see https://docs.gitlab.com/ee/development/development_processes.html#development-guidelines-review.
---
# 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.
|
