How to set up vector similarity search with pgvector

What and why

pgvector adds a vector data type and similarity operators to PostgreSQL, so you can store embeddings next to your relational data and run nearest-neighbor search. This powers semantic search and retrieval-augmented generation without a separate vector database. This tutorial stores embeddings and queries them.

Prerequisites

• A PostgreSQL instance where you can install extensions.
• A way to produce embeddings (an API or a local model) with a fixed dimension.
• A client to run SQL.

Steps

1. Install the pgvector extension

CREATE EXTENSION IF NOT EXISTS vector;

On self-managed PostgreSQL, install the extension package first; most managed providers ship it.

2. Create a vector column

Match the dimension to your embedding model (e.g. 1536):

CREATE TABLE documents (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  content TEXT,
  embedding vector(1536)
);

3. Insert embeddings

Generate an embedding for each document and insert it:

vec = embed(text)  # list of 1536 floats
cur.execute("INSERT INTO documents (content, embedding) VALUES (%s, %s)", (text, vec))

4. Build an ANN index

An approximate nearest neighbor index makes search fast at scale. HNSW offers strong recall:

CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

Use the operator class that matches your distance metric (cosine here).

5. Query nearest neighbors

Find the closest documents to a query embedding with the <=> cosine-distance operator:

SELECT id, content
FROM documents
ORDER BY embedding <=> %s
LIMIT 5;

Pass the query embedding as the parameter.

6. Tune recall and speed

Adjust hnsw.ef_search at query time to trade speed for recall:

SET hnsw.ef_search = 100;

Higher values find better matches but cost more time. Build-time m and ef_construction affect index quality.

Verification

Insert several documents, then query with an embedding of a known phrase; the most relevant documents should rank first. EXPLAIN on the query should show the HNSW index in use rather than a sequential scan.

Next Steps

Combine vector search with SQL filters for hybrid queries, normalize vectors if your model expects it, and batch-insert embeddings for large corpora to keep loads fast.