vector

vector data type and ivfflat and hnsw access methods

Overview

Packages

Source

pig build get vector; # get vector source code
pig build dep vector; # install build dependencies
pig build pkg vector; # build extension rpm or deb
pig build ext vector; # build extension rpms

Install

To add the required PGDG / PIGSTY upstream repository, use:

pig repo add pgsql -u   # add PGDG + Pigsty repo and update cache (leave existing repos)

Install this extension with:

pig ext install vector; # install by extension name, for the current active PG version
pig ext install pgvector; # install via package alias, for the active PG version
pig ext install vector -v 18;   # install for PG 18
pig ext install vector -v 17;   # install for PG 17
pig ext install vector -v 16;   # install for PG 16
pig ext install vector -v 15;   # install for PG 15
pig ext install vector -v 14;   # install for PG 14
pig ext install vector -v 13;   # install for PG 13

Create this extension with:

CREATE EXTENSION vector;

Usage

Getting Started

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;

Also supports inner product (<#>), cosine distance (<=>), and L1 distance (<+>)

Note: <#> returns the negative inner product since Postgres only supports ASC order index scans on operators

CRUD

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);

Also supports half-precision, binary, and sparse vectors

Insert vectors

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

Or load vectors in bulk using COPY (example)

COPY items (embedding) FROM STDIN WITH (FORMAT BINARY);

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;

Querying

Get the nearest neighbors to a vector

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

Supported distance functions are:

• <-> - L2 distance
• <#> - (negative) inner product
• <=> - cosine distance
• <+> - L1 distance
• <~> - Hamming distance (binary vectors)
• <%> - Jaccard distance (binary vectors)

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;

Note: Combine with ORDER BY and LIMIT to use an index

Distances

Get the distance

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

For inner product, multiply by -1 (since <#> 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;

Average groups of vectors

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

HNSW Indexing

By default, pgvector performs exact nearest neighbor search, which provides perfect recall.

You can add an index to use approximate nearest neighbor search, which trades some recall for speed. Unlike typical indexes, you will see different results for queries after adding an approximate index.

Supported index types are:

• HNSW
• IVFFlat

An HNSW index creates a multilayer graph. It has better query performance than IVFFlat (in terms of speed-recall tradeoff), but has slower build times and uses more memory. Also, an index can be created without any data in the table since there isn’t a training step like IVFFlat.

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

L2 distance

CREATE INDEX ON items USING hnsw (embedding vector_l2_ops);

Note: Use halfvec_l2_ops for halfvec and sparsevec_l2_ops for sparsevec (and similar with the other distance functions)

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);

Supported types are:

• vector - up to 2,000 dimensions
• halfvec - up to 4,000 dimensions
• bit - up to 64,000 dimensions
• sparsevec - up to 1,000 non-zero elements

Index Options

Specify HNSW parameters

• m - the max number of connections per layer (16 by default)
• ef_construction - the size of the dynamic candidate list for constructing the graph (64 by default)

CREATE INDEX ON items USING hnsw (embedding vector_l2_ops) WITH (m = 16, ef_construction = 64);

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

Query Options

Specify the size of the dynamic candidate list for search (40 by default)

SET hnsw.ef_search = 100;

A higher value provides better recall at the cost of speed.

Use SET LOCAL inside a transaction to set it for a single query

BEGIN;
SET LOCAL hnsw.ef_search = 100;
SELECT ...
COMMIT;

Index Build Time

Indexes build significantly faster when the graph fits into maintenance_work_mem

SET maintenance_work_mem = '8GB';

A notice is shown when the graph no longer fits

NOTICE:  hnsw graph no longer fits into maintenance_work_mem after 100000 tuples
DETAIL:  Building will take significantly more time.
HINT:  Increase maintenance_work_mem to speed up builds.

Note: Do not set maintenance_work_mem so high that it exhausts the memory on the server

Like other index types, it’s faster to create an index after loading your initial data

You can also speed up index creation by increasing the number of parallel workers (2 by default)

SET max_parallel_maintenance_workers = 7; -- plus leader

For a large number of workers, you may need to increase max_parallel_workers (8 by default)

The index options also have a significant impact on build time (use the defaults unless seeing low recall)

Indexing Progress

Check indexing progress

SELECT phase, round(100.0 * blocks_done / nullif(blocks_total, 0), 1) AS "%" FROM pg_stat_progress_create_index;

The phases for HNSW are:

1. initializing
2. loading tuples