Simple use of pgvector extension-PolarDB(PolarDB)-阿里云帮助中心

This topic describes the basic usage of the vector database.

Note

The PGVector extension haskernel version requirements.If your kernel version does not meet the requirements, upgrade the kernel version.

Enable the pgvector extension and create a vector table

Use a privileged accountto create the extension in the target database.

Note

The pgvector extension is scoped to the database level. To use pgvector in multiple databases within the same cluster, create the extension in each database separately.

CREATE EXTENSION IF NOT EXISTS vector;

After the extension is created, you can run the following statements to create a vector table.

Create a vector table with 3 dimensions.

CREATE TABLE items (id bigserial PRIMARY KEY, embedding vector(3));

Insert vectors.

INSERT INTO items (embedding) VALUES ('[1,2,3]'), ('[4,5,6]');

Get the nearest neighbors by Euclidean distance (L2).
```
SELECT * FROM items ORDER BY embedding <-> '[3,1,2]' LIMIT 5;
```
Inner product (<#>), cosine distance (<=>), and L1 distance (<+>) are also supported.

Note
<#> returns the negative inner product because PostgreSQL only supports ascending index scans on operators.

Storage

Create a vector table with 3 dimensions.
```
CREATE TABLE items_3 (id bigserial PRIMARY KEY, embedding vector(3));
```
Note
You can also add a vector column to an existing table: ALTER TABLE items ADD COLUMN embedding vector(3);.
Insert vectors.
- Basic vector insertion.
```
INSERT INTO items_3 (embedding) VALUES ('[1,2,3]'), ('[4,5,6]');
```
- Use COPY to bulk load vectors through the API.
- Insert vectors and handle potential conflicts such as primary key conflicts.
```
INSERT INTO items_3 (id, embedding) VALUES (1, '[1,2,3]'), (2, '[4,5,6]')
    ON CONFLICT (id) DO UPDATE SET embedding = EXCLUDED.embedding;
```
  If a primary key conflict exists between the new data and existing data, the embedding column of the existing row is updated with the new value to ensure data uniqueness.

Update vectors.

UPDATE items_3 SET embedding = '[1,2,3]' WHERE id = 1;

Delete vectors.
```
DELETE FROM items_3 WHERE id = 1;
```

Query

Distance

Get the nearest neighbors to a vector.

SELECT * FROM items ORDER BY embedding <-> '[3,1,2]' LIMIT 5;

Supported distance functions:

<->: Euclidean distance (L2).
<#>: inner product.
<=>: cosine distance.
<+>: Manhattan distance (L1).
<~>: Hamming distance (binary vectors).
<%>: Jaccard distance (binary vectors).

Examples

Note

When using indexes, we recommend combining ORDER BY with LIMIT.

Get the 5 nearest neighbors to a specific row and sort them.

SELECT * FROM items WHERE id != 1 ORDER BY embedding <-> (SELECT embedding FROM items WHERE id = 1) LIMIT 5;

Get rows within a specific distance range.

SELECT * FROM items WHERE embedding <-> '[3,1,2]' < 5;

Get the distance.

SELECT embedding <-> '[3,1,2]' AS distance FROM items;

Get the inner product distance.

Note
Because <#> returns the negative inner product, multiply by -1.
```
SELECT (embedding <#> '[3,1,2]') * -1 AS inner_product FROM items;
```

Get the cosine similarity using the 1 - cosine distance formula.

SELECT 1 - (embedding <=> '[3,1,2]') AS cosine_similarity FROM items;

Aggregation

Average vector.
```
SELECT AVG(embedding) FROM items;
```

Average vector by group.

SELECT id, AVG(embedding) FROM items GROUP BY id;