AnalyticDB for PostgreSQL:Create a vector index

Background information

AnalyticDB for PostgreSQL vector databases use the FastANN vector search engine to implement the mainstream Hierarchical Navigable Small World (HNSW) algorithm. The FastANN vector search engine uses the segmented paging storage of PostgreSQL and stores only the vector column pointers in indexes to significantly reduce the storage that is occupied by vector indexes. The vector search engine uses the product quantization (PQ) feature to reduce the dimensionality of high-dimensional vectors. This way, low-dimensional vectors are stored in indexes to reduce index lookups when you insert or query vectors and improve vector search performance.

Syntax

CREATE INDEX [INDEX_NAME]
ON [SCHEMA_NAME].[TABLE_NAME]   
USING ANN(COLUMN_NAME) 
WITH (DIM=<DIMENSION>,
      DISTANCEMEASURE=<MEASURE>,
      HNSW_M=<M>,
      HNSW_EF_CONSTRUCTION=<EF_CONSTURCTION>,
      PQ_ENABLE=<PQ_ENABLE>,
      PQ_SEGMENTS=<PQ_SEGMENTS>,
      PQ_CENTERS=<PQ_CENTERS>,
      EXTERNAL_STORAGE=<EXTERNAL_STORAGE>);

Parameters:

• INDEX_NAME: the name of the index.

• SCHEMA_NAME: the name of the schema or namespace.

• TABLE_NAME: the name of the table.

• COLUMN_NAME: the name of the vector index column.

• The following table describes other vector index parameters.

  Vector index parameter	Required	Description	Default value	Valid value
  DIM	Yes	The number of vector dimensions. This parameter is used to check vectors when vectors are inserted. If the number of vector dimensions does not match, the system displays an error message.	0	[1, 8192]
  DISTANCEMEASURE	No	The distance or similarity measurement. Valid values: L2: uses the squared Euclidean distance function to create indexes. This measurement is suitable for image similarity search scenarios. Formula: IP: uses the negative inner product distance function to create indexes. This measurement is used to replace cosine similarity after vector normalization. Formula: COSINE: uses the cosine distance function to create indexes. This measurement is suitable for text similarity search scenarios. Formula: Note If you set the DISTANCEMEASURE parameter to IP or COSINE, make sure that the minor version of the AnalyticDB for PostgreSQL instance is 6.3.10.18 or later. For information about how to view and update the minor version, see Update the minor engine version. To achieve the best performance, we recommend that you store the normalized vector data in databases and use the inner product distance measurement to create indexes. When you use the inner product distance measure to create indexes, you can also query the Euclidean distance and cosine similarity. For more information, see Perform vector search. For information about user-defined functions (UDFs) for vector search, see the "References" section of this topic.	L2	(L2, IP, COSINE)
  HNSW_M	No	The maximum number of neighbors for the HNSW algorithm.	50	[1, 1000]
  HNSW_EF_CONSTRUCTION	No	The size of the candidate set when the HNSW algorithm is used to create indexes.	600	[1, 4000]
  PQ_ENABLE	No	Specifies whether to enable the PQ feature for dimensionality reduction. The PQ feature uses existing vector sample data for training. If the number of rows that are occupied by data is less than 500,000, we recommend that you do not specify this parameter.	0	[0, 1]
  PQ_SEGMENTS	No	The number of segments that are used in the K-means clustering algorithm after you enable the PQ feature. This parameter takes effect only if you set the PQ_ENABLE parameter to 1. If the DIM parameter divided by eight is an integer, you do not need to specify this parameter. Otherwise, you must specify this parameter. The value of the PQ_SEGMENTS parameter must be greater than 0. The DIM parameter divided by the PQ_SEGMENTS parameter must be an integer.	0	[1, 256]
  PQ_CENTERS	No	The number of centroids that are used in the K-means clustering algorithm after you enable the PQ feature. This parameter takes effect only if you set the PQ_ENABLE parameter to 1.	2048	[256, 8192]
  EXTERNAL_STORAGE	No	Specifies whether to use the memory mapping technology to create HNSW indexes. Valid values: 0: uses segmented paging storage to create indexes. This method uses the shared buffer of PostgreSQL for caching and supports the delete and update operations. 1: uses the memory mapping technology to create indexes. This method does not support the delete or update operation.	0	[0, 1]

Example

In this example, a text knowledge base is used. An article in the knowledge base is split into chunks, converted into embedding vectors, and then stored in a database. The following table describes the fields that are contained in a table named chunks.

1. Create a table to store vectors.

   CREATE TABLE chunks (
       id SERIAL PRIMARY KEY,
       chunk VARCHAR(1024),
       intime TIMESTAMP,
       url VARCHAR(1024),
       feature REAL[]
   ) DISTRIBUTED BY (id);

2. Set the storage mode of the vector column to PLAIN to reduce data scanning costs and achieve higher performance.

   ALTER TABLE chunks ALTER COLUMN feature SET STORAGE PLAIN;

3. Create a vector index for the vector column.

   -- Create a vector index based on the Euclidean distance. 
   CREATE INDEX idx_chunks_feature_l2 ON chunks USING ann(feature) WITH (dim=1536, distancemeasure=l2, hnsw_m=64, pq_enable=1);
   -- Create a vector index based on the inner product distance. 
   CREATE INDEX idx_chunks_feature_ip ON chunks USING ann(feature) WITH (dim=1536, distancemeasure=ip, hnsw_m=64, pq_enable=1);
   -- Create a vector index based on the cosine similarity. 
   CREATE INDEX idx_chunks_feature_cosine ON chunks USING ann(feature) WITH (dim=1536, distancemeasure=cosine, hnsw_m=64, pq_enable=1);

   Note

   • You can create multiple vector columns in each vector table and multiple vector indexes on each vector column. To avoid invalid indexes, you must create vector indexes based on your business requirements.

   • The vector search methods must match the vector indexes that are created by using the corresponding distance measurements. For example, the <-> operator works with the vector index created by using the Euclidean distance, the <#> operator works with the vector index created by using the inner product distance, and the <=> operator works with the vector index created by using the cosine similarity. If the operators do not work with the corresponding vector indexes, the vector search method is downgraded to exact search (brute-force search).

   • Use the ANN access method that is provided by the FastANN vector search extension. If you enable the vector search engine optimization feature for an AnalyticDB for PostgreSQL instance, the FastANN vector search extension is automatically created in the AnalyticDB for PostgreSQL instance.

   • If the You must specify an operator class or define a default operator class for the data type error message is displayed, the vector search engine optimization feature is not enabled for the AnalyticDB for PostgreSQL instance. In this case, enable the feature and try again. For more information, see Enable or disable vector search engine optimization.

4. Create an index for structured data columns to accelerate hybrid search.

   CREATE INDEX ON chunks(intime);

Index creation modes

You can create vector indexes in one of the following modes:

• Streaming mode

  Create an empty table and then create a vector index for the table. This way, the streaming index is created in real time when you import vector data. This mode is suitable for real-time vector search scenarios. However, you cannot quickly import vector data in this mode.

• Asynchronous mode

  Create an empty table, import vector data to the table, and then create a vector index for the imported vector data. This mode is suitable for scenarios in which you want to import large amounts of vector data.

For example, you can create an index in asynchronous mode by using Data Management (DMS). Sample code:

-- For example, an AnalyticDB for PostgreSQL instance whose compute node specifications are 8 cores and 32 GB memory is used. 
-- To prevent timeout errors, set the statement_timeout and idle_in_transaction_session_timeout parameters to 0. 
-- Set the fastann.build_parallel_processes parameter to 8 to ensure that the CPU resources of compute nodes are fully utilized. The value of the fastann.build_parallel_processes parameter must be the same as the number of CPU cores in the compute node specifications. 
-- In DMS, you must specify values for parameters in the same line as the index creation statement. 
SET statement_timeout = 0;SET idle_in_transaction_session_timeout = 0;SET fastann.build_parallel_processes = 8;CREATE INDEX ON chunks USING ann(feature) 
WITH (dim=1536, distancemeasure=cosine, hnsw_m=64, pq_enable=1);

You can create an index in asynchronous mode by using the psql tool. Sample code:

-- For example, an AnalyticDB for PostgreSQL instance whose compute node specifications are 8 cores and 32 GB memory is used. 

-- Step 1: Store the following content in the create_index.sql file: 
SET statement_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET fastann.build_parallel_processes = 8;
CREATE INDEX ON chunks USING ann(feature) WITH (dim=1536, distancemeasure=cosine, hnsw_m=64, pq_enable=1);

-- Step 2: Run the following command to create an index in parallel: 
psql -h gp-xxxx-master.gpdb.rds.aliyuncs.com -p 5432 -d dbname -U username -f create_index.sql

References

-- Euclidean distance
SELECT l2_distance(array[1,1,1,1]::real[], array[2,2,2,2]::real[]);
-- Inner product distance
SELECT inner_product_distance(array[1,1,1,1]::real[], array[2,2,2,2]::real[]);
-- Cosine similarity
SELECT cosine_similarity(array[1,1,1,1]::real[], array[2,2,2,2]::real[]);
-- Dot product distance
SELECT dp_distance(array[1,1,1,1]::real[], array[2,2,2,2]::real[]);
-- Hamming distance
SELECT hm_distance(array[1,0,1,0]::int[], array[0,1,0,1]::int[]);
-- Sum of two vector arrays
SELECT vector_add(array[1,1,1,1]::real[], array[2,2,2,2]::real[]);
-- Difference between two vector arrays
SELECT vector_sub(array[1,1,1,1]::real[], array[2,2,2,2]::real[]);
-- Product of two vector arrays
SELECT vector_mul(array[1,1,1,1]::real[], array[2,2,2,2]::real[]);
-- Quotient of two vector arrays
SELECT vector_div(array[1,1,1,1]::real[], array[2,2,2,2]::real[]);
-- Sum of all elements in a vector array
SELECT vector_sum(array[1,1,1,1]::real[]);
-- Smallest value among all elements in a vector array
SELECT vector_min(array[1,1,1,1]::real[]);
-- Largest value among all elements in a vector array
SELECT vector_max(array[1,1,1,1]::real[]);
-- Average value of all elements in a vector array
SELECT vector_avg(array[1,1,1,1]::real[]);
-- Norm of a vector array
SELECT vector_norm(array[1,1,1,1]::real[]);
-- Angle between two vector arrays
SELECT vector_angle(array[1,1,1,1]::real[], array[2,2,2,2]::real[]);

-- Euclidean distance
SELECT array[1,1,1,1]::real[] <-> array[2,2,2,2]::real[] AS score;
-- Inner product distance
SELECT array[1,1,1,1]::real[] <#> array[2,2,2,2]::real[] AS score;
-- Cosine distance
SELECT array[1,1,1,1]::real[] <=> array[2,2,2,2]::real[] AS score;

-- Sum of two vectors
SELECT array[1,1,1,1]::real[] + array[2,2,2,2]::real[] AS value;
-- Difference between two vectors
SELECT array[1,1,1,1]::real[] - array[2,2,2,2]::real[] AS value;
-- Product of two vectors
SELECT array[1,1,1,1]::real[] * array[2,2,2,2]::real[] AS value;
-- Quotient of two vectors
SELECT array[1,1,1,1]::real[] / array[2,2,2,2]::real[] AS value;