AnalyticDB:Use pgvector-compatible vector search

Prerequisites

• The minor version of an AnalyticDB for PostgreSQL V6.0 instance is 6.6.1.0 or later. For information about how to view the minor version of an AnalyticDB for PostgreSQL instance, see View the minor version of an instance.

• The vector search engine optimization feature is enabled for the AnalyticDB for PostgreSQL V6.0 instance. For more information, see Enable or disable vector search engine optimization.

Advantages of pgvector-compatible vector search

AnalyticDB for PostgreSQL vector databases are fully compatible with the read and write operations of vector tables that use the pgvector extension to perform vector search. You need to only modify the SQL syntax for creating indexes. If your business uses the pgvector extension, you can apply pgvector-compatible vector search to your business after slightly modifying business code. Pgvector-compatible vector search provides the following advantages:

• Pgvector-compatible vector search can be used after you enable the vector search engine optimization feature, without the need to install the pgvector extension.

• Pgvector-compatible vector search is fully compatible with the SQL syntax of the pgvector extension and can reuse pgvector-supported language clients.

• The pgvector extension is suitable for standalone PostgreSQL databases, whereas pgvector-compatible vector search uses a distributed architecture to process more vector data.

• Pgvector-compatible vector search uses the in-house FastANN vector search engine to provide better performance than the pgvector extension.

• Pgvector-compatible vector search uses query optimizers and executors to generate and execute hybrid execution plans with powerful hybrid search capabilities, whereas the pgvector extension can use only partitions to perform simple search.

• Pgvector-compatible vector search uses the FLOAT2 type to compress vector tables and provides the product quantization (PQ) feature to compress vector indexes, thus reducing storage costs.

Create a vector table

You can create a pgvector-compatible vector table by using the same syntax for creating ordinary vector tables in AnalyticDB for PostgreSQL. You need to only change the vector columns from the array type, such as SMALLINT[], FLOAT2[], or FLOAT4[], to the VECTOR type that is supported by the pgvector extension. Each pgvector-compatible vector table supports multiple vector columns.

CREATE TABLE [TABLE_NAME]
(  
    C1 DATATYPE,  
    C2 DATATYPE,  
    ......,  
    CN VECTOR(DIM), 
    PRIMARY KEY(C1 [,C2,...CN])
) DISTRIBUTED BY (C1);

CREATE TABLE FACE_TABLE (  
    C1 INT,  
    C2 VECTOR(512) NOT NULL, 
    C3 VECTOR(1536) NOT NULL, 
    C4 TIMESTAMP NOT NULL,  
    C5 VARCHAR(20) NOT NULL,  
    PRIMARY KEY (C1)
) DISTRIBUTED BY (C1);

Create a vector index

You can create a vector index for a pgvector-compatible vector table in AnalyticDB for PostgreSQL by using the syntax of the FastANN vector search engine, which is different from the syntax of the pgvector extension.

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>;

-- Create three types of vector indexes on the C2 column. 
CREATE INDEX idx_c2_l2 ON FACE_TABLE USING ann(C2) WITH (distancemeasure=l2, hnsw_m=64, pq_enable=1);
CREATE INDEX idx_c2_ip ON FACE_TABLE USING ann(C2) WITH (distancemeasure=ip, hnsw_m=64, pq_enable=1);
CREATE INDEX idx_c2_cosine ON FACE_TABLE USING ann(C2) WITH (distancemeasure=cosine, hnsw_m=64, pq_enable=1);
-- Create three types of vector indexes on the C3 column. 
CREATE INDEX idx_c3_l2 ON FACE_TABLE USING ann(C3) WITH (distancemeasure=l2, hnsw_m=64, pq_enable=1);
CREATE INDEX idx_c3_ip ON FACE_TABLE USING ann(C3) WITH (distancemeasure=ip, hnsw_m=64, pq_enable=1);
CREATE INDEX idx_c3_cosine ON FACE_TABLE USING ann(C3) WITH (distancemeasure=cosine, hnsw_m=64, pq_enable=1);

Import vector data

You can import vector data to a pgvector-compatible vector table by using the same syntax of the pgvector extension. For example, execute the following INSERT statement to insert data into the FACE_TABLE table:

INSERT INTO FACE_TABLE 
values (1, '[1,2,3 ... 512]', '[1,2,3 ... 1536]', '2023-12-29 00:00:00', 'aaa.bbb.ccc/face1.jpg');

Vector search

You can perform vector search on a pgvector-compatible vector table by using the same syntax of the pgvector extension.

The engine parameters related to the recall rate of vector search are the same as the parameters of AnalyticDB for PostgreSQL vector databases. For more information, see Perform vector search. For example, execute the following statements to perform vector search on the FACE_TABLE table:

-- Perform exact search based on the Euclidean distance. 
SELECT C1 FROM FACE_TABLE ORDER BY vector_l2_squared_distance(C2, '[1,2,3 ... 512]') LIMIT 10;
-- Perform exact search based on the inner product distance. 
SELECT C1 FROM FACE_TABLE ORDER BY vector_negative_inner_product(C2, '[1,2,3 ... 512]') LIMIT 10;
-- Perform exact search based on the cosine similarity. 
SELECT C1 FROM FACE_TABLE ORDER BY cosine_distance(C2, '[1,2,3 ... 512]') LIMIT 10;

-- Perform approximate index search based on the Euclidean distance. 
SELECT C1 FROM FACE_TABLE ORDER BY C2 <-> '[1,2,3 ... 512]' LIMIT 10;
-- Perform approximate index search based on the inner product distance. 
SELECT C1 FROM FACE_TABLE ORDER BY C2 <#> '[1,2,3 ... 512]' LIMIT 10;
-- Perform approximate index search based on the cosine similarity. 
SELECT C1 FROM FACE_TABLE ORDER BY C2 <=> '[1,2,3 ... 512]' LIMIT 10;

You created vector indexes based on the Euclidean distance, inner product distance, and cosine similarity on the C2 column of the FACE_TABLE table. You can use the approximate index search method to match the vector indexes. When you use the approximate index search method, make sure that the <->, <#>, and <=> operators work with the vector indexes that are created by using the corresponding distance metric. If the operators do not work with the vector indexes, the approximate index search is downgraded to exact search.

Hybrid search

You can perform hybrid search on a pgvector-compatible vector table by using the same syntax for performing hybrid search on ordinary vector tables in AnalyticDB for PostgreSQL. For more information, see Use hybrid search. For example, execute the following statement to perform hybrid search on the FACE_TABLE table:

SELECT C1 FROM FACE_TABLE WHERE
  C4 > '2023-10-01 00:00:00' AND C4 < '2024-01-01 00:00:00'
ORDER BY 
  C2 <-> '[1,2,3 ... 512]'
LIMIT 10;

Adaptation of the open source code repository

This section describes how to adapt the business application code that uses the pgvector extension to AnalyticDB for PostgreSQL vector databases by performing a simple modification. In this example, an open source large language model (LLM) application development platform named Dify is used. The adaptation involves the following modifications:

• Comment out the installation of the pgvector extension. AnalyticDB for PostgreSQL vector databases are compatible with the read and write operations of vector tables that use the pgvector extension, but do not use the pgvector extension. You do not need to install the pgvector extension on AnalyticDB for PostgreSQL vector databases. When you create AnalyticDB for PostgreSQL vector databases, the FastANN vector search engine is automatically installed. You need to only comment out the installation of the pgvector extension. Sample code:

  # The code is stored in the api/core/rag/datasource/vdb/pgvector/pgvector.py path.
  
  def _create_collection(self, dimension: int):
      cache_key = f"vector_indexing_{self._collection_name}"
      lock_name = f"{cache_key}_lock"
      WITH redis_client.lock(lock_name, timeout=20):
          collection_exist_cache_key = f"vector_indexing_{self._collection_name}"
          if redis_client.get(collection_exist_cache_key):
              RETURN
  
          WITH self._get_cursor() AS cur:
              # Comment out the installation of the pgvector extension.
              #cur.execute("CREATE EXTENSION IF NOT EXISTS vector")
              cur.execute(SQL_CREATE_TABLE.format(table_name=self.table_name, dimension=dimension))
              # TODO: CREATE index https://github.com/pgvector/pgvector?tab=readme-ov-file#indexing
          redis_client.set(collection_exist_cache_key, 1, ex=3600)
      

• Modify the method that is used to create vector tables. AnalyticDB for PostgreSQL vector databases are built based on PostgreSQL 9.4 and do not support the USING heap syntax when tables are created. AnalyticDB for PostgreSQL uses a distributed database structure and requires distribution keys. Sample code:

  # The code is stored in the api/core/rag/datasource/vdb/pgvector/pgvector.py path.
  
  SQL_CREATE_TABLE = """
  CREATE TABLE IF NOT EXISTS {table_name} (
      id uuid PRIMARY KEY,
      text text NOT NULL,
      meta jsonb NOT NULL,
      embedding vector({dimension}) NOT NULL
  ) USING heap; 
  """
  
  # Change the preceding table creation statement to the following statement:
  
  SQL_CREATE_TABLE = """
  CREATE TABLE IF NOT EXISTS {table_name} (
      id uuid PRIMARY KEY,
      text text NOT NULL,
      meta jsonb NOT NULL,
      embedding vector({dimension}) NOT NULL
  ) DISTRIBUTED BY (id); 
  """

• Modify the method that is used to create vector indexes. AnalyticDB for PostgreSQL vector databases use the FastANN vector search engine to implement vector search, but do not use the pgvector extension. The syntax that is used to create vector indexes in AnalyticDB for PostgreSQL, especially the index keyword, is different from the syntax that is used in the pgvector extension. You must use the syntax of AnalyticDB for PostgreSQL vector databases to create indexes. Sample code:

  # The code is stored in the api/core/rag/datasource/vdb/pgvector/pgvector.py path.
  
  # Add an SQL statement for creating a vector index. You can also create an index in the database.
  
  SQL_CREATE_INDEX = """
  CREATE INDEX ON  {table_name} USING ann(embedding) WITH (HNSW_M=16, HNSW_EF_CONSTRUCTION=500, PQ_ENABLE=1);
  """
  
  def _create_collection(self, dimension: int):
      cache_key = f"vector_indexing_{self._collection_name}"
      lock_name = f"{cache_key}_lock"
      WITH redis_client.lock(lock_name, timeout=20):
          collection_exist_cache_key = f"vector_indexing_{self._collection_name}"
          if redis_client.get(collection_exist_cache_key):
              RETURN
  
          WITH self._get_cursor() AS cur:
              # Comment out the installation of the pgvector extension.
              #cur.execute("CREATE EXTENSION IF NOT EXISTS vector")
              cur.execute(SQL_CREATE_TABLE.format(table_name=self.table_name, dimension=dimension))
              # TODO: CREATE index https://github.com/pgvector/pgvector?tab=readme-ov-file#indexing
              # Create a vector index for an AnalyticDB for PostgreSQL vector database.
              cur.execute(SQL_CREATE_INDEX.format(table_name=self.table_name))
          redis_client.set(collection_exist_cache_key, 1, ex=3600)

Appendixes

-- Euclidean distance
SELECT l2_distance('[1,1,1,1]'::vector, '[2,2,2,2]'::vector);
-- Inner product distance
SELECT inner_product('[1,1,1,1]'::vector, '[2,2,2,2]'::vector);
-- Cosine similarity
SELECT cosine_similarity('[1,1,1,1]'::vector, '[2,2,2,2]'::vector);
-- Vector dimensions
SELECT vector_dims('[1,1,1,1]'::vector);
-- Vector norm
SELECT vector_norm('[1,1,1,1]'::vector);
-- Vector sum
SELECT vector_add('[1,1,1,1]'::vector, '[2,2,2,2]'::vector);
-- Vector subtraction
SELECT vector_sub('[1,1,1,1]'::vector, '[2,2,2,2]'::vector);
-- Vector multiplication
SELECT vector_mul('[1,1,1,1]'::vector, '[2,2,2,2]'::vector);
-- Vector angle
SELECT vector_angle('[1,1,1,1]'::vector, '[2,2,2,2]'::vector);
-- Squared Euclidean distance
SELECT vector_l2_squared_distance('[1,1,1,1]'::vector, '[2,2,2,2]'::vector);
-- Negative inner product distance
SELECT vector_negative_inner_product('[1,1,1,1]'::vector, '[2,2,2,2]'::vector);
-- Cosine distance
SELECT cosine_distance('[1,1,1,1]'::vector, '[2,2,2,2]'::vector);

-- Squared Euclidean distance
SELECT '[1,1,1,1]'::vector <-> '[2,2,2,2]'::vector AS score;
 
-- Negative inner product distance
SELECT '[1,1,1,1]'::vector <#> '[2,2,2,2]'::vector AS score;
 
-- Cosine distance
SELECT '[1,1,1,1]'::vector <=> '[2,2,2,2]'::vector AS score;

-- Sum
SELECT '[1,1,1,1]'::vector + '[2,2,2,2]'::vector AS value;

-- Subtraction
SELECT '[1,1,1,1]'::vector - '[2,2,2,2]'::vector AS value;

-- Multiplication
SELECT '[1,1,1,1]'::vector * '[2,2,2,2]'::vector AS value;