Version: 0.8.0

GitHub

Enable the extension (do this once in each database where you want to use it)

CREATE EXTENSION vector;

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

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;

Get the nearest neighbors to a vector

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

Supported distance functions are:

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

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

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

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

SELECT AVG(embedding) FROM items;

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

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:

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:

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

SET hnsw.ef_search = 100;

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

SET maintenance_work_mem = '8GB';

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.

SET max_parallel_maintenance_workers = 7; -- plus leader

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

An IVFFlat index divides vectors into lists, and then searches a subset of those lists that are closest to the query vector. It has faster build times and uses less memory than HNSW, but has lower query performance (in terms of speed-recall tradeoff).

Three keys to achieving good recall are:

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

L2 distance

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

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

Inner product

CREATE INDEX ON items USING ivfflat (embedding vector_ip_ops) WITH (lists = 100);

Cosine distance

CREATE INDEX ON items USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

Hamming distance

CREATE INDEX ON items USING ivfflat (embedding bit_hamming_ops) WITH (lists = 100);

Supported types are:

SET ivfflat.probes = 10;

BEGIN;
SET LOCAL ivfflat.probes = 10;
SELECT ...
COMMIT;

SET max_parallel_maintenance_workers = 7; -- plus leader

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

There are a few ways to index nearest neighbor queries with a WHERE clause.

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

A good place to start is creating an index on the filter column. This can provide fast, exact nearest neighbor search in many cases. Postgres has a number of index types for this: B-tree (default), hash, GiST, SP-GiST, GIN, and BRIN.

CREATE INDEX ON items (category_id);

For multiple columns, consider a multicolumn index.

CREATE INDEX ON items (location_id, category_id);

Exact indexes work well for conditions that match a low percentage of rows. Otherwise, approximate indexes can work better.

CREATE INDEX ON items USING hnsw (embedding vector_l2_ops);

With approximate indexes, filtering is applied after the index is scanned. If a condition matches 10% of rows, with HNSW and the default hnsw.ef_search of 40, only 4 rows will match on average. For more rows, increase hnsw.ef_search.

SET hnsw.ef_search = 200;

Starting with 0.8.0, you can enable iterative index scans, which will automatically scan more of the index when needed.

SET hnsw.iterative_scan = strict_order;

If filtering by only a few distinct values, consider partial indexing.

CREATE INDEX ON items USING hnsw (embedding vector_l2_ops) WHERE (category_id = 123);

If filtering by many different values, consider partitioning.

CREATE TABLE items (embedding vector(3), category_id int) PARTITION BY LIST(category_id);

Added in 0.8.0

With approximate indexes, queries with filtering can return less results since filtering is applied after the index is scanned. Starting with 0.8.0, you can enable iterative index scans, which will automatically scan more of the index until enough results are found (or it reaches hnsw.max_scan_tuples or ivfflat.max_probes).

Iterative scans can use strict or relaxed ordering.

Strict ensures results are in the exact order by distance

SET hnsw.iterative_scan = strict_order;

Relaxed allows results to be slightly out of order by distance, but provides better recall

SET hnsw.iterative_scan = relaxed_order;
# or
SET ivfflat.iterative_scan = relaxed_order;

With relaxed ordering, you can use a materialized CTE to get strict ordering

WITH relaxed_results AS MATERIALIZED (
    SELECT id, embedding <-> '[1,2,3]' AS distance FROM items WHERE category_id = 123 ORDER BY distance LIMIT 5
) SELECT * FROM relaxed_results ORDER BY distance;

For queries that filter by distance, use a materialized CTE and place the distance filter outside of it for best performance (due to the current behavior of the Postgres executor)

WITH nearest_results AS MATERIALIZED (
    SELECT id, embedding <-> '[1,2,3]' AS distance FROM items ORDER BY distance LIMIT 5
) SELECT * FROM nearest_results WHERE distance < 5 ORDER BY distance;

Note: Place any other filters inside the CTE

SET hnsw.max_scan_tuples = 20000;

SET hnsw.scan_mem_multiplier = 2;

SET ivfflat.max_probes = 100;

Use the halfvec type to store half-precision vectors

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

Index vectors at half precision for smaller indexes

CREATE INDEX ON items USING hnsw ((embedding::halfvec(3)) halfvec_l2_ops);

Get the nearest neighbors

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

Use the bit type to store binary vectors (example)

CREATE TABLE items (id bigserial PRIMARY KEY, embedding bit(3));
INSERT INTO items (embedding) VALUES ('000'), ('111');

Get the nearest neighbors by Hamming distance

SELECT * FROM items ORDER BY embedding <~> '101' LIMIT 5;

Also supports Jaccard distance (<%>)

Use expression indexing for binary quantization

CREATE INDEX ON items USING hnsw ((binary_quantize(embedding)::bit(3)) bit_hamming_ops);

Get the nearest neighbors by Hamming distance

SELECT * FROM items ORDER BY binary_quantize(embedding)::bit(3) <~> binary_quantize('[1,-2,3]') LIMIT 5;

Re-rank by the original vectors for better recall

SELECT * FROM (
    SELECT * FROM items ORDER BY binary_quantize(embedding)::bit(3) <~> binary_quantize('[1,-2,3]') LIMIT 20
) ORDER BY embedding <=> '[1,-2,3]' LIMIT 5;

Use the sparsevec type to store sparse vectors

CREATE TABLE items (id bigserial PRIMARY KEY, embedding sparsevec(5));

Insert vectors

INSERT INTO items (embedding) VALUES ('{1:1,3:2,5:3}/5'), ('{1:4,3:5,5:6}/5');

The format is {index1:value1,index2:value2}/dimensions and indices start at 1 like SQL arrays

Get the nearest neighbors by L2 distance

SELECT * FROM items ORDER BY embedding <-> '{1:3,3:1,5:2}/5' LIMIT 5;

Use together with Postgres full-text search for hybrid search.

SELECT id, content FROM items, plainto_tsquery('hello search') query
    WHERE textsearch @@ query ORDER BY ts_rank_cd(textsearch, query) DESC LIMIT 5;

You can use Reciprocal Rank Fusion or a cross-encoder to combine results.

Use expression indexing to index subvectors

CREATE INDEX ON items USING hnsw ((subvector(embedding, 1, 3)::vector(3)) vector_cosine_ops);

Get the nearest neighbors by cosine distance

SELECT * FROM items ORDER BY subvector(embedding, 1, 3)::vector(3) <=> subvector('[1,2,3,4,5]'::vector, 1, 3) LIMIT 5;

Re-rank by the full vectors for better recall

SELECT * FROM (
    SELECT * FROM items ORDER BY subvector(embedding, 1, 3)::vector(3) <=> subvector('[1,2,3,4,5]'::vector, 1, 3) LIMIT 20
) ORDER BY embedding <=> '[1,2,3,4,5]' LIMIT 5;

Monitor performance with pg_stat_statements (be sure to add it to shared_preload_libraries).

CREATE EXTENSION pg_stat_statements;

Get the most time-consuming queries with:

SELECT query, calls, ROUND((total_plan_time + total_exec_time) / calls) AS avg_time_ms,
    ROUND((total_plan_time + total_exec_time) / 60000) AS total_time_min
    FROM pg_stat_statements ORDER BY total_plan_time + total_exec_time DESC LIMIT 20;

Note: Replace total_plan_time + total_exec_time with total_time for Postgres < 13

Monitor recall by comparing results from approximate search with exact search.

BEGIN;
SET LOCAL enable_indexscan = off; -- use exact search
SELECT ...
COMMIT;

Scale pgvector the same way you scale Postgres.

Scale vertically by increasing memory, CPU, and storage on a single instance. Use existing tools to tune parameters and monitor performance.

Scale horizontally with replicas, or use Citus or another approach for sharding (example).

Use pgvector from any language with a Postgres client. You can even generate and store vectors in one language and query them in another.

pgvector is available on these providers.

Install the latest version (use the same method as the original installation). Then in each database you want to upgrade, run:

ALTER EXTENSION vector UPDATE;

You can check the version in the current database with:

SELECT extversion FROM pg_extension WHERE extname = 'vector';

Thanks to:

View the changelog

Everyone is encouraged to help improve this project. Here are a few ways you can help:

To get started with development:

git clone https://github.com/pgvector/pgvector.git
cd pgvector
make
make install

To run all tests:

make installcheck        # regression tests
make prove_installcheck  # TAP tests

To run single tests:

make installcheck REGRESS=functions                            # regression test
make prove_installcheck PROVE_TESTS=test/t/001_ivfflat_wal.pl  # TAP test

To enable assertions:

make clean && PG_CFLAGS="-DUSE_ASSERT_CHECKING" make && make install

To enable benchmarking:

make clean && PG_CFLAGS="-DIVFFLAT_BENCH" make && make install

To show memory usage:

make clean && PG_CFLAGS="-DHNSW_MEMORY -DIVFFLAT_MEMORY" make && make install

To get k-means metrics:

make clean && PG_CFLAGS="-DIVFFLAT_KMEANS_DEBUG" make && make install

Resources for contributors