Vector Indexes ​

The DataVec vector engine supports the IVFFlat, HNSW, DISKANN, HNSWPQ, and IVFPQ algorithms. These algorithms are implemented based on ASTORE storage in openGauss, and their index structures enable efficient retrieval of query results.

The syntax for creating vector indexes supports the clauses available in the standard syntax for creating indexes.

HNSW ​

CREATE INDEX [INDEX_NAME] 
ON [TABLE_NAME] 
USING hnsw (COLUMN_NAME [TYPE]_[DISTANCE_FUN]_ops) 
with (m=<M>, ef_construction=<EF_CONSTRUCTION>)
[TABLESPACE tablespace_name]
[COMMENT text]
[VISIBLE | INVISIBLE]
[WHERE predicate];

• INDEX_NAME - index name
• TABLE_NAME - table name
• COLUMN_NAME - name of the vector data column
• TABLESPACE - specifies the tablespace for the index. The usage is the same as that in CREATE INDEX.
• COMMENT text - specifies the comment for the index. The usage is the same as that in CREATE INDEX.
• VISIBLE | INVISIBLE - specifies whether the index is visible. The usage is the same as that in CREATE INDEX.
• WHERE predicate - creates a partial index. The usage is the same as that in CREATE INDEX.

HNSW Index Operators ​

The format of an HNSW index operator is [TYPE]_[DISTANCE_FUN]_ops:

• TYPE - vector type
  • vector
  • bit
  • sparsevec

HNSW supports the following vector dimensions.

• DISTANCE_FUN - distance function
  • l2
  • ip
  • cosine
  • l1
  • hamming
  • jaccard

vector Index Operators ​

bit Index Operators ​

sparsevec Index Operators ​

Index Options ​

• m - maximum number of connections per graph layer (16 by default). The value ranges from 2 to 100. The appropriate value depends on the dataset and application scenario.
• ef_construction - size of the dynamic candidate set used for graph construction (64 by default). The value ranges from 4 to 1,000 and must be greater than or equal to 2 * m. To ensure search quality, ef_construction should preferably be greater than ef_search.

Example 1: Create an HNSW index using L2 distance and set m = 16 and ef_construction = 64.

openGauss=# CREATE INDEX ON items USING hnsw (embedding vector_l2_ops) WITH (m = 16, ef_construction = 64);

Increasing ef_construction and m can improve recall but increases index build time and insertion time.

Query Options ​

• ef_search - size of the dynamic candidate set used during queries (40 by default). For details, see DataVec Vector Engine Parameters.

Example 2: Set ef_search to 100 for the current session and query the top 10 most similar vectors using L2 distance.

openGauss=# SET hnsw_ef_search = 100;
openGauss=# SELECT id, embedding <-> '[1,2,3,4,5]'::vector AS distance FROM items ORDER BY distance LIMIT 10;

Parallelism Options ​

• parallel_workers - index build parallelism (0 by default). The value ranges from 1 to 32.

Example 3: Set the index build parallelism to 32.

openGauss=# ALTER TABLE items SET(parallel_workers=32);

IVFFlat ​

CREATE INDEX [INDEX_NAME]
ON [TABLE_NAME]
USING ivfflat (COLUMN_NAME [TYPE]_[DISTANCE_FUN]_ops)
WITH (lists = <LISTS>)
[TABLESPACE tablespace_name]
[COMMENT text]
[VISIBLE | INVISIBLE]
[WHERE predicate];

• INDEX_NAME - index name
• TABLE_NAME - table name
• COLUMN_NAME - name of the vector data column
• TABLESPACE - specifies the tablespace for the index. The usage is the same as that in CREATE INDEX.
• COMMENT text - specifies the comment for the index. The usage is the same as that in CREATE INDEX.
• VISIBLE | INVISIBLE - specifies whether the index is visible. The usage is the same as that in CREATE INDEX.
• WHERE predicate - creates a partial index. The usage is the same as that in CREATE INDEX.

IVFFlat Index Operators ​

The format of an IVFFlat index operator is [TYPE]_[DISTANCE_FUN]_ops:

• TYPE - vector type
  • vector
  • bit

IVFFlat supports the following vector dimensions.

• DISTANCE_FUN - distance function
  • l2
  • ip
  • cosine
  • hamming

vector Index Operators ​

bit Index Operators ​

sparsevec does not support IVFFlat indexes.

Index Options ​

• lists - number of cluster centers in the inverted lists (cells) (100 by default). The value ranges from 1 to 32,768.

Example 4: Create an IVFFlat index using L2 distance and set lists = 200.

openGauss=# CREATE INDEX ON items USING ivfflat (embedding vector_l2_ops) WITH (lists = 200);

Query Options ​

• probe - size of the candidate set used during queries (1 by default). For details, see DataVec Vector Engine Parameters.

Example 5: Set probes to 10 and query the top 10 most similar vectors using L2 distance.

openGauss=# SET ivfflat_probes = 10;
openGauss=# SELECT id, embedding <-> '[1,2,3,4,5]'::vector AS distance FROM items ORDER BY distance LIMIT 10;

Parallelism Options ​

• parallel_workers - index build parallelism (0 by default). The value ranges from 1 to 32.

Example 6: Set the index build parallelism to 32.

openGauss=# ALTER TABLE items SET(parallel_workers=32);

DISKANN ​

CREATE INDEX [INDEX_NAME]
ON [TABLE_NAME]
USING diskann (COLUMN_NAME [TYPE]_[DISTANCE_FUN]_ops)
WITH (index_size = <INDEX_SIZE>)
[TABLESPACE tablespace_name]
[COMMENT text]
[VISIBLE | INVISIBLE]
[WHERE predicate];

• INDEX_NAME - index name
• TABLE_NAME - table name
• COLUMN_NAME - name of the vector data column
• TABLESPACE - specifies the tablespace for the index. The usage is the same as that in CREATE INDEX.
• COMMENT text - specifies the comment for the index. The usage is the same as that in CREATE INDEX.
• VISIBLE | INVISIBLE - specifies whether the index is visible. The usage is the same as that in CREATE INDEX.
• WHERE predicate - creates a partial index. The usage is the same as that in CREATE INDEX.

DISKANN Index Operators ​

The format of a DISKANN index operator is [TYPE]_[DISTANCE_FUN]_ops:

• TYPE - vector type
  • vector

DISKANN supports the following vector dimensions.

• DISTANCE_FUN - distance function
  • l2
  • ip
  • cosine

vector Index Operators ​

Index Options ​

• index_size - index build parameter that affects recall and build time. The value ranges from 16 to 1,000 (100 by default). A value of 50 is recommended for datasets with millions of rows.
• enable_pq - quantization and compression parameter that controls whether PQ is enabled. Disabled by default.
• pq_m - quantization and compression parameter. The value ranges from 1 to 2,000 (8 by default). dim / 8 is recommended.

Example 7: Create a DISKANN index using L2 distance and set index_size = 50.

openGauss=# CREATE INDEX ON items USING diskann (embedding vector_l2_ops) WITH (index_size = 50);

Query Options ​

• diskann_probes - size of the candidate set used during queries (128 by default). For details, see DataVec Vector Engine Parameters.

Example 8: Set diskann_probes to 256 and query the top 10 most similar vectors using L2 distance.

openGauss=# SET diskann_probes = 256;
openGauss=# SELECT id, embedding <-> '[1,2,3,4,5]'::vector AS distance FROM items ORDER BY distance LIMIT 10;

Parallelism Options ​

• parallel_workers - index build parallelism (0 by default). The value ranges from 1 to 32.

Example 9: Set the index build parallelism to 32.

openGauss=# ALTER TABLE items SET(parallel_workers=32);

Non-Vector Indexes: B-tree/Ubtree ​

Vector data types also support B-tree and Ubtree index creation. The index type is automatically selected based on the storage type of the table.

CREATE INDEX [INDEX_NAME]
ON [TABLE_NAME]
(COLUMN_NAME [TYPE]_ops);

B-tree/Ubtree Index Operators ​

• vector_ops
• bit_ops
• sparsevec_ops

Example 10: Create a B-tree index.

openGauss=# CREATE INDEX ON t (val vector_ops);

Parallel Vector Index Building ​

Enable parallel index building to accelerate vector index creation:

ALTER TABLE [TABLE_NAME]
SET (parallel_workers = <CONCURRENCY_NUM>);

Example 11: Set the index build parallelism to 8.

openGauss=# ALTER TABLE items SET (parallel_workers = 8);

Modifying Vector Indexes ​

Only vector index options can be modified.

ALTER INDEX [INDEX_NAME]
SET (parameter=<OPTIONS>);

Example 12: Modify vector index options.

openGauss=# ALTER INDEX t_val_idx SET (m=24, ef_construction=200);
openGauss=# REINDEX INDEX t_val_idx;

Note that if an error such as ERROR: missing chunk number xx for toast value xx in pg_toast_xx occurs when rebuilding an index after data has been inserted, deleted, or updated, the database may not have cleaned up the dead tuples generated by the update or delete operations in time. As a result, REINDEX may attempt to access TOAST chunks that have already been reclaimed. In this case, manually run VACUUM before using REINDEX to rebuild the index.

Constraints ​

• Vector indexes are supported only on ordinary row-store tables, temporary tables, TOAST tables, unlogged tables, and segment-page tables. Partitioned tables are not currently supported. For other table types, only B-tree and Ubtree indexes can be created on vector data.
• If REINDEX is not executed after ALTER INDEX, the index retains its original option configuration, and subsequently inserted data is indexed according to the original index options. CREATE INDEX and REINDEX do not support online concurrent index building with CONCURRENTLY.
• IVFFlat, HNSW, and DISKANN indexes do not support USTORE.
• When creating a table with vector columns, the INDEX clause can be used to create the default B-tree or Ubtree index, but vector indexes cannot be specified.
• Vector indexes cannot be created when the dimension of the vector column is not specified. Only B-tree and Ubtree indexes can be created in this case.
• When using vector indexes in a B-compatible database, execute set dolphin.nulls_minimal_policy=false to disable the null handling policy.
• Vector indexes are not supported when extreme RTO mode is enabled in a primary/standby environment.