Use pgvector-compatible vector search - AnalyticDB - Alibaba Cloud Documentation Center

Pgvector is an open source PostgreSQL extension for vector similarity search. You can use the pgvector extension to create vector indexes and perform similarity calculation and queries between vectors in scenarios such as vector similarity search, recommendation systems, and clustering analysis.

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.

Syntax

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

The CN parameter specifies the vector columns, which are of the VECTOR type. The DIM parameter specifies the vector dimension.

Example

Create a vector table named FACE_TABLE. Specify C1 as the primary key, and C2 and C3 as the vector columns.

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.

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

The VECTOR type that is supported by the pgvector extension already contains the dimension information. When you create a vector index, you do not need to specify the DIM parameter. For information about other parameters, see Create a vector index.

Example

Create vector indexes for the FACE_TABLE vector table.

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

In this example, multiple vector indexes are created on multiple vector columns. To prevent invalid indexes, you must create vector indexes based on your business requirements.

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.

SQL optimization for vector search

If you want to return the score value of the vector distance, you can execute the following statements to improve performance. To reduce the amount of time required for calculation, use the sort result of the vector index to calculate the final score value of the vector distance. Sample statements:

-- Search vector data based on the Euclidean distance. 
SELECT t.C1 as C1, sqrt(t.score) as score 
FROM 
  (SELECT C1, C2 <-> '[1,2,3 ... 512]' as score
   FROM FACE_TABLE
   ORDER BY score LIMIT topk) t;

-- Search vector data based on the inner product distance. 
SELECT t.C1 as C1, (-1 * t.score) as score
FROM
  (SELECT C1, C2 <#> '[1,2,3 ... 512]' as score
   FROM FACE_TABLE
   ORDER BY score LIMIT topk) t;

-- Search vector data based on the cosine similarity. 
SELECT t.C1 as C1, (1.0 - t.score) as score
FROM 
  (SELECT C1, C2 <=> '[1,2,3 ... 512]' as score
   FROM FACE_TABLE
   ORDER BY score LIMIT topk) t;

In the vector search results, the score value indicates the squared Euclidean distance, inner product distance, or cosine similarity.

If you want to return results that are filtered based on a score range, you can execute the following statements to improve performance. To reduce the amount of time required for calculation, use the sort result of the vector index to calculate the final score value of the vector distance. Sample statements:

-- Search vector data based on the Euclidean distance. 
SELECT t.C1 as C1, sqrt(t.score) as score 
FROM 
  (SELECT C1, C2 <-> '[1,2,3 ... 512]' as score
   FROM FACE_TABLE
   ORDER BY score LIMIT topk) t
WHERE score < 100;

-- Search vector data based on the inner product distance. 
SELECT t.C1 as C1, (-1 * t.score) as score
FROM
  (SELECT C1, C2 <#> '[1,2,3 ... 512]' as score
   FROM FACE_TABLE
   ORDER BY score LIMIT topk) t
WHERE score > 10;

-- Search vector data based on the cosine similarity. 
SELECT t.C1 as id, (1.0 - t.score) as score
FROM 
  (SELECT C1, C2 <=> '[1,2,3 ... 512]' as score
   FROM FACE_TABLE
   ORDER BY score LIMIT topk) t
WHERE score > 0.5;

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

Language clients that are supported by the pgvector extension

Programming language	URL of the client library code

Programming language	URL of the client library code
C	pgvector-C
C++	pgvector-cpp
Go	pgvector-go
Java, Kotlin, Groovy, and Scala	pgvector-java
PHP	pgvector-php
Python	pgvector-python
Rust	pgvector-rust

You can use multiple language clients that are supported by the pgvector extension to access AnalyticDB for PostgreSQL vector databases. You need to only modify the SQL statements related to vector indexes.

Supported vector functions

Purpose	Vector function	Data type of the return value	Description	Supported data type

Purpose	Vector function	Data type of the return value	Description	Supported data type
Calculation	l2_distance	DOUBLE PRECISION	Calculates the rooted Euclidean distance between two vectors. This function is used to measure the distance between two vectors.	VECTOR
	inner_product	DOUBLE PRECISION	Calculates the inner product distance between two vectors, which is equivalent to cosine similarity after vector normalization. This function is used to replace cosine similarity after vector normalization.	VECTOR
	cosine_similarity	DOUBLE PRECISION	Calculates the cosine similarity between two vectors. This function is used to measure the similarity between two vectors based on the direction regardless of the actual length of the vectors. Valid values of the cosine similarity: -1 to 1.	VECTOR
	vector_dims	INTEGER	Calculates the dimensions of a vector.	VECTOR
	vector_norm	DOUBLE PRECISION	Calculates the norm of a vector.	VECTOR
	vector_add	VECTOR	Calculates the sum of two vectors.	VECTOR
	vector_sub	VECTOR	Calculates the difference between two vectors.	VECTOR
	vector_mul	VECTOR	Calculates the product of two vectors.	VECTOR
	vector_angle	DOUBLE PRECISION	Calculates the angle between two vectors.	VECTOR
Sorting	vector_l2_squared_distance	DOUBLE PRECISION	Sorts vectors based on the squared Euclidean distance. This function requires fewer calculation than the sorting method based on the rooted Euclidean distance.	VECTOR
	vector_negative_inner_product	DOUBLE PRECISION	Sorts vectors based on the negative inner product distance. This function is used to sort vectors based on the inner product distance in descending order.	VECTOR
	cosine_distance	DOUBLE PRECISION	Sorts vectors based on the cosine distance. This function is used to sort vectors based on the cosine similarity in descending order. Valid values of the cosine distance: 0 to 2.	VECTOR

For information about the vector distance formulas, see Create a vector index.

Examples of vector functions:

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

Supported vector operators

Operator	Calculation	Sorting	Supported data type

Operator	Calculation	Sorting	Supported data type
<->	Calculates the squared Euclidean distance between two vectors. The result is equivalent to the result of the l2_squared_distance function.	Sorts vectors based on the squared Euclidean distance in ascending order.	VECTOR
<#>	Calculates the negative inner product distance between two vectors. The result is equivalent to the result of the negative_inner_product_distance function.	Sorts vectors based on the dot product distance in descending order.	VECTOR
<=>	Calculates the cosine distance between two vectors. The result is equivalent to the result of the cosine_distance function.	Sorts vectors based on the cosine similarity in descending order.	VECTOR
+	Calculates the sum of two vectors.	None	VECTOR
-	Calculates the difference between two vectors.	None	VECTOR
*	Calculates the product of two vectors.	None	VECTOR

Examples of vector operators:

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

About Alibaba Cloud

Our Global Network

Quick Start

Global Offices

Olympic Games Paris 2024 New

Stade Roland Garros – Glitz from the Past New

Place de la Concorde – “Breaking” the Barriers New

Vaires-sur-Marne Nautical Stadium – Sports with Sustainability New

International Broadcast Center – Images, Sounds, and Data that Captivate Billions New

Customer Success Stories New

Trust Center

Security & Compliance Center

Cloud Compliance Resources

Security Compliance FAQs

Product & Feature Update New

Cloud Forward

Press Room

Alibaba Cloud e-Magazine New

Alibaba Cloud in Analyst Research

Notice

Go Global Service New

Go Global Alliance with Alibaba Cloud

Asia Accelerator Hot

Information Compliance

China Gateway - MLPS 2.0 Compliance New

China Gateway - Networking

China Gateway - Global Application Acceleration New

China Gateway - Security

China Gateway - Data Security New

ICP Support Hot

China Gateway - Omnichannel Data Mid-End New

China Gateway - Organizational Data Mid-End New

China Gateway - Business Mid-End New

China Gateway - AI Service for Conversational Chatbots New

China Gateway - Online Education

China Gateway - Domain Registration

Work at Alibaba Cloud

Experienced Professionals

Students and Graduates

Free Trial

Pricing

Promo Center

Price Reduction

Pay Less and Deploy More

FinOps

Elastic Compute Service (ECS)

Simple Application Server (SAS)

Elastic GPU Service

Elastic Desktop Service (EDS)

Object Storage Service (OSS)

Cloud Enterprise Network (CEN)

Web Application Firewall (WAF)

Domain Names

Container Compute Service (ACS)

Secure Access Service Edge (SASE)

Intelligent Media Services(IMS)

Edge Security Acceleration (ESA)(Original DCDN)

Intelligent Media Management

DingTalk Enterprise

YiDA

Alibaba Cloud Model Studio

Apsara Prime - For Easy Cloud Product Selection

Alibaba Cloud ECS - Cater All Your Cloud Hosting Needs

1TB CDN—Get Free 1 TB Outbound Traffic Plan Now

Security—Under Attack? Get Free Security Support

Short Message Service - Free Testing is Available

Elastic Compute Service (ECS) Hot

CloudBox

Compute Nest

Dedicated Host Hot

ECS Bare Metal Instance

Elastic GPU Service Featured

Simple Application Server (SAS) Hot

Auto Scaling

Cloud Phone Beta

Elastic Desktop Service (EDS) Featured

Batch Compute

Elastic High Performance Computing (E-HPC)

Super Computing Cluster (SCC)

Function Compute (FC)