pgvector Enable the extension
EnableCREATE EXTENSION vector;
Quick reference
pgvector cheatsheet
Every common task as a copy-paste SQL snippet. Each card links to the full explanation in the documentation. Filter by category or search below.
Check the installed version
EnableSELECT extversion FROM pg_extension WHERE extname = 'vector';Upgrade the extension
EnableALTER EXTENSION vector UPDATE;Create a table with a vector column
StoreCREATE TABLE items (id bigserial PRIMARY KEY, embedding vector(3));Add a vector column to a table
StoreALTER TABLE items ADD COLUMN embedding vector(3);Insert vectors
StoreINSERT INTO items (embedding) VALUES ('[1,2,3]'), ('[4,5,6]');Bulk load with COPY
StoreCOPY items (embedding) FROM STDIN WITH (FORMAT BINARY);Upsert a vector
StoreINSERT INTO items (id, embedding) VALUES (1, '[1,2,3]')
ON CONFLICT (id) DO UPDATE SET embedding = EXCLUDED.embedding;Update and delete vectors
StoreUPDATE items SET embedding = '[1,2,3]' WHERE id = 1;
DELETE FROM items WHERE id = 1;Nearest neighbors (L2)
QuerySELECT * FROM items ORDER BY embedding <-> '[3,1,2]' LIMIT 5;Nearest neighbors to a row
QuerySELECT * FROM items WHERE id != 1
ORDER BY embedding <-> (SELECT embedding FROM items WHERE id = 1) LIMIT 5;Rows within a distance
QuerySELECT * FROM items WHERE embedding <-> '[3,1,2]' < 5;Compute a distance value
QuerySELECT embedding <-> '[3,1,2]' AS distance FROM items;Inner product (returns negative)
QuerySELECT (embedding <#> '[3,1,2]') * -1 AS inner_product FROM items;Cosine similarity
QuerySELECT 1 - (embedding <=> '[3,1,2]') AS cosine_similarity FROM items;L1 (taxicab) distance
QuerySELECT embedding <+> '[3,1,2]' AS l1_distance FROM items;Hamming distance (bit)
QuerySELECT * FROM items ORDER BY embedding <~> '101' LIMIT 5;Jaccard distance (bit)
QuerySELECT * FROM items ORDER BY embedding <%> '101' LIMIT 5;Average and sum vectors
QuerySELECT AVG(embedding) FROM items;
SELECT category_id, AVG(embedding) FROM items GROUP BY category_id;Filtered nearest-neighbor query
QuerySELECT * FROM items WHERE category_id = 123
ORDER BY embedding <-> '[3,1,2]' LIMIT 5;Hybrid search with full-text search
QuerySELECT id, content FROM items, plainto_tsquery('hello search') query
WHERE textsearch @@ query ORDER BY ts_rank_cd(textsearch, query) DESC LIMIT 5;Create an HNSW index (L2)
IndexCREATE INDEX ON items USING hnsw (embedding vector_l2_ops);Create an HNSW index (cosine / inner product)
IndexCREATE INDEX ON items USING hnsw (embedding vector_cosine_ops);
CREATE INDEX ON items USING hnsw (embedding vector_ip_ops);HNSW build options
IndexCREATE INDEX ON items USING hnsw (embedding vector_l2_ops)
WITH (m = 16, ef_construction = 64);HNSW query tuning (ef_search)
IndexSET hnsw.ef_search = 100;
BEGIN;
SET LOCAL hnsw.ef_search = 100;
SELECT * FROM items ORDER BY embedding <-> '[3,1,2]' LIMIT 5;
COMMIT;Create an IVFFlat index
IndexCREATE INDEX ON items USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);IVFFlat query tuning (probes)
IndexSET ivfflat.probes = 10;Enable iterative index scans
FilteringSET hnsw.iterative_scan = strict_order;
SET hnsw.iterative_scan = relaxed_order;
SET ivfflat.iterative_scan = relaxed_order;
SET hnsw.max_scan_tuples = 20000;
SET hnsw.scan_mem_multiplier = 2;
SET ivfflat.max_probes = 100;Partial index
FilteringCREATE INDEX ON items USING hnsw (embedding vector_l2_ops) WHERE (category_id = 123);Partitioning
FilteringCREATE TABLE items (embedding vector(3), category_id int) PARTITION BY LIST(category_id);Strict ordering with a materialized CTE
FilteringWITH 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; -- + 0 needed for Postgres 17+Half-precision vectors (halfvec)
TypesCREATE TABLE items (id bigserial PRIMARY KEY, embedding halfvec(3));
SELECT * FROM items ORDER BY embedding <-> '[1,2,3]' LIMIT 5;Half-precision index over a full-precision column
TypesCREATE INDEX ON items USING hnsw ((embedding::halfvec(3)) halfvec_l2_ops);
SELECT * FROM items ORDER BY embedding::halfvec(3) <-> '[1,2,3]' LIMIT 5;Binary vectors (bit)
TypesCREATE TABLE items (id bigserial PRIMARY KEY, embedding bit(3));
INSERT INTO items (embedding) VALUES ('000'), ('111');Binary quantization with re-ranking
TypesCREATE INDEX ON items USING hnsw ((binary_quantize(embedding)::bit(3)) bit_hamming_ops);
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;Sparse vectors (sparsevec)
TypesCREATE TABLE items (id bigserial PRIMARY KEY, embedding sparsevec(5));
INSERT INTO items (embedding) VALUES ('{1:1,3:2,5:3}/5'), ('{1:4,3:5,5:6}/5');
SELECT * FROM items ORDER BY embedding <-> '{1:3,3:1,5:2}/5' LIMIT 5;Subvector index
TypesCREATE INDEX ON items USING hnsw ((subvector(embedding, 1, 3)::vector(3)) vector_cosine_ops);Debug a query with EXPLAIN
PerformanceEXPLAIN (ANALYZE, BUFFERS) SELECT * FROM items ORDER BY embedding <-> '[3,1,2]' LIMIT 5;Raise memory for index builds
PerformanceSET maintenance_work_mem = '8GB';Parallel index build workers
PerformanceSET max_parallel_maintenance_workers = 7; -- plus leaderCreate an index without blocking writes
PerformanceCREATE INDEX CONCURRENTLY ON items USING hnsw (embedding vector_l2_ops);Speed up vacuum with a reindex
PerformanceREINDEX INDEX CONCURRENTLY index_name;
VACUUM table_name;Speed up exact search
PerformanceSET max_parallel_workers_per_gather = 4;Measure recall against exact search
PerformanceBEGIN;
SET LOCAL enable_indexscan = off; -- force exact search
SELECT * FROM items ORDER BY embedding <-> '[3,1,2]' LIMIT 5;
COMMIT;Check index size
PerformanceSELECT pg_size_pretty(pg_relation_size('index_name'));No snippets match your search. Try a different keyword or category.
Need values for your own table? The index planner computes the right index, tuning values, and storage estimate. For explanations behind each snippet, read the documentation. Stuck on an error? See the troubleshooting guide.