Using vector in Yandex Managed Service for PostgreSQL
With the vector extension, you can store vector data and run a vector search in Yandex Managed Service for PostgreSQL.
The extension supports:
- Exact and approximate nearest neighbor search.
- Various vector types: single-precision, half-precision, binary, and sparse vectors.
- Several vector comparison metrics: L2 (Euclidean distance), cosine distance, dot product, L1 (Manhattan distance), Hamming and Jaccard distance.
- Any programming language that supports a connection to PostgreSQL.
Vector search can be combined with familiar PostgreSQL and SQL features.
By default, vector performs exact search where it compares all vectors. For large data volumes, you can create vector indexes and use approximate search. This search is faster, but the results may prove less accurate.
vector supports two index types:
- HNSW, which organizes vectors into a data structure which allows you to go from one similar vector to the next and find nearest neighbors without going through the entire table. This structure is based on a multilayer graph.
- IVFFlat, which breaks all vectors up into several groups (lists). The search does not scan all groups but only those that are closest to the target vector. Compared to HNSW, this index is built faster and uses less memory; however, it is inferior in terms of quality and search speed.
The vector extension is available in Managed Service for PostgreSQL clusters of version 13 and higher.
For more information about vector, see this tutorial
Install the vectorextension in your Yandex Managed Service for PostgreSQL cluster
To install the extension in Managed Service for PostgreSQL, add the vector extension to the appropriate database.
Use cases
For more information on using vector, see this guide.
Run a vector search
To get a distance, the vector extension uses the following functions:
<->: L2 distance (Euclidean distance).<#>: Negative dot product.<=>: Cosine distance.<+>: L1 distance (Manhattan distance).<~>: Hamming distance (for binary vectors).<%>: Jaccard distance (for binary vectors).
To find the records closest to the given vector by L2 distance, run the following query:
SELECT *
FROM <table_name>
ORDER BY <column_name> <-> '<query_vector>'
LIMIT <number_of_results>;
Where:
<query_vector>is the vector against which the vectors from the column are compared, e.g.,[3, 1, 2].<Number>: Number of results returned.
The vector extension also supports the following search queries:
- Searching for records most similar to an existing record.
- Searching for records closer than a given distance.
- Retrieving the distance between vectors.
- Getting the dot product value.
- Getting cosine similarity.
- Calculating the average vector.
An example of running these search queries is provided in this guide.
Create an index
Indexes enable making vector search faster. You need to create a dedicated HNSW index and an IVFFlat index for each distance calculation function.
HNSW
To create an HNSW index for a certain distance, run the following query:
CREATE INDEX ON <table_name>
USING hnsw (<column_name> <distance_type>)
WITH (
m = <number_of_connections>,
ef_construction = <number_of_candidates>
);
Where:
-
<distance_type>: Distance calculation function, e.g.,vector_l2_ops. To learn more, see thevectorextension guide . -
m: Maximum number of connections a graph node can have at each layer. The default value is16. -
ef_construction: Number of candidates considered when building the graph. The default value is64. The higher the value, the greater the probability of finding the most relevant records; however, index build time increases, and insertion operations become slower.The
mandef_constructionparameters are set one time when creating an index. To modify their values, re-create the index.
To configure search accuracy, run this query:
SET hnsw.ef_search = <number_of_vectors>;
Where ef_search is the number of vectors the algorithm considers during the search. The default value is 40. The higher the value, the greater the probability of finding the most relevant records; however, the query runs slower.
Unlike the m and ef_construction index parameters, the ef_search parameter can be modified before running queries.
IVFFlat
To create an IVFFlat index, run the following query:
CREATE INDEX ON <table_name>
USING ivfflat (<column_name> <distance_type>)
WITH (
lists = <number_of_groups>
);
Where:
-
<distance_type>: Distance calculation function, e.g.,vector_l2_ops. To learn more, see thevectorextension guide . -
lists: Number of groups (lists) into which the vectors will be divided. The recommended value depends on the amount of data. The more groups there are, the faster the search, but the search quality may decrease.The
listsparameter is set one time when creating an index. To change its value, re-create the index.
To configure search accuracy, run this query:
SET ivfflat.probes = <number_of_groups_for_search>;
Where probes is the number of groups (lists) viewed when running a search. The default value is 1. The higher the value, the greater the probability of finding the most relevant records; however, the query runs slower.
Unlike the lists index parameter, the probes search parameter can be modified before running queries.