Use pgvector

Eugenia Kuzina

Overview

The pgvector extension provides vector similarity search for PostgreSQL, which involves the use of embedding. Embedding is a technique that maps complex multidimensional data, including text and images, in a vector space of numerical vectors. Objects with similar meanings are placed near each other in this space.

The vector similarity search is useful in the following scenarios:

  • Semantic search. Allows the system to understand the essence of a user’s query, even if the words do not match the keywords in the document.

  • RAG (Retrieval Augmented Generation) systems. Helps AI language models search for relevant information in a company’s knowledge base or on the Internet to provide accurate answers.

  • Recommendation systems. Online cinemas, marketplaces, and other similar systems use vector similarity search to suggest films, music, and products based on similar user interests and product properties.

  • Multimedia search. Allows you to search by an uploaded image or text query and find photos or videos that match the query.

  • Classification. Helps quickly identify the subject and category of documents, as well as detect duplicate texts and images.

The package required for the installation of the pgvector extension is shipped with ADP. To use pgvector, execute the CREATE EXTENSION command:

CREATE EXTENSION vector;
NOTE
If the pgvector extension is created in the template1 database used as the default template, all subsequently created databases will have this extension installed.

ADP uses the 0.8.0 pgvector version. To check it, execute the following query:

SELECT extversion FROM pg_extension
    WHERE extname = 'vector';
 extversion
------------
0.8.0

The pgvector extension allows you to store vectors along with other data. It supports:

  • exact and approximate nearest neighbor search;

  • single-precision, half-precision, binary, and sparse vectors;

  • calculation of different distance metrics listed in the table below.

    Name Syntax

    L2 (Euclidean) distance

    <->

    Inner product. It has a negative value

    <#>

    Cosine distance

    <=>

    L1 (taxicab or Manhattan) distance

    <+>

    Hamming distance (binary vectors)

    <~>

    Jaccard distance (binary vectors)

    <%>

The pgvector extension supports the following vector types:

  • vector

  • halfvec

  • bit

  • sparsevec

To test the pgvector functions, create a table with a vector column. Specify the number of vector dimensions in parentheses:

CREATE TABLE test (
    id serial PRIMARY KEY,
    embedding vector(3)
);

Add vector data to the table:

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

Calculate the L2 distance between the vectors in the table and the vector [1,1,1] and sort the results by distance:

SELECT embedding FROM test ORDER BY embedding <-> '[1,1,1]';
 embedding
-----------
 [1,1,3]
 [1,2,3]
 [3,3,3]
 [4,4,4]
 [4,5,6]
 [1,3,8]

The output shows that the closest vector is [1,1,3], and the most distant is [1,3,8].

Calculate the L1 distance between the vectors in the table and the vector [1,1,1]:

SELECT embedding FROM test ORDER BY embedding <+> '[1,1,1]';
 embedding
-----------
 [1,1,3]
 [1,2,3]
 [3,3,3]
 [1,3,8]
 [4,4,4]
 [4,5,6]

The output is different — now the most distant vector is [4,5,6].

Indexes

By default, the pgvector extension performs exact nearest neighbor search, which provides high accuracy and quite low speed. To improve speed, you can add an index that uses an approximate nearest neighbor algorithm. Unlike regular indexes, adding a pgvector index will change the query results.

Supported index types are:

HNSW

The HNSW index creates a multilayer graph. It has better query performance than IVFFlat but takes more time to build and uses more memory. This index can be created without any data in the table.

The HNSW index supports the following vector types:

  • vector — up to 2000 dimensions;

  • halfvec — up to 4000 dimensions;

  • bit — up to 64000 dimensions;

  • sparsevec — up to 1000 non-zero elements.

You need to add an index for each distance function you want to use and specify a corresponding operator class for each function and vector type.

Operator classes for different distance functions and vector types
Function Vector Halfvec Bit Sparsevec

L2 distance

vector_l2_ops

halfvec_l2_ops

 — 

sparsevec_l2_ops

Inner product

vector_ip_ops

halfvec_ip_ops

 — 

sparsevec_ip_ops

Cosine distance

vector_cosine_ops

halfvec_cosine_ops

 — 

sparsevec_cosine_ops

L1 distance

vector_l1_ops

halfvec_l1_ops

 — 

sparsevec_l1_ops

Hamming distance

 — 

 — 

bit_hamming_ops

 — 

Jaccard distance

 — 

 — 

bit_jaccard_ops

 — 

The following code creates an index for the L2 distance function and the vector type (the table name — table1, the column name — embedding):

CREATE INDEX ON table1 USING hnsw (embedding vector_l2_ops);

The code below builds an index for the Hamming distance function and the bit type:

CREATE INDEX ON table2 USING hnsw (embedding bit_hamming_ops);

IVFFlat

The IVFFlat index partitions vectors into lists and searches the subset of lists closest to the query vector. It has faster build times and uses less memory than HNSW but has lower query performance. Create this index after a table is populated with data.

The IVFFlat index supports the following types:

  • vector — up to 2000 dimensions;

  • halfvec — up to 4000 dimensions;

  • bit — up to 64000 dimensions.

Use the same operator classes as with the HNSW index for supported vector types.

You can also specify the lists parameter of the IVFFlat index that affects performance. Denote the number of rows in a table as rows, then the recommended value is:

  • for up to 1 million rows;

  • for over a million rows.

The following code creates an index for the L2 distance function and the vector type and sets the lists parameter to 100:

CREATE INDEX ON table1 USING ivfflat (embedding vector_l2_ops) WITH (lists = 100);

To determine how many lists are searched for the nearest neighbors, specify the probes parameter. A higher value provides better search completeness, and a lower value increases the speed. Use the SET command to specify the probes parameter:

SET ivfflat.probes = 10;

Examples

These examples use embeddings. To obtain them, utilize any embedding model (some of them are available online). In these examples, each embedding has 3072 dimensions. A column of type vector(3072) is used to store them.

L2 distance

Create a table with a vector column:

CREATE TABLE description (
  id serial PRIMARY KEY,
  title text,
  embedding vector(768)
);

Fill the description table with data. In the code below, the embedding field values are reduced to preserve readability.

INSERT INTO description (title, embedding) VALUES
  ('A solution for managing PostgreSQL clusters', '[-0.009550615, -0.012897374, ... 0.011087871]'),
  ('A binary backup management system', '[-0.010036648, 0.011803862, ... 0.036223497]'),
  ('A separate product designed for simple, convenient, and fast software deployment and exploitation', '[0.020033414, -0.001558028, ... -0.00035061908]'),
  ('A tool for backup creation', '[0.011411955, -0.0039920947, ... 0.013054479]'),
  ('Easy cluster installation', '[0.0138596445, 0.0040847682, ... -0.0062671695]'),
  ('A tool to install and manage PostgreSQL clusters', '[-0.004355611, -0.0049024294, ... 0.004386888]');

You can find the full version of the SQL statements in the file description.sql.

Compare title values in pairs and sort them by L2 distance:

SELECT
    d1.title AS title_d1,
    d2.title AS title_d2,
    d1.embedding <-> d2.embedding AS distance
FROM description d1
JOIN description d2 ON d1.id < d2.id
ORDER BY distance;
                   title_d1                   |                     title_d2                    |      distance
---------------------------------------------+--------------------------------------------------+---------------------
 A solution for managing PostgreSQL clusters | A tool to install and manage PostgreSQL clusters | 0.2973072644418864
 A binary backup management system           | A tool for backup creation                       | 0.4217937355892653
 Easy cluster installation                   | A tool to install and manage PostgreSQL clusters | 0.4695271407723517
 A solution for managing PostgreSQL clusters | Easy cluster installation                        | 0.4905144093027282
 A separate product designed for simple ...  | A tool for backup creation                       | 0.5002226333872557
 A tool for backup creation                  | A tool to install and manage PostgreSQL clusters | 0.5172138587711625
 A separate product designed for simple ...  | Easy cluster installation                        | 0.5173604255461222
 A solution for managing PostgreSQL clusters | A binary backup management system                | 0.5213006656823225
 A binary backup management system           | A separate product designed for simple...        | 0.5278033761319432
 A solution for managing PostgreSQL clusters | A tool for backup creation                       | 0.5297814997533667
 A separate product designed for simple ...  | A tool to install and manage PostgreSQL clusters | 0.5426847414928297
 A binary backup management system           | A tool to install and manage PostgreSQL clusters | 0.5449763171711131
 A solution for managing PostgreSQL clusters | A separate product designed for simple...        | 0.5463234163351685
 A binary backup management system           | Easy cluster installation                        | 0.5631321163341745
 A tool for backup creation                  | Easy cluster installation                        | 0.5684921266921485

The nearest values are A solution for managing PostgreSQL clusters and A tool to install and manage PostgreSQL clusters.

Inner product

Create a new table and fill it with data (the values of the embedding field are reduced):

CREATE TABLE items (
    id serial PRIMARY KEY,
    item text,
    embedding vector(768)
);

INSERT INTO items (item, embedding) VALUES
  ('cookies', '[-0.002850077, -0.0040400615, ... 0.0043995334]'),
  ('apples', '[0.020033414, -0.001558028, ... 0.01091539]'),
  ('chocolate', '[-0.012762195, 0.023601593, ... -0.010077247]'),
  ('croissant', '[0.011127311, -0.008577864, ... 0.01457113]'),
  ('carrot', '[-0.0061358884, 0.027397538, ... 0.008722801]'),
  ('cheesecake', '[-0.01261848, -0.027980627, ... -0.005194875]'),
  ('flour', '[-0.013135226,  0.011132385, ... 0.00960297]'),
  ('bakery', '[0.00017604351, -0.012099541, ...  0.03317081]');

You can find the full version of the SQL statements in the file items.sql.

Calculate the inner product between the vector corresponding to the bakery value and other vectors. Multiply it by -1, since <#> returns a negative inner product.

SELECT item,
       (embedding <#> (SELECT embedding FROM items WHERE item = 'bakery')) * -1 AS inner_product
FROM items
ORDER BY inner_product DESC;
    item    |    inner_product
------------+---------------------
 bakery     | 0.33495470881462097
 croissant  | 0.22958263754844666
 flour      | 0.22580863535404205
 cookies    | 0.21599330008029938
 cheesecake |  0.2063595950603485
 chocolate  | 0.20056280493736267
 carrot     | 0.19186848402023315
 apples     | 0.19073499739170074

In this example, the most relevant value for bakery is croissant.

Found a mistake? Seleсt text and press Ctrl+Enter to report it