How do I do vector search in PostgreSQL with pgvector?

To do vector search in PostgreSQL with pgvector, you generally:

1. install the extension
2. store embeddings in a vector column
3. index that column
4. query by distance/similarity

1) Install pgvector

If it’s not already available:

CREATE EXTENSION IF NOT EXISTS vector;

---

2) Create a table with a vector column

Pick the embedding dimension that matches your model.

CREATE TABLE documents (
  id bigserial PRIMARY KEY,
  content text,
  embedding vector(1536)
);

---

3) Insert embeddings

Store your generated embedding as a vector:

INSERT INTO documents (content, embedding)
VALUES (
  'PostgreSQL with pgvector is great for semantic search',
  '[0.12, 0.34, ...]'::vector
);

---

4) Create an index for fast search

For approximate nearest-neighbor search, use one of these indexes:

HNSW

Good default for many workloads.

CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops);

IVFFLAT

Also common, especially for larger datasets.

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

For IVFFLAT, you usually set:

SET ivfflat.probes = 10;

before querying.

---

5) Query for nearest neighbors

Cosine distance

Return the most similar rows:

SELECT id, content,
       1 - (embedding <=> '[0.12, 0.34, ...]'::vector) AS similarity
FROM documents
ORDER BY embedding <=> '[0.12, 0.34, ...]'::vector
LIMIT 5;

• <=> = cosine distance
• smaller distance = more similar

L2 distance

SELECT id, content
FROM documents
ORDER BY embedding <-> '[0.12, 0.34, ...]'::vector
LIMIT 5;

• <-> = Euclidean distance

Inner product

SELECT id, content
FROM documents
ORDER BY embedding <#> '[0.12, 0.34, ...]'::vector
LIMIT 5;

• <#> = negative inner product
• useful when your model uses dot-product similarity

---

6) Filter + vector search

You can combine metadata filtering with vector search:

SELECT id, content
FROM documents
WHERE category = 'tech'
ORDER BY embedding <=> '[0.12, 0.34, ...]'::vector
LIMIT 10;

---

7) Example end-to-end

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE docs (
  id bigserial PRIMARY KEY,
  title text,
  body text,
  embedding vector(768)
);

CREATE INDEX ON docs
USING hnsw (embedding vector_cosine_ops);

-- query
SELECT id, title
FROM docs
ORDER BY embedding <=> $1
LIMIT 5;

Where $1 is your query embedding passed from your application.

---

Important notes

• Your query vector must have the same dimension as the stored vectors.
• Choose the operator that matches how your embeddings were trained:
  • cosine: common default
  • L2: sometimes used for raw embeddings
  • inner product: often for transformer embeddings with dot-product ranking
• For exact search on small datasets, you can skip the index and use ORDER BY ... LIMIT.

If you want, I can also show:

• a Python example using psycopg
• a Node.js example
• how to do hybrid search with text + vectors
• how to choose between HNSW vs IVFFLAT