Home  /  SQL commands

DROP INDEX

View as Markdown

DROP INDEX removes an index from Materialize.

Syntax

DROP INDEX [IF EXISTS] <index_name> [CASCADE|RESTRICT];
Syntax element Description
IF EXISTS Optional. If specified, do not return an error if the specified index does not exist.
<index_name> Index to drop.
CASCADE Optional. Equivalent to RESTRICT. DROP INDEX never drops the objects that depend on the index. See Dependent objects.
RESTRICT Optional. Do not remove the index if other objects depend on it. (Default.)

Dependent objects

When Materialize creates a materialized view, another index, or a metric sink, its optimizer may plan the new object to read from an existing index rather than from the underlying relation. The object then depends on that index, even though the index does not appear in the object’s definition.

DROP INDEX fails if any materialized view, index, or metric sink reads from the index, and the error names the dependent objects. This holds with CASCADE too: because the dependency is invisible in the dependents’ definitions, DROP INDEX never drops them. To drop the index, first drop the dependent objects yourself, then drop the index and recreate the dependents. In production, use a blue/green deployment to replace them without downtime.

A newer index on a relation can read from an older index on the same relation, so drop indexes on the same relation newest first.

In-progress SELECT, SUBSCRIBE, and COPY TO statements that read from the index do not block the drop. The index continues to be maintained until they finish, and the DROP INDEX statement returns a notice saying so.

You can inspect which objects read from an index with mz_internal.mz_compute_dependencies.

Privileges

To execute the DROP INDEX statement, you need:

  • Ownership of the dropped index.
  • USAGE privileges on the containing schema.

Examples

Remove an index

💡 Tip: In the Materialize Console, you can view existing indexes in the Database object explorer. Alternatively, you can use the SHOW INDEXES command.

Using the DROP INDEX commands, the following example drops an index named q01_geo_idx.

DROP INDEX q01_geo_idx;

If the index q01_geo_idx does not exist, the above operation returns an error.

Remove an index that other objects read from

If a materialized view was planned to read from the index, DROP INDEX returns an error:

DROP INDEX q01_geo_idx;

ERROR:  cannot drop index "q01_geo_idx": still depended upon by materialized view "q01_geo_summary"
DETAIL:  The dependent objects are live dataflows that read from index "q01_geo_idx", so the index cannot be dropped while they exist.
HINT:  Drop the dependent objects first, then drop the index and recreate the dependents. To replace them in production without downtime, use a blue/green deployment.

Drop the materialized view first, then the index, and recreate the view:

DROP MATERIALIZED VIEW q01_geo_summary;
DROP INDEX q01_geo_idx;
CREATE MATERIALIZED VIEW q01_geo_summary AS ...;

Remove an index without erroring if the index does not exist

You can specify the IF EXISTS option so that the DROP INDEX command does not return an error if the index to drop does not exist.

DROP INDEX IF EXISTS q01_geo_idx;
Back to top ↑