pgvector extension EARLY ACCESS

The pgvector PostgreSQL extension allows you to store and query vectors, for use in performing similarity searches.

YugabyteDB includes pgvector 0.8.0-yb-1.0 on PostgreSQL 15-compatible YSQL. Most SQL-level types, functions, operators, casts, and aggregates match upstream pgvector 0.8.0. Approximate nearest neighbor (ANN) search uses the distributed ybhnsw access method (backed by DocDB Vector LSM) instead of native PostgreSQL hnsw / ivfflat.

Vector distance functions measure similarity or difference between high-dimensional data points. Choosing the right function depends on the use case, such as search, ranking, or clustering. YugabyteDB supports the following distance functions:

• Cosine Distance - Measures the angle between two vectors. Used for comparing direction rather than magnitude. Best for text similarity and recommendation systems.
• L2 (Euclidean) Distance - Measures the straight-line distance between two points in space. Best when absolute differences in values matter, like in image recognition.
• Inner Product - Measures similarity by multiplying corresponding elements and summing them. Often used in ranking and recommendation models, where larger values indicate higher similarity.

Supported features

The following pgvector capabilities are supported in YugabyteDB.

Types and SQL

Exact and approximate search

Top-k queries of the form ORDER BY embedding <-> query LIMIT k use the ybhnsw index. You can combine ANN search with a WHERE filter; the filter is applied after the index scan.

Enable the extension

To enable the pgvector extension:

CREATE EXTENSION vector;

Create vectors

Create a vector column 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 L2 distance:

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

The extension also supports inner product (<#>) and cosine distance (<=>).

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

Store vectors

Create a new table with a vector column:

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

Or add a vector column to an existing table:

ALTER TABLE items ADD COLUMN embedding vector(3);

Insert vectors:

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

Upsert vectors:

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

Update vectors:

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

Delete vectors:

DELETE FROM items WHERE id = 1;

Query vectors

Get the nearest neighbors to a vector:

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

Get the nearest neighbors to a row:

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

Get rows within a certain distance:

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

Distances

Get the distance:

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

For inner product, multiply by -1 (<#> returns the negative inner product)

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

For cosine similarity, use 1 - cosine distance:

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

Aggregates

Average vectors:

SELECT AVG(embedding) FROM items;

Create a table with a vector column and a category column:

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

Insert multiple vectors belonging to the same category:

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

Average groups of vectors belonging to the same category:

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

Vector indexing

EA By default, vector search performs exact nearest neighbor search, ensuring perfect recall.

To improve query performance, you can use approximate nearest neighbor (ANN) search, which trades some recall for speed. Unlike traditional indexes, approximate indexes may return different results for queries.

YugabyteDB currently supports the HNSW (Hierarchical Navigable Small World) index type.

HNSW

HNSW indexing creates a multilayer graph to enable efficient high-dimensional vector search. HNSW offers faster query performance but requires more memory and has longer build times. You can create an index before inserting any data into the table.

Add an index for each distance function you want to use.

To use the L2 distance function:

CREATE INDEX NONCONCURRENTLY ON items USING ybhnsw (embedding vector_l2_ops);

For PostgreSQL backwards compatibility, USING hnsw is also supported and is internally mapped to the ybhnsw index access method. For example, the following statement is equivalent to the one above:

CREATE INDEX NONCONCURRENTLY ON items USING hnsw (embedding vector_l2_ops);

To use the inner product function:

CREATE INDEX NONCONCURRENTLY ON items USING ybhnsw (embedding vector_ip_ops);

To use the Cosine distance function:

CREATE INDEX NONCONCURRENTLY ON items USING ybhnsw (embedding vector_cosine_ops);

ANN indexes are supported on the vector type. You can store and run exact searches on halfvec and sparsevec columns; for HNSW indexing, use a vector column (or cast / densify to vector).

HNSW index options

You can fine-tune HNSW indexing using the following parameters:

For example:

CREATE INDEX NONCONCURRENTLY ON items USING ybhnsw (embedding vector_l2_ops) WITH (m = 16, ef_construction = 128);

A higher ef_construction value provides faster recall at the cost of index build time / insert speed.

Query-time tuning

You can tune query-time behavior of HNSW search using the following GUC:

For example, to increase recall for the current session:

SET hnsw.ef_search = 100;

Limitations

• Concurrent index creation is not currently supported. For example, the following syntax falls back to non-concurrent implementation:

  CREATE INDEX CONCURRENTLY on <table> USING ybhnsw (vec vector_l2_ops);
  

  Unlike concurrent index creation on non-vector data types, the index backfill will take an exclusive lock (ACCESS_EXCLUSIVE) on the table, and writes to the table are blocked while index backfill is in progress. #26402

• Partial indexes on vector columns are not supported yet. #31441

• Vector indexes are not supported for xCluster replication.

• Time travel queries are not currently supported. #20829

Learn more

• Tutorial: Build and Learn
• Tutorials: Build scalable generative AI applications with YugabyteDB
• PostgreSQL pgvector: Getting Started and Scaling
• Multimodal Search with PostgreSQL pgvector