Vector Computing

DWS integrates the pgvector plugin to provide vector storage and retrieval capabilities. For details about the specifications and syntax of the plugin, see pgvector.

Precautions

This function is in the beta test phase. To use it, contact technical support.
To use vector computing, contact technical support to add the feature_support_options parameter and set its value to enable_pgvector. This function is supported only by clusters running version 9.1.1.200 and later.

pgvector Plugin Capabilities

Vector data type: You can create vector(n) columns in a table to store high-dimensional vectors.
Vector similarity calculation: Multiple distance or similarity operators are supported, such as L2 (Euclidean distance), inner product, cosine distance, L1 (Manhattan distance), and even Hamming/Jaccard.
Nearest neighbor search (NNS): including exact nearest neighbor (ENN) search and approximate nearest neighbor (ANN) search
Vector indexes: IVFFlat and HNSW indexes, used to accelerate retrieval of a large number of vectors

The pgvector plugin is available only for row-store tables in the current version.

Vector Data Types

Data Type	Precision	Supported Dimension	Typical Use Case
vector	Single-precision floating point number, 4 bytes	≤ 16,000	Traditional embedding
halfvec	Half-precision floating point number (16 bits, 2 bytes per element)	≤ 16,000	High-dimensional embedding and memory-sensitive scenarios
bit	Binary vector (bit vector)	≤ 64,000	Binary hash, image/feature Boolean representation
sparsevec	Sparse vector	≤ 16,000 non-zero dimensions	Text TF-IDF sparse features and ultra-high-dimensional sparse embedding

The dimensions supported by the preceding vectors are restricted by the data types. IVFFlat and HNSW indexes support additional dimensions of vectors.

vector: ≤ 2,000
halfvec: ≤ 4,000
bit: ≤ 64,000
sparsevec: ≤ 1,000, non-zero dimensions (supported only by HNSW indexes)

Vector Distance/Similarity Functions

Operator	Distance/Similarity Type	Description
<->	Euclidean distance (L2)	Euclidean distance
<#>	(Negative) inner product	Inner product
<=>	Cosine distance	Vector direction difference
<+>	L1 distance (Taxicab)	Sum of absolute differences of vector coordinates
<~>	Hamming distance	Number of positions at which the corresponding binary bits are different
<%>	Jaccard distance	Used for binary bit vector/set representation to measure the intersection/union set differences

Index Types

Index Type	Principle	Construction Cost	Query Speed/Recall	Main Optimization Parameter
IVFFlat	Divides vectors into lists (clusters/buckets). Only some lists are scanned during queries.	Fast construction and low memory usage	Medium query speed and low recall. The scan range is affected by the probes parameter.	lists (number of clusters), probes (number of clusters to be scanned), and maxProbes (maximum number of clusters to be scanned)
HNSW	Accesses neighboring points through jumps based on a navigable small world graph.	Slow construction and high memory usage	High query speed and recall. It is especially suitable for high-dimensional and low-latency scenarios.	m (maximum number of connections at each layer), ef_construction (number of construction candidates), ef_search (number of query candidates), max_scan_tuples (maximum number of nodes to be scanned), and mem_scan_mutiplier (dynamic memory expansion rate)

Using the pgvector Plug-in

Initialize the plug-in.
```
CREATE EXTENSION pgvector;
```

Create a vector column with three dimensions.

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

Insert vector data.

INSERT INTO items (embedding) VALUES
('[1, 2, 3]'),
('[0.5, 0.1, 0.9]'),
('[3, 2, 1]'),
('[0, 0, 1]'),
('[1.1, 2.2, 3.3]');

Query the nearest neighbor vector by L2 distance.

SELECT * FROM items ORDER BY embedding <-> '[3,1,2]' LIMIT 5;
 id |   embedding   
----+---------------
  3 | [3,2,1]
  1 | [1,2,3]
  5 | [1.1,2.2,3.3]
  2 | [0.5,0.1,0.9]
  4 | [0,0,1]
(5 rows)

Query the distance.

SELECT embedding <-> '[3,1,2]' AS distance FROM items; 
     distance     
------------------
 2.44948974278318
 2.87576084849969
  1.4142135623731
  3.3166247903554
 2.59615095306844
(5 rows)

For the inner product, use -1 * inner product to obtain the distance.

SELECT (embedding <#> '[3,1,2]') * -1 AS inner_product FROM items;

For the cosine distance, use 1 - cosine distance to obtain the distance.

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

Perform aggregate calculation.

SELECT AVG(embedding) FROM items;
       avg        
------------------
 [1.12,1.26,1.84]
(1 row)

SELECT SUM(embedding) FROM items;
      sum      
---------------
 [5.6,6.3,9.2]
(1 row)

Using Indexes

pgvector uses ENN search by default and provides 100% recall, but the query speed is low. You can use ANN search indexes as needed to improve the query speed, but this will lower the recall. Unlike traditional indexes, approximate search indexes may return varied query results. pgvector supports HNSW and IVFFlat indexes.

HNSW

Add indexes.

-- L2 distance
CREATE INDEX ON items USING hnsw (embedding vector_l2_ops);
-- Inner product
CREATE INDEX ON items USING hnsw (embedding vector_ip_ops);
-- Cosine distance
CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops);
-- L1 distance
CREATE INDEX ON items USING hnsw (embedding vector_l1_ops);
-- Hamming distance
CREATE INDEX ON items USING hnsw (embedding bit_hamming_ops);
-- Jaccard distance
CREATE INDEX ON items USING hnsw (embedding bit_jaccard_ops);

Use the distance function that matches the vector type. For example, use halfvec_l2_ops for the halfvec vector (L2 distance) and sparsevec_l2_ops for the sparsevec vector.

Build parameters.
m - Maximum number of connections on the layer (default value: 16)

ef_construction - Number of candidates for index creation (default value: 64)
```
CREATE INDEX ON items USING hnsw (embedding vector_l2_ops) WITH (m = 16, ef_construction = 64);
```
Greater values of the preceding parameters provide a higher recall but increase the index creation and data insertion time.

IVFFlat
1. Add indexes.
```
-- L2 distance
CREATE INDEX ON items USING ivfflat (embedding vector_l2_ops) WITH (lists = 100);
-- Inner product
CREATE INDEX ON items USING ivfflat (embedding vector_ip_ops) WITH (lists = 100);
-- Cosine distance
CREATE INDEX ON items USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);
-- Hamming distance
CREATE INDEX ON items USING ivfflat (embedding bit_hamming_ops) WITH (lists = 100);
```
2. Build parameters.
  lists - Number of clusters calculated during index creation. A greater value indicates a higher recall and a longer index creation time.
  - You are advised to create IVFFlat indexes after data is imported.
  - Set an appropriate value for lists. If the data volume is less than 1 MB, set the value to the number of rows divided by 1,000. If the data volume exceeds 1 MB, set the value to sqrt (number of rows).
Index parameters
- Iterative scan mode
  Both HNSW and IVFFlat indexes support the iterative scan mode, which automatically scans more indexes to obtain sufficient data.
  
  They use the hnsw_iterative_scan and ivfflat_iterative_scan parameters respectively to control whether to enable this function.
  
  Parameter values: off (default), relaxed_order, and strict_order (only supported by HNSW indexes)
- HNSW query options
  hnsw_options: There are three parameters: ef_search (number of query candidates), max_scan_tuples (maximum number of nodes to be scanned), and mem_scan_multiplier (dynamic memory expansion rate).
  
  Default value: '40, 20000, 1.0'
- IVFFlat query options
  ivfflat_options: There are two parameters: probes (number of clusters to be scanned) and maxProbes (maximum number of clusters to be scanned).
  
  Default value: '1, 32768'