pgvector — Vector Search Inside PostgreSQL — Semantic Search & Embeddings

pgvector — Vector Search Inside PostgreSQL

What you will learn

Install pgvector and add a vector column to an existing table
Create an HNSW index and run cosine similarity queries
Combine vector search with metadata filters

Why pgvector?

If you already have PostgreSQL, pgvector lets you add vector search without running a separate service. You get ACID guarantees, joins, full-text search, and vector search in one database — and you already know how to back it up and monitor it.

Setup

# Docker (easiest for local dev)
docker run -d --name pgvector -e POSTGRES_PASSWORD=pass -p 5432:5432 pgvector/pgvector:pg16

# Or enable the extension on an existing PG instance (needs pg16+)
# psql -c "CREATE EXTENSION IF NOT EXISTS vector;"

pip install psycopg2-binary sentence-transformers

Creating a table with a vector column

import psycopg2
from sentence_transformers import SentenceTransformer

conn = psycopg2.connect("postgresql://postgres:pass@localhost:5432/postgres")
cur = conn.cursor()

# Enable the extension
cur.execute("CREATE EXTENSION IF NOT EXISTS vector")

# Create a table with a 384-dim vector column
cur.execute("""
    CREATE TABLE IF NOT EXISTS documents (
        id SERIAL PRIMARY KEY,
        title TEXT NOT NULL,
        body TEXT NOT NULL,
        category TEXT,
        embedding vector(384)
    )
""")
conn.commit()

Inserting documents with embeddings

model = SentenceTransformer("BAAI/bge-small-en-v1.5")

docs = [
    ("MCP Authentication", "MCP servers can implement OAuth 2.0.", "security"),
    ("MCP Tools vs Resources", "Tools are callable; resources are readable URIs.", "concepts"),
    ("LangGraph StateGraph", "LangGraph builds agent flows as explicit graphs.", "agents"),
    ("Vector Databases", "HNSW enables sub-millisecond similarity search.", "infrastructure"),
]

for title, body, category in docs:
    embedding = model.encode(body, normalize_embeddings=True).tolist()
    cur.execute(
        "INSERT INTO documents (title, body, category, embedding) VALUES (%s, %s, %s, %s)",
        (title, body, category, embedding)
    )
conn.commit()

Creating an HNSW index

Without an index, pgvector performs exact (brute-force) search. The HNSW index makes it approximate and fast:

-- Cosine distance index (use for normalised embeddings)
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

For small tables (under 100k rows), the index is not needed — exact search is fast enough. Add it when you start seeing slow queries.

Running a similarity query

def search(query: str, top_k: int = 3, category: str | None = None) -> list[dict]:
    q_embedding = model.encode(query, normalize_embeddings=True).tolist()

    if category:
        cur.execute("""
            SELECT title, body, category,
                   1 - (embedding <=> %s::vector) AS score
            FROM documents
            WHERE category = %s
            ORDER BY embedding <=> %s::vector
            LIMIT %s
        """, (q_embedding, category, q_embedding, top_k))
    else:
        cur.execute("""
            SELECT title, body, category,
                   1 - (embedding <=> %s::vector) AS score
            FROM documents
            ORDER BY embedding <=> %s::vector
            LIMIT %s
        """, (q_embedding, q_embedding, top_k))

    return [{"title": r[0], "body": r[1], "cat": r[2], "score": r[3]} for r in cur.fetchall()]


results = search("how does authentication work in MCP?")
for r in results:
    print(f"[{r['score']:.3f}] {r['title']}")

Distance operators in pgvector

Operator	Distance type	When to use
`<=>`	Cosine distance	Default for normalised embeddings
`<->`	Euclidean (L2)	When magnitude matters
`<#>`	Negative inner product	Equivalent to cosine for unit vectors

Use <=> (cosine) unless you have a specific reason not to.

Chapter summary

pgvector adds a vector(N) column type and ANN index to PostgreSQL
<=> is the cosine distance operator — ORDER BY ascending returns closest first
HNSW index makes search fast at scale; exact search is fine for small tables
Combine with WHERE clauses to filter by metadata before or after vector search

Check your understanding

What does 1 - (embedding <=> query_vec) return, and why do we subtract from 1?
When should you add an HNSW index and when is it unnecessary?
How do you filter results to a specific category while still using vector search?