CREATE INDEX

Creates a secondary index on an existing table. The index kind is selected by the USING clause. This page is the syntax reference. For how each index works, when to use it, and how to tune it, follow the link next to each form into Storage and indexing.

Aggregating indexes use a different grammar; see CREATE AGGREGATING INDEX. To remove an index, see DROP INDEX.

Syntax

CREATE INDEX [IF NOT EXISTS] <index_name>
  ON <table_name>
  USING <method>(<arguments>)
  [WITH (<parameters>)];

Parameter	Description
`<index_name>`	Unique name for the index across all tables, indexes, and views in the database.
`<table_name>`	The `FACT` or `DIMENSION` table to index. External tables are not supported.
`<method>`	The index kind: `SKIP_INDEX`, `INVERTED_INDEX`, `FULL_TEXT`, or `HNSW`.
`<arguments>`, `<parameters>`	Method-specific. See the forms below.

Index names are unique per database. You can create multiple indexes on one table, but at most one index per indexed expression.

`SKIP_INDEX`: data skipping (minmax)

Skips granules whose stored min/max range cannot match a comparison predicate. For mechanism and tuning, see Data skipping index.

CREATE INDEX <index_name>
  ON <table_name>
  USING SKIP_INDEX(<expression>)
  WITH (TYPE = minmax, GRANULARITY = <granularity>);

Parameter	Description
`<expression>`	A column or scalar expression of a comparable type (`INT`, `BIGINT`, `REAL`, `DOUBLE PRECISION`, `DECIMAL`, `DATE`, `TIMESTAMP`, `TIMESTAMPTZ`, `TEXT`, `BOOLEAN`, `BYTEA`). `ARRAY` and `STRUCT` are not supported.
`TYPE`	The index type. Only `minmax` is supported. Required.
`GRANULARITY`	Positive integer: how many consecutive data granules share one min/max summary. Required.

CREATE INDEX dsi_amount ON sales
  USING SKIP_INDEX(amount) WITH (TYPE = minmax, GRANULARITY = 1);

`INVERTED_INDEX`: exact value membership

Maps each value to the rows that contain it. Once the index exists, =, IN, and bounded range predicates over the column are matched automatically, with has_all_tokens available for ARRAY(TEXT) membership. For mechanism and tuning, see Inverted index.

CREATE INDEX [IF NOT EXISTS] <index_name>
  ON <table_name>
  USING INVERTED_INDEX(<expression>);

Parameter	Description
`<expression>`	A column or scalar expression returning `TEXT`, `ARRAY(TEXT)`, or a scalar type (`BOOLEAN`, `INTEGER`, `BIGINT`, `NUMERIC`, `DATE`, `TIMESTAMP`, `TIMESTAMPTZ`). For `ARRAY(TEXT)`, each element is indexed as a separate token.

CREATE INDEX idx_tags ON events USING INVERTED_INDEX(tags);

`FULL_TEXT`: substring search

Indexes overlapping n-grams to accelerate search, which is equivalent to LIKE '%pattern%'. For mechanism and tuning, see Full-text search index.

CREATE INDEX [IF NOT EXISTS] <index_name>
  ON <table_name>
  USING FULL_TEXT(<column>)
  WITH (ngram_size = <N>);

Parameter	Description
`<column>`	A `TEXT` column. Arbitrary expressions are not supported.
`ngram_size`	Positive integer n-gram length. The shortest pattern that can use the index. Typical values are 2 to 4. Required.

CREATE INDEX idx_title ON articles USING FULL_TEXT(title) WITH (ngram_size = 3);

`HNSW`: vector search

Builds an HNSW graph for approximate nearest-neighbor search, queried with the vector_search() TVF. The HNSW form has its own parameter set, documented on CREATE VECTOR SEARCH INDEX. For mechanism and tuning, see Vector search index.

CREATE INDEX <index_name> ON <table_name> USING HNSW (
  <column_name> <distance_metric>
) WITH (
  dimension = <dimension> [, m = 16] [, ef_construction = 128] [, quantization = 'bf16']
);

Index creation on populated tables

When you create an index on a table that already contains data, existing tablets carry no index data until they are rewritten. Run VACUUM with REINDEX=TRUE to backfill them. All subsequent inserts populate the index automatically.

VACUUM (REINDEX=TRUE) <table_name>;

Overview

Performance and Observability

Security

Self-Managed

Managed service

Guides

SQL reference

Release notes

API reference

Legal

Syntax

`SKIP_INDEX`: data skipping (minmax)

`INVERTED_INDEX`: exact value membership

`FULL_TEXT`: substring search

`HNSW`: vector search

Index creation on populated tables

See also

​Syntax

​SKIP_INDEX: data skipping (minmax)

​INVERTED_INDEX: exact value membership

​FULL_TEXT: substring search

​HNSW: vector search

​Index creation on populated tables

​See also

Syntax

`SKIP_INDEX`: data skipping (minmax)

`INVERTED_INDEX`: exact value membership

`FULL_TEXT`: substring search

`HNSW`: vector search

Index creation on populated tables

See also