Open-source vector similarity search for Postgres

Store your vectors with the rest of your data. Supports:

• exact and approximate nearest neighbor search
• single-precision, half-precision, binary, and sparse vectors
• L2 distance, inner product, cosine distance, L1 distance, Hamming distance, and Jaccard distance
• any language with a Postgres client

Plus ACID compliance, point-in-time recovery, JOINs, and all of the other great features of Postgres

Compile and install the extension (supports Postgres 13+)

cd /tmp
git clone --branch v0.8.1 https://github.com/pgvector/pgvector.git
cd pgvector
make
make install # may need sudo

See the installation notes if you run into issues

You can also install it with Docker, Homebrew, PGXN, APT, Yum, pkg, or conda-forge, and it comes preinstalled with Postgres.app and many hosted providers. There are also instructions for GitHub Actions.

Ensure C++ support in Visual Studio is installed and run x64 Native Tools Command Prompt for VS [version] as administrator. Then use nmake to build:

set "PGROOT=C:\Program Files\PostgreSQL\17"
cd %TEMP%
git clone --branch v0.8.1 https://github.com/pgvector/pgvector.git
cd pgvector
nmake /F Makefile.win
nmake /F Makefile.win install

See the installation notes if you run into issues

You can also install it with Docker or conda-forge.

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:

• <-> - 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

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;

Average vectors

SELECT AVG(embedding) FROM items;

Average groups of vectors

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:

• 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

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.

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;

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)

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

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:

1. Create the index after the table has some data
2. Choose an appropriate number of lists - a good place to start is rows / 1000 for up to 1M rows and sqrt(rows) for over 1M rows
3. When querying, specify an appropriate number of probes (higher is better for recall, lower is better for speed) - a good place to start is sqrt(lists)

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:

• vector - up to 2,000 dimensions
• halfvec - up to 4,000 dimensions
• bit - up to 64,000 dimensions

Specify the number of probes (1 by default)

SET ivfflat.probes = 10;

A higher value provides better recall at the cost of speed, and it can be set to the number of lists for exact nearest neighbor search (at which point the planner won’t use the index)

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

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

Speed up index creation on large tables 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 also need to increase max_parallel_workers (8 by default)

Check indexing progress

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

The phases for IVFFlat are:

1. initializing
2. performing k-means
3. assigning tuples
4. loading tuples

Note: % is only populated during the loading tuples phase

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

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 + 0;

Note: + 0 is needed for Postgres 17+

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

Since scanning a large portion of an approximate index is expensive, there are options to control when a scan ends.

Specify the max number of tuples to visit (20,000 by default)

SET hnsw.max_scan_tuples = 20000;

Note: This is approximate and does not affect the initial scan

Specify the max amount of memory to use, as a multiple of work_mem (1 by default)

SET hnsw.scan_mem_multiplier = 2;

Note: Try increasing this if increasing hnsw.max_scan_tuples does not improve recall

Specify the max number of probes

SET ivfflat.max_probes = 100;

Note: If this is lower than ivfflat.probes, ivfflat.probes will be used

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;