Edit

Share via

Enable and use pgvector in Azure Database for PostgreSQL - Flexible Server

  • Article

APPLIES TO: Azure Database for PostgreSQL - Flexible Server

The pgvector extension adds an open-source vector similarity search to PostgreSQL.

This article introduces us to extra capabilities enabled by pgvector. It covers the concepts of vector similarity and embeddings, and provides guidance on how to enable the pgvector extension. We learn about, how to create, store and query the vectors. You might also want to refer to the official README of the project.

Enable extension

Before you can enable pgvector on your Azure Database for PostgreSQL flexible server instance, you need to add it to your allowlist as described in how to use PostgreSQL extensions and check if correctly added by running SHOW azure.extensions;.

Important

Notice that although all PostgreSQL community tends to refer to this extension as pgvector, the name of the binary and the extension itself is simply vector. Take that into consideration, because that is the name you must use to allowlist it or to create it on any database via the CREATE EXTENSION command.

Then you can install the extension, by connecting to your target database and running the CREATE EXTENSION command. You need to repeat the command separately for every database you want the extension to be available in.

CREATE EXTENSION vector;

Note

To remove the extension from the currently connected database use DROP EXTENSION vector;.

Concepts

Vector similarity

Vector similarity is a method used to measure how similar two items are by representing them as vectors, which are series of numbers. Vectors are often used to represent data points, where each element of the vector represents a feature or attribute of the data point.

Vector similarity is commonly calculated using distance metrics, such as Euclidean distance or cosine similarity. Euclidean distance measures the straight-line distance between two vectors in the n-dimensional space, while cosine similarity measures the cosine of the angle between two vectors. The values of similarity metrics typically range between 0 and 1, with higher values indicating greater similarity between the vectors.

Vector similarity is widely used in various applications, such as recommendation systems, text classification, image recognition, and clustering. For example, in recommendation systems, vector similarity can be used to identify similar items based on the user's preferences. In text classification, vector similarity can be used to determine the similarity between two documents or sentences based on their vector representations.

Embeddings

An embedding is a technique of evaluating "relatedness" of text, images, videos, or other types of information. The evaluation permits machine learning models to efficiently identify the relationships and similarities between data, allowing algorithms to identify patterns and make accurate predictions. For example, in a sentiment analysis task, words with similar embeddings might be expected to have similar sentiment scores.

Getting started

Create a table tblvector with an embedding column of type vector(3) representing a three-dimensional vector.

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

Once you generated an embedding using a service like the OpenAI API, you can store the resulting vector in your database. Defining a vector as vector(3) designates [x,y,z] coordinates in three-dimension plane. The command inserts five new rows into the tblvector table with the provided embeddings.

INSERT INTO tblvector (id, embedding) VALUES (1, '[1,2,3]'), (2, '[4,5,6]'), (3, '[5,4,6]'), (4, '[3,5,7]'), (5, '[7,8,9]');

By using the INSERT INTO ... ON CONFLICT statement, you can specify an alternative action, such as updating records that match the criteria. It allows you to handle potential conflicts in a more efficient and effective manner.

INSERT INTO tblvector (id, embedding) VALUES (1, '[1,2,3]'), (2, '[4,5,6]')
ON CONFLICT (id) DO UPDATE SET embedding = EXCLUDED.embedding;

The DELETE command removes rows from a specified table based on the conditions specified in the WHERE clause. When the WHERE clause isn't present, all the rows in the table are deleted.

DELETE FROM tblvector WHERE id = 1;

To retrieve vectors and calculate similarity, use SELECT statements and the built-in vector operators. For instance, the query computes the Euclidean distance (L2 distance) between the given vector and the vectors stored in the tblvector table, sorts the results by the calculated distance, and returns the closest five most similar items.

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

The query uses the "<->" operator, which is the "distance operator" used to calculate the distance between two vectors in a multi-dimensional space. The query returns all rows with the distance of less than 6 from the vector [3,1,2].

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

The command retrieves the average value of the "embedding" column from the "tblvector" table. For example, if the "embedding" column contains word embeddings for a language model, then the average value of these embeddings could be used to represent the entire sentence or document.

SELECT AVG(embedding) FROM tblvector;

Vector operators

pgvector introduces six new operators that can be used on vectors:

Operator Description
+ element-wise addition
- element-wise subtraction
* element-wise multiplication
<-> Euclidean distance
<#> negative inner product
<=> cosine distance

Vector functions

cosine_distance

Calculates the cosine distance between two vectors.

cosine_distance(vector, vector)

Arguments

vector

First vector.

vector

Second vector.

Return type

double precision as distance between the two provided vectors.

inner_product

Calculates the inner product of two vectors.

inner_product(vector, vector)

Arguments

vector

First vector.

vector

Second vector

Return type

double precision as inner product of the two vectors.

l2_distance

Calculates the Euclidean distance (also known as L2) between two vectors.

l2_distance(vector, vector)

Arguments

vector

First vector.

vector

Second vector

Return type

double precision as the Euclidean distance between the two vectors.

l1_distance

Calculates the taxicab distance (also known as L1) between two vectors.

l1_distance(vector, vector)

Arguments

vector

First vector.

vector

Second vector

Return type

double precision as the taxicab distance between the two vectors.

vector_dims(vector)

Returns the dimensions of a given vector.

Arguments

vector

A vector.

Return type

integer representing the number of dimensions of the given vector.

vector_norms(vector)

Calculates the Euclidean norm of a given vector.

Arguments

vector

A vector.

Return type

double precision representing the Euclidean norm of the given vector.

Vector aggregates

AVG

Calculates the average of the processed vectors.

Arguments

vector

A vector.

Return type

vector representing the average of processed vectors.

SUM

Arguments

vector

A vector.

Return type

vector representing the sum of processed vectors.

Share your suggestions and bugs with the Azure Database for PostgreSQL product team.

Related content