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 and use the "Production Change" template.

2. Inform the database team in the issue @gl-database or in Slack #database.

3. Add a step to verify the index is used (this would likely be an EXPLAIN command known to use the index).

4. Add the step to disable the index:

   UPDATE pg_index SET indisvalid = false WHERE indexrelid = 'index_issues_on_foo'::regclass;

5. Add a step to verify the index is invalid (this would likely be the same as used to verify before disabling the index).

6. Verify the index is invalid on replicas:

   SELECT indisvalid FROM pg_index WHERE indexrelid = 'index_issues_on_foo'::regclass;

7. Add steps for rolling back the invalidation:

   1. Rollback the index invalidation

      UPDATE pg_index SET indisvalid = true WHERE indexrelid = 'index_issues_on_foo'::regclass;

   2. Verify the index is being used again.

See this example infrastructure issue for reference.