Maintenance operations

This page details various database related operations that may relate to development.

Disabling an index is not safe

{{< alert type=”warning” >}}

Previously, this section described a procedure to mark the index as invalid before removing it. It’s no longer recommended, as it is not safe.

{{< /alert >}}

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.

In such situations, the index was disabled in a coordinated manner with the infrastructure team and the database team by opening a production infrastructure issue with the “Production Change” template and then running the following commands: 

-- Disable the index then run an EXPLAIN command known to use the index:
UPDATE pg_index SET indisvalid = false WHERE indexrelid = 'index_issues_on_foo'::regclass;
-- Verify the index is invalid on replicas:
SELECT indisvalid FROM pg_index WHERE indexrelid = 'index_issues_on_foo'::regclass;

-- Rollback the invalidation:
UPDATE pg_index SET indisvalid = true WHERE indexrelid = 'index_issues_on_foo'::regclass;

See this example infrastructure issue for reference.