pgvector Cheatsheet: Tested SQL for Embeddings, Indexes & Queries

Conventions: table items, column embedding, query vector $1 (or a literal like '[1,1,0]'). Replace vector(3) with your model's dimensions, e.g. vector(1536).

Setup#

Enable the extension and check the version#

psqlpsql · PostgreSQL 18.6 · pgvector 0.8.7

demo=# CREATE EXTENSION IF NOT EXISTS vector;
CREATE EXTENSION

demo=# SELECT extversion FROM pg_extension WHERE extname = 'vector';
 extversion 
------------
 0.8.7
(1 row)

Upgrade after installing new binaries#

sql

ALTER EXTENSION vector UPDATE;

List every pgvector setting with its range#

psqlpsql · PostgreSQL 18.6 · pgvector 0.8.7

demo=# SELECT name, setting, min_val, max_val, enumvals, short_desc
demo-# FROM pg_settings WHERE name LIKE 'hnsw.%' OR name LIKE 'ivfflat.%' ORDER BY name;
           name           | setting | min_val |  max_val   |             enumvals             |                         short_desc                         
--------------------------+---------+---------+------------+----------------------------------+------------------------------------------------------------
 hnsw.ef_search           | 40      | 1       | 1000       |                                  | Sets the size of the dynamic candidate list for search
 hnsw.iterative_scan      | off     |         |            | {off,relaxed_order,strict_order} | Sets the mode for iterative scans
 hnsw.max_scan_tuples     | 20000   | 1       | 2147483647 |                                  | Sets the max number of tuples to visit for iterative scans
 hnsw.scan_mem_multiplier | 1       | 1       | 1000       |                                  | Sets the multiple of work_mem to use for iterative scans
 ivfflat.iterative_scan   | off     |         |            | {off,relaxed_order}              | Sets the mode for iterative scans
 ivfflat.max_probes       | 32768   | 1       | 32768      |                                  | Sets the max number of probes for iterative scans
 ivfflat.probes           | 1       | 1       | 32768      |                                  | Sets the number of probes
(7 rows)

Tables and data#

Create a table with a vector column#

psqlpsql · PostgreSQL 18.6 · pgvector 0.8.7

demo=# CREATE TABLE items (
demo-#   id bigserial PRIMARY KEY,
demo-#   content text,
demo-#   embedding vector(3)
demo-# );
CREATE TABLE

Insert vectors#

psqlpsql · PostgreSQL 18.6 · pgvector 0.8.7

demo=# INSERT INTO items (content, embedding) VALUES
demo-#   ('cat', '[1, 0.9, 0.1]'),
demo-#   ('kitten', '[0.95, 1, 0.2]'),
demo-#   ('car', '[0.1, 0.2, 1]'),
demo-#   ('truck', '[0.2, 0.1, 0.9]');
INSERT 0 4

Vectors are written as '[x, y, z]' text, or sent as a typed parameter by a language binding.

Add a vector column to an existing table#

psqlpsql · PostgreSQL 18.6 · pgvector 0.8.7

demo=# ALTER TABLE items ADD COLUMN embedding_small halfvec(3);
ALTER TABLE

demo=# UPDATE items SET embedding_small = embedding::halfvec(3);
UPDATE 4

Bulk-load with binary COPY (Python)#

pythonpgvector-python + psycopg 3 — the loader behind our benchmarks

import psycopg
from pgvector.psycopg import register_vector

conn = psycopg.connect("dbname=app", autocommit=True)
register_vector(conn)

with conn.cursor().copy("COPY items (id, embedding) FROM STDIN WITH (FORMAT BINARY)") as copy:
    copy.set_types(["int8", "vector"])
    for i, emb in enumerate(embeddings):      # emb: numpy float32 array
        copy.write_row([i, emb])

Binary COPY loaded our 100,000 × 1,536-dim vectors in 65 s (1,538 rows/s) on 4 vCPUs. Load first, index second — it's much faster.

Upsert an embedding#

psqlpsql · PostgreSQL 18.6 · pgvector 0.8.7

demo=# CREATE UNIQUE INDEX ON items (content);
CREATE INDEX

demo=# INSERT INTO items (content, embedding) VALUES ('cat', '[0.9, 0.9, 0.1]')
demo-# ON CONFLICT (content) DO UPDATE SET embedding = EXCLUDED.embedding
demo-# RETURNING id, content, embedding;
 id | content |   embedding   
----+---------+---------------
  1 | cat     | [0.9,0.9,0.1]
(1 row)

INSERT 0 1

Update and delete#

sql

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

Find rows with unexpected dimensions#

psqlpsql · PostgreSQL 18.6 · pgvector 0.8.7

demo=# SELECT vector_dims(embedding) AS dims, count(*) FROM items GROUP BY 1;
 dims | count 
------+-------
    3 |     4
(1 row)

Normalise vectors to unit length#

psqlpsql · PostgreSQL 18.6 · pgvector 0.8.7

demo=# UPDATE items SET embedding = l2_normalize(embedding);
UPDATE 4

demo=# SELECT content, vector_norm(embedding) FROM items;
 content |    vector_norm     
---------+--------------------
 cat     | 1.0000000084049105
 kitten  |   0.99999997681686
 car     | 0.9999999812880557
 truck   |  1.000000026670053
(4 rows)

With unit vectors, inner product and cosine rank identically, and <#> is slightly cheaper. OpenAI embeddings are already normalised.

Querying#

Nearest neighbours by L2 (Euclidean) distance#

psqlpsql · PostgreSQL 18.6 · pgvector 0.8.7

demo=# SELECT content, embedding <-> '[1, 1, 0]' AS l2_distance
demo-# FROM items ORDER BY embedding <-> '[1, 1, 0]' LIMIT 3;
 content |     l2_distance     
---------+---------------------
 cat     | 0.14142137441313676
 kitten  | 0.20615529140025762
 truck   |  1.5033296346654172
(3 rows)

Cosine distance and cosine similarity#

psqlpsql · PostgreSQL 18.6 · pgvector 0.8.7

demo=# SELECT content, embedding <=> '[1, 1, 0]' AS cosine_distance,
demo-#        1 - (embedding <=> '[1, 1, 0]') AS cosine_similarity
demo-# FROM items ORDER BY embedding <=> '[1, 1, 0]' LIMIT 3;
 content |   cosine_distance    |  cosine_similarity  
---------+----------------------+---------------------
 cat     | 0.004129399346548546 |  0.9958706006534515
 kitten  | 0.010674679844520418 |  0.9893253201554796
 truck   |   0.7712521293869518 | 0.22874787061304824
(3 rows)

Order by the distance (<=>) so an index can be used; compute similarity in the select list.

Inner product (note the sign)#

psqlpsql · PostgreSQL 18.6 · pgvector 0.8.7

demo=# SELECT content, embedding <#> '[1, 1, 0]' AS negative_inner_product,
demo-#        (embedding <#> '[1, 1, 0]') * -1 AS inner_product
demo-# FROM items ORDER BY embedding <#> '[1, 1, 0]' LIMIT 3;
 content | negative_inner_product |    inner_product    
---------+------------------------+---------------------
 kitten  |    -1.9500000476837158 |  1.9500000476837158
 cat     |     -1.899999976158142 |   1.899999976158142
 car     |   -0.30000001192092896 | 0.30000001192092896
(3 rows)

<#> returns the negative inner product so that "smaller is closer" holds for every operator. Multiply by −1 for the real value.

L1 (taxicab) distance#

psqlpsql · PostgreSQL 18.6 · pgvector 0.8.7

demo=# SELECT content, embedding <+> '[1, 1, 0]' AS l1_distance
demo-# FROM items ORDER BY embedding <+> '[1, 1, 0]' LIMIT 3;
 content |     l1_distance     
---------+---------------------
 cat     | 0.20000001788139343
 kitten  |                0.25
 truck   |  2.5999999046325684
(3 rows)

Nearest neighbours of an existing row#

psqlpsql · PostgreSQL 18.6 · pgvector 0.8.7

demo=# SELECT b.content, a.embedding <=> b.embedding AS distance
demo-# FROM items a JOIN items b ON a.id <> b.id
demo-# WHERE a.content = 'cat'
demo-# ORDER BY distance LIMIT 3;
 content |       distance       
---------+----------------------
 kitten  | 0.005452811413041769
 truck   |   0.6962623063286324
 car     |    0.725113592529271
(3 rows)

Rows within a distance#

psqlpsql · PostgreSQL 18.6 · pgvector 0.8.7

demo=# SELECT content FROM items WHERE embedding <-> '[1, 1, 0]' < 0.5;
 content 
---------
 cat
 kitten
(2 rows)

This form scans every row. To use an index, order and limit in a subquery, then filter — plans compared here.

Distance functions and vector maths#

psqlpsql · PostgreSQL 18.6 · pgvector 0.8.7

demo=# SELECT l2_distance('[1,2,3]'::vector, '[4,5,6]') AS l2,
demo-#        inner_product('[1,2,3]'::vector, '[4,5,6]') AS ip,
demo-#        cosine_distance('[1,2,3]'::vector, '[4,5,6]') AS cosine,
demo-#        l1_distance('[1,2,3]'::vector, '[4,5,6]') AS l1;
        l2         | ip |        cosine        | l1 
-------------------+----+----------------------+----
 5.196152422706632 | 32 | 0.025368153802923787 |  9
(1 row)

demo=# SELECT vector_dims('[1,2,3]'::vector) AS dims,
demo-#        vector_norm('[3,4]'::vector) AS norm,
demo-#        l2_normalize('[3,4]'::vector) AS normalized,
demo-#        subvector('[1,2,3,4,5]'::vector, 2, 3) AS sub;
 dims | norm | normalized |   sub   
------+------+------------+---------
    3 |    5 | [0.6,0.8]  | [2,3,4]
(1 row)

demo=# SELECT '[1,2]'::vector + '[3,4]' AS added, '[1,2]'::vector - '[3,4]' AS subtracted,
demo-#        '[1,2]'::vector * '[3,4]' AS multiplied, '[1,2]'::vector || '[3]' AS concatenated;
 added | subtracted | multiplied | concatenated 
-------+------------+------------+--------------
 [4,6] | [-2,-2]    | [3,8]      | [1,2,3]
(1 row)

Average and sum (centroids)#

psqlpsql · PostgreSQL 18.6 · pgvector 0.8.7

demo=# SELECT avg(embedding) AS centroid, sum(embedding) AS total FROM items;
      centroid      |           total            
--------------------+----------------------------
 [0.5625,0.55,0.55] | [2.25,2.1999998,2.1999998]
(1 row)

demo=# SELECT content ~ '^(cat|kitten)$' AS is_cat, avg(embedding) FROM items GROUP BY 1;
 is_cat |        avg        
--------+-------------------
 f      | [0.15,0.15,0.95]
 t      | [0.975,0.95,0.15]
(2 rows)

Filtered nearest-neighbour query#

sql

SET hnsw.iterative_scan = relaxed_order;   -- pgvector 0.8.0+, keeps LIMIT satisfied
SELECT id FROM items
WHERE category_id = 7
ORDER BY embedding <=> $1
LIMIT 10;

Why the setting matters, measured: filtering guide.

Hybrid search (full-text + vector, RRF)#

A complete, tested query is in the hybrid search guide.

Indexes#

Create an HNSW index#

psqlpsql · PostgreSQL 18.6 · pgvector 0.8.7

demo=# CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops);
CREATE INDEX

Pick the operator class for the distance you query:

Query operator	vector	halfvec	sparsevec	bit
`<->` L2	`vector_l2_ops`	`halfvec_l2_ops`	`sparsevec_l2_ops`	—
`<=>` cosine	`vector_cosine_ops`	`halfvec_cosine_ops`	`sparsevec_cosine_ops`	—
`<#>` inner product	`vector_ip_ops`	`halfvec_ip_ops`	`sparsevec_ip_ops`	—
`<+>` L1	`vector_l1_ops`	`halfvec_l1_ops`	`sparsevec_l1_ops`	—
`<~>` Hamming	—	—	—	`bit_hamming_ops`
`<%>` Jaccard	—	—	—	`bit_jaccard_ops` (HNSW only)

HNSW build options#

psqlpsql · PostgreSQL 18.6 · pgvector 0.8.7

demo=# DROP INDEX items_embedding_idx;
DROP INDEX

demo=# CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops) WITH (m = 24, ef_construction = 100);
CREATE INDEX

Defaults are m = 16, ef_construction = 64. What changing them costs and buys.

Raise ef_search for one query or transaction#

psqlpsql · PostgreSQL 18.6 · pgvector 0.8.7

demo=# SET hnsw.ef_search = 100;
SET

demo=# BEGIN;
BEGIN

demo=# SET LOCAL hnsw.ef_search = 200;
SET

demo=# SHOW hnsw.ef_search;
 hnsw.ef_search 
----------------
 200
(1 row)

demo=# COMMIT;
COMMIT

demo=# SHOW hnsw.ef_search;
 hnsw.ef_search 
----------------
 100
(1 row)

Create an IVFFlat index and set probes#

psqlpsql · PostgreSQL 18.6 · pgvector 0.8.7

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

demo=# SET ivfflat.probes = 10;
SET

Create IVFFlat after loading data. Choosing lists and probes.

Make index builds faster#

sql

ALTER TABLE items SET (parallel_workers = 3); -- small tables of TOASTed vectors get 0 workers without it
SET max_parallel_maintenance_workers = 3;     -- plus the leader process
SET maintenance_work_mem = '4GB';             -- fit the graph in memory
CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops);

On our 100K × 1,536 table this took the build from 3.4 min to 56 s. Why the ALTER TABLE matters.

Build without blocking writes#

psqlpsql · PostgreSQL 18.6 · pgvector 0.8.7

demo=# CREATE INDEX CONCURRENTLY items_emb_l2 ON items USING hnsw (embedding vector_l2_ops);
CREATE INDEX

Watch index build progress#

psqlpsql · PostgreSQL 18.6 · pgvector 0.8.7

demo=# SELECT phase, round(100.0 * blocks_done / nullif(blocks_total, 0), 1) AS "%",
demo-#        tuples_done, tuples_total
demo-# FROM pg_stat_progress_create_index;
     phase      |  %   | tuples_done | tuples_total 
----------------+------+-------------+--------------
 building index | 39.6 |       39634 |            0
(1 row)

Run it from a second session while CREATE INDEX is running — the view covers every database on the server. The row above is real: one of our benchmark builds was running in another database when this snippet was captured.

Check that a query uses the index#

psqlpsql · PostgreSQL 18.6 · pgvector 0.8.7

demo=# EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF, SUMMARY OFF)
demo-# SELECT id FROM items ORDER BY embedding <=> (SELECT embedding FROM items WHERE id = 1) LIMIT 10;
                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 Limit (actual rows=10.00 loops=1)
   Buffers: shared hit=1761
   InitPlan 1
     ->  Index Scan using items_pkey on items items_1 (actual rows=1.00 loops=1)
           Index Cond: (id = 1)
           Index Searches: 1
           Buffers: shared hit=6
   ->  Index Scan using items_embedding_idx on items (actual rows=10.00 loops=1)
         Order By: (embedding <=> (InitPlan 1).col1)
         Index Searches: 1
         Buffers: shared hit=1761
 Planning:
   Buffers: shared hit=1
(13 rows)

Index and table sizes#

psqlpsql · PostgreSQL 18.6 · pgvector 0.8.7

demo=# SELECT indexrelid::regclass AS index, pg_size_pretty(pg_relation_size(indexrelid)) AS size, am.amname AS type
demo-# FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid JOIN pg_am am ON am.oid = c.relam
demo-# WHERE indrelid = 'items'::regclass ORDER BY pg_relation_size(indexrelid) DESC;
        index         |  size  | type  
----------------------+--------+-------
 items_embedding_idx  | 13 MB  | hnsw
 items_pkey           | 456 kB | btree
 items_embedding_idx1 | 120 kB | hnsw
(3 rows)

demo=# SELECT pg_size_pretty(pg_table_size('items')) AS table_size, pg_size_pretty(pg_indexes_size('items')) AS indexes_size;
 table_size | indexes_size 
------------+--------------
 6192 kB    | 14 MB
(1 row)

Partial index for one hot filter value#

psqlpsql · PostgreSQL 18.6 · pgvector 0.8.7

demo=# CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops) WHERE (category = 7);
CREATE INDEX

demo=# EXPLAIN (COSTS OFF) SELECT id FROM items WHERE category = 7 ORDER BY embedding <=> (SELECT embedding FROM items WHERE id = 1) LIMIT 10;
                      QUERY PLAN                      
------------------------------------------------------
 Limit
   InitPlan 1
     ->  Index Scan using items_pkey on items items_1
           Index Cond: (id = 1)
   ->  Index Scan using items_embedding_idx1 on items
         Order By: (embedding <=> (InitPlan 1).col1)
(6 rows)

Half-precision index over a full-precision column#

psqlpsql · PostgreSQL 18.6 · pgvector 0.8.7

demo=# CREATE INDEX ON items USING hnsw ((embedding::halfvec(64)) halfvec_cosine_ops);
CREATE INDEX

demo=# EXPLAIN (COSTS OFF) SELECT id FROM items ORDER BY embedding::halfvec(64) <=> (SELECT embedding FROM items WHERE id = 1)::halfvec(64) LIMIT 10;
                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
 Limit
   InitPlan 1
     ->  Index Scan using items_pkey on items items_1
           Index Cond: (id = 1)
   ->  Index Scan using items_embedding_idx3 on items
         Order By: ((embedding)::halfvec(64) <=> ((InitPlan 1).col1)::halfvec(64))
(6 rows)

Binary quantization index with re-ranking#

psqlpsql · PostgreSQL 18.6 · pgvector 0.8.7

demo=# CREATE INDEX ON items USING hnsw ((binary_quantize(embedding)::bit(64)) bit_hamming_ops);
CREATE INDEX

demo=# EXPLAIN (COSTS OFF)
demo-# SELECT id FROM (
demo-#   SELECT id, embedding FROM items
demo-#   ORDER BY binary_quantize(embedding)::bit(64) <~> binary_quantize((SELECT embedding FROM items WHERE id = 1))
demo-#   LIMIT 40
demo-# ) candidates
demo-# ORDER BY embedding <=> (SELECT embedding FROM items WHERE id = 1)
demo-# LIMIT 10;
                                                     QUERY PLAN                                                     
--------------------------------------------------------------------------------------------------------------------
 Limit
   InitPlan 1
     ->  Index Scan using items_pkey on items items_1
           Index Cond: (id = 1)
   ->  Sort
         Sort Key: ((candidates.embedding <=> (InitPlan 1).col1))
         ->  Subquery Scan on candidates
               ->  Limit
                     InitPlan 2
                       ->  Index Scan using items_pkey on items items_2
                             Index Cond: (id = 1)
                     ->  Index Scan using items_binary_quantize_idx on items
                           Order By: ((binary_quantize(embedding))::bit(64) <~> binary_quantize((InitPlan 2).col1))
(13 rows)

Index more than 2,000 dimensions#

psqlpsql · PostgreSQL 18.6 · pgvector 0.8.7

demo=# CREATE INDEX ON big USING hnsw ((embedding::halfvec(3072)) halfvec_cosine_ops);
CREATE INDEX

Different models (dimensions) in one column#

psqlpsql · PostgreSQL 18.6 · pgvector 0.8.7

demo=# CREATE TABLE multi (id bigserial PRIMARY KEY, model_id int, embedding vector);
CREATE TABLE

demo=# INSERT INTO multi (model_id, embedding) VALUES (1, '[1,2,3]'), (2, '[1,2,3,4,5]');
INSERT 0 2

demo=# CREATE INDEX ON multi USING hnsw ((embedding::vector(3)) vector_l2_ops) WHERE (model_id = 1);
CREATE INDEX

demo=# SET enable_seqscan = off;
SET

demo=# EXPLAIN (COSTS OFF) SELECT id FROM multi WHERE model_id = 1 ORDER BY embedding::vector(3) <-> '[3,1,2]' LIMIT 5;
                            QUERY PLAN                            
------------------------------------------------------------------
 Limit
   ->  Index Scan using multi_embedding_idx on multi
         Order By: ((embedding)::vector(3) <-> '[3,1,2]'::vector)
(3 rows)

demo=# RESET enable_seqscan;
RESET

The query must repeat the cast and the WHERE condition. enable_seqscan is off only because the demo table has two rows.

Rebuild an index online#

psqlpsql · PostgreSQL 18.6 · pgvector 0.8.7

demo=# REINDEX INDEX CONCURRENTLY items_emb_l2;
REINDEX

Measure recall against exact search#

psqlpsql · PostgreSQL 18.6 · pgvector 0.8.7

demo=# BEGIN;
BEGIN

demo=# SET LOCAL enable_indexscan = off;
SET

demo=# CREATE TEMP TABLE exact AS
demo-#   SELECT id FROM items ORDER BY embedding <=> (SELECT embedding FROM items WHERE id = 42) LIMIT 10;
SELECT 10

demo=# COMMIT;
COMMIT

demo=# SELECT count(*)::float / 10 AS recall_at_10
demo-# FROM (SELECT id FROM items ORDER BY embedding <=> (SELECT embedding FROM items WHERE id = 42) LIMIT 10) approx
demo-# JOIN exact USING (id);
 recall_at_10 
--------------
          0.4
(1 row)

Yes, 0.6: this demo table holds uniformly random 64-dim vectors, the hardest possible case for an approximate index, so it's a good illustration of why you measure. Real embeddings cluster and score far higher — 0.9+ at default settings in our benchmarks, which run this comparison over 1,000 held-out queries. Repeat it for a few hundred of your real queries and average.

Filtering#

Iterative index scans#

psqlpsql · PostgreSQL 18.6 · pgvector 0.8.7

demo=# RESET hnsw.ef_search;
RESET

demo=# SET enable_seqscan = off;
SET

demo=# SET enable_bitmapscan = off;
SET

demo=# SELECT count(*) FROM (SELECT id FROM items WHERE category = 7 ORDER BY embedding <=> (SELECT embedding FROM items WHERE id = 1) LIMIT 10) t;
 count 
-------
     0
(1 row)

demo=# SET hnsw.iterative_scan = relaxed_order;
SET

demo=# SELECT count(*) FROM (SELECT id FROM items WHERE category = 7 ORDER BY embedding <=> (SELECT embedding FROM items WHERE id = 1) LIMIT 10) t;
 count 
-------
    10
(1 row)

demo=# RESET hnsw.iterative_scan;
RESET

demo=# RESET enable_bitmapscan;
RESET

demo=# RESET enable_seqscan;
RESET

Exact ordering with relaxed iterative scans#

psqlpsql · PostgreSQL 18.6 · pgvector 0.8.7

demo=# SET hnsw.iterative_scan = relaxed_order;
SET

demo=# WITH relaxed AS MATERIALIZED (
demo-#   SELECT id, embedding <=> (SELECT embedding FROM items WHERE id = 1) AS distance
demo-#   FROM items WHERE category = 7
demo-#   ORDER BY distance LIMIT 10
demo-# )
demo-# SELECT id, round(distance::numeric, 4) AS distance FROM relaxed ORDER BY distance;
  id   | distance 
-------+----------
  9007 |   0.1632
  3407 |   0.1720
 10107 |   0.1762
  1907 |   0.1766
  6707 |   0.1779
  9807 |   0.1898
 15307 |   0.1943
  1007 |   0.1947
 17207 |   0.1963
 17807 |   0.1980
(10 rows)

demo=# RESET hnsw.iterative_scan;
RESET

Other vector types#

halfvec (2 bytes per dimension)#

psqlpsql · PostgreSQL 18.6 · pgvector 0.8.7

demo=# SELECT '[1.0001, 2.5, 65504]'::halfvec AS h, '[1.0001]'::vector::halfvec AS rounded;
       h       | rounded 
---------------+---------
 [1,2.5,65504] | [1]
(1 row)

bit: Hamming, Jaccard, binary_quantize#

psqlpsql · PostgreSQL 18.6 · pgvector 0.8.7

demo=# SELECT '1010'::bit(4) <~> '1100' AS hamming, '1010'::bit(4) <%> '1100' AS jaccard;
 hamming |      jaccard       
---------+--------------------
       2 | 0.6666666666666667
(1 row)

demo=# SELECT binary_quantize('[0.5, -0.2, 0.9, -1.1]'::vector) AS bq;
  bq  
------
 1010
(1 row)

sparsevec#

psqlpsql · PostgreSQL 18.6 · pgvector 0.8.7

demo=# SELECT '{1:1.5,3:2,5:-1}/5'::sparsevec AS sv, '{1:1.5,3:2,5:-1}/5'::sparsevec::vector AS dense;
         sv         |     dense      
--------------------+----------------
 {1:1.5,3:2,5:-1}/5 | [1.5,0,2,0,-1]
(1 row)

demo=# SELECT '[0,0,3,0,1]'::vector::sparsevec AS from_dense;
 from_dense  
-------------
 {3:3,5:1}/5
(1 row)

demo=# SELECT '{1:1,3:2}/5'::sparsevec <-> '{2:1,3:2}/5'::sparsevec AS l2;
         l2         
--------------------
 1.4142135623730951
(1 row)

Format is {index:value,…}/dimensions with 1-based indexes. Indexable up to 1,000 non-zero elements.

Casts between arrays and vectors#

psqlpsql · PostgreSQL 18.6 · pgvector 0.8.7

demo=# SELECT ARRAY[1, 2, 3]::vector AS from_int_array,
demo-#        '[1,2,3]'::vector::real[] AS to_real_array,
demo-#        ARRAY[1.5, 2.5]::float8[]::vector AS from_float8;
 from_int_array | to_real_array | from_float8 
----------------+---------------+-------------
 [1,2,3]        | {1,2,3}       | [1.5,2.5]
(1 row)

pgvector SQL cheatsheet

Setup#

Enable the extension and check the version#

Upgrade after installing new binaries#

List every pgvector setting with its range#

Tables and data#

Create a table with a vector column#

Insert vectors#

Add a vector column to an existing table#

Bulk-load with binary COPY (Python)#

Upsert an embedding#

Update and delete#

Find rows with unexpected dimensions#

Normalise vectors to unit length#

Querying#

Nearest neighbours by L2 (Euclidean) distance#

Cosine distance and cosine similarity#

Inner product (note the sign)#

L1 (taxicab) distance#

Nearest neighbours of an existing row#

Rows within a distance#

Distance functions and vector maths#

Average and sum (centroids)#

Filtered nearest-neighbour query#

Hybrid search (full-text + vector, RRF)#

Indexes#

Create an HNSW index#

HNSW build options#

Raise ef_search for one query or transaction#

Create an IVFFlat index and set probes#

Make index builds faster#

Build without blocking writes#

Watch index build progress#

Check that a query uses the index#

Index and table sizes#

Partial index for one hot filter value#

Half-precision index over a full-precision column#

Binary quantization index with re-ranking#

Index more than 2,000 dimensions#

Different models (dimensions) in one column#

Rebuild an index online#

Measure recall against exact search#

Filtering#

Iterative index scans#

Exact ordering with relaxed iterative scans#

Other vector types#

halfvec (2 bytes per dimension)#

bit: Hamming, Jaccard, binary_quantize#

sparsevec#

Casts between arrays and vectors#